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

Chào anh em! Chúng ta đã đi qua một hành trình cực kỳ mãn nhãn trong Phần 2: Làm chủ GoogleSQL & Xử lý dữ liệu bán cấu trúc. Từ việc hiểu sâu về kiểu dữ liệu số, mảng ARRAY, cấu trúc STRUCT, toán tử UNNEST, xử lý JSON, Regex, múi giờ Time-series, cho đến các hàm phân tích Window Functions đỉnh cao.

Hôm nay, trước khi khép lại Phần 2 để bước sang phần lập trình thủ tục nâng cao, chúng ta sẽ học cách "gọt giũa" mã nguồn SQL sao cho vừa ngắn gọn, dễ đọc, vừa tối ưu hóa hiệu năng thực thi dưới tầng Dremel.

Nhiều lập trình viên có thói quen viết những câu lệnh SQL dài dằng dặc, lồng nhau 5-6 lớp Subquery trông rối như canh hẹ. Trong GoogleSQL, Google đã trang bị sẵn hàng loạt cú pháp mở rộng cực kỳ thông minh giúp bạn biến những đoạn code cồng kềnh đó trở nên tinh gọn và chuyên nghiệp chỉ trong tích tắc. Hãy cùng khám phá ngay!

1. Mệnh đề QUALIFY – "Vũ khí bí mật" tiêu diệt CTE rườm rà

Ở Bài 16, khi học cách khử trùng lặp dữ liệu (Deduplication) bằng ROW_NUMBER(), chúng ta buộc phải viết một cấu trúc CTE (WITH ... AS) lồng nhau gồm 2 bước:

1. Bước 1: Viết CTE bọc hàm ROW_NUMBER().

2. Bước 2: SELECT ở bảng ngoài cùng với điều kiện WHERE rn = 1.

Với GoogleSQL, bạn có thể vứt bỏ hoàn toàn lớp CTE bọc ngoài đó nhờ có mệnh đề QUALIFY!

So sánh trực quan:

• Cách cũ (Dùng CTE):

  SQL

  WITH RankedEvents AS (
      SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) AS rn
      FROM `my-project.logs.user_actions`
  )
  SELECT * FROM RankedEvents WHERE rn = 1;
  
  

• Cách hiện đại, siêu gọn với QUALIFY:

  SQL

  SELECT * 
  FROM `my-project.logs.user_actions`
  QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) = 1;
  
  

QUALIFY hoạt động thế nào?

Bản chất của QUALIFY giống như một mệnh đề WHERE, nhưng nó được thiết kế dành riêng cho các Window Functions. Thứ tự thực hiện của GoogleSQL trong câu lệnh là: FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ HAVING $\rightarrow$ QUALIFY $\rightarrow$ SELECT $\rightarrow$ DISTINCT $\rightarrow$ ORDER BY $\rightarrow$ LIMIT.

Nhờ QUALIFY, bạn có thể lọc kết quả trực tiếp ngay sau hàm xếp hạng mà không cần tốn công tạo bảng tạm hay CTE trung gian!

2. PIVOT và UNPIVOT – Chuyển đổi hàng thành cột (và ngược lại) một cách tự động

Trong các báo cáo BI, sếp rất hay thích nhìn biểu đồ theo dạng ma trận: Cột là các tháng trong năm (Jan, Feb, Mar...), hàng là các danh mục sản phẩm (Electronics, Clothing...).

Trước đây, để làm được việc này, bạn phải dùng kỹ thuật "Conditional Aggregation" (dùng hàng loạt hàm SUM(CASE WHEN month = 'Jan' THEN revenue END) viết rất mỏi tay). GoogleSQL hỗ trợ sẵn hai toán tử PIVOT và UNPIVOT chuẩn mực:

2.1. PIVOT: Biến giá trị của hàng thành tên cột

Giả sử bạn có bảng chi tiết doanh thu theo từng tháng, bạn muốn xoay nó lại thành ma trận ngang:

SQL

SELECT * FROM (
    SELECT category, month, revenue 
    FROM `my-project.mart_sales.monthly_revenue`
)
PIVOT(
    SUM(revenue) FOR month IN ('Jan', 'Feb', 'Mar', 'Apr')
);

Ngay lập tức, các giá trị 'Jan', 'Feb'... sẽ tự động biến thành các cột riêng biệt trên bảng kết quả với giá trị tổng doanh thu tương ứng!

2.2. UNPIVOT: Biến tên cột thành hàng

Ngược lại với PIVOT, nếu bạn nhận được một bảng Excel báo cáo thô có sẵn các cột tháng ngang phè phè và bạn muốn trải phẳng chúng dọc lại để nạp vào Database:

SQL

SELECT category, month, revenue
FROM `my-project.mart_sales.wide_revenue_table`
UNPIVOT(
    revenue FOR month IN (Jan, Feb, Mar, Apr)
);

