Aggregate functions — COUNT/SUM + FILTER + STRING_AGG/JSON_AGG

Dashboard TaskFlow cần report theo project: tổng số task, task đã xong, task trễ deadline, số ngày hoàn thành trung bình, và danh sách người được giao đang active. Cách đơn giản nhất: 5 query riêng, mỗi query GROUP BY project. Kết quả không atomic — snapshot mỗi query tại một thời điểm khác nhau, có thể inconsistent nếu data thay đổi giữa chừng. Và 5 round-trip database thay vì 1.

Bài này map 5 aggregate cốt lõi (COUNT, SUM, AVG, MIN, MAX), 3 advanced aggregate (STRING_AGG, ARRAY_AGG, JSON_AGG), và FILTER clause — cho phép thu gọn 5 query CASE WHEN thành 1 line duy nhất với atomic snapshot.

1. Analogy — Báo cáo Excel per region

Hãy hình dung aggregate như bảng báo cáo Excel pivot: bạn có danh sách đơn hàng từng vùng, và pivot table tóm tắt per region thành tổng doanh thu, số đơn, và list tên khách. Mỗi region từ nhiều row trở thành một dòng tổng kết.

SQL aggregate function làm đúng việc đó — nhận một group (nhiều row) và trả về một giá trị duy nhất đại diện cho cả nhóm.

flowchart LR
  G1["Nhom project 1\n(nhieu dong tasks)"] --> A1["COUNT(*) = 32\nSUM(hours) = 128\nAVG = 4.0"]
  G2["Nhom project 2\n(nhieu dong tasks)"] --> A2["COUNT(*) = 18\nSUM(hours) = 54\nAVG = 3.0"]
  G3["Nhom project 3\n(nhieu dong tasks)"] --> A3["COUNT(*) = 8\nSUM(hours) = 16\nAVG = 2.0"]

2. 5 core aggregate — COUNT, SUM, AVG, MIN, MAX

SELECT
  status,
  COUNT(*)                        AS row_count,         -- dem moi row ke ca NULL
  COUNT(assignee_id)              AS assigned_count,    -- skip NULL
  COUNT(DISTINCT assignee_id)     AS unique_assignees,  -- distinct value only
  SUM(EXTRACT(DAY FROM now() - created_at))  AS sum_age_days,
  AVG(EXTRACT(DAY FROM now() - created_at))  AS avg_age_days,
  MIN(created_at)                 AS oldest,
  MAX(created_at)                 AS newest
FROM tasks
GROUP BY status;

Các quirk quan trọng:

• COUNT(*) đếm mọi row kể cả NULL. COUNT(col) bỏ qua row có col IS NULL — hai giá trị này khác nhau khi có NULL trong column.
• COUNT(DISTINCT col) đếm giá trị phân biệt. Bài học này sẽ đề cập pitfall performance ở section 6.
• AVG(int_col) trong PostgreSQL trả về kiểu numeric, không phải int — bài này giải thích pitfall ở section 5.
• SUM trên empty group trả về NULL không phải 0 — cũng là pitfall section 5.

3. STRING_AGG / ARRAY_AGG / JSON_AGG — gom rows thành single value

Ba aggregate này không trả về số — chúng gom nhiều row thành một string, array, hoặc JSON array. Đặc biệt hữu ích khi cần trả về parent kèm danh sách children trong một row duy nhất (REST API single-call pattern).

-- STRING_AGG: concat row trong group voi separator
SELECT
  project_id,
  STRING_AGG(title, ' | ' ORDER BY created_at) AS task_titles
FROM tasks
GROUP BY project_id;

-- ARRAY_AGG: gom row thanh PostgreSQL array
SELECT
  project_id,
  ARRAY_AGG(id ORDER BY created_at) AS task_ids
FROM tasks
GROUP BY project_id;

-- JSON_AGG: gom row thanh JSON array (huu ich tra API)
SELECT
  project_id,
  JSON_AGG(
    JSON_BUILD_OBJECT('id', id, 'title', title)
    ORDER BY created_at
  ) AS tasks_json
FROM tasks
GROUP BY project_id;

JSON_AGG kết hợp JSON_BUILD_OBJECT trả về một JSON array of objects — đúng định dạng mà nhiều REST API trả về. Thay vì query tasks riêng rồi join trong application code, một query duy nhất trả về project kèm toàn bộ tasks dạng JSON.

Cross-vendor:

