PostgreSQL Bài 45: Window Functions: Hàm cửa sổ và Ứng dụng thực chiến (OVER, PARTITION BY, ROW_NUMBER)

Chào mừng bạn đến với Bài 45, khép lại hành trình chinh phục các kỹ thuật truy vấn dữ liệu nâng cao trong PostgreSQL.

Nếu như GROUP BY buộc bạn phải gộp nhiều dòng lại thành một (làm mất chi tiết từng dòng) để tính tổng/trung bình, thì Window Functions (Hàm cửa sổ) ra đời để giải quyết giới hạn đó. Nó cho phép bạn thực hiện các phép tính phân tích (như xếp hạng, tính lũy kế, so sánh dòng hiện tại với dòng trước/sau) trên một tập hợp các dòng có liên quan, trong khi vẫn giữ nguyên vẹn từng dòng dữ liệu gốc.

Bài học này sẽ đi sâu vào cú pháp cốt lõi OVER (), cách chia cửa sổ bằng PARTITION BY, và các hàm ứng dụng thực chiến như ROW_NUMBER, RANK, LAG, LEAD.

1. Window Functions khác GROUP BY như thế nào?

Hãy tưởng tượng bạn có bảng nhân viên employees:

SQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary NUMERIC
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'IT', 5000),
('Bob', 'IT', 6000),
('Charlie', 'IT', 4500),
('David', 'HR', 4000),
('Eve', 'HR', 4200),
('Frank', 'Sales', 5500);

Bài toán: Bạn muốn in ra danh sách tất cả nhân viên, nhưng kèm theo một cột hiển thị Lương trung bình của phòng ban mà họ đang làm việc để tiện so sánh.

Nếu dùng GROUP BY (Sai lầm):

SQL

SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

Kết quả chỉ trả về 3 dòng (IT, HR, Sales). Tên của Alice, Bob, Charlie... đã bị gộp mất!

Nếu dùng Window Function (Chính xác):

SQL

SELECT 
    name, 
    department, 
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

Kết quả:

name	departments	salary	avg_dept_salary
Alice	IT	5000	5166.66
Bob	IT	6000	5166.66
Charlie	IT	4500	5166.66
David	HR	4000	4100.00
Eve	HR	4200	4100.00
Frank	Sales	5500	5500.00

Nhận xét: Hàm AVG được tính toán trên "cửa sổ" là từng phòng ban, nhưng các dòng chi tiết (name, salary) vẫn được bảo toàn trọn vẹn.

2. Giải phẫu cú pháp mệnh đề OVER ()

Mệnh đề OVER là linh hồn của Window Functions. Bất kỳ hàm thống kê nào (như SUM, COUNT, MAX) nếu đi kèm với OVER sẽ biến thành Window Function.

Cú pháp tổng quát:

SQL

function_name(args) OVER (
    [PARTITION BY expression, ...]
    [ORDER BY expression [ASC | DESC], ...]
    [FRAME_CLAUSE]
)

2.1. PARTITION BY (Chia nhóm cửa sổ)

Tương tự như GROUP BY, nó gom các dòng có cùng giá trị thành một "Cửa sổ" (Window) để tính toán. Nếu bạn bỏ trống OVER () (không có PARTITION BY), cửa sổ tính toán sẽ là toàn bộ bảng.

SQL

-- Lấy tổng lương của TOÀN CÔNG TY hiển thị trên mỗi dòng
SELECT name, salary, SUM(salary) OVER () AS total_company_salary 
FROM employees;

2.2. ORDER BY (Sắp xếp trong cửa sổ)

Dùng để xác định thứ tự tính toán các dòng bên trong một cửa sổ. Nó là yếu tố bắt buộc đối với các hàm xếp hạng (RANK, ROW_NUMBER) và các bài toán tính tổng lũy kế (Running Total).

3. Các nhóm Window Functions cực mạnh trên Production

3.1. Nhóm Hàm Xếp hạng (Ranking)

Đây là ứng dụng phổ biến nhất để giải quyết bài toán: "Lấy ra Top N sản phẩm theo từng Category".

• ROW_NUMBER(): Đánh số thứ tự liên tiếp (1, 2, 3...) bất kể giá trị có trùng nhau hay không.

• RANK(): Đánh số thứ tự, nếu có 2 người bằng điểm nhau sẽ cùng xếp hạng 1, người tiếp theo xếp hạng 3 (Bỏ qua hạng 2).

• DENSE_RANK(): Giống RANK nhưng không bỏ qua thứ hạng (1, 1, 2, 3).

Thực chiến: Lấy ra 2 người lương cao nhất của TỪNG phòng ban

SQL

WITH RankedEmployees AS (
    SELECT 
        name, 
        department, 
        salary,
        -- Đánh số thứ tự giảm dần theo lương, reset lại từ 1 khi sang phòng ban mới
        ROW_NUMBER() OVER (
            PARTITION BY department 
            ORDER BY salary DESC
        ) AS rank_in_dept
    FROM employees
)
-- Dùng CTE (đã học ở Bài 44) để bọc lại và lọc WHERE
SELECT * FROM RankedEmployees WHERE rank_in_dept <= 2;