3. Các toán tử mở rộng siêu tiện lợi: SELECT * EXCEPT và SELECT * REPLACE

Khi một bảng của bạn có tới 50 cột, và bạn muốn thực hiện một câu lệnh SELECT * nhưng có 2 yêu cầu:

1. Bỏ qua cột password và cột internal_token vì lý do bảo mật.

2. Sửa lại giá trị của cột status thành chữ hoa (UPPER(status)) ngay trong cùng câu lệnh mà không muốn gõ lại 48 cột còn lại.

Trong SQL truyền thống, bạn buộc phải gõ thủ công 48 tên cột còn lại – cực kỳ oải và dễ gõ sai. GoogleSQL cung cấp hai toán tử cực kỳ thanh lịch:

3.1. EXCEPT – Loại bỏ các cột không cần thiết

SQL

-- Lấy toàn bộ các cột trong bảng users, ngoại trừ password và token
SELECT * EXCEPT (password, internal_token)
FROM `my-project.customer.users`;

3.2. REPLACE – Thay thế / Ghi đè giá trị cột

SQL

-- Lấy toàn bộ bảng, nhưng ghi đè cột status bằng giá trị viết hoa và cột updated_at bằng thời gian hiện tại
SELECT * REPLACE (
    UPPER(status) AS status,
    CURRENT_TIMESTAMP() AS updated_at
)
FROM `my-project.customer.orders`;

Bạn có thể kết hợp cả EXCEPT và REPLACE trong cùng một câu lệnh SELECT để làm sạch dữ liệu nhanh như chớp!

4. Tận dụng CTE (WITH) thông minh để tối ưu hóa Dremel Optimizer

Nhiều lập trình viên nghĩ rằng CTE (WITH ... AS (...)) chỉ là một công cụ để viết code cho đẹp mắt (giống như một biến tạm). Nhưng dưới gầm máy của BigQuery, Trình tối ưu hóa truy vấn (Query Optimizer của Dremel) đối xử với CTE rất thông minh:

1. Inline CTE: Nếu bạn định nghĩa một CTE nhưng chỉ gọi nó đúng 1 lần ở câu lệnh bên dưới, Dremel sẽ tự động "hòa nhập" (inline) đoạn code đó vào câu lệnh chính, hoạt động hệt như một Subquery thông thường mà không tốn chi phí lưu trữ trung gian.

2. Materialized CTE (Lưu tạm kết quả): Nếu bạn gọi một CTE nhiều lần ở các phần khác nhau của câu lệnh phức tạp, Dremel có thể tự động tính toán một lần và lưu tạm kết quả trên RAM của các Slots để tái sử dụng, giúp tiết kiệm đáng kể thời gian tính toán lặp lại.

Mẹo thực chiến: Hãy chia nhỏ các bước logic phức tạp thành nhiều khối WITH step1 AS (...), step2 AS (...). Điều này vừa giúp câu lệnh SQL của bạn đọc sạch sẽ như văn xuôi, vừa giúp đội ngũ maintain hệ thống sau này dễ dàng debug từng tầng một.

Tổng kết Phần 2 & Chào mừng đến với Phần 3!

Khép lại Bài học 18, chúng ta đã hoàn thành xuất sắc Phần 2: Làm chủ GoogleSQL & Xử lý dữ liệu bán cấu trúc:

• Nắm vững chuẩn GoogleSQL và các kiểu dữ liệu từ số học (NUMERIC), thời gian (TIMESTAMP/DATETIME), cho đến cấu trúc lồng nhau (ARRAY/STRUCT), UNNEST và JSON nguyên bản.

• Thành thạo các kỹ thuật xử lý chuỗi Regex, Window Functions (ROW_NUMBER, LAG, LEAD, Sliding Windows).

• Và tối ưu hóa cú pháp với QUALIFY, PIVOT/UNPIVOT, cùng EXCEPT/REPLACE.

Nền tảng SQL của bạn giờ đây đã đạt đến trình độ chuẩn xác của một Senior Data Engineer. Nhưng điều gì sẽ xảy ra khi bạn cần viết một kịch bản ETL phức tạp gồm nhiều bước điều kiện (IF/ELSE), vòng lặp (LOOP/WHILE), xử lý ngoại lệ (BEGIN...EXCEPTION) để tự động hóa hoàn toàn quy trình dữ liệu ngay bên trong BigQuery mà không cần dựng thêm server Airflow bên ngoài?

Hẹn gặp lại anh em ở Phần 3 (Bài 19 - 25): Lập trình hóa SQL, Hàm tự định nghĩa & GIS – bắt đầu với Bài học 19: Procedural SQL Scripting – Sử dụng biến, vòng lặp và điều kiện trong BigQuery!