GROUP BY + HAVING — SQL logical processing order quyết định visibility

Bạn đang viết report TaskFlow: "project nào có vượt 10 task done?" Câu query đầu tiên trông hợp lý:

SELECT project_id, COUNT(*)
FROM tasks
WHERE status = 'done' AND COUNT(*) > 10
GROUP BY project_id;

PostgreSQL báo ngay: ERROR: aggregate functions are not allowed in WHERE. Không phải lỗi cú pháp thông thường — đây là lỗi semantic từ SQL engine. Và nếu bạn chưa biết lý do, sửa sai sẽ mất nhiều thời gian hơn cần thiết.

Nguyên nhân: WHERE chạy trước khi group được tạo. COUNT(*) là aggregate — nó cần group mới có thể tính. Bạn đang yêu cầu SQL filter bằng một giá trị chưa tồn tại ở thời điểm filter đang chạy.

SQL có logical processing order strict — mỗi clause được evaluate theo thứ tự cố định, không phải thứ tự bạn viết trên màn hình. Hiểu thứ tự này giải quyết 80% bug GROUP BY. Bài này map toàn bộ order, phân biệt WHERE vs HAVING, và giải thích visibility rule strict của PostgreSQL.

1. Analogy — Quy trình nấu ăn theo thứ tự bắt buộc

Hãy hình dung SQL như một quy trình nấu ăn: bạn không thể nêm gia vị trước khi có nguyên liệu, không thể bày đĩa trước khi nấu xong. Mỗi bước phụ thuộc bước trước — bỏ qua thứ tự là hỏng món.

SQL clause cũng vậy. WHERE phải xong trước GROUP BY vì bạn cần biết row nào hợp lệ trước khi gom nhóm. HAVING phải sau GROUP BY vì bạn cần group để tính aggregate mà HAVING dùng để filter. SELECT phải sau cùng (trừ ORDER BY/LIMIT) vì đây là bước chọn và đặt tên — alias chưa tồn tại trước bước này.

2. SQL logical processing order

Đây là thứ tự SQL engine evaluate từng clause, không phải thứ tự bạn viết:

Quy tắc visibility — column nào thấy được ở đâu:

• WHERE chỉ thấy column từ FROM/JOIN. Chưa thấy aggregate, chưa thấy SELECT alias.
• HAVING thấy column trong GROUP BY và aggregate function. Không thấy SELECT alias (alias chưa tồn tại).
• ORDER BY thấy SELECT alias (sau SELECT — alias đã được đặt). Đây là clause duy nhất thấy alias.

Ví dụ annotated theo thứ tự chạy:

SELECT
  user_id,                       -- 5. SELECT
  COUNT(*) AS task_count         -- 5. SELECT (alias task_count chua available truoc day)
FROM tasks                       -- 1. FROM
WHERE status = 'done'            -- 2. WHERE (truoc GROUP BY, khong thay COUNT(*))
GROUP BY user_id                 -- 3. GROUP BY
HAVING COUNT(*) > 10             -- 4. HAVING (sau aggregate, co the dung COUNT(*))
ORDER BY task_count DESC         -- 6. ORDER BY (alias OK vi sau SELECT)
LIMIT 5;                         -- 7. LIMIT

3. WHERE vs HAVING — phân biệt theo thời điểm chạy

Hai clause đều filter — nhưng filter ở hai thời điểm khác nhau trong logical order:

-- WHERE filter row TRUOC khi group
-- Chi tinh task co status = 'done'
SELECT user_id, COUNT(*)
FROM tasks
WHERE status = 'done'
GROUP BY user_id;

-- HAVING filter group SAU khi aggregate
-- Chi giu user co vuot 10 task (bat ky status nao)
SELECT user_id, COUNT(*)
FROM tasks
GROUP BY user_id
HAVING COUNT(*) > 10;