3.2. Nhóm Hàm Phân tích Trượt (Lead & Lag)

Dùng để so sánh giá trị của dòng hiện tại với dòng trước đó hoặc dòng sau đó mà không cần dùng SELF JOIN. Rất hữu ích trong phân tích chứng khoán, thay đổi doanh thu qua từng tháng, hoặc độ trễ giữa các sự kiện của một User.

• LAG(col, offset): Lấy giá trị của dòng nằm trước dòng hiện tại.

• LEAD(col, offset): Lấy giá trị của dòng nằm sau dòng hiện tại.

Thực chiến: Tính độ chênh lệch lương so với người xếp ngay dưới mình trong cùng phòng ban

SQL

SELECT 
    name, 
    department, 
    salary,
    -- Lấy lương của người có mức lương ngay dưới (sắp xếp giảm dần)
    LEAD(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS next_lower_salary,
    -- Tính phần chênh lệch
    salary - LEAD(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS diff_to_next
FROM employees;

3.3. Bài toán Tính Tổng Lũy kế (Running Total) với Frame Clause

Một lỗi logic kinh điển khi dùng Window Function là quên mất ảnh hưởng của ORDER BY lên khung tính toán (Frame).

Nếu bạn muốn tính tổng doanh thu lũy kế (Cộng dồn qua từng ngày):

SQL

CREATE TABLE daily_sales (day DATE, revenue INT);
INSERT INTO daily_sales VALUES ('2026-10-01', 100), ('2026-10-02', 200), ('2026-10-03', 150);

SELECT 
    day, 
    revenue,
    SUM(revenue) OVER (ORDER BY day ASC) AS running_total
FROM daily_sales;

Kết quả:

day	revenue	running_total
2026-10-01	100	100
2026-10-02	200	300 (100+200)
2026-10-03	150	450 (100+200+150)

Tại sao có được kết quả này? Bởi vì khi bạn thêm ORDER BY vào OVER, PostgreSQL tự động áp dụng một Frame mặc định là: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (Tính từ dòng đầu tiên của cửa sổ đến dòng hiện tại). Nhờ đó, nó tự động tạo ra tính chất lũy kế!

4. Bẫy Hiệu năng (Performance Pitfalls) khi dùng Window Functions

1. Window Functions luôn chạy cuối cùng: Nó được tính toán sau khi mệnh đề WHERE, GROUP BY, và HAVING đã lọc xong dữ liệu. Đó là lý do bạn không thể viết WHERE ROW_NUMBER() <= 2 trực tiếp, mà bắt buộc phải bọc nó trong CTE hoặc Subquery.

2. Chi phí Sắp xếp (Sort Cost): Các hàm yêu cầu ORDER BY bên trong OVER sẽ buộc engine phải thực hiện Sắp xếp (Sort). Nếu tập dữ liệu lớn, thao tác này sẽ ngốn work_mem và có thể bị tràn ra đĩa (Spill to Disk).

   • Cách tối ưu: Nếu bảng đã có Index sẵn trên cột ORDER BY hoặc trùng với PARTITION BY, Query Planner có thể tận dụng index để bỏ qua bước Sort.

5. Tổng kết Phần nâng cao SQL (Bài 43 - 45)

• Temporary Tables / Materialized Views (Bài 43): Cho phép bạn lưu trữ kết quả tính toán trung gian để tái sử dụng, biến các query phân tích nặng nề thành các bảng tạm tốc độ cao, hỗ trợ Refresh ngầm (CONCURRENTLY).

• CTE & Đệ quy (Bài 44): WITH giúp chia nhỏ logic query thành các khối dễ đọc; WITH RECURSIVE là chìa khóa vạn năng để duyệt cấu trúc phân cấp (cây danh mục) không giới hạn độ sâu.

• Window Functions (Bài 45): Thay đổi tư duy viết báo cáo. Không cần lạm dụng JOIN hay Subquery để lấy Top N hay so sánh chéo dòng. Cú pháp OVER(PARTITION BY ... ORDER BY ...) giải quyết gọn gàng các bài toán thống kê, xếp hạng, và tính toán trượt (Lag/Lead).

Lộ trình tiếp theo: Khép lại kỹ năng viết truy vấn và phân mảnh dữ liệu, chúng ta sẽ bước sang một khía cạnh hoàn toàn mới nhưng mang tính chất sống còn của hệ thống: Bảo mật Nâng cao & Mã hóa Dữ liệu (Encryption & Advanced Security).

Bài 46 xem tiếp: Mã hóa Dữ liệu Nhạy cảm: Transparent Data Encryption (TDE) vs Application-Level Encryption — Khi lưu trữ thông tin thẻ tín dụng, CCCD, hay hồ sơ y tế, việc cấu hình SSL hay phân quyền RLS là chưa đủ. Nếu kẻ gian trộm được file vật lý ổ cứng, chúng ta phải làm gì? Khám phá cách kết hợp pgcrypto và kiến trúc mã hóa tại tầng ứng dụng để bảo vệ dữ liệu tuyệt đối.