Posted inDatabase

Vượt xa các truy vấn cơ bản: Làm chủ SQL Window Functions cho phân tích thực tế

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Rào cản hiệu năng: Cuộc sống trước khi có Window Functions

Hồi năm 2019, tôi đang xây dựng một bảng điều khiển (dashboard) doanh số hàng ngày mà suýt chút nữa đã bị treo vì khối lượng dữ liệu quá lớn. Yêu cầu rất đơn giản: hiển thị doanh thu hàng ngày, tổng lũy kế trong tháng và so sánh với ngày hôm trước. Lúc đầu, tôi xử lý việc này ở tầng ứng dụng. Tôi lấy 500 dòng dữ liệu vào một tập lệnh Python và sử dụng một vòng lặp đơn giản. Nó hoạt động hoàn hảo—cho đến khi tập dữ liệu lên tới 100.000 dòng và dashboard bắt đầu mất 15 giây để tải.

Ban đầu, tôi cố gắng chuyển logic vào SQL bằng cách sử dụng self-join và subquery. Nếu bạn đã từng thử tính tổng lũy kế bằng cách join một bảng với chính nó, bạn sẽ hiểu sự ức chế đó. Các câu truy vấn là một cơn ác mộng về bảo trì. Hiệu năng giảm sút vì công cụ cơ sở dữ liệu phải quét bảng 5 triệu dòng lặp đi lặp lại cho mỗi dòng kết quả. Đó là lúc tôi nhận ra mình cần ngừng coi SQL như một cái thùng chứa dữ liệu đơn thuần và bắt đầu sử dụng Window Functions.

Các cơ sở dữ liệu hiện đại như MySQL 8.0+, PostgreSQL và SQL Server cung cấp các hàm này như một yếu tố bắt buộc cho các hệ thống hiện nay. Chúng cho phép bạn thực hiện các tính toán trên một tập hợp các hàng cụ thể liên quan đến hàng hiện tại. Quan trọng nhất là chúng thực hiện việc này mà không làm gộp dữ liệu thành một dòng tóm tắt duy nhất như mệnh đề GROUP BY thông thường vẫn làm.

Bí quyết nằm ở: OVER và PARTITION BY

Mệnh đề OVER() là trái tim của mọi Window Function. Nó báo hiệu cho công cụ cơ sở dữ liệu rằng: “Hãy thực hiện tính toán này trên một ‘cửa sổ’ dữ liệu cụ thể.” Bên trong mệnh đề đó, bạn xác định ranh giới của mình bằng cách sử dụng PARTITION BY và ORDER BY.

Tại sao GROUP BY thường không đáp ứng đủ nhu cầu

Các kỹ sư thường vấp ngã ở điểm này. Khi sử dụng GROUP BY, về cơ bản bạn đang “nén” dữ liệu của mình lại. Bạn mất đi chi tiết của từng hàng riêng lẻ vì mọi thứ được tổng hợp thành một giá trị duy nhất cho mỗi nhóm. Window Functions thì khác. Mỗi hàng vẫn giữ được đặc điểm riêng, nhưng hàm sẽ bổ sung thêm một giá trị đã tính toán—như mức trung bình của phòng ban hoặc thứ hạng—ngay cạnh dữ liệu thô.

Xác định ranh giới dữ liệu

  • PARTITION BY: Hoạt động giống như một bộ lọc cục bộ. Nó phân đoạn tập kết quả thành các nhóm riêng biệt và hàm sẽ tính toán lại từ đầu cho mỗi nhóm đó.
  • ORDER BY: Xác định thứ tự của các hàng trong mỗi nhóm. Điều này cực kỳ quan trọng cho việc tính tổng tích lũy hoặc phân tích chuỗi thời gian, nơi mà thứ tự thời gian là tất cả.
-- Mẫu cú pháp chuẩn
SELECT 
    column_name,
    FUNCTION() OVER (PARTITION BY group_col ORDER BY sort_col) as result_alias
FROM table_name;

Logic thực tế: Giải quyết các vấn đề thực tiễn

Hãy cùng xem xét một bảng sales thực tế chứa employee_name, department, sale_amount và sale_date. Mặc dù tôi đang sử dụng cú pháp PostgreSQL, các ví dụ này đều là chuẩn chung cho hầu hết các phương ngữ SQL hiện đại.

1. Giải quyết bài toán bảng xếp hạng

Bảng xếp hạng là một trường hợp sử dụng kinh điển. Việc chọn đúng hàm xếp hạng phụ thuộc hoàn toàn vào cách bạn xử lý các trường hợp bằng điểm. Giả sử hai nhân viên Alice và Bob đều bán được chính xác 5.000 USD tiền phần mềm.

  • ROW_NUMBER(): Gán một thứ hạng duy nhất (1 và 2) bất kể có bằng điểm hay không.
  • RANK(): Gán cả hai cùng vị trí thứ 1, nhưng người tiếp theo sẽ là thứ 3.
  • DENSE_RANK(): Gán cả hai cùng vị trí thứ 1, và người tiếp theo sẽ là thứ 2.
