Google BigQuery - 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)

Chào anh em! Ở Bài học 16, chúng ta đã thuần phục thành công hàm ROW_NUMBER() để quét sạch rác và khử trùng lặp dữ liệu (Deduplication) một cách cực kỳ mượt mà. Hôm nay, chúng ta tiếp tục đi sâu vào phần 2 của chuỗi Window Functions – nơi chứa những "vũ khí tối thượng" giúp bạn phân tích xu hướng, so sánh kỳ trước kỳ này và tính toán trung bình di động mà không cần phải tự JOIN bảng với chính nó.

Hãy tưởng tượng sếp giao cho bạn bài toán: "Hãy tính tốc độ tăng trưởng doanh thu theo từng ngày so với ngày hôm qua, đồng thời tính luôn đường trung bình di động 7 ngày gần nhất để vẽ biểu đồ tài chính."

Nếu dùng tư duy lập trình RDBMS truyền thống, bạn sẽ phải viết một câu lệnh JOIN bảng doanh thu với chính nó (Self-join) qua khóa ngày tháng ($T$ nối với $T-1$). Trên bảng dữ liệu hàng trăm triệu dòng, phép Self-join này sẽ làm mạng Jupiter "khóc thét" vì tốn tài nguyên Shuffle.

Nhưng với Window Functions trong GoogleSQL, mọi thứ được giải quyết gọn gàng chỉ trong vòng một nốt nhạc nhờ cặp bài trùng LAG() và LEAD() kết hợp với Sliding Windows. Hãy cùng mổ xẻ ngay!

1. LAG() và LEAD() – "Quay ngược quá khứ" và "Nhìn về tương lai"

Bản chất của hai hàm này rất trực quan:

• LAG(column, offset) (Time-shift lùi): Cho phép bạn lấy giá trị của một cột từ dòng đứng trước dòng hiện tại trong cửa sổ sắp xếp (Mặc định offset = 1 tức là dòng liền trước ngay phía trên).

• LEAD(column, offset) (Time-shift tiến): Cho phép bạn lấy giá trị của một cột từ dòng đứng sau dòng hiện tại trong cửa sổ sắp xếp (dòng liền ngay phía dưới).

Trực quan hóa cách hoạt động:

Giả sử ta có bảng doanh thu theo ngày:

2. Thực chiến SQL: Tính tốc độ tăng trưởng doanh thu hàng ngày

Hãy áp dụng ngay LAG() vào một bài toán thực tế: Tính chênh lệch tuyệt đối và tỷ lệ phần trăm tăng trưởng doanh thu so với ngày hôm trước.

SQL

WITH DailyRevenue AS (
    -- Bước 1: Gom nhóm tính tổng doanh thu theo từng ngày
    SELECT 
        DATE(DATETIME(created_at, 'Asia/Ho_Chi_Minh')) AS order_date,
        SUM(total_amount) AS current_revenue
    FROM `my-project.core_dwh.fact_orders`
    WHERE created_at >= TIMESTAMP('2026-10-01 00:00:00', 'Asia/Ho_Chi_Minh')
    GROUP BY order_date
),
RevenueWithLag AS (
    -- Bước 2: Sử dụng LAG() để kéo doanh thu của ngày hôm trước lên cùng một dòng
    SELECT 
        order_date,
        current_revenue,
        LAG(current_revenue, 1) OVER (ORDER BY order_date ASC) AS previous_revenue
    FROM DailyRevenue
)
-- Bước 3: Tính toán độ lệch và phần trăm tăng trưởng
SELECT 
    order_date,
    current_revenue,
    previous_revenue,
    (current_revenue - LAG(current_revenue, 1) OVER (ORDER BY order_date ASC)) AS revenue_diff,
    SAFE_DIVIDE(current_revenue - previous_revenue, previous_revenue) * 100 AS growth_percentage
FROM RevenueWithLag
ORDER BY order_date DESC;

