Hàm tổng hợp SQL trong SQLite: COUNT, SUM, AVG, MIN, MAX

Hàm tổng hợp thực sự làm gì

Phần lớn các hàm SQL bạn gặp đến giờ đều hoạt động theo từng hàng: UPPER(name) chạy một lần cho mỗi hàng, ROUND(price, 2) chạy một lần cho mỗi hàng. Hàm tổng hợp (aggregate function) thì khác. Chúng nhìn vào cả một tập hàng và gộp tập đó lại thành một giá trị duy nhất.

Tạo một bảng nhỏ để thử nghiệm:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

Năm hàng đi vào, một hàng đi ra. Đó là toàn bộ mô hình cần nhớ: hàm tổng hợp ép nhiều hàng thành một bản tóm tắt. Khi không có GROUP BY, bản tóm tắt bao trùm mọi hàng trong kết quả.

COUNT: đếm hàng và đếm giá trị

COUNT có ba dạng, và sự khác biệt giữa chúng rất quan trọng:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)               AS rows_total,
    COUNT(amount)          AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

• COUNT(*) đếm hàng. Tính cả NULL. Luôn trả về một con số.
• COUNT(column) đếm các giá trị không NULL trong cột đó.
• COUNT(DISTINCT column) đếm các giá trị không NULL và không trùng nhau.

Năm hàng, ba hàng có amount, ba khách hàng khác nhau. Nếu có lúc bạn thấy COUNT(amount) và thắc mắc vì sao nó nhỏ hơn COUNT(*), thì đó là lý do: NULL không được đếm.

SUM, AVG, MIN, MAX

Các hàm tổng hợp số học hoạt động đúng như bạn nghĩ, kèm một quy tắc ngầm: tất cả đều bỏ qua NULL:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount) AS total,
    AVG(amount) AS mean,
    MIN(amount) AS smallest,
    MAX(amount) AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG là (10 + 20 + 30) / 3 = 20.0, không phải 60 / 4 = 15.0. Mẫu số là số lượng giá trị không NULL. Nếu đó không phải điều bạn muốn, tức là bạn muốn coi dữ liệu bị thiếu là số không, hãy viết rõ ra:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

MIN và MAX cũng dùng được với văn bản và ngày tháng: văn bản được so sánh theo thứ tự từ điển, còn ngày tháng ở định dạng chuẩn được so sánh như chuỗi ISO.

SUM và TOTAL

SQLite có thêm một hàm tổng hợp kiểu cộng dồn thứ hai, TOTAL, khắc phục hai điểm khó chịu của SUM:

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

• SUM trên không hàng nào trả về NULL. TOTAL trả về 0.0.
• SUM trên toàn giá trị NULL trả về NULL. TOTAL trả về 0.0.
• TOTAL luôn trả về số thực, nên không bao giờ bị tràn số như phép tính số nguyên.

Cái giá phải trả: TOTAL không theo chuẩn SQL, và kết quả luôn là REAL có thể khiến bạn bất ngờ nếu đang chờ một số nguyên. Hãy dùng nó khi "không có hàng nghĩa là bằng không" là câu trả lời đúng cho ứng dụng của bạn, và giữ SUM khi bạn cần hành vi đúng chuẩn SQL.

DISTINCT bên trong hàm tổng hợp

DISTINCT có thể đặt bên trong bất kỳ hàm tổng hợp nào, không chỉ COUNT. Nó loại bỏ các giá trị trùng trước khi phép tổng hợp chạy:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)          AS sum_all,
    SUM(DISTINCT amount) AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount) cộng số tiền của mọi hàng. SUM(DISTINCT amount) cộng mỗi số tiền khác nhau đúng một lần: hữu ích cho những việc như "tổng các số tiền hóa đơn không trùng", nhưng hiếm khi là thứ bạn cần. COUNT(DISTINCT customer) mới là cách dùng phổ biến.

FILTER: tổng hợp một phần dữ liệu

Khi bạn chỉ muốn tổng hợp một số hàng, cách hiển nhiên là dùng WHERE. Nhưng WHERE lọc tất cả, nên bạn không thể vừa "đếm đơn đã thanh toán" vừa "đếm đơn hoàn tiền" trong cùng một truy vấn theo cách đó. FILTER giải quyết chuyện này:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders;

Mỗi mệnh đề FILTER (WHERE ...) chỉ áp dụng cho đúng một hàm tổng hợp đó. Duyệt bảng một lần, tóm tắt được nhiều phần dữ liệu. Trước khi có FILTER, người ta viết SUM(CASE WHEN status = 'paid' THEN amount END): cùng ý tưởng, nhưng phải gõ nhiều hơn.

GROUP_CONCAT: nối chuỗi

GROUP_CONCAT là trường hợp đặc biệt. Thay vì trả về một con số, nó nối các giá trị thành một chuỗi duy nhất:

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

Dấu phân cách mặc định là dấu phẩy. Truyền đối số thứ hai để dùng ký tự khác. Thứ tự không được đảm bảo trừ khi bạn viết lời gọi thành GROUP_CONCAT(tag ORDER BY tag), rất tiện khi kết quả hiển thị trên giao diện và bạn muốn nó ổn định.

Tổng hợp không có GROUP BY

Mọi ví dụ đến giờ dùng hàm tổng hợp mà không có GROUP BY đều cho ra đúng một hàng. Đó là quy tắc: một câu SELECT có hàm tổng hợp và không có GROUP BY là bản tóm tắt một hàng của toàn bộ bảng (sau khi áp dụng WHERE).

Bạn có thể kết hợp các hàm tổng hợp tùy ý:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

Điều bạn không thể làm là trộn cột không tổng hợp với hàm tổng hợp và mong nhận được kết quả hợp lý:

-- Allowed by SQLite, but the value of `customer` is arbitrary.
SELECT customer, SUM(amount) FROM orders;

SQLite không báo lỗi ở đây (các cơ sở dữ liệu khác thì có), nhưng nó sẽ chọn ngẫu nhiên tên một khách hàng nào đó để hiển thị cạnh tổng số. Nếu bạn muốn tổng theo từng khách hàng, bạn cần GROUP BY, chính là nội dung của trang tiếp theo.

Tiếp theo: GROUP BY và HAVING

Hàm tổng hợp trên toàn bảng trả lời câu hỏi "tổng cộng là bao nhiêu". Hàm tổng hợp theo từng nhóm, theo khách hàng, theo tháng, theo trạng thái, trả lời những câu hỏi thú vị hơn. GROUP BY là cách chia các hàng thành từng nhóm trước khi tổng hợp, còn HAVING là cách lọc trên kết quả đã tổng hợp. Đó là nội dung tiếp theo.