SQL
Fonctions (analytique)

Introduction

Syntaxe

1. FIRST_VALUE (expression_ scalaire) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
2. LAST_VALUE (expression_ scalaire) 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

Vous utilisez la fonction FIRST_VALUE pour déterminer la première valeur d'un jeu de résultats ordonné, que vous identifiez à l'aide d'une expression scalaire.

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

Dans cet exemple, la fonction FIRST_VALUE est utilisée pour renvoyer l' ID de l'état ou de la province ayant le taux de taxe le plus bas. La clause OVER est utilisée pour ordonner les taux de taxe afin d'obtenir le taux le plus bas.

LAST_VALUE

La fonction LAST_VALUE fournit la dernière valeur d'un ensemble de résultats ordonnés, que vous spécifiez à l'aide d'une expression scalaire.

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

Cet exemple utilise la fonction LAST_VALUE pour renvoyer la dernière valeur de chaque jeu de lignes dans les valeurs LAST_VALUE .

LAG et LEAD

La fonction LAG fournit des données sur les lignes avant la ligne en cours dans le même jeu de résultats. Par exemple, dans une SELECT , vous pouvez comparer les valeurs de la ligne actuelle avec les valeurs d'une ligne précédente.

Vous utilisez une expression scalaire pour spécifier les valeurs à comparer. Le paramètre offset est le nombre de lignes avant la ligne en cours qui sera utilisé dans la comparaison. Si vous ne spécifiez pas le nombre de lignes, la valeur par défaut d'une ligne est utilisée.

Le paramètre par défaut spécifie la valeur à renvoyer lorsque l'expression à l'offset a une valeur NULL . Si vous ne spécifiez pas de valeur, la valeur NULL est renvoyée.

La fonction LEAD fournit des données sur les lignes après la ligne en cours dans le jeu de lignes. Par exemple, dans une SELECT , vous pouvez comparer les valeurs de la ligne en cours avec les valeurs de la ligne suivante.

Vous spécifiez les valeurs à comparer en utilisant une expression scalaire. Le paramètre offset est le nombre de lignes après la ligne en cours à utiliser dans la comparaison.

Vous spécifiez la valeur à renvoyer lorsque l'expression à l'offset a une valeur NULL à l'aide du paramètre par défaut. Si vous ne spécifiez pas ces paramètres, la valeur par défaut d'une ligne est utilisée et la valeur NULL est renvoyée.

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;

Cet exemple utilise les fonctions LEAD et LAG pour comparer les valeurs de vente de chaque employé à ce jour avec celles des employés répertoriés ci-dessus et ci-dessous, les enregistrements étant classés en fonction de la colonne BusinessEntityID.

PERCENT_RANK et CUME_DIST

La fonction PERCENT_RANK calcule le classement d'une ligne par rapport à l'ensemble de lignes. Le pourcentage est basé sur le nombre de lignes du groupe dont la valeur est inférieure à la ligne actuelle.

La première valeur du jeu de résultats a toujours un classement en pourcentage de zéro. La valeur de la plus haute ou de la dernière valeur du jeu est toujours une.

La fonction CUME_DIST calcule la position relative d'une valeur spécifiée dans un groupe de valeurs en déterminant le pourcentage de valeurs inférieures ou égales à cette valeur. Cela s'appelle la distribution cumulative.

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;

Dans cet exemple, vous utilisez une clause ORDER pour partitionner ou grouper les lignes extraites par l' SELECT en fonction des titres de travail des employés, les résultats de chaque groupe étant triés en fonction du nombre d'heures de congé de maladie utilisées par les employés.

La fonction PERCENT_RANK classe les entrées dans chaque groupe. Pour chaque entrée, il renvoie le pourcentage d'entrées du même groupe qui ont des valeurs inférieures.

La fonction CUME_DIST est similaire, sauf qu'elle renvoie le pourcentage de valeurs inférieures ou égales à la valeur actuelle.

PERCENTILE_DISC et PERCENTILE_CONT

La fonction PERCENTILE_DISC répertorie la valeur de la première entrée où la distribution cumulative est supérieure au centile que vous fournissez à l'aide du paramètre numeric_literal .

Les valeurs sont regroupées par jeu de lignes ou partition, comme spécifié par la clause WITHIN GROUP .

La fonction PERCENTILE_CONT est similaire à la fonction PERCENTILE_DISC , mais renvoie la moyenne de la somme de la première entrée correspondante et de l'entrée suivante.

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;

Pour rechercher la valeur exacte de la ligne qui correspond ou dépasse le 0,5 centile, vous transmettez le percentile en tant que littéral numérique dans la fonction PERCENTILE_DISC . La colonne Percentile Discreet d'un jeu de résultats répertorie la valeur de la ligne à laquelle la distribution cumulative est supérieure au centile spécifié.

Pour baser le calcul sur un ensemble de valeurs, vous utilisez la fonction PERCENTILE_CONT . La colonne "Percentile Continuous" dans les résultats indique la valeur moyenne de la somme de la valeur du résultat et de la valeur correspondante la plus élevée suivante.

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;