PostgreSQL Bài 16: Window Functions toàn tập: ROW_NUMBER(), RANK(), LEAD(), LAG() và Window Frames

Trong phân tích dữ liệu và lập trình backend, một trong những giới hạn lớn nhất của mệnh đề GROUP BY truyền thống là nó thu gom (collapse) toàn bộ các dòng thuộc cùng một nhóm thành một dòng kết quả duy nhất. Nếu bạn muốn vừa giữ nguyên từng dòng chi tiết của đơn hàng, vừa hiển thị tổng doanh thu của toàn bộ tháng đó bên cạnh, GROUP BY buộc bạn phải viết các câu lệnh JOIN hoặc Subquery tương quan cồng kềnh.

Window Functions (Hàm cửa sổ) giải quyết triệt để vấn đề này. Chúng thực hiện các phép toán phân tích trên một tập hợp các dòng liên quan (gọi là Window Frame), nhưng vẫn giữ nguyên danh tính của từng dòng riêng lẻ trong tập kết quả.

1. Cấu trúc tổng quát của Window Function

Một Window Function luôn được nhận diện bởi mệnh đề OVER (...):

SQL

function_name(...) OVER (
    [PARTITION BY partition_column, ...]
    [ORDER BY sort_column [ASC | DESC], ...]
    [window_frame_clause]
)

TẬP KẾT QUẢ ĐẦU VÀO
┌────────────────────────────────────────────────────────┐
│ PARTITION BY department_id (Chia thành các phòng ban)  │
│ ┌────────────────────────────────────────────────────┐ │
│ │ Window: IT Dept                                    │ │
│ │   Row 1: Alice   (ORDER BY salary) ──► Window Frame│ │
│ │   Row 2: Bob                       ──► Window Frame│ │
│ └────────────────────────────────────────────────────┘ │
│ ┌────────────────────────────────────────────────────┐ │
│ │ Window: Sales Dept                                 │ │
│ │   Row 3: Charlie                   ──► Window Frame│ │
│ └────────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────┘

1.1. Ba thành phần cấu thành mệnh đề OVER:

1. PARTITION BY (Tùy chọn): Chia tập dữ liệu thành các nhóm độc lập (cửa sổ). Nếu bỏ qua mệnh đề này, toàn bộ tập kết quả truy vấn sẽ được coi như một cửa sổ duy nhất.

2. ORDER BY (Tùy chọn): Xác định thứ tự duyệt của các dòng bên trong từng Partition. Thứ tự này quyết định kết quả của các hàm xếp hạng (RANK) hoặc hàm trích xuất vị trí (LEAD, LAG).

3. Window Frame (Khung cửa sổ): Xác định phạm vi con của các dòng xung quanh dòng hiện tại được đưa vào tính toán (ví dụ: "tính tổng từ dòng đầu tiên đến dòng hiện tại").

2. Dữ liệu thực hành mẫu

Tạo một bảng theo dõi doanh số của nhân viên theo phòng ban:

SQL

CREATE SCHEMA IF NOT EXISTS analytics_lab;

CREATE TABLE analytics_lab.sales_records (
    id INT PRIMARY KEY,
    employee_name TEXT NOT NULL,
    department TEXT NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
);

INSERT INTO analytics_lab.sales_records VALUES
(1, 'Alice',   'IT Hardware', '2026-10-01', 5000),
(2, 'Bob',     'IT Hardware', '2026-10-02', 3000),
(3, 'Charlie', 'IT Hardware', '2026-10-03', 5000),
(4, 'David',   'IT Hardware', '2026-10-04', 2000),
(5, 'Eva',     'Software',    '2026-10-01', 7000),
(6, 'Frank',   'Software',    '2026-10-02', 4000),
(7, 'Grace',   'Software',    '2026-10-03', 7000);

3. Nhóm hàm xếp hạng: ROW_NUMBER(), RANK(), DENSE_RANK()

Cả ba hàm này đều dùng để đánh số thứ tự hoặc xếp hạng các dòng trong một nhóm, nhưng cách xử lý khi các dòng có giá trị bằng nhau (Ties) là điểm khác biệt cốt tử.

3.1. Bảng phân biệt hành vi

Hàm	Cách xử lý giá trị bằng nhau	Chuỗi số sinh ra (Ví dụ với giá trị: 5000, 5000, 3000)
ROW_NUMBER()	Đánh số tăng dần liên tục, không bao giờ trùng, gán tùy tiện nếu giá trị bằng nhau.	1, 2, 3
RANK()	Các dòng bằng nhau nhận cùng hạng; để lại khoảng trống (Gap) cho hạng kế tiếp.	1, 1, 3 (Nhảy qua số 2)
DENSE_RANK()	Các dòng bằng nhau nhận cùng hạng; không để lại khoảng trống.	1, 1, 2 (Đảm bảo số liên tục)

