Funzioni analitiche

Funzioni per manipolare i dati in SQL Server

Ana Voicu

Data Engineer

FIRST_VALUE()

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

Restituisce il primo valore in un set ordinato.

OVER componenti

Componente	Stato	Descrizione
PARTITION by column	opzionale	divide il result set in partizioni
ORDER BY column	obbligatorio	ordina il result set
ROW_or_RANGE frame	opzionale	imposta i limiti della partizione

LAST_VALUE()

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

Restituisce l’ultimo valore in un set ordinato.

Limiti di partizione

RANGE BETWEEN start_boundary AND end_boundary
ROWS BETWEEN start_boundary AND end_boundary

Limite	Descrizione
UNBOUNDED PRECEDING	prima riga della partizione
UNBOUNDED FOLLOWING	ultima riga della partizione
CURRENT ROW	riga corrente
PRECEDING	riga precedente
FOLLOWING	riga successiva

Esempio FIRST_VALUE() e 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() e LEAD()

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

Accede ai dati della riga precedente nello stesso result set.

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

Accede ai dati della riga successiva nello stesso result set.

Esempio LAG() e 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                  |

Ayo berlatih!

Funzioni per manipolare i dati in SQL Server