-- Ket hop: WHERE filter row truoc, HAVING filter group sau
-- "User moi trong 90 ngay co vuot 10 task done"
SELECT user_id, COUNT(*)
FROM tasks
WHERE status = 'done'
GROUP BY user_id
HAVING COUNT(*) > 10
   AND user_id IN (SELECT id FROM users WHERE created_at > now() - INTERVAL '90 days');

Quy tắc phân loại predicate:

Đặt predicate vào WHERE khi có thể — database lọc row trước khi group, working set nhỏ hơn, planner có thể dùng index. HAVING chỉ dùng khi predicate cần aggregate.

4. Strict GROUP BY — column visibility trong PostgreSQL

PostgreSQL theo đúng SQL standard: mọi column trong SELECT phải nằm trong GROUP BY hoặc được bọc bởi aggregate function. Không có ngoại lệ.

-- ERROR: title khong trong GROUP BY, khong duoc aggregate
SELECT user_id, title
FROM tasks
GROUP BY user_id;
-- ERROR: column "tasks.title" must appear in the GROUP BY clause
--        or be used in an aggregate function

-- Fix 1: them title vao GROUP BY
-- Semantic: nhom theo (user_id, title) -- moi cap (user, title) la 1 group
SELECT user_id, title
FROM tasks
GROUP BY user_id, title;

-- Fix 2: aggregate title de gop nhieu title thanh 1 gia tri
SELECT user_id, STRING_AGG(title, ', ') AS titles
FROM tasks
GROUP BY user_id;

So sánh PG vs MySQL:

PostgreSQL và MySQL 8.0+ (với ONLY_FULL_GROUP_BY=ON mặc định) đều strict như SQL standard. MySQL 5.x và 6.x lax hơn — cho phép column ngoài GROUP BY trong SELECT, trả về giá trị tùy ý (không deterministic) từ nhóm. Đây là nguồn gốc của nhiều bug im lặng khi migrate sang MySQL 8 hoặc PostgreSQL.

5. Pitfall tổng hợp

-- BUG 1: aggregate trong WHERE
SELECT user_id
FROM tasks
WHERE COUNT(*) > 10
GROUP BY user_id;
-- ERROR: aggregate functions are not allowed in WHERE
-- Fix: dung HAVING COUNT(*) > 10

-- BUG 2: SELECT alias trong WHERE (alias chua ton tai)
SELECT user_id, COUNT(*) AS cnt
FROM tasks
WHERE cnt > 10          -- ERROR: column "cnt" does not exist
GROUP BY user_id;
-- Fix: WHERE khong the dung alias. Dung HAVING COUNT(*) > 10

-- BUG 3: SELECT alias trong HAVING (alias chua ton tai o buoc 4)
SELECT user_id, COUNT(*) AS cnt
FROM tasks
GROUP BY user_id
HAVING cnt > 10;        -- ERROR trong PostgreSQL (alias chua co)
-- Fix: HAVING COUNT(*) > 10 (lap lai expression)
-- Luu y: mot so DB (MySQL, SQLite) cho phep alias trong HAVING
--        nhung la extension, khong phai SQL standard

-- BUG 4: positional GROUP BY -- fragile khi refactor SELECT
SELECT user_id, status, COUNT(*)
FROM tasks
GROUP BY 1, 2;
-- OK nhung reorder SELECT -> GROUP BY sai theo. Tot hon: GROUP BY user_id, status

6. ORDER BY — clause duy nhất thấy SELECT alias

ORDER BY là bước thứ 6 — sau SELECT (5). Vì vậy nó là clause duy nhất có thể tham chiếu alias đặt trong SELECT:

SELECT user_id, COUNT(*) AS task_count
FROM tasks
GROUP BY user_id
ORDER BY task_count DESC;   -- alias OK vi ORDER BY chay sau SELECT

Đây là quirk của logical order — alias chỉ "sống" từ bước SELECT trở đi. WHERE, GROUP BY, HAVING không thấy alias. ORDER BY thấy vì nó đến sau. LIMIT/OFFSET cũng sau SELECT nhưng không tham chiếu column nên không liên quan.

7. Applied — TaskFlow analytics

