BigQuery 3 — GoogleSQL: ARRAY, STRUCT & analytic

Đến với BigQuery từ PostgreSQL hay Oracle, cảm giác đầu tiên là "trông cũng giống SQL bình thường". Đúng — GoogleSQL (tên chính thức của phương ngữ SQL trong BigQuery, trước gọi là "Standard SQL") bám ANSI SQL:2011 khá sát. Nhưng đi sâu vài bước là gặp thế giới khác: cột có thể chứa cả một mảng struct lồng nhau; câu truy vấn quét hàng nghìn bảng chỉ bằng một dấu *; và mệnh đề QUALIFY cho lọc theo kết quả window function mà không cần subquery.

Bài này đi qua những đặc trưng làm nên sức mạnh của GoogleSQL: ARRAY & STRUCT cùng UNNEST, window function và QUALIFY, wildcard table với _TABLE_SUFFIX, scripting & procedural, DDL/DML và MERGE, cùng một bộ hàm hữu ích đáng nhớ. Đây là phần tiếp nối Storage & tables — nơi giải thích vì sao lưu trữ columnar của BigQuery lại "thích" dữ liệu nested.

Lưu ý về ví dụ. Sandbox SQL Builder của trang này chạy trên PostgreSQL, nên các cú pháp đặc trưng GoogleSQL (ARRAY/STRUCT/UNNEST/QUALIFY/wildcard) không chạy được ở đây. Toàn bộ ví dụ bên dưới gắn nhãn (minh hoạ — BigQuery) và chỉ để đọc hiểu, hãy chạy trực tiếp trong BigQuery console hoặc bq CLI.

Vì sao BigQuery khuyến khích denormalize?

Trong RDBMS truyền thống, quy tắc vàng là chuẩn hoá (normalize): tách dữ liệu ra nhiều bảng, nối bằng JOIN khi cần, để tránh trùng lặp và bất nhất khi cập nhật. Nhưng BigQuery là kho dữ liệu columnar, phân tích, ít cập nhật lẻ; JOIN quy mô lớn giữa các bảng khổng lồ đòi hỏi shuffle dữ liệu qua mạng — tốn kém. Vì thế Google khuyến nghị ngược lại: denormalize bằng cách nhúng dữ liệu con vào bản ghi cha, dùng repeated field (ARRAY) và nested field (STRUCT). Nhờ columnar, dù một cột là mảng struct, engine vẫn chỉ đọc đúng các cột con bạn truy vấn.

Tinh thần là: thay vì hai bảng nối bằng JOIN, ta có một bảng với cột items là mảng struct; khi cần bảng phẳng để phân tích, ta dùng UNNEST bung mảng thành nhiều hàng.

ARRAY và STRUCT — kiểu lồng nhau

STRUCT là bản ghi có các trường được đặt tên (giống ROW/composite type của Postgres, nhưng dùng thoải mái hơn). ARRAY là danh sách có thứ tự gồm nhiều phần tử cùng kiểu. Lưu ý một giới hạn quan trọng của GoogleSQL: không có ARRAY của ARRAY trực tiếp — muốn lồng mảng trong mảng, bạn phải bọc qua STRUCT.

Tạo bảng có mảng struct:

-- (minh hoạ — BigQuery)
CREATE OR REPLACE TABLE shop.orders (
  order_id   INT64,
  customer   STRUCT<name STRING, city STRING>,
  items      ARRAY<STRUCT<sku STRING, qty INT64, price NUMERIC>>
);

INSERT INTO shop.orders VALUES
(1001,
 STRUCT('An' AS name, 'Hà Nội' AS city),
 [STRUCT('A' AS sku, 2 AS qty, 50000 AS price),
  STRUCT('B' AS sku, 1 AS qty, 30000 AS price)]);

Truy cập trường struct bằng dấu chấm; truy cập phần tử mảng bằng OFFSET (0-based) hoặc ORDINAL (1-based), và bản an toàn SAFE_OFFSET/SAFE_ORDINAL trả về NULL thay vì lỗi khi vượt biên:

-- (minh hoạ — BigQuery)
SELECT
  order_id,
  customer.name          AS ten_khach,
  customer.city          AS thanh_pho,
  ARRAY_LENGTH(items)    AS so_dong_hang,
  items[SAFE_OFFSET(0)].sku AS sku_dau_tien
FROM shop.orders;

