Hàm phân tích

Các hàm thao tác dữ liệu trong SQL Server

Ana Voicu

Data Engineer

FIRST_VALUE()

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

Trả về giá trị đầu tiên trong một tập có thứ tự.

Thành phần của mệnh đề OVER

Thành phần	Trạng thái	Mô tả
PARTITION BY column	tùy chọn	chia tập kết quả thành phân vùng
ORDER BY column	bắt buộc	sắp xếp tập kết quả
ROW_or_RANGE frame	tùy chọn	đặt giới hạn phân vùng

LAST_VALUE()

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

Trả về giá trị cuối cùng trong một tập có thứ tự.

Giới hạn phân vùng

RANGE BETWEEN start_boundary AND end_boundary
ROWS BETWEEN start_boundary AND end_boundary

Giới hạn	Mô tả
UNBOUNDED PRECEDING	hàng đầu tiên trong phân vùng
UNBOUNDED FOLLOWING	hàng cuối cùng trong phân vùng
CURRENT ROW	hàng hiện tại
PRECEDING	hàng trước
FOLLOWING	hàng sau

Ví dụ về FIRST_VALUE() và 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() và LEAD()

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

Truy cập dữ liệu từ hàng trước trong cùng tập kết quả.

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

Truy cập dữ liệu từ hàng sau trong cùng tập kết quả.

Ví dụ về LAG() và 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                  |

Hãy luyện tập!

Các hàm thao tác dữ liệu trong SQL Server