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.

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:

• MySQL dùng GROUP_CONCAT(col SEPARATOR ', ') thay STRING_AGG.
• ARRAY_AGG và JSON_AGG là PostgreSQL-specific. MySQL 5.7+ có JSON_ARRAYAGG tương đương JSON_AGG.
• PostgreSQL còn có JSONB_AGG (binary JSON, indexable) — Module 9 của khoá này đi sâu về JSONB.

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
-- PostgreSQL: AVG(int) tra ve numeric, KHONG phai int
SELECT AVG(score) FROM ratings;
-- Ket qua: 4.333333333333333... (numeric)
-- Neu can round: ROUND(AVG(score)::numeric, 2) hoac ROUND(AVG(score), 2)

-- MySQL: ket qua phu thuoc column type va version
-- An toan: luon ROUND() hoac cast explicit khi dung AVG

-- 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 approximate aggregate — extension postgresql-hll. Module 9 của khoá này đề cập khi nói về approximate query và advanced indexing.

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 < now()
                    AND t.status != 'done')                   AS overdue,
  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
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 — bài 9.3 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.
• Cross-vendor: STRING_AGG (PG) vs GROUP_CONCAT (MySQL); FILTER (PG/SQLite 3.30+), MySQL fallback SUM(CASE WHEN ...).
• Forward: Module 9 của khoá này (JSONB deep + JSON aggregate), Module 7 của khoá này (planner 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;

-- Cai extension truoc: CREATE EXTENSION hll;
SELECT project_id, hll_cardinality(hll_add_agg(hll_hash_integer(user_id)))
FROM tasks
GROUP BY project_id;

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