MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > Microsoft Access - Code Archiv
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 09.12.2014, 17:25   #1
ebs17
MOF Guru
MOF Guru
Standard Grundlagen - SQL ist leicht (6) - Komplexe Abfragen schreiben und lesen

Richtig funktionell und spannend wird es, wenn man mehrere Ideen und somit mehrere Teillösungen zu einer Gesamtlösung kombiniert oder gar zu einer Gesamtanweisung (alles im Überblick) vereint. Eigentlich gilt es ja, auf komplexere Aufgabenstellungen reagieren zu können.

Schauen wir uns das an einer konkreten Aufgabe an, deren Lösung dann in einer komplex(er)en Abfrage endet: Es gibt eine Firma, die über mehrere Filialen Kunden betreut und Waren verkauft. Die erzielten täglichen Umsätze werden in einer Zwischentabelle einer m:n-Beziehung erfasst, die Kunden sowie die Filialen bilden die zugehörigen Primärtabellen. Aufgabe: Die TOP-Kunden sollen für besondere Kundenmaßnahmen ermittelt werden. Ein TOP-Kunde erbringt per selbst gewählter Definition mehr als 20 Prozent des Umsatzes einer Filiale im Betrachtungszeitraum.

Tabellenstruktur der Zwischentabelle:
ID (Autowert, PK), Fil_ID (Long, FK), Kd_ID (Long, FK), Umsatz (Long), Umsatztag (Date)

Wie man sehen kann, sind alle notwendigen Daten für die eigentliche Berechnung in dieser Tabelle vorhanden, ein Kunde wird durch den vorhandenen Schlüssel ausreichend definiert. Wir werfen also nicht alle vorhandenen Tabellen in den Abfrageeditor, um dann etwas zusammen zu klicken, sondern wir fangen gleich an zu rechnen. Kundennamen können am Ende hinzuverknüpft werden, falls die jemand dann braucht.
Wir ermitteln erst einmal die Umsatzsummen pro Filiale und pro Kunde (ein Kunde kauft hier nur in einer Filiale ein).
Code:

qryKdUmsatz

SELECT Kd_ID, Fil_ID, Sum(Umsatz) AS KundenUmsatz
FROM tblUmsatz
GROUP BY Kd_ID, Fil_ID;


qryFilUmsatz

SELECT Fil_ID, Sum(Umsatz) AS FilialUmsatz
FROM tblUmsatz
GROUP BY Fil_ID;
Nun können wir die Teillösungen über den gemeinsamen Schlüssel Fil_ID zusammen setzen und dann die Umsätze ins Verhältnis bringen. Das Ergebnis sieht dann so aus:
Code:

qryTOPKunden

SELECT qryKdUmsatz.Kd_ID, qryKdUmsatz.Fil_ID
FROM qryFilUmsatz INNER JOIN qryKdUmsatz 
ON qryFilUmsatz.Fil_ID = qryKdUmsatz.Fil_ID
WHERE qryKdUmsatz.KundenUmsatz / qryFilUmsatz.FilialUmsatz > 0.2;
Jetzt wollen wir noch zwei Änderungen vornehmen, die anfangs nur wie Kosmetik wirken, hinten hinaus aber Wesentliches für eine Funktionalität leisten können.

1) Einsatz von Tabellenaliasen: Hiermit kann man die Anweisungen verkürzen, was eine Übersichtlichkeit verbessert - man denke nur mal an eine Übernahme der Anweisung in VBA-Codes. Ein Tabellenalias erscheint nirgendwo nach außen, daher darf er sehr kurz sein, trotzdem aber "sprechend" wie F für qryFilUmsatz (Filialumsätze).

Nebenbei: Dass hier permanent auf überflüssige Tabellenbezeichner und Klammern, die der Abfragegenerator reichlich erzeugt, verzichtet wird, dürfte schon aufgefallen sein.

Code:

SELECT K.Kd_ID, K.Fil_ID
FROM qryFilUmsatz AS F INNER JOIN qryKdUmsatz AS K 
ON F.Fil_ID = K.Fil_ID
WHERE K.KundenUmsatz / F.FilialUmsatz > 0.2;
Dieser Schritt erleichtert bzw. ermöglicht erst den zweiten Schritt

2) Zusammenfassung der drei Abfragen zu einer zusammengefassten Anweisung
Nutzbare Vorteile dadurch:

- Man gewinnt in der Gesamtanweisung den besseren Überblick über die verwendeten Maßnahmen. So würde man z.B. einfacher erkennen können, dass Sortierungen in den Unterabfragen funktionell und in der Außenwirkung überflüssig sind und somit entfernt werden könnten (um Aufwand zu senken).

- Die Gesamtanweisung bezieht sich nur auf Tabellen und deren Felder. Abfragen als abhängige Objekte muss man nicht mehr beachten.

- Möchte man Parameter in die Abfrage einbringen (z.B. Zeitraumfilterungen für die Umsatzermittlungen), gelingt das für die Gesamtanweisung sehr leicht, bei den Einzelanweisungen wird es dann eher schwieriger.

Wie geht das Zusammensetzen? Man ersetzt einfach die verbliebenen Abfragenamen durch deren SQL-Anweisungen, ohne das abschließende Semikolon sowie eingeschlossen in Klammern. Das ist einfaches Copy&Paste. Mit Formatierung sieht es dann so aus:
Code:

qryTOPKunden_A

