Window Function tính toán trên một tập dòng liên quan nhưng không gom chúng thành một dòng như GROUP BY. Bài viết xây bảng xếp hạng cửa hàng theo ngày, doanh thu lũy kế và tăng trưởng so với kỳ trước từ daily_store_sales.

Mục lục

1. Bài toán thực tế
2. Sản phẩm hoàn thành
3. Kiến thức cần dùng
4. Chuẩn bị môi trường
5. Dữ liệu thực hành
6. Xây dựng từng bước
7. Chương trình hoàn chỉnh
8. Kiểm tra kết quả
9. Lỗi thường gặp
10. Đưa vào thực tế
11. Bài tập thực hành
12. Project mở rộng
13. Tổng kết
14. Tài liệu tham khảo

1. Bài toán thực tế

Dashboard cần giữ một dòng mỗi cửa hàng mỗi ngày, đồng thời hiển thị thứ hạng trong khu vực, doanh thu ngày trước và tổng lũy kế tháng. GROUP BY đã tạo đúng grain nhưng không thể thêm các phép so sánh mà vẫn giữ từng dòng.

Một lỗi tinh tế nằm ở window frame. SUM có ORDER BY nhưng không chỉ rõ ROWS có thể xử lý các dòng đồng hạng khác với kỳ vọng.

2. Sản phẩm hoàn thành

Sản phẩm là view store_performance_window với revenue_rank, previous_revenue, growth_pct, month_to_date_revenue và rolling_7d_revenue.

Mỗi hàm cửa sổ được giải thích bằng partition, order và frame riêng.

• Hiểu khác biệt giữa GROUP BY và window.
• Dùng ROW_NUMBER, RANK và DENSE_RANK.
• Dùng LAG, LEAD và SUM OVER.
• Khai báo ROWS frame rõ ràng.
• Nhận diện chi phí sort và index hỗ trợ.

3. Kiến thức cần dùng

OVER biến hàm thành window function. PARTITION BY chia tập thành nhóm độc lập; ORDER BY xác định thứ tự bên trong nhóm; frame xác định những dòng quanh dòng hiện tại được đưa vào phép tính.

ROW_NUMBER luôn tạo số liên tiếp; RANK để lại khoảng trống sau đồng hạng; DENSE_RANK không để khoảng trống. Báo cáo thi đua thường dùng RANK, còn chọn một dòng đại diện thường dùng ROW_NUMBER với tie-breaker.

Window được tính sau WHERE, GROUP BY và HAVING nhưng trước ORDER BY cuối. Muốn lọc rank <= 3, cần bọc trong CTE hoặc subquery.

4. Chuẩn bị môi trường

Đầu vào daily_store_sales phải duy nhất theo sale_date + store_id và đã có region. Nếu region nằm ở stores, JOIN dimension trước window.

Tạo ít nhất hai cửa hàng đồng doanh thu để thấy khác biệt giữa ba hàm xếp hạng.

SELECT sale_date,store_id,COUNT(*)
FROM daily_store_sales
GROUP BY sale_date,store_id HAVING COUNT(*)>1;

5. Dữ liệu thực hành

Mỗi dòng gồm sale_date, store_id, region và revenue. Window không thay đổi số dòng; nếu đầu vào 100 dòng, đầu ra vẫn 100 dòng.

Để tính lũy kế tháng, partition gồm store_id và date_trunc month. Rolling 7 rows chỉ tương đương 7 ngày khi dữ liệu có đủ mỗi ngày.

CREATE INDEX IF NOT EXISTS idx_daily_store_sales_window
ON daily_store_sales (store_id, sale_date);

6. Xây dựng từng bước

Bước 1: Xếp hạng trong khu vực

RANK dùng partition theo ngày và khu vực, vì bảng xếp hạng được khởi động lại mỗi ngày.

RANK() OVER (PARTITION BY sale_date,region ORDER BY revenue DESC) AS revenue_rank

Kết quả mong đợi: Hai cửa hàng đồng doanh thu cùng hạng.

Bước 2: Chọn top 3

Không lọc alias window trong WHERE cùng cấp. Tạo CTE ranked rồi lọc ngoài.

WITH ranked AS (
 SELECT *,RANK() OVER(PARTITION BY sale_date,region ORDER BY revenue DESC) revenue_rank
 FROM daily_store_sales
)
SELECT * FROM ranked WHERE revenue_rank<=3;

Kết quả mong đợi: Top 3 hạng; có thể hơn ba dòng nếu đồng hạng.

Bước 3: So sánh kỳ trước

LAG revenue trả giá trị dòng trước của cùng cửa hàng.

LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_date) AS previous_revenue

Kết quả mong đợi: Ngày đầu mỗi cửa hàng nhận NULL.

Bước 4: Lũy kế tháng

ROWS frame được viết rõ để cộng từ đầu partition đến dòng hiện tại.

