DISTINCT vs GROUP BY — cùng plan, khác intent

Hai query trả về cùng kết quả: SELECT DISTINCT user_id FROM tasks và SELECT user_id FROM tasks GROUP BY user_id. Chạy EXPLAIN cho cả hai — plan giống hệt: HashAggregate. Vậy khác gì nhau?

Bài này chỉ ra sự khác biệt là semantic intent chứ không phải hiệu năng, giải thích khi nào nên chọn cái nào, và giới thiệu DISTINCT ON — cú pháp PG-specific giải bài "top N per group" trong một dòng mà không cần window function.

1. Analogy — "lấy danh sách" vs "gom nhóm để đếm"

Hình dung bảng chấm công: mỗi nhân viên có nhiều dòng ứng với nhiều ngày làm việc. Bạn cần hai thứ khác nhau:

Yêu cầu A: "Cho tôi danh sách nhân viên đã từng chấm công." Intent: loại bỏ trùng lặp.

Yêu cầu B: "Cho tôi số ngày làm việc của mỗi nhân viên." Intent: gom nhóm để tính tổng hợp.

2. Cùng plan, khác intent

-- Ca hai query tra ve ket qua giong het nhau
SELECT DISTINCT user_id FROM tasks;
SELECT user_id FROM tasks GROUP BY user_id;

Chạy EXPLAIN cho cả hai:

EXPLAIN SELECT DISTINCT user_id FROM tasks;
-- HashAggregate  (cost=1820.00..1870.00 rows=5000 ...)
--   Group Key: user_id
--   ->  Seq Scan on tasks ...

EXPLAIN SELECT user_id FROM tasks GROUP BY user_id;
-- HashAggregate  (cost=1820.00..1870.00 rows=5000 ...)
--   Group Key: user_id
--   ->  Seq Scan on tasks ...

PostgreSQL planner biến DISTINCT col thành Group/HashAggregate node — về bản chất giống GROUP BY col. Cost giống nhau, execution plan giống nhau.

Sự khác biệt nằm ở readability và extensibility:

• Dùng DISTINCT khi chỉ cần dedupe một hoặc vài column — intent rõ ràng cho người đọc code.
• Dùng GROUP BY khi cần aggregate (COUNT, SUM, AVG) hoặc filter sau aggregate (HAVING).

Demo bắt buộc dùng GROUP BY khi thêm aggregate:

-- DISTINCT khong the lam duoc dieu nay
SELECT user_id, COUNT(*) AS task_count FROM tasks GROUP BY user_id;

-- Loi neu co gang them aggregate vao DISTINCT
-- ERROR: aggregate functions are not allowed in DISTINCT ON expressions

3. DISTINCT ON — PG-specific superpower

Bài toán: "lấy task gần nhất của mỗi user". Giải pháp thông thường là window function (Module 3 của khoá này sẽ đi sâu). Với PostgreSQL, có cách ngắn hơn: DISTINCT ON.

-- Lay task moi nhat cho moi user
SELECT DISTINCT ON (assignee_id) *
FROM tasks
WHERE assignee_id IS NOT NULL
ORDER BY assignee_id, created_at DESC;

Cú pháp: DISTINCT ON (col1, col2) * giữ lại row đầu tiên trong mỗi nhóm unique theo col1, col2 — theo thứ tự ORDER BY quyết định row nào là "đầu tiên".

Quan trọng: column trong DISTINCT ON (...) phải là leftmost prefix của ORDER BY. Nếu DISTINCT ON (assignee_id) thì ORDER BY phải bắt đầu bằng assignee_id.

Tương đương với window function (verbose hơn nhiều):

-- Window function equivalent -- dai hon nhung portable sang MySQL/SQLite
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY assignee_id ORDER BY created_at DESC) AS rn
  FROM tasks
  WHERE assignee_id IS NOT NULL
) sub
WHERE rn = 1;

DISTINCT ON ngắn hơn và planner thường tối ưu tốt hơn cho case đơn giản vì có thể tận dụng composite index trên (assignee_id, created_at DESC).

Hạn chế: DISTINCT ON chỉ có trong PostgreSQL. MySQL, SQLite, SQL Server không hỗ trợ — phải dùng window function khi cần portable.

4. Pitfall — DISTINCT * trên large table

Ví dụ anti-pattern kinh điển:

-- ANTI-PATTERN: dung DISTINCT de hide JOIN bug
-- Implicit cross join: moi user ghep voi moi task -> duplicate
SELECT DISTINCT u.*
FROM users u, tasks t
WHERE u.id = t.assignee_id;

