PDA

Vollständige Version anzeigen : SQL ist leicht (6) - Komplexe Abfragen schreiben und lesen


ebs17
09.12.2014, 16:25
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).
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:
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 (http://www.ms-office-forum.net/forum/showthread.php?t=298432): 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.

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:
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.

ebs17
11.12.2014, 19:35
Als Ergänzung die Ergebnisabfrage, angereichert um eine Parametrisierung. Dabei werden die zu übergebenden Parameter in diesem Fall doppelt genutzt:
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:
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
;