Hàm Window

Tự động hóa Data Pipeline trong Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Window Function là gì?

Tính trên nhiều dòng mà không gom chúng

Tính kết quả cho từng dòng dựa trên tập lân cận — là cửa sổ của nó
Khác GROUP BY: vào bao nhiêu dòng, ra bấy nhiêu dòng
Xác định bằng mệnh đề OVER

3 nhóm chính:

Xếp hạng: ROW_NUMBER(), RANK(), DENSE_RANK()
Tổng hợp: SUM(), AVG(), COUNT() with OVER()
Dò tìm: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Xem thêm trong khóa Window Functions in Snowflake của DataCamp

Mệnh đề OVER()

SELECT shipment_id
, region
, delivery_days
, AVG(delivery_days) 
    OVER ( PARTITION BY region ORDER BY dispatched_at ) AS avg_days_in_region 
FROM shipments;

SHIPMENT_ID	REGION	DELIVERY_DAYS	RUNNING_AVG_DAYS_IN_REGION
SHP-001	EMEA	3	3.00
SHP-002	EMEA	5	4.00
SHP-003	EMEA	4	4.00
SHP-004	APAC	6	6.00

ROW_NUMBER vs RANK vs DENSE_RANK

SELECT
    shipment_id AS shipment, delivery_days AS days,
    ROW_NUMBER() OVER (ORDER BY delivery_days) AS row_number,
    RANK()       OVER (ORDER BY delivery_days) AS rank,
    DENSE_RANK() OVER (ORDER BY delivery_days) AS dense_rank
FROM shipments ORDER BY delivery_days;

SHIPMENT	DELIVERY_DAYS	ROW_NUMBER	RANK	DENSE_RANK
SHP-001	3	1	1	1
SHP-002	5	2	2	2
SHP-003	5	3	2	2
SHP-004	7	4	4	3

LAG và LEAD

Dữ liệu vào

Đầu vào SQL Lag và Lead

Kết quả

Đầu ra Lag và Lead

Tổng chạy và trung bình trượt

Khung ROWS vs RANGE

ROWS BETWEEN: đếm vị trí dòng vật lý — bù trừ chính xác, không phụ thuộc giá trị

-- ROWS: luôn lấy đúng N dòng vật lý liền trước
SUM(credits) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Gồm dòng hiện tại và hai dòng trước

RANGE BETWEEN: dựa trên giá trị, gom các dòng có cùng giá trị ORDER BY

-- RANGE: gồm mọi dòng trùng tại giá trị ranh giới
SUM(credits) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

Chỉ dùng RANGE khi logic dựa trên ranh giới giá trị

Ayo berlatih!

Tự động hóa Data Pipeline trong Snowflake