Ví dụ sử dụng window functions để phân tích dữ liệu

Bây giờ bạn đã sẵn sàng lặn sâu vào thế giới ví dụ thực tế để xem mọi thứ hoạt động thế nào trong các bài toán đời thường nhé!

Ví dụ: tính rank doanh số theo vùng

Giả sử mình có bảng sales, chứa dữ liệu về doanh số ở các vùng khác nhau. Nhiệm vụ là xác định rank doanh số cho từng vùng.

Bài toán: tìm rank (RANK) doanh số cho từng vùng

SELECT
    region,
    sales_amount,
    RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM 
    sales;

Kết quả:

Lưu ý là mình dùng PARTITION BY region để tính rank riêng cho từng vùng. Nếu không dùng PARTITION BY thì rank sẽ tính toàn bộ bảng luôn.

Ví dụ: tính tổng tích lũy doanh thu

Giờ thử xử lý bảng transactions để tính tổng tích lũy (cumulative sum) doanh thu cho từng khách hàng nhé.

Bài toán: tính tổng tích lũy cho từng khách hàng

SELECT
    customer_id,
    purchase_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM 
    transactions;

Kết quả:

Điểm mấu chốt ở đây là dùng ORDER BY purchase_date trong OVER() để tổng tích lũy được tính theo thứ tự thời gian.

Ví dụ: chia dữ liệu thành các quantile

Giả sử mình có bảng students, trong đó có tên và điểm kiểm tra. Mình muốn chia học sinh thành 4 nhóm dựa trên điểm số.

Bài toán: chia học sinh thành 4 nhóm dùng NTILE()

SELECT
    name,
    test_score,
    NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM 
    students;

Kết quả truy vấn sẽ như này:

NTILE(4) chia dữ liệu thành 4 nhóm. Học sinh có điểm cao nhất sẽ vào nhóm đầu, điểm thấp nhất vào nhóm cuối.

Ví dụ: phân tích dữ liệu thời gian

Trong bảng site_visits lưu dữ liệu về số lượt truy cập site theo ngày. Nhiệm vụ là tính chênh lệch số lượt truy cập giữa các ngày cho từng site.

Bài toán — tính chênh lệch lượt truy cập giữa các ngày

SELECT
    site_id,
    visit_date,
    visits,
    visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM 
    site_visits;

Kết quả:

Hàm LAG() cho phép lấy giá trị từ dòng trước đó. Nếu không có dữ liệu dòng trước thì kết quả sẽ là NULL. Chi tiết về nó bạn sẽ học ở mấy bài sau nha :P