Ở đây customer.name đọc trường của một struct đơn, còn items[SAFE_OFFSET(0)] lấy phần tử đầu của mảng. Toàn bộ vẫn nằm trên một hàng — mảng chưa bị bung.

UNNEST — bung mảng thành hàng

Muốn phân tích từng dòng hàng trong đơn, ta phải "làm phẳng" mảng. UNNEST nhận một ARRAY và trả về một bảng có một hàng cho mỗi phần tử của mảng. Khi UNNEST một cột mảng của bảng, đây thực chất là một correlated cross join giữa mỗi hàng cha và các phần tử mảng của nó:

-- (minh hoạ — BigQuery)
SELECT
  o.order_id,
  o.customer.name,
  it.sku,
  it.qty,
  it.price
FROM shop.orders AS o,
     UNNEST(o.items) AS it;   -- dấu phẩy = CROSS JOIN tương quan

Đơn 1001 có 2 phần tử trong items sẽ nở thành 2 hàng, mỗi hàng đi kèm đầy đủ thông tin cha (order_id, customer.name). Đây là điểm cốt lõi: cha được nhân bản, con được trải ra.

Vài biến thể quan trọng:

• Giữ thứ tự phần tử: thêm WITH OFFSET để lấy chỉ số gốc.

-- (minh hoạ — BigQuery)
SELECT o.order_id, it.sku, pos
FROM shop.orders AS o,
     UNNEST(o.items) AS it WITH OFFSET AS pos
ORDER BY o.order_id, pos;

• Giữ cả hàng có mảng rỗng/NULL: dùng LEFT JOIN UNNEST(...) thay vì cross join. Cross join sẽ loại bỏ những hàng cha có mảng rỗng.

-- (minh hoạ — BigQuery)
SELECT o.order_id, it.sku
FROM shop.orders AS o
LEFT JOIN UNNEST(o.items) AS it;

ARRAY_AGG — gộp ngược lại thành mảng

Chiều ngược của UNNEST là ARRAY_AGG — hàm tổng hợp gom nhiều hàng thành một mảng, có thể kèm ORDER BY để mảng có thứ tự xác định và LIMIT:

-- (minh hoạ — BigQuery)
SELECT
  customer.city AS thanh_pho,
  ARRAY_AGG(order_id ORDER BY order_id) AS cac_don
FROM shop.orders
GROUP BY thanh_pho;

ARRAY subquery

Hàm ARRAY(subquery) biến kết quả một truy vấn con thành mảng — tiện để lọc, khử trùng lặp hay biến đổi ngay trong một biểu thức:

-- (minh hoạ — BigQuery)
SELECT
  order_id,
  ARRAY(SELECT it.sku
        FROM UNNEST(items) AS it
        WHERE it.qty >= 2) AS sku_mua_nhieu
FROM shop.orders;

Cùng nhóm còn có ARRAY_LENGTH, ARRAY_TO_STRING, ARRAY_CONCAT, ARRAY_CONCAT_AGG (nối các cột mảng qua nhiều hàng), ARRAY_FIRST/ARRAY_LAST.

Window / analytic functions & QUALIFY

Window functions của BigQuery bám sát ANSI: func(...) OVER (PARTITION BY ... ORDER BY ... [frame]). Bộ hàm quen thuộc gồm xếp hạng (ROW_NUMBER, RANK, DENSE_RANK), điều hướng (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), và các tổng hợp chạy (SUM, AVG, COUNT... dùng làm running total).

