Trino SQL Bài học 31: Khai phá và chuẩn hóa cấu trúc mảng với UNNEST

Mục tiêu bài học:

• Hiểu khái niệm "làm phẳng" (Flattening) dữ liệu từ cấu trúc mảng thành các dòng vật lý độc lập.

• Sử dụng cú pháp CROSS JOIN UNNEST chuẩn mực của Trino.

• Bảo toàn thứ tự ban đầu của các phần tử trong mảng bằng WITH ORDINALITY.

• Tránh rủi ro mất mát dữ liệu đối với mảng rỗng bằng kỹ thuật LEFT JOIN UNNEST.

1. Bản chất của việc "Làm phẳng" (Flattening)

Ở Bài 30, chúng ta đã lưu trữ một mảng ['Sách', 'Bút'] vào trong một ô dữ liệu duy nhất. Tuy nhiên, nếu bạn muốn đếm xem chữ 'Sách' xuất hiện tổng cộng bao nhiêu lần trên toàn hệ thống (bằng GROUP BY), việc để nó nằm chung trong một mảng sẽ khiến bạn không thể tính toán được.

Bạn cần một cơ chế để "bẻ gãy" cái mảng đó ra, biến 1 dòng chứa mảng 2 phần tử thành 2 dòng độc lập, mỗi dòng chứa 1 phần tử. Quá trình này được gọi là Làm phẳng (Flattening), và UNNEST chính là công cụ thực hiện việc đó.

2. Cú pháp chuẩn mực: CROSS JOIN UNNEST

Trong một số hệ quản trị CSDL cũ, bạn có thể gọi hàm UNNEST trực tiếp trên mệnh đề SELECT. Nhưng tại Trino (và chuẩn ANSI SQL hiện đại), UNNEST được coi như một Bảng ảo sinh ra tức thời (Table Function), nên nó bắt buộc phải nằm ở mệnh đề FROM.

Logic hoạt động: Trino sẽ lấy dòng dữ liệu gốc (Bảng bên trái), đem nhân chéo (CROSS JOIN) với từng phần tử được bung ra từ Bảng ảo (mảng UNNEST bên phải).

SQL

-- Dữ liệu giả lập: Khách hàng và mảng các từ khóa họ đã tìm kiếm
WITH user_searches AS (
    SELECT 101 AS user_id, ARRAY['iphone', 'macbook'] AS search_keywords
    UNION ALL
    SELECT 102 AS user_id, ARRAY['trino sql'] AS search_keywords
)
SELECT 
    u.user_id, 
    k.keyword
FROM user_searches AS u
-- Bung mảng search_keywords ra thành một bảng ảo t, cột chứa giá trị tên là keyword
CROSS JOIN UNNEST(u.search_keywords) AS t(keyword);

Kết quả trả về:

(Khách hàng 101 bị nhân bản thành 2 dòng để đi kèm với 2 phần tử mảng).

3. Giữ lại vết tích vị trí với WITH ORDINALITY

Khi UNNEST bẻ gãy mảng thành các dòng rời rạc, thứ tự ban đầu của mảng (phần tử nào đứng trước, phần tử nào đứng sau) sẽ bị mất đi trong môi trường phân tán.

Nếu vị trí của phần tử mang ý nghĩa quan trọng (ví dụ: các bước trong phễu mua hàng - Step 1, Step 2), bạn phải dùng mệnh đề WITH ORDINALITY để sinh ra một cột đánh số thứ tự.

SQL

WITH funnel_logs AS (
    SELECT 'session_99' AS session_id, ARRAY['Homepage', 'Product Page', 'Cart', 'Checkout'] AS steps
)
SELECT 
    f.session_id, 
    s.step_name, 
    s.step_index
FROM funnel_logs AS f
-- t(step_name, step_index): cột 1 lưu giá trị mảng, cột 2 lưu số thứ tự
CROSS JOIN UNNEST(f.steps) WITH ORDINALITY AS t(step_name, step_index);

Kết quả sẽ có thêm cột step_index chạy từ 1 đến 4, giúp bạn dễ dàng ORDER BY hoặc lọc lấy hành động đầu tiên/cuối cùng.

4. Khai phá nhiều mảng cùng lúc (Zipping Arrays)

Nếu một dòng dữ liệu chứa 2 mảng khác nhau (ví dụ: mảng mã sản phẩm và mảng số lượng tương ứng), bạn có thể ném cả 2 mảng đó vào cùng một hàm UNNEST.

Trino sẽ ghép chúng lại theo chiều ngang (phần tử thứ 1 của mảng A đi với phần tử thứ 1 của mảng B).

SQL

WITH order_data AS (
    SELECT 
        'ORD-001' AS order_id, 
        ARRAY['P_100', 'P_200'] AS product_ids, 
        ARRAY[2, 5] AS quantities
)
SELECT 
    o.order_id, 
    u.product_id, 
    u.quantity
FROM order_data AS o
-- Bung đồng thời 2 mảng, khai báo bí danh cho 2 cột tương ứng
CROSS JOIN UNNEST(o.product_ids, o.quantities) AS u(product_id, quantity);

(Lưu ý: Nếu hai mảng có độ dài không bằng nhau, Trino sẽ điền NULL vào các vị trí bị thiếu của mảng ngắn hơn để cân bằng).

5. Cạm bẫy mất dữ liệu: LEFT JOIN UNNEST

Đây là một bẫy kinh điển khiến báo cáo tổng bị thiếu hụt.

Bản chất của CROSS JOIN UNNEST là: Nếu mảng của bạn bị rỗng (Empty Array []) hoặc bị NULL, thì nó không sinh ra phần tử nào. Khi Bảng trái đem nhân chéo với 0 phần tử, dòng dữ liệu đó sẽ biến mất hoàn toàn khỏi kết quả.

Để bảo toàn dòng gốc (những khách hàng không có từ khóa tìm kiếm vẫn phải hiện ra), hãy đổi CROSS JOIN thành LEFT JOIN và sử dụng điều kiện ON true.

SQL

WITH raw_users AS (
    SELECT 1 AS id, ARRAY['tag1'] AS tags
    UNION ALL
    SELECT 2 AS id, NULL AS tags  -- Mảng bị rỗng
)
SELECT 
    u.id, 
    t.tag
FROM raw_users AS u
-- Giữ lại user 2, cột tag sẽ trả về NULL
LEFT JOIN UNNEST(u.tags) AS t(tag) ON true;