• STRING_AGG(col, sep) — PostgreSQL, SQL Server 2017+, SQLite 3.44+. MySQL dùng GROUP_CONCAT(col SEPARATOR ', '). Oracle 19c+ dùng LISTAGG(col, ', ') WITHIN GROUP (ORDER BY col).
• ARRAY_AGG — PostgreSQL-specific (trả về array native). Các engine khác không có tương đương trực tiếp.
• JSON_AGG — PostgreSQL. MySQL 5.7+ có JSON_ARRAYAGG. SQL Server có FOR JSON AUTO. Oracle 12c+ có JSON_ARRAYAGG.
• PostgreSQL còn có JSONB_AGG (binary JSON, indexable) — chỉ PostgreSQL. Module 9 của khoá này đề cập khi nói về JSON storage.

4. FILTER clause — conditional aggregate

FILTER là cú pháp ANSI SQL chuẩn, PostgreSQL hỗ trợ đầy đủ. Thay vì viết 5 query riêng hay 5 CASE WHEN, một query với nhiều FILTER clause làm tất cả trong một lần scan.

-- WITHOUT FILTER (verbose, kho doc)
SELECT
  project_id,
  SUM(CASE WHEN status = 'done'  THEN 1 ELSE 0 END) AS done_count,
  SUM(CASE WHEN status = 'doing' THEN 1 ELSE 0 END) AS doing_count,
  SUM(CASE WHEN status = 'todo'  THEN 1 ELSE 0 END) AS todo_count
FROM tasks
GROUP BY project_id;

-- WITH FILTER (gon, de doc, same performance)
SELECT
  project_id,
  COUNT(*) FILTER (WHERE status = 'done')  AS done_count,
  COUNT(*) FILTER (WHERE status = 'doing') AS doing_count,
  COUNT(*) FILTER (WHERE status = 'todo')  AS todo_count
FROM tasks
GROUP BY project_id;

FILTER hoạt động với mọi aggregate, không chỉ COUNT:

SELECT
  region,
  SUM(amount)   FILTER (WHERE category = 'hardware')            AS hardware_revenue,
  AVG(rating)   FILTER (WHERE created_at > now() - INTERVAL '30 days') AS recent_avg_rating,
  STRING_AGG(tag, ', ' ORDER BY tag)
                FILTER (WHERE tag IS NOT NULL)                  AS tags
FROM orders
GROUP BY region;

Cross-vendor: PostgreSQL hỗ trợ FILTER. SQLite từ phiên bản 3.30+. MySQL không có FILTER native — dùng SUM(CASE WHEN ...) làm fallback.

5. Pitfall — AVG integer truncation + SUM empty trả NULL

-- PITFALL 1: AVG column integer
-- Ket qua kieu du lieu phu thuoc engine:
--   PostgreSQL: AVG(int) tra ve numeric (floating point)
--   MySQL: AVG(int) tra ve double
--   SQL Server: AVG(int) tra ve int (truncated!) -- nguy hiem nhat
SELECT AVG(score) FROM ratings;
-- An toan: luon ROUND() hoac cast explicit khi dung AVG
-- PostgreSQL: ROUND(AVG(score), 2)
-- SQL Server: ROUND(AVG(CAST(score AS FLOAT)), 2)

-- PITFALL 2: SUM tren empty group tra NULL khong phai 0
SELECT SUM(amount) FROM payments WHERE user_id = 9999;
-- User khong co payment nao -> ket qua: NULL
-- WRONG assumption: "0 payment = 0 tong" -- SQL khong suy luan nhu vay

-- Fix: COALESCE wrap SUM
SELECT COALESCE(SUM(amount), 0) AS total_paid
FROM payments
WHERE user_id = 9999;

6. Pitfall — COUNT DISTINCT chậm trên large group

-- CHAM tren large table (ví du 10M row)
SELECT project_id, COUNT(DISTINCT assignee_id)
FROM tasks
GROUP BY project_id;
-- Internal: moi group build hash set cua assignee_id roi dem
-- O(N log N) hoac O(N) per group tuy implementation

-- Alternative khi cardinality thap: subquery 2 stage
-- Distinct truoc, COUNT sau -- planner co them lua chon plan
SELECT project_id, COUNT(*) AS unique_assignees
FROM (
  SELECT DISTINCT project_id, assignee_id FROM tasks
) sub
GROUP BY project_id;