-- (minh hoạ — BigQuery)
SELECT
  order_id,
  customer.city,
  total,
  ROW_NUMBER() OVER (PARTITION BY customer.city ORDER BY total DESC) AS hang,
  SUM(total)   OVER (PARTITION BY customer.city ORDER BY order_id
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS luy_ke,
  LAG(total)   OVER (PARTITION BY customer.city ORDER BY order_id) AS don_truoc
FROM (
  SELECT order_id, customer,
         (SELECT SUM(it.qty * it.price) FROM UNNEST(items) it) AS total
  FROM shop.orders
);

QUALIFY — lọc theo kết quả window

Đây là mệnh đề đặc trưng cực tiện của GoogleSQL. Bình thường bạn không thể đặt window function trong WHERE (vì window được tính sau khi WHERE/GROUP BY đã lọc xong), nên phải bọc câu vào subquery rồi lọc ở ngoài. QUALIFY xoá bỏ bước bọc đó — nó lọc trực tiếp trên kết quả của window function, chạy sau SELECT/window, tương tự như HAVING lọc sau GROUP BY:

-- (minh hoạ — BigQuery)
SELECT
  order_id, customer.city, total
FROM (
  SELECT order_id, customer,
         (SELECT SUM(it.qty * it.price) FROM UNNEST(items) it) AS total
  FROM shop.orders
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer.city ORDER BY total DESC) = 1;

Câu trên lấy đơn giá trị cao nhất mỗi thành phố trong một tầng, không cần subquery lồng để lọc hang = 1. Lưu ý ràng buộc: QUALIFY chỉ dùng được khi trong câu có ít nhất một trong các mệnh đề WHERE, GROUP BY, HAVING, hoặc bản thân biểu thức QUALIFY chứa một window function. Thứ tự đánh giá logic: FROM → WHERE → GROUP BY → HAVING → window → QUALIFY → DISTINCT → ORDER BY → LIMIT.

So sánh với Postgres: Postgres không có QUALIFY (tính đến các phiên bản phổ biến), bạn phải dùng subquery/CTE như đã trình bày trong SQL nâng cao PostgreSQL. QUALIFY là một tiện ích rõ rệt của GoogleSQL/BigQuery.

Wildcard tables & _TABLE_SUFFIX

BigQuery thường xuyên gặp mẫu bảng theo ngày/theo shard: events_20260101, events_20260102, ... Thay vì UNION ALL tay, wildcard table cho phép truy vấn "hợp" (union) của tất cả bảng khớp mẫu bằng một dấu * ở cuối tên bảng. Tên bảng chứa wildcard phải đặt trong backtick:

-- (minh hoạ — BigQuery)
SELECT event_name, COUNT(*) AS n
FROM `myproj.analytics.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
GROUP BY event_name;

Cột giả (pseudo column) _TABLE_SUFFIX chứa phần chuỗi khớp với dấu *. Điều mấu chốt về hiệu năng và chi phí: nếu bạn lọc _TABLE_SUFFIX bằng biểu thức hằng (constant), BigQuery sẽ prune — chỉ quét đúng các bảng trong khoảng, giảm dữ liệu đọc và tiền phải trả. Ngược lại, nếu điều kiện _TABLE_SUFFIX chứa biểu thức không hằng (ví dụ so với một cột, một subquery), BigQuery không thể giới hạn số bảng và sẽ quét toàn bộ — một cái bẫy chi phí kinh điển.

Vài lưu ý: prefix dài hơn (nhiều ký tự trước *) thường tối ưu tốt hơn; wildcard không dùng được với view hay external table; các bảng phải cùng dataset và schema tương thích; kết quả wildcard không được cache; và DML không nhắm vào wildcard table được. Với dữ liệu lớn, phân vùng theo cột thời gian (partition) thường ưu việt hơn sharding bằng wildcard — xem chi tiết ở Partition & cluster.

Scripting & procedural

GoogleSQL không chỉ là câu đơn lẻ — nó hỗ trợ script nhiều câu lệnh với biến, điều kiện, vòng lặp, chạy trong một job.

-- (minh hoạ — BigQuery)
DECLARE nguong NUMERIC DEFAULT 100000;
DECLARE i INT64 DEFAULT 0;

SET nguong = nguong * 1.1;

CREATE TEMP TABLE don_lon AS
SELECT order_id, total
FROM `myproj.shop.don_tong`
WHERE total > nguong;

IF (SELECT COUNT(*) FROM don_lon) = 0 THEN
  SELECT 'Không có đơn lớn' AS thong_bao;
ELSE
  SELECT * FROM don_lon;
END IF;

WHILE i < 3 DO
  SET i = i + 1;
END WHILE;

• Biến: DECLARE ... DEFAULT ... khai báo, SET gán.
• Điều khiển luồng: IF ... THEN ... ELSEIF ... ELSE ... END IF, WHILE ... DO ... END WHILE, LOOP ... END LOOP với BREAK/CONTINUE, FOR ... IN (...) DO.
• Temp table: CREATE TEMP TABLE tồn tại trong phạm vi script.
• SQL động: EXECUTE IMMEDIATE 'SELECT ...' USING @param để dựng và chạy câu lệnh từ chuỗi.

Stored procedures, UDF & table functions

BigQuery cho phóng đóng gói logic:

-- (minh hoạ — BigQuery) UDF viết bằng SQL
CREATE OR REPLACE FUNCTION shop.thanh_tien(qty INT64, price NUMERIC)
RETURNS NUMERIC AS (qty * price);

-- UDF viết bằng JavaScript
CREATE OR REPLACE FUNCTION shop.viet_hoa(s STRING)
RETURNS STRING
LANGUAGE js AS r"""
  return s.toUpperCase();
""";

-- Stored procedure
CREATE OR REPLACE PROCEDURE shop.don_theo_tp(IN tp STRING)
BEGIN
  SELECT * FROM `myproj.shop.don_tong` WHERE city = tp;
END;

-- Table function (TVF) — trả về một bảng
CREATE OR REPLACE TABLE FUNCTION shop.don_tu(nguong NUMERIC)
AS (SELECT * FROM `myproj.shop.don_tong` WHERE total >= nguong);

Gọi bằng CALL shop.don_theo_tp('Hà Nội') hay SELECT * FROM shop.don_tu(100000). UDF JavaScript rất tiện cho logic khó viết thuần SQL nhưng thường chậm hơn UDF SQL, nên cân nhắc khi xử lý khối lượng lớn.

DDL & DML

DDL của BigQuery quen thuộc: CREATE TABLE, CREATE OR REPLACE TABLE, ALTER TABLE ... ADD COLUMN, DROP TABLE, và đặc biệt hữu ích là CTAS (CREATE TABLE AS SELECT) để vật chất hoá kết quả một truy vấn:

-- (minh hoạ — BigQuery)
CREATE OR REPLACE TABLE shop.don_hang_2026 AS
SELECT * FROM `myproj.shop.orders`
WHERE EXTRACT(YEAR FROM ngay_dat) = 2026;

DML gồm INSERT, UPDATE, DELETE, và ngôi sao của ELT là MERGE (upsert). MERGE gộp chèn/cập nhật/xoá trong một câu — nền tảng cho pattern nạp incremental từ bảng staging vào bảng đích:

-- (minh hoạ — BigQuery)
MERGE `myproj.shop.dim_khach` AS T
USING `myproj.staging.khach_moi` AS S
ON T.customer_id = S.customer_id
WHEN MATCHED THEN
  UPDATE SET name = S.name, city = S.city, updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, city, updated_at)
  VALUES (S.customer_id, S.name, S.city, CURRENT_TIMESTAMP());

Cảnh báo quan trọng. DML trên BigQuery không hợp với cập nhật lẻ, tần suất cao (kiểu OLTP mỗi lần một hàng). Mỗi câu DML là một job quét/ghi lại theo lô, tốn tài nguyên và có giới hạn đồng thời. Muốn nạp dòng liên tục, hãy dùng Storage Write API / streaming insert; muốn cập nhật, hãy gom thành lô và chạy MERGE định kỳ. Đây là khác biệt tư duy lớn so với RDBMS OLTP.

Bộ hàm hữu ích nên nhớ

GoogleSQL có nhiều họ hàm giúp code gọn và rẻ:

• SAFE. prefix & hàm SAFE_*: bọc một hàm để trả về NULL thay vì lỗi khi gặp đầu vào xấu — SAFE.DIVIDE(a, b) (không lỗi khi chia 0), SAFE_CAST(x AS INT64), SAFE.PARSE_DATE(...). Rất hợp khi làm sạch dữ liệu bẩn theo lô.
• Họ APPROX_*: đếm/ước lượng gần đúng nhưng rẻ và nhanh hơn nhiều trên dữ liệu lớn — nổi bật là APPROX_COUNT_DISTINCT (thay cho COUNT(DISTINCT ...) đắt đỏ), cùng APPROX_QUANTILES, APPROX_TOP_COUNT.
• Ngày giờ: FORMAT_DATE, PARSE_DATE, PARSE_TIMESTAMP, DATE_TRUNC, DATE_DIFF, EXTRACT.
• JSON: JSON_VALUE, JSON_QUERY, PARSE_JSON, TO_JSON_STRING, và kiểu JSON gốc.
• Sinh dữ liệu: GENERATE_ARRAY(1, 10), GENERATE_DATE_ARRAY('2026-01-01', '2026-01-31', INTERVAL 1 DAY) — cực tiện để tạo chuỗi ngày rồi LEFT JOIN lấp khoảng trống.

-- (minh hoạ — BigQuery)
SELECT
  APPROX_COUNT_DISTINCT(user_id) AS uu_gan_dung,
  SAFE.DIVIDE(SUM(doanh_thu), NULLIF(COUNT(*),0)) AS tb_an_toan
FROM `myproj.analytics.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630';

Khác biệt phương ngữ cần lưu ý

Khi chuyển từ ANSI/Postgres sang GoogleSQL, để ý:

• Backtick bao định danh có ký tự đặc biệt/tên đủ (`project.dataset.table`), không phải dấu nháy kép.
• Tham chiếu bảng ba phần: project.dataset.table; trong dấu chấm, dataset.table là mặc định.
• Kiểu: INT64, FLOAT64, NUMERIC/BIGNUMERIC, STRING (không phải VARCHAR), BOOL, BYTES, TIMESTAMP vs DATETIME (khác múi giờ), ARRAY, STRUCT, JSON, GEOGRAPHY.
• Không có khoá chính/ngoại được cưỡng chế (chỉ khai báo tham khảo, không enforce), không index thủ công (dùng partition/cluster thay thế).
• Truy vấn có thể tính tiền theo byte quét — cấu trúc câu ảnh hưởng trực tiếp chi phí, xem Chi phí & hiệu năng.

Use case thực tế: phân tích sự kiện lồng nhau bằng UNNEST

Bảng sự kiện kiểu Google Analytics 4 (GA4) trong BigQuery là ví dụ kinh điển của dữ liệu nested/repeated. Mỗi hàng là một sự kiện, và event_params là mảng struct {key, value} — vì mỗi sự kiện có số tham số khác nhau. Đây chính là lúc UNNEST toả sáng:

-- (minh hoạ — BigQuery)
SELECT
  event_name,
  (SELECT p.value.string_value
   FROM UNNEST(event_params) AS p
   WHERE p.key = 'page_location')            AS trang,
  (SELECT p.value.int_value
   FROM UNNEST(event_params) AS p
   WHERE p.key = 'engagement_time_msec')     AS thoi_gian_ms
FROM `myproj.analytics_123.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
  AND event_name = 'page_view';

Ở đây có hai kỹ thuật kết hợp: (1) subquery UNNEST tương quan để rút một tham số cụ thể ra thành cột — mẫu "pivot key/value" rất phổ biến với GA4; (2) wildcard + _TABLE_SUFFIX để chỉ quét đúng các bảng ngày trong tháng, tránh quét cả kho.

Muốn đếm phiên (session) duy nhất theo trang rẻ, thêm APPROX_COUNT_DISTINCT; muốn "top event mỗi ngày" thì bọc QUALIFY ROW_NUMBER() OVER (...) <= 5. Ba đặc trưng — nested data, wildcard, window/QUALIFY — hầu như luôn đi cùng nhau trong phân tích sự kiện trên BigQuery.

Ghi nhớ

• Denormalize là bạn: dùng ARRAY & STRUCT để nhúng dữ liệu con thay vì tách bảng + JOIN; hợp với columnar, giảm shuffle.
• UNNEST bung mảng thành hàng (cross join tương quan); dùng LEFT JOIN UNNEST để giữ hàng có mảng rỗng, WITH OFFSET để giữ thứ tự; ARRAY_AGG gộp ngược lại.
• QUALIFY lọc theo window function ngay trong một tầng — không cần subquery; đặc trưng tiện của GoogleSQL mà Postgres không có.
• _TABLE_SUFFIX phải là điều kiện hằng thì mới prune bảng và tiết kiệm chi phí; điều kiện không hằng sẽ quét toàn bộ.
• DML không dành cho cập nhật lẻ tần suất cao: gom lô + MERGE cho ELT; dùng Storage Write API cho streaming.
• Nhớ dùng SAFE. để tránh lỗi khi làm sạch dữ liệu, và APPROX_* khi chấp nhận gần đúng để tiết kiệm.
• Phương ngữ khác Postgres: backtick, project.dataset.table, kiểu INT64/STRING, không index/khoá cưỡng chế.

Đọc tiếp: Storage & tables để hiểu vì sao nested rẻ, Partition & cluster để tối ưu quét, và Chi phí & hiệu năng để câu SQL của bạn không "đốt tiền". Nếu đến từ Postgres, đối chiếu với SQL nâng cao PostgreSQL để thấy điểm giống và khác.