Các hàm cửa sổ SQL nâng cao: Giải quyết các vấn đề kinh doanh thực tế

SQL Window Functions Beyond Basics: Giải quyết các vấn đề kinh doanh thực tế - KDnuggets Blog Bài viết hàng đầu Giới thiệu Chủ đề AI Lời khuyên nghề nghiệp Thị giác máy tính Kỹ thuật dữ liệu Khoa học dữ liệu Mô hình ngôn ngữ Học máy MLOps NLP Lập trình Python SQL Tập dữ liệu Sự kiện Tài nguyên Bảng tổng hợp Đề xuất Tóm tắt công nghệ Quảng cáo Tham gia Bản tin SQL Window Functions Beyond Basics: Giải quyết các vấn đề kinh doanh thực tế Bạn biết về window functions, nhưng bạn có biết cách sử dụng chúng để giải quyết các vấn đề kinh doanh không? Bạn sẽ biết sau khi đọc bài viết này. Bởi Nate Rosidi, Chuyên gia nội dung SQL & Xu hướng thị trường của KDnuggets vào ngày 20/5/2026 trong SQL # Giới thiệu Hầu hết các bạn đều sử dụng SQL window functions, nhưng bạn mới chỉ khai thác bề mặt — một ROW_NUMBER() ở đây, một SUM() OVER() ở đó. Tiềm năng thực sự của window functions được bộc lộ khi bạn áp dụng chúng vào các vấn đề khó hơn. Tôi sẽ hướng dẫn bạn bốn mẫu hình cho thấy window functions hữu ích nhất. Các ví dụ đều là những câu hỏi phỏng vấn thực tế mà bạn có thể thực hành trên StrataScratch. # Tổng lũy kế Tính toán tổng lũy kế là một trong những ứng dụng kinh doanh phổ biến nhất của window functions. Những người làm tài chính rất yêu thích nó! Nó được sử dụng để theo dõi doanh thu tích lũy hàng tháng, sau đó dễ dàng chuyển sang tính toán vị trí của bạn so với mục tiêu doanh thu hàng năm. Điều khiến đây trở thành một vấn đề của window function là, thông thường, bạn nên bao gồm cả giá trị theo từng kỳ và tổng tích lũy trong cùng một kết quả đầu ra. Bạn không thể sử dụng GROUP BY với SUM(), vì điều đó làm gộp các hàng riêng lẻ. Vì vậy, giải pháp rõ ràng là sử dụng một window function, tức là SUM() OVER(). // Ví dụ: Tính toán doanh thu theo thời gian Câu hỏi này của Amazon ban đầu yêu cầu bạn tính toán mức trung bình động 3 tháng. Tuy nhiên, chúng ta sẽ bỏ qua điều đó và tính toán doanh thu tích lũy cho mỗi tháng. Dữ liệu: Dưới đây là bản xem trước bảng amazon_purchases. user_id created_at purchase_amt 10 2020-01-01 3742 11 2020-01-04 1290 12 2020-01-07 4249 ... ... ... 109 2020-10-24 1749 Mã: Truy vấn bên trong chuyển đổi ngày thành định dạng YYYY-MM bằng cách sử dụng TO_CHAR() và tổng hợp doanh thu hàng tháng, lọc ra các giao dịch trả lại với WHERE purchase_amt > 0. Truy vấn bên ngoài áp dụng window function trên các tổng hàng tháng mà chúng ta đã tính toán. Tôi không chỉ định một mệnh đề khung rõ ràng (có chủ ý) trong OVER(), vì vậy window function mặc định là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Điều đó có nghĩa là cửa sổ là tất cả các hàng đứng trước hàng hiện tại, tức là tháng. Nói cách khác, tổng lũy kế là: tất cả các tháng trước đó + tháng hiện tại. Không có gì đáng ngạc nhiên, đó là định nghĩa sách giáo khoa về tổng lũy kế. SELECT t.month, t.monthly_revenue, SUM(t.monthly_revenue) OVER(ORDER BY t.month) AS cumulative_revenue FROM ( SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month, SUM(purchase_amt) AS monthly_revenue FROM amazon_purchases WHERE purchase_amt > 0 GROUP BY TO_CHAR(created_at::date, 'YYYY-MM') ORDER BY TO_CHAR(created_at::date, 'YYYY-MM') ) t ORDER BY t.month ASC; Kết quả đầu ra: month monthly_revenue cumulative_revenue 2020-01 26292 26292 2020-02 20695 46987 2020-03 29620 76607 ... ... ... 2020-10 15310 239869 # Khoảng trống và Đảo (Sessionization) Mô hình này cũng liên quan đến dữ liệu tuần tự, tương tự như tổng tích lũy, nhưng sử dụng các hàm cửa sổ khác nhau. Một "island" (đảo) là một chuỗi các hàng có cùng điều kiện, ví dụ: các lần đăng nhập hàng ngày liên tiếp. Một "gap" (khoảng trống) là khoảng cách giữa các "island". Một trong những ứng dụng thực tế phổ biến nhất của mô hình này là phân tích phiên (sessionization) — nhóm một luồng sự kiện thô thành các phiên. Một phiên thường được định nghĩa là một chuỗi các sự kiện từ cùng một người dùng mà không có khoảng cách giữa các sự kiện liên tiếp vượt quá một khoảng thời gian chờ nhất định (30 phút là tiêu chuẩn phân tích web). Phân tích phiên thường được áp dụng trong kỹ thuật sản phẩm và dữ liệu. Nó được sử dụng ở bất cứ nơi nào cần nhóm các luồng sự kiện thô thành các đơn vị hoạt động có ý nghĩa. Việc phát hiện cổ điển trong SQL bao gồm hai bước: LAG() hoặc LEAD() — để so sánh từng hàng với hàng trước hoặc sau nó, và đánh dấu nơi bắt đầu một chuỗi mới. SUM(flag) OVER (PARTITION BY user ORDER BY date) — để tích lũy các cờ thành một ID chuỗi, vì nó giữ nguyên trong một chuỗi và tăng lên ở mỗi ranh giới. // Ví dụ: Tìm chuỗi truy cập của người dùng Câu hỏi từ các cuộc phỏng vấn của LinkedIn và Meta yêu cầu tìm ba người dùng hàng đầu có chuỗi truy cập nền tảng dài nhất cho đến ngày 10/8/2022. Cần xuất tất cả người dùng có ba chuỗi dài nhất, nếu có nhiều hơn một người dùng cho mỗi độ dài chuỗi. Dữ liệu: Bảng là user_streaks. user_id date_visited u001 2022-08-01 u001 2022-08-01 u004 2022-08-01 ... ... u005 2022-08-11 Mã: Truy vấn dài, nhưng được cấu trúc gọn gàng thành các CTE (Common Table Expressions), nên dễ theo dõi. unique_visits: Loại bỏ các bản ghi truy cập trùng lặp và giới hạn dữ liệu đến ngày 10/8/2022. streak_flags: Sử dụng LAG() để lấy ngày truy cập trước đó của mỗi người dùng và đánh dấu hàng là 0 (tiếp tục chuỗi nếu khoảng cách là 1 ngày) hoặc 1 (bắt đầu chuỗi mới cho bất kỳ khoảng cách nào khác). streak_ids: Chuyển đổi các cờ thành ID nhóm chuỗi bằng cách sử dụng hàm SUM() tích lũy. streak_lengths: Đếm số ngày cho mỗi chuỗi. longest_per_user: Chỉ giữ lại chuỗi dài nhất của mỗi người dùng. ranked_lengths: Xếp hạng các độ dài chuỗi riêng biệt. top_lengths: Tìm ba giá trị độ dài chuỗi hàng đầu. Lệnh SELECT cuối cùng tổng hợp mọi thứ: nó hiển thị tất cả người dùng có ba chuỗi hàng đầu và độ dài chuỗi tương ứng của họ theo ngày. WITH unique_visits AS ( SELECT DISTINCT user_id, date_visited FROM user_streaks WHERE date_visited Đầu ra: user_id streak_length u004 10 u005 10 u003 5 u001 4 u006 4 # Phân tích nhóm (Cohort Analysis) Một nhóm (cohort) là một nhóm người dùng có chung một sự kiện khởi đầu.