SUM(revenue) OVER (
 PARTITION BY store_id,date_trunc('month',sale_date)
 ORDER BY sale_date
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS month_to_date_revenue

Kết quả mong đợi: Lũy kế trở về đầu khi sang tháng.

Bước 5: Trung bình trượt bảy dòng

Frame gồm dòng hiện tại và sáu dòng trước. Nếu thiếu ngày, đây là bảy quan sát, không phải bảy ngày lịch.

AVG(revenue) OVER (
 PARTITION BY store_id ORDER BY sale_date
 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7_observation_avg

Kết quả mong đợi: Trung bình tối đa bảy quan sát gần nhất.

Window chạy trên tập dòng nào

Trước mỗi OVER, viết ra số dòng đầu vào sau WHERE và GROUP BY. Window không nhìn các dòng đã bị lọc. Nếu lọc chỉ tháng 9 rồi LAG, ngày đầu tháng không thể thấy 31/8 dù dữ liệu tồn tại trong bảng.

Muốn so sánh với kỳ ngoài phạm vi hiển thị, tính window trên phạm vi rộng hơn trong CTE rồi lọc ngày ở SELECT ngoài. Thứ tự này thay đổi kết quả một cách có chủ đích.

Đồng hạng và ngôn ngữ kinh doanh

Top 3 bằng RANK có thể trả bốn cửa hàng nếu hạng ba đồng điểm. Nếu giao diện buộc đúng ba dòng, dùng ROW_NUMBER và tie-breaker rõ như order_count rồi store_id. Hai yêu cầu không giống nhau.

Hỏi stakeholder muốn “ba hạng cao nhất” hay “ba cửa hàng”. Cú pháp chọn theo nghĩa kinh doanh, không theo sở thích kỹ thuật.

Ngày thiếu trong rolling metric

Một cửa hàng đóng cửa một ngày sẽ không có dòng nếu mart chỉ ghi ngày có giao dịch. Rolling 7 rows khi đó có thể kéo dài tám hoặc chín ngày lịch. Date spine tạo đủ store-day rồi COALESCE 0 nếu nghiệp vụ xem không giao dịch là doanh thu 0.

Không COALESCE khi ngày thiếu nghĩa là pipeline chưa nạp. Cùng một NULL có thể là zero hợp lệ hoặc sự cố freshness; cần phân biệt bằng metadata.

Tỷ trọng trong tổng bằng SUM OVER

Window có thể đặt aggregate bên trong aggregate window khi đầu vào đã GROUP BY. SUM(SUM(revenue)) OVER tạo tổng của các nhóm và cho phép tính tỷ trọng mà không JOIN lại.

Mẫu này mạnh nhưng cần đọc từ trong ra ngoài: SUM(revenue) tạo doanh thu nhóm; SUM của kết quả nhóm qua window tạo mẫu số toàn partition.

SELECT sale_date,store_id,SUM(revenue) store_revenue,
 ROUND(100*SUM(revenue)/NULLIF(SUM(SUM(revenue)) OVER(PARTITION BY sale_date),0),2) share_pct
FROM sales_detail
GROUP BY sale_date,store_id;

Giá trị đầu và cuối khung

FIRST_VALUE và LAST_VALUE phụ thuộc frame. LAST_VALUE với frame mặc định thường trả giá trị dòng hiện tại, không phải cuối partition. Phải mở frame đến UNBOUNDED FOLLOWING khi muốn ngày cuối.

Đây là lỗi cú pháp chạy thành công nhưng ý nghĩa sai, minh họa vì sao window frame cần được đọc như một phần của hàm.

SELECT store_id,sale_date,revenue,
 FIRST_VALUE(revenue) OVER(PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_revenue,
 LAST_VALUE(revenue) OVER(PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_revenue
FROM daily_store_sales;

Median và percentile

Doanh thu trung bình dễ bị kéo bởi vài ngày cao đột biến. percentile_cont(0.5) cho median; percentile_cont(0.9) cho ngưỡng 90th percentile. PostgreSQL cung cấp ordered-set aggregate, sau đó có thể JOIN kết quả với dữ liệu ngày.

Median là aggregate giảm dòng, không phải window trong cú pháp cơ bản này. Tách thành CTE store_threshold rồi nối lại nếu cần gắn cờ ngày bất thường.

WITH thresholds AS (
 SELECT store_id,percentile_cont(0.5) WITHIN GROUP(ORDER BY revenue) median_revenue,
 percentile_cont(0.9) WITHIN GROUP(ORDER BY revenue) p90_revenue
 FROM daily_store_sales GROUP BY store_id
)
SELECT d.*,t.median_revenue,t.p90_revenue,d.revenue>t.p90_revenue AS is_high_day
FROM daily_store_sales d JOIN thresholds t USING(store_id);

Gaps and islands cho chuỗi tăng trưởng

Bài toán tìm chuỗi ngày doanh thu vượt mục tiêu liên tục thường dùng row_number hoặc LAG để phát hiện điểm bắt đầu nhóm. Đây là ứng dụng window nâng cao nhưng thực tế trong vận hành.

Tạo cờ đạt mục tiêu, giữ các ngày đạt, rồi sale_date - row_number tạo khóa island cho chuỗi liên tục.

WITH hit AS (
 SELECT *,sale_date-ROW_NUMBER() OVER(PARTITION BY store_id ORDER BY sale_date)::int grp
 FROM daily_store_sales WHERE revenue>=target_revenue
)
SELECT store_id,MIN(sale_date) start_date,MAX(sale_date) end_date,COUNT(*) streak_days
FROM hit GROUP BY store_id,grp ORDER BY streak_days DESC;

Kiểm tra plan nhiều window

Hai window cùng PARTITION BY và ORDER BY có thể chia sẻ sort. Một window đổi order từ ngày sang revenue có thể yêu cầu sort khác. EXPLAIN cho thấy Sort và WindowAgg nodes.

Chỉ tính window cần dùng. Nếu dashboard yêu cầu top 3 của một ngày, lọc phạm vi ngày trước khi window để giảm dữ liệu nhưng không lọc mất kỳ trước khi đang tính LAG.

EXPLAIN (ANALYZE,BUFFERS)
SELECT store_id,sale_date,revenue,
 LAG(revenue) OVER(PARTITION BY store_id ORDER BY sale_date),
 SUM(revenue) OVER(PARTITION BY store_id ORDER BY sale_date ROWS UNBOUNDED PRECEDING)
FROM daily_store_sales;

7. Chương trình hoàn chỉnh

View cuối tính các window cơ bản trong CTE, sau đó dùng previous_revenue để tính growth. Không gọi lại cùng LAG hai lần.

Để rolling đúng bảy ngày lịch, tạo date spine cho mọi store-day và COALESCE revenue theo quy tắc đã chốt.

CREATE OR REPLACE VIEW store_performance_window AS
WITH w AS (
 SELECT d.*,
 RANK() OVER(PARTITION BY sale_date,region ORDER BY revenue DESC) revenue_rank,
 LAG(revenue) OVER(PARTITION BY store_id ORDER BY sale_date) previous_revenue,
 SUM(revenue) OVER(PARTITION BY store_id,date_trunc('month',sale_date) ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) month_to_date_revenue,
 AVG(revenue) OVER(PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) rolling_7_observation_avg
 FROM daily_store_sales d
)
SELECT *,ROUND(100*(revenue-previous_revenue)/NULLIF(previous_revenue,0),2) growth_pct
FROM w;

8. Kiểm tra kết quả

Số dòng trước và sau view phải bằng nhau. Chọn một cửa hàng và tự cộng năm ngày để xác nhận lũy kế.

Tạo hai cửa hàng đồng doanh thu và so sánh RANK, DENSE_RANK, ROW_NUMBER. Kiểm tra partition reset đúng ngày và khu vực.

SELECT (SELECT COUNT(*) FROM daily_store_sales) source_rows,
       (SELECT COUNT(*) FROM store_performance_window) result_rows;

SELECT * FROM store_performance_window
WHERE month_to_date_revenue < revenue;

9. Lỗi thường gặp

• Nhầm window với GROUP BY: Window không giảm số dòng.
• Thiếu PARTITION BY: Lũy kế hoặc rank chạy toàn bảng.
• Frame mặc định mơ hồ: Viết ROWS rõ khi cần hành vi theo dòng.
• Lọc rank ở WHERE cùng cấp: Window chưa được tính tại thời điểm WHERE.
• Rolling 7 rows thành 7 days: Cần date spine khi ngày có thể thiếu.

10. Đưa vào thực tế

Nhiều window có cùng partition và order có thể dùng chung bước sort; các order khác nhau tạo thêm sort. Đọc plan để tránh cửa sổ không cần thiết.

Với bảng rất lớn, tính metric ngày trước rồi mới window thường rẻ hơn chạy trên giao dịch chi tiết.

11. Bài tập thực hành

1. So sánh RANK và DENSE_RANK trên dữ liệu đồng hạng.
2. Tính tỷ trọng doanh thu cửa hàng trong khu vực.
3. Dùng LEAD để xem doanh thu ngày kế tiếp.
4. Tính rolling 30 ngày với RANGE hoặc date spine.
5. Chọn ngày doanh thu cao nhất mỗi cửa hàng bằng ROW_NUMBER.

12. Project mở rộng

Xây cohort retention theo tháng mua đầu tiên. Window có thể hỗ trợ xác định first_order_month, nhưng ma trận cohort cần thêm tổng hợp theo cohort_age.

13. Tổng kết

Window Function mở rộng SQL từ báo cáo tổng hợp sang phân tích theo chuỗi và tương quan giữa các dòng. Ba thành phần cần đọc cùng nhau là partition, order và frame.

Bài tiếp theo quay về chất lượng dữ liệu: NULL, duplicate và định dạng sai phải được xử lý trước khi các window metric đáng tin cậy.

14. Tài liệu tham khảo

• Window Functions
• Window Function Catalog
• SELECT Processing

TechData.AI - Leading the Future.
Tham khảo các khoá học theo link: https://techdata.ai/techdata-ai-course/
Hoàng Minh.