Analytic functions

Functions for Manipulating Data in SQL Server

Ana Voicu

Data Engineer

FIRST_VALUE()

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

Returns the first value in an ordered set.

OVER clause components

Component	Status	Description
PARTITION by column	optional	divide the result set into partitions
ORDER BY column	mandatory	order the result set
ROW_or_RANGE frame	optional	set the partition limits

LAST_VALUE()

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

Returns the last value in an ordered set.

Partition limits

RANGE BETWEEN start_boundary AND end_boundary
ROWS BETWEEN start_boundary AND end_boundary

Boundary	Description
UNBOUNDED PRECEDING	first row in the partition
UNBOUNDED FOLLOWING	last row in the partition
CURRENT ROW	current row
PRECEDING	previous row
FOLLOWING	next row

FIRST_VALUE() and LAST_VALUE() example

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() and LEAD()

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

Accesses data from a previous row in the same result set.

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

Accesses data from a subsequent row in the same result set.

LAG() and LEAD() example

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                  |

Let's practice!

Functions for Manipulating Data in SQL Server