Với cardinality rất cao (ví dụ đếm distinct user_id trên bảng hàng triệu row), có thể dùng HyperLogLog — thuật toán xấp xỉ cardinality với sai số dưới 1%, dùng constant memory per group thay vì O(N). Mỗi engine có cách hỗ trợ khác nhau: PostgreSQL qua extension pg_hll; ClickHouse có uniq(); BigQuery có APPROX_COUNT_DISTINCT() chuẩn. Module 9 của khoá này đề cập khi nói về approximate query.

7. Applied — TaskFlow project analytics một query

-- Per project: total + done + active + overdue + avg completion days + active assignees
SELECT
  p.id,
  p.name,
  COUNT(*)                                                     AS total,
  COUNT(*) FILTER (WHERE t.status = 'done')                   AS done,
  COUNT(*) FILTER (WHERE t.status IN ('todo','doing'))         AS active,
  COUNT(*) FILTER (WHERE t.due_at < CURRENT_TIMESTAMP
                    AND t.status != 'done')                   AS overdue,
  -- CURRENT_TIMESTAMP: ANSI SQL; PG also accepts now()
  ROUND(
    AVG(EXTRACT(DAY FROM (t.updated_at - t.created_at)))
      FILTER (WHERE t.status = 'done'),
    1
  )                                                            AS avg_complete_days,
  STRING_AGG(DISTINCT u.name, ', ' ORDER BY u.name)
    FILTER (WHERE t.status IN ('todo','doing'))                AS active_assignees
  -- STRING_AGG: PG/SQL Server 2017+/SQLite 3.44+; MySQL fallback: GROUP_CONCAT(DISTINCT u.name ORDER BY u.name SEPARATOR ', ')
FROM projects p
LEFT JOIN tasks t     ON t.project_id = p.id
LEFT JOIN users u     ON t.assignee_id = u.id
GROUP BY p.id, p.name
ORDER BY p.name;

id | name      | total | done | active | overdue | avg_complete_days | active_assignees
---+-----------+-------+------+--------+---------+-------------------+------------------
 1 | OLHub     |    32 |   28 |      4 |       1 |               5.2 | An, Binh, Cuong
 2 | Marketing |    18 |   12 |      6 |       0 |               3.8 | Binh, Em

Một query thay 5 query — một lần scan table, một atomic snapshot tại cùng một thời điểm. Module 7 của khoá này (query planner) giải thích cách planner chọn HashAggregate vs SortAggregate cho GROUP BY và tại sao LEFT JOIN + aggregate ảnh hưởng plan.

8. Deep Dive — Aggregate functions

Liên kết khoá học khác

• Java — map / filter / reduce — Stream.reduce() và Collectors.summarizing*() tương ứng SQL aggregate, khi nào nên xử lý ở app vs DB.

9. Tóm tắt

• 5 core: COUNT / SUM / AVG / MIN / MAX — aggregate nhận group, trả 1 giá trị.
• 3 advanced: STRING_AGG (concat với separator), ARRAY_AGG (PG array), JSON_AGG (JSON array) — gom rows thành single structured value.
• FILTER clause: COUNT(*) FILTER (WHERE ...) — ANSI SQL chuẩn, thay thế SUM(CASE WHEN ...) bằng cú pháp gọn hơn, cùng hiệu năng.
• Pitfall: AVG(int) trả numeric không phải int — cast hoặc ROUND explicit khi cần. SUM empty group trả NULL không phải 0 — wrap COALESCE(SUM(col), 0).
• COUNT(DISTINCT col) chậm trên large group — subquery 2 stage hoặc HyperLogLog approximate là alternative.
• JSON_AGG kết hợp JSON_BUILD_OBJECT cho REST API single-call pattern — một query trả parent kèm children dạng JSON (PostgreSQL; các engine khác có tương đương riêng).
• Cross-vendor: STRING_AGG (PG/SQL Server/SQLite) vs GROUP_CONCAT (MySQL) vs LISTAGG (Oracle); FILTER (ANSI SQL, PG/SQLite 3.30+), MySQL fallback SUM(CASE WHEN ...).
• Forward: Module 9 của khoá này (JSON storage & aggregate), Module 8 của khoá này (query execution — HashAggregate vs SortAggregate).

10. Tự kiểm tra

SELECT COALESCE(SUM(amount), 0) AS total FROM payments WHERE user_id = 9999;

SELECT project_id, COUNT(*) AS unique_users
FROM (
SELECT DISTINCT project_id, user_id FROM tasks
) sub
GROUP BY project_id;

Bài tiếp theo: Window functions intro — OVER + PARTITION BY