Statistikfunktionen in Abfragen verwenden

By , 18. Januar 2014

Zur statistischen Auswertung von Datenreihen können in Abfragen Funktionen eingesetzt werden wie z.B. AVG() für den aritmetischen Mittelwert oder StDev() für die Standardabweichung.
Eine nicht ganz triviale Aufgabe wäre es z.B., wenn man das gespeicherte Bestelldatum von Bestellungen in einer Tabelle hat, und jetzt den durchschnittlichen Bestellabstand in Tagen ermitteln möchte.

Manuell ist das leicht zu lösen indem man die Abstände zur nächsten Bestellung in Tagen aufsummiert und durch die Anzahl Bestellungen -1 dividiert. Beispiel:

  1. Bestellung: 12.10.2013
  2. Bestellung: 18.10.2013
  3. Bestellung: 23.10.2013
  4. Bestellung: 02.11.2013
  5. Bestellung: 14.11.2013

Die Bestellabstände sind: 6 Tage, 5 Tage, 10 Tage und 12 Tage.
In Summe 33 Tage geteilt durch 5-1 ergibt einen durchschnittlichen Bestellabstand von 8,25 Tagen.

Möchte man das nun in einer Abfrage berechnen lassen, brauchen wir also immer das Datum vom aktuellen Daten und das Datum vom folgenden Datensatz. Daher müssen wir die Bestell-Tabelle 2x in die Abfrage einbinden einmal mit Alias T1 und einmal mit Alias T2, und der Bedingung dass das Bestelldatum von T2 kleiner dem Bestelldatum von T1 sein muss. Fangen wir also erst mal damit an die Bestellabstände aufzulisten:

SELECT T1.Bestelldatum - (
       SELECT Max(T2.Bestelldatum)
       FROM  tblBestellung  AS T2
       WHERE T2.Bestelldatum < T1.Bestelldatum
       ) AS Bestellabstaende
FROM tblBestellung AS T1
Order By T1.Bestelldatum ASC;

Führt man die Abfrage aus wird man sehen dass die erste Zeile leer bleibt, und ab der zweiten Zeile dann jeweils die einzelnen Bestellabstände in Tagen stehen.
Jetzt bleibt nur noch übrig die Tage zu addieren und durch die Anzahl Bestellungen -1 zu dividieren. Und genau das erledigt die Funktion AVG() für uns. Die komplette Abfrage sieht also wie folgt aus:

SELECT Avg(T1.Bestelldatum - (
                SELECT MAX(T2.Bestelldatum)
                FROM tblBestellung  AS T2
                WHERE T2.Bestelldatum < T1.Bestelldatum
                )
            ) AS Durchschnitt
FROM tblBestellung AS T1
Order By T1.Bestelldatum ASC;

So, nun ist nur noch zu klären, warum die MAX() Funktion eingesetzt wird.
Nun, gehen wir die Abfrage Datensatz für Datensatz einmal durch:

Am Anfang ist T1.Bestelldatum ist der kleinste Wert 12.10.2013, kein Datum ist kleiner. Also kann T2.Bestelldatum nicht < T1.Bestelldatum sein. das Feld bleibt leer. 2. Durchgang: T1.Bestelldatum ist jetzt 18.10.2013. von diesem Datum muss das größte Datum welches aber kleiner als 18.10.2013 ist abgezogen werden. Und das ist der 12.10. Im 3. Durchgang ist jetzt T1.Bestelldatum 23.10.2013. Abgezogen werden muss der 18.10.2013, also wieder das größte Datum welches kleiner als T1.Bestelldatum ist. usw. Also lautet die Regel:
T1.Bestelldatum minus der größte Wert von T2.Bestelldatum welcher aber kleiner als T1.Bestelldatum ist.
Deswegen also wird die Max-Funktion eingesetzt.

Interessant an dieser Stelle ist noch, wenn man T1.Bestelldatum absteigend Sortiert, erhält man das gleiche Ergebnis.
Selbst wenn man nach T1.Bestelldatum überhaupt nicht sortieren würde, stimmt das Ergebnis.

Bis dahin
© Andreas Vogt 2014

OfficeFolders theme by Themocracy