Falls einer Datenreihe eine schiefe Verteilung zugrunde liegt oder extreme Ausreisser vorhanden sind kann sich die Betrachtung des Medians – anstelle des auch als Durchschnitt bezeichneten, allgegenwärtigen arithmetischen Mittels – lohnen.

Während man das arithmetische Mittel mit der SQL Aggregatfunktion AVG leicht ermitteln kann, gibt es in den meisten SQL Dialekten keine Aggregatfunktion zur Bestimmung des Medians. Man muß daher „zu Fuß“ die Datenreihe wertmäßig sortieren, und dann im Fall einer ungeraden Anzahl von Werten den den genau in der Mitte liegenden Wert auswählen bzw. im Fall einer geraden Anzahl von Werten das arithmetische Mittel der beiden genau in der Mitte liegenden Werte bestimmen. Diese Aufgabenstellung kann in Teradata SQL recht elegant mit Hilfe von OLAP Funktionen gelöst werden.

Ermitteln wir zur Veranschaulichung den Median-Rechnungsbetrag jedes Kunden aus einer hypothetischen, denormalisierten Rechnungstabelle:

CREATE SET TABLE Invoice
    ,NO FALLBACK
    ,NO BEFORE JOURNAL
    ,NO AFTER JOURNAL
(
    ItemId         INTEGER       NOT NULL
    ,Quantity      DECIMAL(12,3) NOT NULL
    ,InvoiceAmount DECIMAL(11,2) NOT NULL
    ,InvoiceId     INTEGER       NOT NULL
    ,CustomerId    INTEGER       NOT NULL
)
PRIMARY INDEX(InvoiceId)
UNIQUE INDEX(InvoiceId, ItemId)
;

Verkaufte Waren werden durch die ItemId identifiziert, und für jeden Verkauf werden Verkaufsmenge und verrechneter Betrag gespeichert. Alle gemeinsam verkauften Waren erkennt man über die InvoiceId. Die Zuordnung zu einem Kunden erfolgt via CustomerId.

Fügen wir einige Rechnungen in diese Tabelle ein:

-- customer 7001
 INSERT INTO Invoice(100, 1,  30, 222, 7001)  -- invoice 222
;INSERT INTO Invoice(101, 5,  55, 222, 7001)
;INSERT INTO Invoice(102, 2,  10, 222, 7001)
;INSERT INTO Invoice(100, 2,  60, 298, 7001)  -- invoice 298
;INSERT INTO Invoice(101, 1,  11, 298, 7001) 
;INSERT INTO Invoice(188, 1, 500, 755, 7001)  -- invoice 755
-- customer 7444
;INSERT INTO Invoice(100,  7, 210, 225, 7444) -- invoice 225
;INSERT INTO Invoice(102, 50, 250, 225, 7444)
;INSERT INTO Invoice(188,0.5, 250, 679, 7444) -- invoice 679
;INSERT INTO Invoice(102, 10,  50, 679, 7444)
;

Zunächst ermitteln wir je Kunde eine sortierte Liste aller Rechnungsbeträge:

SELECT
    InvoiceId
    ,CustomerId
    ,SUM(InvoiceAmount) AS InvoiceAmount
FROM Invoice
GROUP BY InvoiceId, CustomerId
ORDER BY CustomerId, SUM(InvoiceAmount)
;

Diesem Zwischenergebnis entnehmen wir nun die relevanten Zeilen. Für Kundennummer 7001 ist dies (ungerade Anzahl an Rechnungen!) die Rechnung 222. Da für Kundennummer 7444 eine gerade Anzahl an Rechnungen vorliegt benötigen wir sowohl Rechnung 679 als auch Rechnung 225, um das arithmetische Mittel derer Rechnungsbeträge zu bilden.

Unter Zuhilfenahme ganzzahliger Division kann man die Position der relevanten Zeile(n) im sortierten Zwischenergebnis mit n Zeilen berechnen:

• m1=(n+1)/2
• m2=(n+2)/2

Sowohl die Gesamtanzahl an Rechnungen als auch Position jeder einzelnen Zeile lassen sich mit OLAP Funktionen bestimmen. Gleichzeitig eliminiert die QUALIFY Klausel alle nicht benötigten Zeilen des Zwischenergebnisses:

SELECT
    InvoiceId
    ,CustomerId
    ,SUM(InvoiceAmount) AS InvoiceAmount
FROM Invoice
GROUP BY InvoiceId, CustomerId
QUALIFY
             COUNT(*) OVER (PARTITION BY CustomerId ORDER BY SUM(InvoiceAmount) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    BETWEEN (COUNT(*) OVER (PARTITION BY CustomerId ORDER BY SUM(InvoiceAmount) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) + 1) / 2
    AND     (COUNT(*) OVER (PARTITION BY CustomerId ORDER BY SUM(InvoiceAmount) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) + 2) / 2
;

Zuletzt bilden wir noch je Kunde das arithmetische Mittel aller verbliebenen Rechnungsbeträge. Die gesamte Abfrage lautet:

SELECT
    CustomerId
    ,SUM(InvoiceAmount) / COUNT(*) AS MedianInvoiceAmount -- or simply AVG(InvoiceAmount)
FROM
(
    SELECT
        InvoiceId
        ,CustomerId
        ,SUM(InvoiceAmount) AS InvoiceAmount
    FROM Invoice
    GROUP BY InvoiceId, CustomerId
    QUALIFY
                 COUNT(*) OVER (PARTITION BY CustomerId ORDER BY SUM(InvoiceAmount) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        BETWEEN (COUNT(*) OVER (PARTITION BY CustomerId ORDER BY SUM(InvoiceAmount) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) + 1) / 2
        AND     (COUNT(*) OVER (PARTITION BY CustomerId ORDER BY SUM(InvoiceAmount) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) + 2) / 2
) t
GROUP BY 1
ORDER BY 1    
;