SELECT 
    K.Kd_ID, 
    K.Fil_ID 
FROM 
    (
        SELECT 
            Fil_ID, 
            Sum(Umsatz) AS FilialUmsatz 
        FROM 
            tblUmsatz 
        GROUP BY 
            Fil_ID 
    ) AS F 
        INNER JOIN 
            (
                SELECT 
                    Kd_ID, 
                    Fil_ID, 
                    Sum(Umsatz) AS KundenUmsatz 
                FROM 
                    tblUmsatz 
                GROUP BY 
                    Kd_ID, 
                    Fil_ID 
            ) AS K 
            ON F.Fil_ID = K.Fil_ID 
WHERE 
    K.KundenUmsatz / F.FilialUmsatz > 0.2 
;
Rückblickend kann man durchaus zu der Erkenntnis kommen, dass komplexe Abfragen manchmal einfach nur länger sind, aber nicht zwingend schwierig sein müssen.

Wer wie gezeigt eine Abfrage zusammensetzen kann, tut sich dann beim Lesen auch leichter, diese (gedanklich) wieder in sinnvolle Elemente zu zerlegen und aus den Elementen und deren Zusammenwirken auf die Wirkungsweise der Abfrage zu schließen. Es empfiehlt sich, die Arbeitsweise des SQL-Interpreters zu verstehen und nachzuahmen:

a) Blick auf den FROM-Teil der Hauptabfrage. Hier prüft der Interpreter, ob die aufgeführten Tabellen auch verfügbar sind. Ohne dortige Listung der Tabellen sind deren Felder nicht verwendbar.

b) Hier im Beispiel sind diese Tabellen erst einmal Unterabfragen. Diese müssen gleichlautend überprüft werden: Tabelle da, Felder verfügbar, (Syntax in Ordnung,) Unterabfrage kann berechnet werden, das Ergebnis ist nutzbar durch die nächsthöhere Abfrageebene. Dieser Ablauf wiederholt sich dann, so oft wie benötigt.
Die Gesamtabfrage wird also von innen nach außen ausgeführt, das Lesen der SQL-Anweisung macht man dann ebenso.

Jetzt, da längere Abfragen nicht mehr so abschreckend unverständlich sind, könnte man sich verstärkt an wirklich komplexe Abfragen heranwagen, wo also einfache Teillösungen und Ansätze in verstärkter Dichte kombiniert werden.
Angehängte Dateien
Dateityp: zip SQL_Query.zip (15,7 KB, 32x aufgerufen)

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.12.2014, 20:35   #2
ebs17
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

Als Ergänzung die Ergebnisabfrage, angereichert um eine Parametrisierung. Dabei werden die zu übergebenden Parameter in diesem Fall doppelt genutzt:
Code:

PARAMETERS 
    pZeitraumBeginn Date, 
    pZeitraumEnde Date 
; 
SELECT 
    K.Kd_ID, 
    K.Fil_ID 
FROM 
    (
        SELECT 
            Fil_ID, 
            Sum(Umsatz) AS FilialUmsatz 
        FROM 
            tblUmsatz 
        WHERE 
            Umsatztag BETWEEN pZeitraumBeginn AND pZeitraumEnde 
        GROUP BY 
            Fil_ID 
    ) AS F 
        INNER JOIN 
            (
                SELECT 
                    Kd_ID, 
                    Fil_ID, 
                    Sum(Umsatz) AS KundenUmsatz 
                FROM 
                    tblUmsatz 
                WHERE 
                    Umsatztag BETWEEN pZeitraumBeginn AND pZeitraumEnde 
                GROUP BY 
                    Kd_ID, 
                    Fil_ID 
            ) AS K 
            ON F.Fil_ID = K.Fil_ID 
WHERE 
    K.KundenUmsatz / F.FilialUmsatz > 0.2 
;
Das Ganze etwas gewandelt als eine Anfügeabfrage mit einem zusätzlichen Parameter, der konstant in die Datensätze eingefügt wird:
Code:

PARAMETERS 
    pZeitraumBeginn Date, 
    pZeitraumEnde Date, 
    pkonstanterText Text(255) 
; 
INSERT INTO 
    tblXY(
        k_FilID, 
        k_KdID, 
        k_Text 
    ) 
SELECT 
    K.Kd_ID, 
    K.Fil_ID, 
    pkonstanterText 
FROM 
    (
        SELECT 
            Fil_ID, 
            Sum(Umsatz) AS FilialUmsatz 
        FROM 
            tblUmsatz 
        WHERE 
            Umsatztag BETWEEN pZeitraumBeginn AND pZeitraumEnde 
        GROUP BY 
            Fil_ID 
    ) AS F 
        INNER JOIN 
            (
                SELECT 
                    Kd_ID, 
                    Fil_ID, 
                    Sum(Umsatz) AS KundenUmsatz 
                FROM 
                    tblUmsatz 
                WHERE 
                    Umsatztag BETWEEN pZeitraumBeginn AND pZeitraumEnde 
                GROUP BY 
                    Kd_ID, 
                    Fil_ID 
            ) AS K 
            ON F.Fil_ID = K.Fil_ID 
WHERE 
    K.KundenUmsatz / F.FilialUmsatz > 0.2 
;

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}
Dein Dankeschön: DBWiki=>Spende

Geändert von ebs17 (11.12.2014 um 20:53 Uhr).
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 11:00 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.