Những lỗi thường gặp khi dùng window functions

Bây giờ là lúc nói về những khó khăn có thể gặp phải khi làm việc với window functions. Như thường lệ, trong lập trình (và cả ngoài đời), tốt nhất là học từ lỗi của người khác. Mình sẽ phân tích các lỗi điển hình mà newbie hay mắc phải, thậm chí cả dev có kinh nghiệm cũng dính, và học cách tránh chúng nhé.

Lỗi số 1: Dùng sai PARTITION BY

Một trong những lỗi phổ biến nhất là quên hoặc đặt sai tham số PARTITION BY, nhất là khi bạn muốn chia dữ liệu thành các nhóm. Nếu không có nó, PostgreSQL sẽ coi tất cả các dòng là một nhóm lớn, dẫn đến kết quả có thể hoàn toàn không như bạn mong đợi.

Giả sử bạn có bảng sales, chứa dữ liệu về bán hàng:

Bạn muốn tìm tổng cộng dồn (SUM()) doanh số theo tháng cho từng khu vực. Có thể viết truy vấn như sau:

SELECT
    region,
    month,
    SUM(total) OVER (ORDER BY month) AS running_total
FROM 
    sales;

Kết quả:

Nhìn qua thì tưởng đúng, nhưng kết quả rõ ràng không như mong đợi, vì tổng cộng dồn được tính cho tất cả các dòng, không phải theo từng khu vực. Vấn đề là bạn đã quên thêm PARTITION BY region.

Code đã sửa:

SELECT 
    region,
    month,
    SUM(total) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM 
    sales;

Kết quả:

Bây giờ mọi thứ đã đúng: dữ liệu được nhóm theo khu vực, và tổng cộng dồn được tính riêng cho từng khu vực.

Lỗi số 2: Sai thứ tự trong ORDER BY

ORDER BY bên trong OVER() kiểm soát thứ tự các dòng trong phạm vi window. Nếu thứ tự sai, kết quả sẽ bất ngờ lắm luôn.

Bạn muốn tìm tổng cộng dồn doanh số, sắp xếp theo tháng giảm dần. Có thể viết truy vấn như sau:

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month DESC) AS running_total
FROM 
    sales;

Kết quả:

Nhìn qua tưởng đúng, nhưng chú ý nhé: các dòng được nhóm theo tháng, nhưng tổng cộng dồn không đúng do thứ tự giảm dần. Kết quả bị lộn xộn.

Cách sửa: viết lại truy vấn để dùng đúng thứ tự ORDER BY:

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month ASC) AS running_total
FROM 
    sales;

Lỗi số 3: Dùng window functions mà không có index

Window functions thường làm việc với lượng dữ liệu lớn, và nếu không có index trên các cột quan trọng thì hiệu năng sẽ tụt dốc không phanh.

Ví dụ: bạn có bảng large_sales với hàng triệu dòng, và muốn tính rank của doanh số:

SELECT
    id,
    total,
    RANK() OVER (ORDER BY total DESC) AS rank
FROM 
    large_sales;

Với dữ liệu nhỏ thì truy vấn chạy nhanh, nhưng với dữ liệu lớn thì chờ mòn mỏi luôn.

Cách sửa: thêm index cho cột dùng trong ORDER BY:

CREATE INDEX idx_total ON large_sales(total DESC);

Bây giờ truy vấn sẽ chạy nhanh hơn nhiều.

Lỗi số 4: Không hiểu window do ROWS hoặc RANGE tạo ra

Khi dùng ROWS và RANGE, cần hiểu cách chúng xác định phạm vi window. Hiểu sai mấy từ khóa này là kết quả sẽ bất ngờ lắm.

Ví dụ: bạn muốn tính trung bình trượt doanh số cho tháng hiện tại và hai tháng trước:

SELECT
    month,
    AVG(total) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Nếu thay ROWS bằng RANGE:

SELECT
    month,
    AVG(total) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Kết quả sẽ khác, vì RANGE làm việc với giá trị trong khoảng, không phải số dòng cụ thể.

Lỗi số 5: Lạm dụng window functions

Dùng nhiều window functions trong một truy vấn có thể gây lặp lại tính toán và giảm hiệu năng.

Ví dụ:

SELECT 
    id,
    total,
    SUM(total) OVER (PARTITION BY region) AS region_total,
    SUM(total) OVER (PARTITION BY region) / COUNT(total) OVER (PARTITION BY region) AS region_avg
FROM 
    sales;

Ở đây SUM(total) và COUNT(total) bị tính lại nhiều lần cho mỗi dòng.

Cách sửa: rút gọn truy vấn bằng subquery hoặc CTE:

WITH cte_region_totals AS (
    SELECT 
        region,
        SUM(total) AS region_total,
        COUNT(total) AS region_count
    FROM 
        sales
    GROUP BY 
        region
)
SELECT 
    s.id,
    s.total,
    t.region_total,
    t.region_total / t.region_count AS region_avg
FROM 
    sales s
JOIN 
    cte_region_totals t ON s.region = t.region;

Tips để tránh lỗi

Kiểm tra PARTITION BY và ORDER BY: luôn xác nhận window đã đặt đúng chưa.

Tạo index cho dữ liệu: nhất là khi dùng sort (ORDER BY) hoặc filter.

Dùng CTE cho tính toán lặp lại: giúp giảm các bước trùng lặp.

Xem execution plan: dùng EXPLAIN và EXPLAIN ANALYZE để hiểu PostgreSQL xử lý truy vấn thế nào.

Test trên dữ liệu thực tế: đảm bảo kết quả đúng như mong đợi và giải quyết đúng bài toán.