SQL

SELECT 
    department,
    employee_name,
    amount,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS dense_rnk
FROM analytics_lab.sales_records
WHERE department = 'IT Hardware';

Kết quả:

Plaintext

 department  | employee_name | amount  | row_num | rnk | dense_rnk 
-------------+---------------+---------+---------+-----+-----------
 IT Hardware | Alice         | 5000.00 |       1 |   1 |         1
 IT Hardware | Charlie       | 5000.00 |       2 |   1 |         1
 IT Hardware | Bob           | 3000.00 |       3 |   3 |         2
 IT Hardware | David         | 2000.00 |       4 |   4 |         3

Ứng dụng thực tế (Top-N per group): Tìm nhân viên có doanh số cao nhất ở mỗi phòng ban:

SQL

WITH ranked_sales AS (
    SELECT *,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS rk
    FROM analytics_lab.sales_records
)
SELECT department, employee_name, amount 
FROM ranked_sales 
WHERE rk = 1;

(Lưu ý: Không thể đặt trực tiếp WHERE DENSE_RANK() = 1 ở cùng cấp câu lệnh, vì Window Function được thực thi sau mệnh đề WHERE và HAVING).

4. Nhóm hàm dịch chuyển: LEAD() và LAG()

Trong SQL cổ điển, để so sánh một giá trị của dòng hiện tại với dòng đứng ngay trước đó (ví dụ: doanh thu ngày hôm nay tăng/giảm bao nhiêu % so với ngày hôm qua), bạn phải thực hiện phép SELF JOIN rất chậm.

LAG() và LEAD() cho phép bạn "nhìn" về quá khứ hoặc "ngó" sang tương lai mà không cần join:

• LAG(column, offset, default): Lấy giá trị của cột đó ở dòng đứng trước đó offset vị trí (mặc định offset là 1).

• LEAD(column, offset, default): Lấy giá trị của cột đó ở dòng đứng sau đó offset vị trí.

SQL

SELECT 
    department,
    sale_date,
    amount,
    -- Lấy doanh số của ngày trước đó cùng phòng ban:
    LAG(amount, 1, 0.00) OVER (PARTITION BY department ORDER BY sale_date) AS prev_day_amount,
    -- Tính mức chênh lệch doanh số:
    amount - LAG(amount, 1, amount) OVER (PARTITION BY department ORDER BY sale_date) AS diff_amount,
    -- Lấy doanh số của ngày tiếp theo:
    LEAD(amount, 1, 0.00) OVER (PARTITION BY department ORDER BY sale_date) AS next_day_amount
FROM analytics_lab.sales_records;

5. Khung cửa sổ chuyên sâu (Window Frames): ROWS vs RANGE

Khi sử dụng các hàm tổng hợp như SUM(), AVG(), COUNT() bên trong mệnh đề OVER, kết quả phụ thuộc rất lớn vào định nghĩa Window Frame.

Nếu bạn chỉ viết: SUM(amount) OVER (ORDER BY sale_date), PostgreSQL sẽ áp dụng một frame mặc định:

$$\text{RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW}$$

5.1. Cú pháp định nghĩa Frame

Plaintext

{ ROWS | RANGE | GROUPS } BETWEEN frame_start AND frame_end

Các mốc chỉ định vị trí:

• UNBOUNDED PRECEDING: Từ dòng đầu tiên của Partition.

• n PRECEDING: Lùi về trước n dòng.

• CURRENT ROW: Dòng hiện tại.

• n FOLLOWING: Tiến về sau n dòng.

• UNBOUNDED FOLLOWING: Đến dòng cuối cùng của Partition.

5.2. Sự khác biệt cốt tử giữa ROWS và RANGE

• ROWS: Tính toán dựa trên số lượng dòng vật lý.

• RANGE: Tính toán dựa trên giá trị logic của cột trong ORDER BY. Nếu nhiều dòng có cùng giá trị ORDER BY, chúng được gộp chung vào một nhóm xử lý, dễ dẫn đến kết quả cộng dồn nhảy vọt không mong muốn.

Dữ liệu: amount = [5000, 5000, 3000]

Khi chạy: SUM(amount) OVER (ORDER BY amount DESC ...)

Với ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:
- Row 1: 5000
- Row 2: 5000 + 5000 = 10000 (Cộng dồn chuẩn từng dòng)
- Row 3: 10000 + 3000 = 13000

