SQL
Funktionen (Analyse)

Einführung

Syntax

1. FIRST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
2. LAST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
3. LAG (scalar_expression [, offset] [, default]) OVER ([partition_by_clause] order_by_clause)
4. LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
5. PERCENT_RANK () OVER ([partition_by_clause] order_by_clause)
6. CUME_DIST () OVER ([partition_by_clause] order_by_clause)
7. PERCENTILE_DISC (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])
8. PERCENTILE_CONT (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])

FIRST_VALUE

Mit der Funktion FIRST_VALUE bestimmen Sie den ersten Wert in einer geordneten Ergebnismenge, den Sie mit einem FIRST_VALUE identifizieren.

SELECT StateProvinceID, Name, TaxRate,
       FIRST_VALUE(StateProvinceID)
        OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

In diesem Beispiel wird die FIRST_VALUE Funktion verwendet, um die ID des Staates oder der Provinz mit dem niedrigsten Steuersatz zurückzugeben. Die OVER Klausel wird verwendet, um die Steuersätze anzuordnen, um den niedrigsten Satz zu erhalten.

LAST_VALUE

Die LAST_VALUE Funktion stellt den letzten Wert in einer geordneten Ergebnismenge bereit, den Sie mit einem LAST_VALUE angeben.

SELECT TerritoryID, StartDate, BusinessentityID,
       LAST_VALUE(BusinessentityID) 
        OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

In diesem Beispiel wird die LAST_VALUE Funktion verwendet, um den letzten Wert für jedes Rowset in den geordneten Werten zurückzugeben.

LAG und LEAD

Die LAG Funktion liefert Daten in Zeilen vor der aktuellen Zeile in derselben Ergebnismenge. In einer SELECT Anweisung können Sie beispielsweise Werte in der aktuellen Zeile mit Werten in einer vorherigen Zeile vergleichen.

Sie verwenden einen Skalarausdruck, um die Werte anzugeben, die verglichen werden sollen. Der Versatzparameter ist die Anzahl der Zeilen vor der aktuellen Zeile, die im Vergleich verwendet werden. Wenn Sie die Anzahl der Zeilen nicht angeben, wird der Standardwert einer Zeile verwendet.

Der Standardparameter gibt den Wert an, der zurückgegeben werden soll, wenn der Ausdruck bei offset einen NULL Wert hat. Wenn Sie keinen Wert angeben, wird der Wert NULL zurückgegeben.

Die LEAD Funktion liefert Daten in Zeilen nach der aktuellen Zeile in der Zeilengruppe. In einer SELECT Anweisung können Sie beispielsweise Werte in der aktuellen Zeile mit Werten in der folgenden Zeile vergleichen.

Sie geben die Werte an, die mit einem Skalarausdruck verglichen werden sollen. Der Versatzparameter ist die Anzahl der Zeilen nach der aktuellen Zeile, die im Vergleich verwendet werden.

Sie geben den Wert an, der zurückgegeben werden soll, wenn der Ausdruck bei offset einen NULL Wert unter Verwendung des Standardparameters hat. Wenn Sie diese Parameter nicht angeben, wird der Standardwert einer Zeile verwendet und der Wert NULL wird zurückgegeben.

SELECT BusinessEntityID, SalesYTD,
       LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
       LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

In diesem Beispiel werden die LEAD- und LAG-Funktionen verwendet, um die Verkaufswerte für jeden Mitarbeiter bis dato mit denen der oben und unten aufgeführten Mitarbeiter zu vergleichen, wobei die Datensätze basierend auf der Spalte BusinessEntityID angeordnet werden.

PERCENT_RANK und CUME_DIST

Die PERCENT_RANK Funktion berechnet die Rangfolge einer Zeile relativ zum PERCENT_RANK . Der Prozentsatz basiert auf der Anzahl der Zeilen in der Gruppe, die einen niedrigeren Wert als die aktuelle Zeile haben.

Der erste Wert in der Ergebnismenge hat immer einen prozentualen Rang von Null. Der Wert für den höchsten Wert oder den letzten Wert in der Gruppe ist immer eins.

Die CUME_DIST Funktion berechnet die relative Position eines angegebenen Werts in einer Gruppe von Werten, indem er den Prozentsatz der Werte bestimmt, die kleiner oder gleich diesem Wert sind. Dies wird als kumulative Verteilung bezeichnet.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Cumulative Distribution"
FROM Employee;

In diesem Beispiel verwenden Sie eine ORDER Klausel, um die Zeilen, die von der SELECT Anweisung basierend auf den SELECT der Mitarbeiter abgerufen werden, zu partitionieren oder zu gruppieren. Die Ergebnisse in jeder Gruppe werden basierend auf der Anzahl der von Mitarbeitern in Anspruch genommenen Ausfallzeiten sortiert.

Die PERCENT_RANK Funktion PERCENT_RANK die Einträge innerhalb jeder Gruppe ein. Für jeden Eintrag wird der Prozentsatz der Einträge in derselben Gruppe mit niedrigeren Werten zurückgegeben.

Die CUME_DIST Funktion ist ähnlich, abgesehen davon, dass der Prozentsatz von Werten zurückgegeben wird, die kleiner oder gleich dem aktuellen Wert sind.

PERCENTILE_DISC und PERCENTILE_CONT

Die Funktion PERCENTILE_DISC listet den Wert des ersten Eintrags auf, bei dem die kumulative Verteilung höher ist als das Perzentil, das Sie mit dem Parameter numeric_literal .

Die Werte werden nach Rowset oder Partition gruppiert, wie von der WITHIN GROUP Klausel angegeben.

Die Funktion PERCENTILE_CONT ähnelt der Funktion PERCENTILE_DISC , gibt jedoch den Durchschnitt der Summe des ersten übereinstimmenden Eintrags und des nächsten Eintrags zurück.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

Um den genauen Wert aus der Zeile zu ermitteln, der dem 0,5-Perzentil entspricht oder diesen überschreitet, übergeben Sie das Perzentil als numerisches Literal in der Funktion PERCENTILE_DISC . Die Spalte Percentile Discreet in einer Ergebnismenge listet den Wert der Zeile auf, bei der die kumulative Verteilung höher ist als das angegebene Perzentil.

Um die Berechnung auf einer Menge von Werten zu PERCENTILE_CONT , verwenden Sie die Funktion PERCENTILE_CONT . Die Spalte "Percentile Continuous" in den Ergebnissen listet den Durchschnittswert der Summe des Ergebniswerts und des nächsthöheren Übereinstimmungswerts auf.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;