Maximum von drei Daten ermitteln

Viele Wege führen nach Rom und hier zeige ich drei unterschiedliche Ansätze, um das Maximum von drei Daten zu ermitteln. Nicht jeder Weg erschließt sich automatisch, aber kann doch zum Ziel führen.

Aufgabenstellung

Es gibt eine Tabelle mit drei Spalten, in denen jeweils Daten abgelegt sind. Zu jeder Zeile soll das maximale Datum ermittelt werden.

CREATE TABLE #myTable(ID integer IDENTITY, date_t1 date, date_t2 date, date_t3 date);

Ansatz mit Case

Diese Variante kommt den meisten sofort in den Sinn, sie ist einfach zu verstehen und schnell zu codieren:

SELECT ID, date_t1, date_t2, date_t3,
   CASE WHEN date_t1 >= date_t2 AND date_t1 >= date_t3 THEN date_t1 
              WHEN date_t2 >= date_t3                                        THEN date_t2
              ELSE                                                                                    date_t3 
   END AS MaxDate
FROM #myTable;

Lösung mit Integer Arithmetik

Dies ist mit Sicherheit die am schwersten zu verstehende Lösung, aber besticht durch den ausgefallenen Lösungsweg. Dank an Steve Kass für diesen Ansatz und an meine Kollegin Antje, für die Erläuterung der mathematischen Zusammenhänge.

DECLARE @DateBase date = '0001-01-01';
   WITH IntegerifiedDates AS (
     SELECT
       ID, date_t1, date_t2, date_t3,
       COALESCE(DATEDIFF(d,@DateBase,date_t1),0) AS i1,
       COALESCE(DATEDIFF(d,@DateBase,date_t2),0) AS i2,
       COALESCE(DATEDIFF(d,@DateBase,date_t3),0) AS i3
     FROM #myTable
   )
     SELECT
       ID, date_t1, date_t2, date_t3,
       DATEADD(d,((i1+i2) + ABS(i1-i2) + 2*i3 +
          ABS((i1+i2)+ABS(i1-i2) - 2*i3))/4,@DateBase) AS maxDate
     FROM IntegerifiedDates;

Um die oben verwendete Formel zu verstehen, schauen wir uns zunächst mal den Weg für das Maximum von zwei Integer-Zahlen an:
Für zwei int-Zahlen x,y gilt:

2 max(x,y)= x + y + |x-y|
bzw.
max(x,y)= (x + y + |x-y|)/2

(denn für x>=y gilt x + y + |x-y|=x+y+x-y=2x und für y>x gilt x + y + |x-y|=x+y+y-x=2y … es bleibt also - gemäß Definition des Maximums - jeweils das Doppelte des Maximums übrig).

Jetzt wird es eine Spur komplexer, wenn wir die Formel für drei Werte ansetzen wollen:
Da max(a,b,c) = max(max(a,b),c) gilt, folgt
(max(a,b) + c + |max(a,b) - c|)/2 --> Formel für Maximum zweier Zahlen auf max(a,b) und c anwenden
=(((a + b + |a-b|)/2) + c + |((a + b + |a - b|)/2)-c|)/2 --> Formel für Maximum zweier Zahlen auf a und b anwenden
=( a + b + |a - b| + 2c + |a + b + |a - b|- 2c|)/4 --> Zähler und Nenner mit 2 multiplizieren und Faktor reinziehen

Lösung mit CROSS APPLY

Diese Variante ist erst möglich, wenn beim SQL Server der Kompatibilitätsgrad der Datenbank auf mindestens 90 gestellt wird. Die Syntax ist dann etwas gewöhnungsbedürftig, basiert aber darauf, dass für jede Zeile der Tabelle eine Tabellenwertfunktion aufgerufen werden soll.

SELECT
   ID, date_t1, date_t2, date_t3,
   R.max_date
FROM
   #myTable AS T
   CROSS APPLY
   (
   SELECT MAX(dt)
   FROM (VALUES (date_t1), (date_t2), (date_t3) ) AS S(dt)
   ) AS R(max_date);

Vergleich der Lösungen

Auch wenn jetzt mancher erwarten dürfte, dass die letzte (modernste) Variante auch gleichzeitig die schnellste ist, so sind die Ergebnisse nicht ganz so eindeutig.
In allen Fällen kommt es natürlich zu einem Full-Table-Scan, da wir keine Einschränkungen und Indizes angegeben haben. Beim CROSS APPLY haben wir zusätzlich einen Constant Scan und ein Stream Aggregat im Ausführungsplan. In meinen Tests mit einer Tabelle mit 200.000 Zeilen lagen alle drei SQLs nahe beieinander, die Abweichungen lagen bei ca. 100 msec, wobei immer die Variante mit der Integer-Logik am langsamsten war.

Maximum von drei Werten.txt