Trino SQL Bài học 16: Các hàm tổng hợp cơ bản (COUNT, SUM, AVG, MIN, MAX)

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

• Hiểu cơ chế nén dữ liệu (Aggregation) trong môi trường tính toán phân tán.

• Nắm rõ sự khác biệt mang tính sống còn giữa COUNT(*), COUNT(cột) và COUNT(DISTINCT).

• Nhận biết hành vi phớt lờ giá trị NULL của các hàm tổng hợp.

• Hiểu luật thép về việc kết hợp cột tổng hợp và cột thông thường trong mệnh đề SELECT.

1. Cơ chế tổng hợp dữ liệu trên Trino

Hàm tổng hợp (Aggregate Functions) là các hàm nhận vào một tập hợp nhiều dòng dữ liệu và "nén" chúng lại thành một kết quả duy nhất.

Trong kiến trúc phân tán của Trino, quá trình này diễn ra qua 2 giai đoạn để tối ưu hiệu năng:

1. Partial Aggregation (Tổng hợp cục bộ): Mỗi Worker tự tính toán tổng số lượng/doanh thu trên phần dữ liệu (split) mà nó đang giữ.

2. Final Aggregation (Tổng hợp chốt): Các Worker gửi con số cục bộ của mình về cho Coordinator (hoặc một Worker được chỉ định). Node này sẽ cộng dồn các con số cục bộ lại để ra kết quả cuối cùng trả cho người dùng. Cách làm này ngăn chặn việc phải truyền hàng tỷ dòng dữ liệu thô qua mạng.

2. Hàm đếm số lượng (COUNT)

Hàm COUNT là hàm duy nhất có 3 biến thể với các hành vi hoàn toàn khác nhau.

A. COUNT(*): Đếm số dòng vật lý Hàm này đếm mọi dòng tồn tại trong bảng, bất kể dòng đó chứa giá trị thực hay chứa NULL.

SQL

SELECT COUNT(*) AS total_orders 
FROM tpch.tiny.orders;

B. COUNT(cột): Đếm dữ liệu thực Chỉ đếm các dòng mà giá trị tại cột được chỉ định KHÔNG BỊ NULL.

SQL

-- Nếu khách hàng chưa nhập số điện thoại (NULL), họ sẽ không được đếm
SELECT COUNT(phone) AS customers_with_phone 
FROM tpch.tiny.customer;

C. COUNT(DISTINCT cột): Đếm số lượng giá trị duy nhất Loại bỏ các giá trị trùng lặp và loại luôn cả NULL trước khi đếm.

SQL

-- Trả về số lượng các phân khúc thị trường (mktsegment) duy nhất
SELECT COUNT(DISTINCT mktsegment) AS unique_segments 
FROM tpch.tiny.customer;

(Cảnh báo kỹ sư: COUNT(DISTINCT) tiêu tốn lượng RAM rất lớn trong môi trường phân tán vì Trino phải lưu lại lịch sử các giá trị đã đếm để đối chiếu trùng lặp).

3. Tính Tổng (SUM) và Trung bình (AVG)

Hai hàm này chỉ hoạt động trên các kiểu dữ liệu số (Integer, Double, Decimal).

Đặc tính quan trọng: Cả SUM và AVG đều tự động bỏ qua các giá trị NULL trước khi tính toán.

• Hàm AVG(cột) thực chất bằng SUM(cột) / COUNT(cột). Vì nó chia cho COUNT(cột) chứ không chia cho COUNT(*), kết quả trung bình sẽ không bị kéo tụt xuống bởi các dòng rỗng.

SQL

SELECT 
    SUM(totalprice) AS total_revenue,
    AVG(totalprice) AS average_order_value
FROM tpch.tiny.orders;

4. Tìm GTLN (MAX) và GTNN (MIN)

Khác với SUM và AVG, hàm MAX và MIN có thể hoạt động trên cả kiểu Số, Chuỗi (theo thứ tự Alphabet), và Ngày tháng.

SQL

SELECT 
    MIN(orderdate) AS first_order_date,    -- Ngày cũ nhất
    MAX(orderdate) AS last_order_date,     -- Ngày gần đây nhất
    MAX(totalprice) AS highest_order_value -- Đơn hàng giá trị cao nhất
FROM tpch.tiny.orders;

5. Cạm bẫy: Cột tổng hợp và Cột vô hướng (Scalar)

Đây là lỗi cú pháp phổ biến nhất khi mới làm quen với hàm tổng hợp.

Luật thép: Một khi bạn đã sử dụng hàm tổng hợp trong mệnh đề SELECT, bạn không thể để một cột thông thường đứng bơ vơ bên cạnh nó, trừ khi cột thông thường đó được khai báo trong mệnh đề GROUP BY (sẽ học ở Bài 17).

Cú pháp SAI (Trino sẽ báo lỗi: must be an aggregate expression or appear in GROUP BY clause):

SQL

-- Lỗi logic: totalprice bị nén lại thành 1 dòng duy nhất, 
-- nhưng orderkey lại có 15,000 dòng. Trino không biết hiển thị thế nào.
SELECT 
    orderkey, 
    SUM(totalprice) 
FROM tpch.tiny.orders; 

6. Tổng hợp có điều kiện (Conditional Aggregation)

Bằng cách kết hợp SUM hoặc COUNT với mệnh đề CASE WHEN (Bài 15), bạn có thể bóc tách các chỉ số đo lường ra thành từng cột riêng biệt ngay trong một câu lệnh duy nhất. Đây là kỹ thuật tạo Pivot Table bằng SQL.

SQL

SELECT 
    SUM(CASE WHEN orderstatus = 'O' THEN totalprice ELSE 0 END) AS open_revenue,
    SUM(CASE WHEN orderstatus = 'F' THEN totalprice ELSE 0 END) AS finished_revenue,
    SUM(CASE WHEN orderstatus = 'P' THEN totalprice ELSE 0 END) AS pending_revenue
FROM tpch.tiny.orders;

Trong truy vấn trên, dữ liệu chỉ cần quét một lần duy nhất qua Worker, nhưng phân bổ dòng tiền được vào 3 phễu khác nhau dựa trên điều kiện, tối ưu I/O cực kỳ hiệu quả.