Query thực chiến: "tỉ lệ hoàn thành theo project, chỉ project có vượt 5 task":

SELECT
  p.name                                                         AS project_name,
  COUNT(*)                                                       AS total_tasks,
  COUNT(*) FILTER (WHERE t.status = 'done')                     AS done_tasks,
  ROUND(
    COUNT(*) FILTER (WHERE t.status = 'done') * 100.0
      / NULLIF(COUNT(*), 0),
    2
  )                                                              AS completion_pct
FROM tasks t
INNER JOIN projects p ON t.project_id = p.id
GROUP BY p.id, p.name
HAVING COUNT(*) > 5
ORDER BY completion_pct DESC NULLS LAST;

project_name  | total_tasks | done_tasks | completion_pct
--------------+-------------+------------+----------------
OLHub         |          32 |         28 |          87.50
Marketing     |          18 |         12 |          66.67
Mobile        |           8 |          3 |          37.50

Giải thích các điểm đáng chú ý:

• GROUP BY p.id, p.name — include cả p.id (primary key) và p.name (để SELECT dùng được p.name mà không aggregate). Thêm p.id vào GROUP BY là pattern phổ biến: group theo PK thực sự, thêm các column display vào GROUP BY để thoả strict mode.
• NULLIF(COUNT(*), 0) — tránh division by zero nếu project có 0 task (mặc dù HAVING loại project đó — defence in depth).
• ORDER BY completion_pct DESC NULLS LAST — NULL (từ NULLIF) đẩy xuống cuối.
• FILTER (WHERE t.status = 'done') — conditional aggregate, Module 3 bài 5 của khoá này đi sâu về pattern này.

Module 7 của khoá này (query planner) giải thích cách planner reorder WHERE → GROUP BY để tận dụng index và tại sao filter sớm trong WHERE quan trọng hơn filter muộn trong HAVING về mặt hiệu năng.

8. Deep Dive — GROUP BY semantics

9. Tóm tắt

• SQL logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT — không phải thứ tự viết trên màn hình.
• WHERE filter row trước khi group — chỉ thấy column từ FROM/JOIN, không thấy aggregate, không thấy SELECT alias.
• HAVING filter group sau aggregate — thấy column GROUP BY và aggregate function, nhưng không thấy SELECT alias.
• Aggregate function trong WHERE báo error — dùng HAVING thay thế.
• SELECT alias chỉ available trong ORDER BY (clause duy nhất chạy sau SELECT). WHERE, GROUP BY, HAVING không thấy alias.
• Strict GROUP BY (PostgreSQL, MySQL 8+): SELECT chỉ được chứa column nằm trong GROUP BY hoặc được bọc bởi aggregate.
• Predicate không dùng aggregate → WHERE (filter sớm, hiệu quả). Predicate dùng aggregate → HAVING (bắt buộc).
• Module 3 bài 5 của khoá này: FILTER aggregate deep dive. Module 7 của khoá này: planner reorder và tối ưu GROUP BY.

10. Tự kiểm tra

SELECT project_id, COUNT(*)
FROM tasks
WHERE status = 'done'
GROUP BY project_id
HAVING COUNT(*) > 10;

SELECT user_id, COUNT(*) AS cnt
FROM tasks
GROUP BY user_id
HAVING COUNT(*) > 10;

SELECT user_id, cnt
FROM (
SELECT user_id, COUNT(*) AS cnt
FROM tasks
GROUP BY user_id
) sub
WHERE cnt > 10;

SELECT user_id, COUNT(*) AS active_count
FROM tasks
WHERE status IN ('todo', 'doing')
GROUP BY user_id
HAVING COUNT(*) > 10;

SELECT user_id,
COUNT(*) FILTER (WHERE status IN ('todo', 'doing')) AS active_count
FROM tasks
GROUP BY user_id
HAVING COUNT(*) FILTER (WHERE status IN ('todo', 'doing')) > 10;

Bài tiếp theo: Aggregate functions — COUNT/SUM + FILTER + STRING_AGG/JSON_AGG