Die Funktionen FIRST_VALUE und LAST_VALUE liefern Werte aus der ersten und der letzten Zeile eines Fensters. Dies ermöglicht es einer Abfrage, gleichzeitig auf Werte aus mehreren Zeilen zuzugreifen, ohne dass ein Selbst-Join erforderlich ist.

Diese beiden Funktionen unterscheiden sich von den anderen Fenster-Aggregatfunktionen, da sie mit einem Fenster benutzt werden müssen. Im Gegensatz zu den anderen Fenster-Aggregatfunktionen erlauben diese Funktionen die Klausel IGNORE NULLS. Falls IGNORE NULLS angegeben wird, wird der erste oder letzte Nicht-NULL-Wert des gewünschten Ausdrucks zurückgegeben. Ansonsten wird der erste oder der letzte Wert zurückgegeben.

Beispiel 1: Erster Eintrag in einer Gruppe

Die Funktion FIRST_VALUE kann benutzt werden, um den ersten Eintrag aus einer sortierten Gruppe von Werten abzurufen. Die folgende Abfrage liefert für jede Bestellung die Produkt-ID des ersten Elements der Bestellung. Dies ist die "ProductID" des Elements mit der niedrigsten "LineID" der jeweiligen Bestellung.

Beachten Sie, dass die Abfrage das Schlüsselwort DISTINCT benutzt, um Duplikate zu entfernen. Andernfalls würden für jedes Element in jeder Bestellung Duplikatzeilen zurückgegeben.

SELECT DISTINCT ID,
FIRST_VALUE( ProductID ) OVER ( PARTITION BY ID ORDER BY LineID )
FROM SalesOrderItems
ORDER BY ID;

Beispiel 2: Prozentsatz der maximalen Verkäufe

Die Funktion FIRST_VALUE wird häufig dazu verwendet, einen Wert in jeder Zeile mit dem Maximum oder Minimum der aktuellen Gruppe zu vergleichen. Die folgende Abfrage berechnet den Gesamtumsatz für jeden Vertriebsmitarbeiter und vergleicht dann diesen Gesamtumsatz mit dem Maximum des Gesamtumsatzes des gleichen Produkts. Das Ergebnis wird als Prozentsatz des maximalen Gesamtumsatzes angezeigt.

SELECT s.ProductID AS prod_id, o.SalesRepresentative AS sales_rep,
    SUM( s.Quantity * p.UnitPrice ) AS total_sales,
    100 * total_sales / ( FIRST_VALUE( SUM( s.Quantity * p.UnitPrice ) )
                          OVER Sales_Window ) AS total_sales_percentage
  FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
  GROUP BY o.SalesRepresentative, s.ProductID    
    WINDOW Sales_Window AS ( PARTITION BY s.ProductID 
                             ORDER BY SUM( s.Quantity * p.UnitPrice ) DESC )
  ORDER BY s.ProductID;

Beispiel 3: Das Einlesen von NULL-Werten macht Daten dichter

Die Funktionen FIRST_VALUE und LAST_VALUE sind nützlich, wenn Sie die Daten verdichtet haben und Werte anstelle von NULL eintragen müssen. Angenommen, der Vertriebsmitarbeiter mit dem höchsten Gesamtumsatz eines Tages wird als Vertriebsmitarbeiter des Tages ausgezeichnet. Die folgende Abfrage listet die Gewinner für die erste Aprilwoche 2001 auf:

SELECT v.OrderDate, v.SalesRepresentative AS rep_of_the_day
FROM ( SELECT o.SalesRepresentative, o.OrderDate,
              RANK() OVER ( PARTITION BY o.OrderDate
                            ORDER BY SUM( s.Quantity * 
                              p.UnitPrice ) DESC ) AS sales_ranking
       FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
       GROUP BY o.SalesRepresentative, o.OrderDate ) AS v
WHERE v.sales_ranking = 1
AND v.OrderDate BETWEEN '2001-04-01' AND '2001-04-07'
ORDER BY v.OrderDate;

Diese Abfrage liefert folgende Ergebnisse:

Beachten Sie, dass für die Tage ohne Umsatz keine Ergebnisse zurückgegeben werden. Die folgende Abfrage verdichtet die Daten und zeigt Datensätze für die Tage an, an denen es keine Umsätze gab. Außerdem verwendet sie die Funktion LAST_VALUE, um anstelle von NULL für "rep_of_the_day" (an Tagen ohne Gewinner) die ID des letzten Gewinners einzutragen, bis ein neuer Gewinner in den Ergebnissen erscheint.

SELECT d.dense_order_date,
                   LAST_VALUE( v.SalesRepresentative IGNORE NULLS )
                       OVER ( ORDER BY d.dense_order_date )
                       AS rep_of_the_day
FROM ( SELECT o.SalesRepresentative, o.OrderDate,
              RANK() OVER ( PARTITION BY o.OrderDate
                            ORDER BY SUM( s.Quantity *
                             p.UnitPrice ) DESC ) AS sales_ranking
       FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
       GROUP BY o.SalesRepresentative, o.OrderDate ) AS v
RIGHT OUTER JOIN ( SELECT DATEADD( day, row_num, '2001-04-01' )
                        AS dense_order_date
                   FROM sa_rowgenerator( 0, 6 )) AS d 
ON v.OrderDate = d.dense_order_date AND sales_ranking = 1
ORDER BY d.dense_order_date;

Diese Abfrage liefert folgende Ergebnisse:

Die abgeleitete Tabelle "v" aus der vorigen Abfrage wird durch einen Join mit der abgeleiteten Tabelle "d" verknüpft, die alle in Betracht kommenden Tage enthält. Dies ergibt eine Zeile für jeden gewünschten Tag, aber der Outer-Join enthält NULL in der Spalte "SalesRepresentative" für die Tage, an denen es keine Umsätze gab. Mit der Funktion LAST_VALUE wird dieses Problem gelöst, indem festgelegt wird, dass "rep_of_the_day" für die jeweilige Zeile den letzten Nicht-NULL-Wert aus "SalesRepresentative" bis zu dem Tag enthält, an dem die Spalte wieder einen Umsatzwert aufweist.

Siehe auch

• FIRST_VALUE-Funktion [Aggregat]
• LAST_VALUE-Funktion [Aggregat]
• Fensterfunktionen