• Sức mạnh tuyệt đối: Không cần JOIN bảng, không cần băm dữ liệu phức tạp qua mạng Jupiter. Dremel sắp xếp theo ngày một lần duy nhất và dùng LAG() để "chiếu" giá trị ngày hôm trước lên thẳng dòng hiện tại trong một đường băng duy nhất!

3. Sliding Window (Khung trượt): Tính trung bình di động 7 ngày (7-Day Moving Average)

Trong phân tích tài chính và vận hành, việc nhìn vào biến động doanh thu từng ngày đôi khi bị nhiễu bởi các yếu tố ngoại cảnh (ví dụ: ngày cuối tuần khách mua nhiều hơn ngày thường). Do đó, các nhà quản lý luôn yêu cầu tính chỉ số Trung bình di động 7 ngày (7-Day Moving Average) để nhìn rõ xu hướng tổng thể.

Để giới hạn phạm vi của cửa sổ tính toán thay vì lấy toàn bộ từ đầu bảng đến hiện tại, chúng ta sử dụng mệnh đề ROWS BETWEEN:

Cú pháp khung trượt kinh điển:

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: Tính từ 6 dòng phía trước cộng với dòng hiện tại (Đủ bộ 7 ngày gần nhất).

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Từ dòng đầu tiên của bảng cho đến dòng hiện tại (Dùng khi tính tổng tích lũy - Running Total).

• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: Dòng trước, dòng hiện tại và dòng sau.

Ví dụ thực chiến tính Moving Average trên BigQuery:

SQL

SELECT 
    order_date,
    current_revenue,
    -- Tính trung bình doanh thu của 7 ngày gần nhất (6 ngày trước + ngày hiện tại)
    AVG(current_revenue) OVER (
        ORDER BY order_date ASC
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM DailyRevenue
ORDER BY order_date DESC;

4. Cạm bẫy chí mạng: Thiếu ORDER BY trong Frame Clause

Khi viết Sliding Window, có một quy tắc cú pháp bất di bất dịch trong GoogleSQL mà anh em rất hay quên:

Nếu bạn đã khai báo khung trượt (ROWS BETWEEN ...), cửa sổ đó BẮT BUỘC phải có mệnh đề ORDER BY rõ ràng.

Nếu bạn quên ORDER BY trong mệnh đề OVER(), Dremel sẽ không biết dòng nào là "trước" (PRECEDING) và dòng nào là "hiện tại" (CURRENT ROW), dẫn đến câu lệnh bị báo lỗi cú pháp ngay lập tức.

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

Khép lại Phần 2 về Window Functions, chúng ta đã nắm trọn trong tay những công cụ phân tích xu hướng đỉnh cao:

1. LAG() và LEAD(): Quay ngược quá khứ hoặc nhìn về tương lai để so sánh dữ liệu giữa các dòng liên tiếp mà không cần dùng phép Self-join tốn kém.

2. Khung trượt (ROWS BETWEEN): Định nghĩa chính xác phạm vi các dòng tham chiếu để tính toán trung bình di động (Moving Average) hoặc tổng tích lũy (Running Total) một cách linh hoạt.

Tuy nhiên, khi viết các câu lệnh SQL dài với nhiều bước CTE (WITH ... AS), mã nguồn của bạn đôi khi trở nên cồng kềnh và khó đọc. GoogleSQL cung cấp rất nhiều cú pháp mở rộng cực kỳ thông minh giúp code ngắn gọn hơn như QUALIFY, PIVOT/UNPIVOT, và SELECT * EXCEPT / REPLACE.

Làm thế nào để tinh chỉnh và viết câu lệnh SQL trên BigQuery chuẩn phong cách Senior Data Engineer? Hãy đón xem Bài học 18: Tối ưu hóa cú pháp SQL – Sử dụng CTE, Pivot/Unpivot, QUALIFY và các toán tử mở rộng!