-- FIX: explicit JOIN, khong can DISTINCT
SELECT u.*
FROM users u
INNER JOIN tasks t ON u.id = t.assignee_id;
-- Van co the co duplicate neu user co nhieu task -- can DISTINCT hoac GROUP BY dung nghia
-- Fix hoan chinh:
SELECT DISTINCT u.*
FROM users u
INNER JOIN tasks t ON u.id = t.assignee_id;
-- Hoac:
SELECT u.* FROM users u WHERE EXISTS (
  SELECT 1 FROM tasks t WHERE t.assignee_id = u.id
);

Nguyên tắc: nếu bạn đang thêm DISTINCT để "fix" duplicate trong kết quả, hãy hỏi tại sao có duplicate trước — thường là JOIN condition thiếu hoặc sai.

5. GROUP BY — strict mode của PostgreSQL

PostgreSQL yêu cầu mọi column trong SELECT list phải nằm trong GROUP BY clause hoặc được bọc trong aggregate function. MySQL trước 5.7 không enforce rule này — một nguồn gây bug khi migrate.

-- ERROR trong PostgreSQL
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
SELECT user_id, title FROM tasks GROUP BY user_id, title;

-- Fix 2: dung aggregate function
SELECT user_id, STRING_AGG(title, ', ') AS all_titles FROM tasks GROUP BY user_id;

MySQL trước 5.7 chạy thành công câu query lỗi trên nhưng trả về giá trị title ngẫu nhiên (không xác định row nào được chọn) — silent bug khó phát hiện. Port sang PostgreSQL sẽ fail ngay tại compile time, đây là hành vi đúng đắn hơn.

Module 3 của khoá này sẽ đi sâu vào GROUP BY kết hợp HAVING và các visibility rule của aggregate.

6. Applied — TaskFlow scenarios

Ba tình huống thực tế cho thấy khi nào chọn gì:

-- Scenario 1: list user da tung comment trong project X
-- DISTINCT phu hop: chi can dedupe, khong can count
SELECT DISTINCT u.id, u.name
FROM users u
JOIN comments c ON c.user_id = u.id
JOIN tasks t ON t.id = c.task_id
WHERE t.project_id = 5;

-- Scenario 2: count so comment moi user trong project X
-- GROUP BY bat buoc: can aggregate COUNT
SELECT u.name, COUNT(c.id) AS comment_count
FROM users u
JOIN comments c ON c.user_id = u.id
JOIN tasks t ON t.id = c.task_id
WHERE t.project_id = 5
GROUP BY u.id, u.name
ORDER BY comment_count DESC;

-- Scenario 3: lay comment moi nhat cua moi user trong project X
-- DISTINCT ON elegant: top 1 per group
SELECT DISTINCT ON (user_id) user_id, body, created_at
FROM comments
WHERE task_id IN (SELECT id FROM tasks WHERE project_id = 5)
ORDER BY user_id, created_at DESC;

7. Deep Dive — DISTINCT semantics

8. Tóm tắt

• DISTINCT và GROUP BY có cùng plan (HashAggregate) khi chỉ dedupe một column — khác biệt là semantic intent, không phải performance.
• Dùng DISTINCT khi chỉ cần danh sách không trùng, không cần tổng hợp số liệu — intent rõ ràng hơn cho người đọc code.
• Dùng GROUP BY khi cần aggregate (COUNT, SUM, AVG) hoặc filter qua HAVING.
• DISTINCT ON (col) là PG-specific — giữ row đầu tiên mỗi nhóm theo ORDER BY, giải bài "top N per group" ngắn gọn mà không cần window function. Column trong DISTINCT ON phải là leftmost prefix của ORDER BY.
• DISTINCT * thường là dấu hiệu JOIN sai — debug nguyên nhân duplicate thay vì patch bằng DISTINCT.
• PostgreSQL strict mode: mọi column trong SELECT phải nằm trong GROUP BY hoặc aggregate — MySQL trước 5.7 lax, port sang PG sẽ fail.
• Forward link: Module 3 của khoá này sẽ đi sâu vào GROUP BY + HAVING + aggregate visibility rules, và window function thay thế DISTINCT ON khi cần portable.

9. Tự kiểm tra

SELECT u.*
FROM users u
WHERE EXISTS (
SELECT 1 FROM tasks t WHERE t.assignee_id = u.id
);

-- Fix 1: them title vao GROUP BY
SELECT user_id, title FROM tasks GROUP BY user_id, title;

-- Fix 2: dung aggregate
SELECT user_id, MIN(title) AS first_title FROM tasks GROUP BY user_id;

Bài tiếp theo: Pattern matching — vì sao LIKE '%x' kill index