SELECT 
    employee_name, 
    department, 
    sale_amount,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) as dept_rank
FROM sales;

Bằng cách phân đoạn theo phòng ban, chúng ta có thể tạo ra các bảng xếp hạng riêng biệt cho Sales, Marketing và Engineering cùng một lúc mà không cần nhiều câu truy vấn.

2. Tính tổng lũy kế hiệu quả

Đây là phần tôi tiết kiệm được nhiều thời gian xử lý nhất trong dự án báo cáo năm 2019 đó. Tổng tích lũy chỉ đơn giản là phép cộng dồn tăng dần khi bạn di chuyển xuống các hàng. Bằng cách thêm ORDER BY vào trong OVER, SQL sẽ tự động tính toán tổng từ hàng đầu tiên của phân đoạn cho đến hàng hiện tại.

SELECT 
    sale_date, 
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date) as cumulative_revenue
FROM sales;

3. Phát hiện xu hướng với LAG và LEAD

Tốc độ là yếu tố then chốt khi giám sát hệ thống hoặc doanh thu. LAG() lấy dữ liệu từ hàng trước đó, trong khi LEAD() nhìn về phía trước. Gần đây tôi đã sử dụng tính năng này để phát hiện các đỉnh trễ của máy chủ bằng cách so sánh thời gian phản hồi mili giây hiện tại với chỉ số được ghi lại 60 giây trước đó.

Đối với tăng trưởng kinh doanh, đây là cách sạch sẽ nhất để tính toán sự thay đổi qua từng ngày:

SELECT 
    sale_date, 
    sale_amount,
    LAG(sale_amount) OVER (ORDER BY sale_date) as yesterday_revenue,
    sale_amount - LAG(sale_amount) OVER (ORDER BY sale_date) as daily_delta
FROM sales;

Sử dụng LAG hiệu quả hơn đáng kể so với việc self-join theo điều kiện t1.date = t2.date - 1, vốn thường thất bại nếu ngày lễ hoặc ngày cuối tuần tạo ra khoảng trống trong các bản ghi.

4. Làm mượt dữ liệu với Trung bình trượt

Nhiễu là kẻ thù của phân tích. Một giao dịch giá trị cao đơn lẻ có thể làm sai lệch cái nhìn của bạn về hiệu suất của cả tuần. Tôi thường áp dụng trung bình trượt 7 ngày để làm phẳng các giá trị ngoại lai này. Điều này đòi hỏi một “khung cửa sổ” (window frame) sử dụng ROWS BETWEEN.

SELECT 
    sale_date, 
    sale_amount,
    AVG(sale_amount) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as weekly_rolling_avg
FROM sales;

Logic này hướng dẫn cơ sở dữ liệu nhìn vào hàng hiện tại cộng với sáu hàng ngay trước đó. Thực hiện việc này với SQL tiêu chuẩn sẽ yêu cầu các subquery phức tạp; ở đây, nó chỉ là một dòng mã duy nhất và dễ đọc.

Những chi phí tiềm ẩn

Mặc dù các công cụ này rất mạnh mẽ, nhưng chúng không miễn phí. Tôi đã học được cách thận trọng khi áp dụng Window Functions cho các bảng có hàng chục triệu dòng, đặc biệt nếu mệnh đề ORDER BY buộc phải thực hiện sắp xếp lớn trên đĩa cứng. Nếu cột phân đoạn của bạn không được lập chỉ mục, bạn có thể thấy mức sử dụng CPU tăng vọt.

Để giảm thiểu điều này, tôi thường lọc tập dữ liệu nhiều nhất có thể bằng cách sử dụng Biểu thức bảng chung (CTE) trước. Bằng cách thu hẹp phạm vi trong 30 ngày qua trước khi áp dụng Window Function, bạn sẽ giữ cho mức sử dụng bộ nhớ thấp và đảm bảo báo cáo trả về trong vài mili giây thay vì vài phút.

Vượt ra khỏi vòng lặp

Những tác vụ nặng nề không nên diễn ra trong mã ứng dụng nếu cơ sở dữ liệu của bạn có thể làm điều đó nhanh hơn. Việc chuyển logic phức tạp vào tầng cơ sở dữ liệu giúp phản hồi API nhanh hơn và mã backend của bạn sạch sẽ hơn đáng kể. Window Functions chính là cầu nối giúp quá trình chuyển đổi này khả thi.

Tôi khuyên bạn nên thử nghiệm một hàm DENSE_RANK() đơn giản trên một tập dữ liệu mà bạn đã nắm rõ. Một khi bạn thấy được sự cải thiện về hiệu năng và việc giảm bớt độ phức tạp của mã nguồn, bạn sẽ không muốn quay lại cách lồng các subquery cũ kỹ nữa. Đó là một bước tiến cơ bản để trở thành một kỹ sư dữ liệu thực thụ, người hiểu cách trích xuất những thông tin chi tiết có giá trị một cách hiệu quả.

Share:
Tags: