Google BigQuery - Bài học 16: Window Functions (Analytic Functions) - Phần 1: Thực chiến với ROW_NUMBER(), RANK(), DENSE_RANK() để khử trùng lặp (Deduplication)

Chào anh em! Ở Bài học 15, chúng ta đã giải quyết dứt điểm "cục máu đông" về múi giờ và chuỗi thời gian (Time-series). Hôm nay, chúng ta bước vào một trong những chương quan trọng và "phê" nhất của GoogleSQL: Window Functions (Hàm cửa sổ / Hàm phân tích).

Nếu bạn là một Backend Developer hay Data Engineer, chắc chắn bạn đã từng gặp cơn ác mộng này: Hệ thống Webhook của bên thứ ba bị lỗi mạng và gửi trùng lặp một sự kiện thanh toán 3 lần về hệ thống của bạn. Hoặc bảng log người dùng bị dính hàng vạn bản ghi trùng nhau y hệt do client gửi request retry.

Khi dùng mệnh đề GROUP BY thông thường, bạn buộc phải gom nhóm và làm mất đi toàn bộ các thông tin chi tiết (như mô tả lỗi, thiết bị, địa chỉ IP) của từng dòng. Làm thế nào để lọc bỏ các bản ghi trùng lặp mà vẫn giữ lại vẹn nguyên thông tin của dòng mới nhất?

Câu trả lời nằm ở các hàm xếp hạng trong Window Functions. Hãy cùng mổ xẻ ngay bây giờ!

1. Window Function là gì? Khác gì so với GROUP BY truyền thống?

Nhiều người mới học SQL thường nhầm lẫn giữa Aggregate Functions (kết hợp với GROUP BY) và Analytic Functions (Window Functions). Hãy nhìn vào bảng so sánh trực quan sau để thấy sự khác biệt cốt lõi:

• GROUP BY (Bóp méo không gian): Thu hẹp hàng triệu dòng dữ liệu ban đầu lại thành một vài dòng tổng kết (Ví dụ: 1 triệu dòng đơn hàng gộp lại thành 10 dòng theo ngày). Bạn mất sạch thông tin của từng đơn hàng lẻ tẻ bên trong.

• Window Functions (Bảo toàn không gian): Tính toán các chỉ số xếp hạng, tổng tích lũy, hoặc giá trị trước/sau dựa trên một "cửa sổ" (window) dữ liệu xác định, nhưng giữ nguyên số lượng dòng ban đầu (1 triệu dòng đi vào, vẫn đúng 1 triệu dòng đi ra, nhưng mỗi dòng được đính kèm thêm một cột kết quả tính toán).

Cú pháp thần thánh của Window Function:

Mọi Window Function trong GoogleSQL đều bắt buộc phải đi kèm với mệnh đề OVER():

SQL

FUNCTION_NAME(...) OVER (
    PARTITION BY column_to_group_by  -- Chia nhỏ dữ liệu thành các nhóm độc lập
    ORDER BY column_to_sort          -- Sắp xếp thứ tự bên trong từng nhóm
    [ROWS / RANGE frame_clause]      -- Khung trượt (tùy chọn)
)

2. Bộ ba quyền lực: ROW_NUMBER(), RANK(), và DENSE_RANK()

Để giải quyết bài toán khử trùng lặp (Deduplication), chúng ta phải làm quen với 3 hàm xếp hạng phổ biến nhất. Giả sử ta có 4 điểm số của học sinh trong một lớp: [90, 85, 85, 70]. Hãy xem cách 3 hàm này đối xử với các giá trị trùng nhau:

Phân Tích Chi Tiết Sự Khác Biệt Giữa Các Window Function Xếp Hạng (ROW_NUMBER(), RANK(), DENSE_RANK())

Lựa chọn nào để khử trùng lặp (Deduplication)?

• Đối với bài toán khử trùng lặp (lấy ra 1 bản ghi duy nhất cho mỗi đối tượng), vũ khí duy nhất và chính xác tuyệt đối mà bạn cần dùng chính là ROW_NUMBER()! Nó đảm bảo dù dữ liệu có trùng điểm số hay trùng thời gian đến đâu, mỗi dòng vẫn nhận được một số thứ tự duy nhất phân biệt ($1, 2, 3...$).

3. Thực chiến: Khử trùng lặp bảng Log sự kiện (Deduplication)

Hãy áp dụng ngay vào một bài toán thực tế trên BigQuery. Giả sử bạn có bảng user_events chứa lịch sử thao tác của người dùng, nhưng do lỗi mạng, một event_id có thể bị ghi nhận nhiều lần với cùng một thời điểm hoặc chênh lệch vài mili-giây. Bạn muốn làm sạch bảng này, chỉ giữ lại bản ghi mới nhất cho mỗi event_id.

Bước sử dụng CTE (WITH) kết hợp ROW_NUMBER():

SQL

WITH RankedEvents AS (
    SELECT 
        event_id,
        user_id,
        action_name,
        created_at,
        payload,
        -- Đánh số thứ tự cho các dòng trùng event_id, sắp xếp dòng mới nhất lên đầu tiên (DESC)
        ROW_NUMBER() OVER (
            PARTITION BY event_id 
            ORDER BY created_at DESC
        ) AS rn
    FROM `my-project.core_dwh.user_events`
    WHERE DATE(created_at) = '2026-10-18'
)
-- Chỉ lọc lấy những dòng có số thứ tự bằng 1 (Dòng mới nhất)
SELECT 
    event_id,
    user_id,
    action_name,
    created_at,
    payload
FROM RankedEvents
WHERE rn = 1;

Cơ chế hoạt động vô cùng mượt mà:

1. PARTITION BY event_id: Gom tất cả các dòng có cùng một event_id vào chung một "nhóm" (giống như gom theo nhóm).

2. ORDER BY created_at DESC: Sắp xếp các dòng trong từng nhóm theo thời gian giảm dần (dòng nào xảy ra sau cùng / mới nhất sẽ đứng ở vị trí số 1).

3. ROW_NUMBER() AS rn: Gán số thứ tự 1, 2, 3... cho từng dòng trong nhóm.

4. Ở tầng SELECT bên ngoài, câu lệnh WHERE rn = 1 bóc trọn vẹn toàn bộ các dòng số 1 (dòng mới nhất), vứt bỏ toàn bộ các bản ghi trùng lặp phía sau (rn = 2, 3...).

4. Tối ưu hiệu năng Window Functions trên BigQuery: Tránh bẫy "Shuffle quá đà"

Mặc dù Window Functions cực kỳ mạnh mẽ, nhưng nếu không cẩn thận, chúng có thể ngốn lượng lớn tài nguyên tính toán của các Slots. Hãy ghi nhớ 2 nguyên tắc tối ưu sau:

1. Tận dụng PARTITION BY để giảm phạm vi tính toán:

   Thay vì bắt Dremel phải sắp xếp (ORDER BY) toàn bộ hàng tỷ dòng của cả bảng trong một cửa sổ duy nhất (gây ra hiện tượng dồn cục dữ liệu tại một Node - Data Skew), mệnh đề PARTITION BY sẽ chia nhỏ dữ liệu ra hàng triệu nhóm độc lập. Các Slots có thể xử lý song song từng nhóm một trên các máy chủ khác nhau.

2. Lọc dữ liệu (Filtering) trước khi gọi Window Function:

   Đừng bao giờ gọi ROW_NUMBER() trên toàn bộ bảng lịch sử 5 năm rồi mới đem đi cắt ngày ở ngoài. Hãy luôn dùng mệnh đề WHERE để lọc Partition ngày tháng cần xử lý ngay bên trong CTE trước, sau đó mới áp dụng Window Function trên tập dữ liệu đã thu gọn đó!

Tổng kết Bài học 16

Window Functions là kỹ thuật phân tích nâng cao định hình ranh giới giữa một lập trình viên SQL cơ bản và một Data Engineer chuyên nghiệp:

1. Bảo toàn không gian: Khác với GROUP BY, Window Functions tính toán trên cửa sổ dữ liệu nhưng giữ nguyên toàn bộ số lượng dòng ban đầu.

2. ROW_NUMBER() là vũ khí tối thượng cho Deduplication: Giúp đánh số thứ tự và dễ dàng lọc bỏ các bản ghi trùng lặp thông qua CTE (WITH).

3. Phân vùng thông minh: Luôn kết hợp PARTITION BY và lọc WHERE từ sớm để giảm thiểu chi phí Shuffle qua mạng Jupiter.

Nhưng thế giới phân tích dữ liệu không chỉ dừng lại ở việc xếp hạng và lọc dòng. Trong các bài toán tài chính và kinh doanh, chúng ta thường xuyên cần nhìn về quá khứ hoặc tương lai của một dòng dữ liệu (ví dụ: so sánh giá cổ phiếu hôm nay với ngày hôm qua, hoặc lấy giá trị của dòng liền trước để tính tốc độ tăng trưởng).

Làm thế nào để làm chủ các hàm cửa sổ trượt (Sliding Windows) như LEAD, LAG, FIRST_VALUE? Hãy đón xem Bài học 17: Window Functions (Analytic Functions) - Phần 2: Phân tích xu hướng với LEAD(), LAG() và khung trượt (Sliding Window)!