Trino SQL Bài học 12: Các hàm toán học và thống kê cơ bản (Math Functions)

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

• Nắm vững các phép toán số học và cơ chế chia số nguyên (Integer Division) đặc thù của Trino.

• Sử dụng các hàm làm tròn số một cách chính xác trong các bài toán tài chính.

• Xử lý các số liệu ngoại lệ (Căn bậc hai của số âm, lỗi Not-a-Number, Infinity).

• Ứng dụng hàm ngẫu nhiên trong việc phân cụm và lấy mẫu dữ liệu.

1. Toán tử số học và Cạm bẫy "Chia số nguyên"

Trino hỗ trợ các toán tử số học cơ bản: Cộng (+), Trừ (-), Nhân (*), Chia (/) và Chia lấy dư (%). Tuy nhiên, Trino tuân thủ chặt chẽ định dạng kiểu tĩnh (Strongly Typed), dẫn đến một lỗi cực kỳ phổ biến đối với những người mới chuyển sang từ MySQL hay Python.

Luật chia số nguyên (Integer Division): Trong Trino, nếu bạn lấy một số nguyên chia cho một số nguyên, kết quả trả về bắt buộc phải là một số nguyên (phần thập phân bị cắt bỏ hoàn toàn, không làm tròn).

Cú pháp gây sai số:

SQL

SELECT 5 / 2 AS result; 
-- Kết quả trả về: 2 (Phần dư 0.5 bị vứt bỏ)

Cách xử lý đúng (Ép kiểu ngầm định hoặc tường minh):

SQL

-- Cách 1: Thêm phần thập phân vào một trong hai số
SELECT 5.0 / 2 AS result; -- Kết quả: 2.5

-- Cách 2: Ép kiểu dữ liệu (CAST) khi làm việc với các cột trong bảng
SELECT CAST(totalprice AS DOUBLE) / quantity AS unit_price
FROM tpch.tiny.orders;

Toán tử chia lấy dư (Modulo):

SQL

-- Tìm các đơn hàng có orderkey là số chẵn
SELECT orderkey, totalprice 
FROM tpch.tiny.orders 
WHERE orderkey % 2 = 0;

2. Các hàm làm tròn số (Rounding Functions)

Trong các hệ thống phân tích doanh thu hoặc điểm số, dữ liệu thô thường chứa nhiều chữ số thập phân. Bạn cần sử dụng các hàm làm tròn để chuẩn hóa dữ liệu.

SQL

-- Tính thuế 8% cho từng đơn hàng và làm tròn đến 2 chữ số thập phân
SELECT 
    orderkey, 
    totalprice,
    ROUND(totalprice * 0.08, 2) AS tax_amount
FROM tpch.tiny.orders;

3. Các hàm toán học tuyệt đối và lũy thừa

Trino cung cấp đầy đủ các hàm phục vụ cho việc tính toán công thức khoa học hoặc mô hình hóa dữ liệu (Data Modeling).

• ABS(x): Lấy giá trị tuyệt đối. (Rất hữu ích khi tính toán độ lệch - variance).

• POWER(x, p) hoặc POW(x, p): Lấy x lũy thừa p.

• SQRT(x): Căn bậc hai. (Lưu ý: x phải $\ge 0$, nếu truyền số âm sẽ báo lỗi).

• EXP(x): Hàm số mũ $e^x$.

• LN(x): Lôgarit tự nhiên (cơ số $e$).

• LOG10(x) / LOG2(x): Lôgarit cơ số 10 và cơ số 2.

SQL

-- Tính khoảng cách biến động (độ lệch tuyệt đối) so với mục tiêu doanh thu 100,000
SELECT 
    orderkey, 
    totalprice,
    ABS(totalprice - 100000) AS deviation
FROM tpch.tiny.orders;

4. Hàm Random và kỹ thuật lấy mẫu (Sampling)

Trong Big Data, đôi khi bạn không cần query toàn bộ 1 tỷ dòng để vẽ một biểu đồ, bạn chỉ cần lấy một tập mẫu ngẫu nhiên (Random Sample) khoảng 1% để phân tích nhằm tiết kiệm tài nguyên.

Hàm RAND() hoặc RANDOM() trả về một số thực ngẫu nhiên dạng DOUBLE trong khoảng $0.0 \le x < 1.0$.

Ứng dụng thực tế 1: Lấy mẫu ngẫu nhiên 5% dữ liệu.

SQL

SELECT orderkey, totalprice
FROM tpch.tiny.orders
WHERE RAND() < 0.05; -- Lọc ra ngẫu nhiên khoảng 5% số lượng bản ghi

Ứng dụng thực tế 2: Phân chia tập thử nghiệm (A/B Testing).

SQL

SELECT 
    custkey,
    CASE WHEN RAND() > 0.5 THEN 'Group A' ELSE 'Group B' END AS testing_group
FROM tpch.tiny.customer;

5. Xử lý các trạng thái đặc biệt: NaN và Infinity

Khi thực hiện các phép toán phức tạp trên kiểu dữ liệu dấu phẩy động (Double/Real), có thể sinh ra các kết quả phi toán học. Trino hỗ trợ nhận diện và xử lý các trạng thái này thay vì sập hệ thống.

• NaN (Not a Number): Xảy ra khi thực hiện các phép toán vô lý (ví dụ: $0.0 / 0.0$).

• Infinity / -Infinity: Xảy ra khi chia một số khác không cho không (ví dụ: $1.0 / 0.0$).

Trino cung cấp các hàm logic để kiểm tra:

• is_nan(x): Trả về TRUE nếu x là Not-a-Number.

• is_infinite(x): Trả về TRUE nếu x là vô cực.

• is_finite(x): Trả về TRUE nếu x là một số bình thường.

SQL

-- Xử lý an toàn các dòng bị lỗi chia vô cực
SELECT 
    metric_a, 
    metric_b,
    CASE 
        WHEN is_infinite(metric_a / metric_b) THEN NULL
        ELSE metric_a / metric_b 
    END AS safe_ratio
FROM data_table;

(Ghi chú: Lỗi Infinity chỉ xảy ra với số thực DOUBLE/REAL. Nếu bạn chia một số nguyên INTEGER cho 0, Trino sẽ báo lỗi cứng "Divide by Zero". Vì vậy, hàm NULLIF đã học ở Bài 10 vẫn là giải pháp phòng vệ tốt nhất).