Fonctions analytiques

Fonctions pour manipuler les données dans SQL Server

Ana Voicu

Data Engineer

FIRST_VALUE()

FIRST_VALUE(numeric_expression) 
    OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)

Retourne la première valeur d'un ensemble ordonné.

Éléments de la clause OVER

Composant	Statut	Description
PARTITION by column	facultatif	diviser le jeu de résultats en partitions
ORDER BY column	obligatoire	ordonner le jeu de résultats
ROW_or_RANGE frame	facultatif	définir les limites de partition

LAST_VALUE()

LAST_VALUE(numeric_expression) 
    OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)

Retourne la dernière valeur d'un ensemble ordonné.

Limites de partition

RANGE BETWEEN start_boundary AND end_boundary
ROWS BETWEEN start_boundary AND end_boundary

Limite	Description
UNBOUNDED PRECEDING	première ligne de la partition
UNBOUNDED FOLLOWING	dernière ligne de la partition
CURRENT ROW	ligne courante
PRECEDING	ligne précédente
FOLLOWING	ligne suivante

Exemple : FIRST_VALUE() et LAST_VALUE()

SELECT
    first_name + ' ' + last_name AS name,
    gender,
    total_votes AS votes,    
    FIRST_VALUE(total_votes) 
    OVER (PARTITION BY gender ORDER BY total_votes) AS min_votes,
    LAST_VALUE(total_votes) 
        OVER (PARTITION BY gender ORDER BY total_votes 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_votes
FROM voters;

| name            | gender | votes | min_votes | max_votes |
|-----------------|--------|-------|-----------|-----------|
| Michele Suarez  | F      | 20    | 20        | 189       |
| ...             | ...    | ...   | 20        | 189       |
| Marcus Jenkins  | M      | 16    | 16        | 182       |
| Micheal Vazquez | M      | 18    | 16        | 182       |

LAG() et LEAD()

LAG(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)

Accède aux données d'une ligne précédente du même jeu de résultats.

LEAD(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)

Accède aux données d'une ligne suivante du même jeu de résultats.

Exemple : LAG() et LEAD()

SELECT 
    broad_bean_origin AS bean_origin,
    rating,
    cocoa_percent,
    LAG(cocoa_percent) OVER(ORDER BY rating ) AS percent_lower_rating,
    LEAD(cocoa_percent) OVER(ORDER BY rating ) AS percent_higher_rating
FROM ratings
WHERE company = 'Felchlin'
ORDER BY rating ASC;

| bean_origin        | rating | cocoa_percent | percent_lower_rating | percent_higher_rating |
|--------------------|--------|---------------|----------------------|-----------------------|
| Grenada            | 3      | 0.58          | NULL                 | 0.62                  |
| Dominican Republic | 3.75   | 0.62          | 0.58                 | 0.64                  |
| Madagascar         | 3.75   | 0.64          | 0.74                 | 0.65                  |
| Venezuela          | 4      | 0.65          | 0.74                 | NULL                  |

Passons à la pratique !

Fonctions pour manipuler les données dans SQL Server