Với RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (Mặc định khi có ORDER BY):
- Row 1 & 2 cùng giá trị 5000 ──► Nhảy vọt lên 10000 ngay ở Row 1!
- Row 1: 10000
- Row 2: 10000
- Row 3: 13000

Quy tắc thực chiến: Khi tính toán cộng dồn lũy kế (Running Total) hoặc trung bình trượt (Moving Average), luôn luôn sử dụng ROWS BETWEEN ... một cách tường minh để tránh lỗi logic và giúp câu lệnh chạy nhanh hơn (PostgreSQL xử lý ROWS tốn ít CPU hơn RANGE).

5.3. Kịch bản ứng dụng: Tính trung bình trượt 3 ngày (Moving Average)

SQL

SELECT 
    sale_date,
    amount,
    -- Trung bình trượt của: Ngày hôm trước + Ngày hiện tại + Ngày tiếp theo
    AVG(amount) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_3_days,
    -- Tổng cộng dồn từ đầu đến ngày hiện tại (Running Total chuẩn xác):
    SUM(amount) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM analytics_lab.sales_records
WHERE department = 'IT Hardware';

6. Mệnh đề WINDOW độc lập: Tối ưu và Clean Code

Nếu câu truy vấn của bạn sử dụng nhiều Window Function có cùng chung một cấu trúc OVER, việc lặp lại định nghĩa cửa sổ nhiều lần vừa làm câu lệnh dài dòng, vừa khiến Query Planner phải khởi tạo nhiều cấu trúc phân vùng giống hệt nhau.

PostgreSQL hỗ trợ định nghĩa một cửa sổ độc lập bằng mệnh đề WINDOW đặt ở cuối câu truy vấn:

SQL

-- Viết lặp lại (Chưa tối ưu code):
SELECT 
    employee_name,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC),
    RANK()       OVER (PARTITION BY department ORDER BY amount DESC),
    SUM(amount)  OVER (PARTITION BY department ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM analytics_lab.sales_records;

-- Tối ưu hóa bằng mệnh đề WINDOW:
SELECT 
    employee_name,
    ROW_NUMBER() OVER w,
    RANK()       OVER w,
    SUM(amount)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM analytics_lab.sales_records
WINDOW w AS (PARTITION BY department ORDER BY amount DESC);

7. Thứ tự thực thi của Window Function trong SQL Engine

Để tránh các lỗi cú pháp kinh điển, hãy ghi nhớ vị trí thực thi của Window Function trong chu trình xử lý của SQL:

$$\text{FROM} \longrightarrow \text{JOIN} \longrightarrow \text{WHERE} \longrightarrow \text{GROUP BY} \longrightarrow \text{HAVING} \longrightarrow \mathbf{WINDOW} \longrightarrow \text{SELECT} \longrightarrow \text{DISTINCT} \longrightarrow \text{ORDER BY} \longrightarrow \text{LIMIT}$$

Hai hệ quả bắt buộc phải nhớ:

1. Window Function có thể nhìn thấy kết quả của GROUP BY: Bạn hoàn toàn có thể viết SUM(COUNT(*)) OVER ().

2. Không được dùng Window Function trong WHERE hoặc HAVING: Vì WHERE chạy trước WINDOW. Nếu muốn lọc kết quả dựa trên giá trị của Window Function (ví dụ WHERE rnk <= 3), bạn bắt buộc phải bọc nó bên trong một CTE hoặc Subquery.

8. Tóm tắt & Bài tiếp theo

• Window Function cho phép tính toán trên một tập hợp con nhưng không làm gộp dòng như GROUP BY.

• Phân biệt rõ: ROW_NUMBER() (số thứ tự liên tục duy nhất), RANK() (nhảy cóc khi bằng điểm), và DENSE_RANK() (xếp hạng sát nhau không để khoảng trống).

• LEAD() và LAG() loại bỏ nhu cầu SELF JOIN khi so sánh dữ liệu giữa các mốc thời gian liền kề.

• Khi tính Running Total, luôn dùng ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW thay vì để RANGE mặc định.

• Tái sử dụng cấu trúc cửa sổ bằng mệnh đề WINDOW để code sạch và dễ bảo trì.

Bài 17 xem tiếp: Các mệnh đề tổng hợp nâng cao: GROUPING SETS, ROLLUP và CUBE — chúng ta sẽ tìm hiểu cách tạo các báo cáo tổng hợp đa chiều (OLAP report), tính toán Subtotal và Grand Total chỉ trong một câu lệnh duy nhất mà không cần phải dùng UNION ALL nhiều lần.