1.1 Mục tiêu tuần 3

Hai tuần trước bạn đã làm chủ cấp độ "một dòng vào, một dòng ra" — lọc và biến đổi. Tuần này nâng cấp lên cấp độ "nhiều dòng vào, một dòng ra" — gốc rễ của phân tích dữ liệu trong SQL.

Sau tuần 3 bạn cần làm được

• Hiểu chính xác vì sao COUNT(*), COUNT(col), COUNT(DISTINCT col) cho 3 kết quả khác nhau.
• Phân biệt WHERE vs HAVING trong 5 giây — đây là câu hỏi screening kinh điển.
• Viết được conditional aggregation dạng SUM(CASE WHEN ...) không cần suy nghĩ.
• Giải được khoảng 30% bài LeetCode SQL Easy/Medium chỉ với GROUP BY + HAVING + conditional aggregation.

1.2 Mental model: Phân hoạch và Reduce

GROUP BY là phân hoạch tập dữ liệu thành các nhóm theo khóa, sau đó reduce mỗi nhóm thành một dòng bằng aggregator.

SQL: SELECT customer, SUM(total) FROM orders GROUP BY customer;

2.1 COUNT — ba phiên bản, ba semantics khác nhau

Đây là chủ đề bị nhầm nhiều nhất. Cho bảng customers(id, country) với 5 dòng:

2.2 SUM, AVG, MIN, MAX — và NULL

Tất cả aggregator (trừ COUNT(*)) bỏ qua NULL trước khi tính:

AVG(COALESCE(salary, 0))   -- coi NULL như 0
SUM(salary) / COUNT(*)     -- chia cho tổng số dòng

2.3 Numeric edge cases — integer division

SQL kế thừa quy tắc số học từ kiểu dữ liệu — và kiểu INT chia INT có thể không như bạn mong đợi.

-- 3 đơn paid trên 7 đơn tổng
SELECT
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) / COUNT(*) AS rate
FROM orders;
-- MySQL/PostgreSQL: 0.4286 (decimal — OK)
-- SQL Server: 0 (integer division!)

Cách an toàn

-- Cast một vế sang DECIMAL/FLOAT
SUM(CASE WHEN status = 'paid' THEN 1.0 ELSE 0 END) / COUNT(*)

-- Hoặc nhân với 1.0
1.0 * paid_count / total_count

-- AVG bỏ trắng việc đếm — tự nó đã là float trong nhiều dialect
AVG(CASE WHEN status = 'paid' THEN 1.0 ELSE 0 END)

3.1 Cú pháp cơ bản và ràng buộc cột

SELECT customer_id, COUNT(*) AS n_orders, SUM(total) AS revenue
FROM orders
GROUP BY customer_id;

-- SAI (chuẩn ANSI và PostgreSQL/SQL Server reject)
SELECT customer_id, name, COUNT(*)
FROM orders GROUP BY customer_id;
-- name không trong GROUP BY và không aggregate → ambiguous

3.2 GROUP BY nhiều cột

Khi GROUP BY nhiều cột, nhóm được định nghĩa bởi tổ hợp các giá trị.

-- Đếm số đơn theo cặp (customer, status)
SELECT customer_id, status, COUNT(*) AS n
FROM orders
GROUP BY customer_id, status;

Một customer có thể xuất hiện nhiều dòng nếu có đơn ở nhiều status khác nhau. Ví dụ:

-- Số đơn và doanh thu theo ngày + theo status
SELECT order_date, status, COUNT(*) AS n_orders, SUM(total) AS revenue
FROM orders
GROUP BY order_date, status
ORDER BY order_date, status;

3.3 GROUP BY và NULL

Khác với hầu hết hành vi của NULL trong SQL, GROUP BY coi tất cả NULL là cùng một nhóm.

SELECT country, COUNT(*) FROM customers GROUP BY country;

3.4 Quy tắc sạch khi viết GROUP BY

1. Nhất quán column list. Cột không-aggregate trong SELECT phải xuất hiện đầy đủ trong GROUP BY. Đừng dùng MySQL old-mode để "ăn gian".
2. Đặt tên alias rõ. COUNT(*) nên là n_orders, không phải cnt.
3. Group theo cột định danh, không theo expression nếu có thể. GROUP BY customer_id tốt hơn GROUP BY UPPER(customer_email) nếu hai cột tương đương về semantic.
4. Chỉ aggregate cột cần thiết. Đừng SELECT customer_id, SUM(total), MAX(total), MIN(total), AVG(total) ... nếu chỉ cần SUM. Performance.

Có thể GROUP BY theo alias không?

-- PostgreSQL/MySQL cho phép
SELECT YEAR(order_date) AS y, COUNT(*) FROM orders GROUP BY y;

-- Standard ANSI yêu cầu lặp lại expression — chạy mọi nơi
SELECT YEAR(order_date) AS y, COUNT(*) FROM orders GROUP BY YEAR(order_date);

Trong portable code, lặp expression. Trong code dialect-specific, dùng alias cho ngắn.

4.1 Thứ tự thực thi logic — neo cho cả tuần

Đây là sơ đồ phải thuộc. Cú pháp viết theo thứ tự nào không quan trọng — engine luôn thực thi theo logic này:

4.2 WHERE vs HAVING — luật phân chia

-- 1. "Khách hàng có hơn 5 đơn paid" — KHÔNG phải hơn 5 đơn tổng
SELECT customer_id, COUNT(*) AS n_paid
FROM orders
WHERE  status = 'paid'      -- lọc dòng trước khi đếm
GROUP BY customer_id
HAVING COUNT(*) > 5;        -- lọc nhóm theo số đếm

4.3 Pattern: chọn WHERE hay HAVING?

Quy tắc đơn giản: nếu predicate dùng aggregator, đặt vào HAVING. Ngược lại, đặt vào WHERE.

-- Cách 1: filter dòng trước
SELECT customer_id FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.country = 'Vietnam'
GROUP BY o.customer_id
HAVING COUNT(*) > 3;

-- Cách 2: HAVING làm tất (không khuyên — chậm hơn)
SELECT o.customer_id FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY o.customer_id, c.country
HAVING c.country = 'Vietnam' AND COUNT(*) > 3;

5.1 SUM(CASE WHEN ...) — vũ khí mạnh nhất ở level này

Đây là pattern giải được khoảng 30% bài LeetCode SQL Medium. Ý tưởng: aggregate có điều kiện bằng cách bọc CASE WHEN bên trong aggregator.

SELECT
    COUNT(*)                                              AS total_orders,
    SUM(CASE WHEN status = 'paid'      THEN 1 ELSE 0 END) AS n_paid,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS n_cancelled,
    SUM(CASE WHEN total > 1000         THEN 1 ELSE 0 END) AS n_big
FROM orders;

5.2 COUNT(CASE WHEN) — viết ngắn hơn

Trick: COUNT bỏ NULL. Nếu CASE không có ELSE thì giá trị mặc định là NULL — ta tận dụng để viết ngắn:

-- Hai biểu thức tương đương:
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)
COUNT(CASE WHEN status = 'paid' THEN 1 END)
-- (Không có ELSE → giá trị NULL → COUNT bỏ qua)

-- Trước
SELECT
    SUM(CASE WHEN status='paid'      THEN 1 ELSE 0 END) AS n_paid,
    SUM(CASE WHEN status='cancelled' THEN 1 ELSE 0 END) AS n_cancelled
FROM orders;

-- Sau (gọn hơn)
SELECT
    COUNT(CASE WHEN status='paid'      THEN 1 END) AS n_paid,
    COUNT(CASE WHEN status='cancelled' THEN 1 END) AS n_cancelled
FROM orders;

Cả hai đều đúng, độ rõ ràng tương đương — chọn pattern bạn dùng nhất quán. Trong code review, tránh trộn cả hai trong cùng một query.

5.3 Pivot pattern — biến dòng thành cột

Đây là một trong những use case đẹp nhất của conditional aggregation: chuyển dữ liệu "long format" thành "wide format".

SELECT
    customer_id,
    SUM(CASE WHEN status = 'paid'      THEN amount ELSE 0 END) AS paid,
    SUM(CASE WHEN status = 'pending'   THEN amount ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled
FROM sales
GROUP BY customer_id;

5.4 Ratio và percentage queries

Pattern phổ biến: tính tỷ lệ dòng thỏa điều kiện X trên tổng số dòng (hoặc tổng có điều kiện Y).

SELECT
    customer_id,
    COUNT(*)                                              AS n_total,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)      AS n_paid,
    -- 1.0 phòng integer division
    SUM(CASE WHEN status = 'paid' THEN 1.0 ELSE 0 END) / COUNT(*) AS paid_rate
FROM orders
GROUP BY customer_id;

SELECT
    SUM(CASE WHEN status='paid' AND total > 1000 THEN 1.0 ELSE 0 END)
    / NULLIF(SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END), 0) AS big_paid_rate
FROM orders;

5.5 FILTER clause — chuẩn ANSI gọn hơn

Một số phương ngữ hỗ trợ FILTER (WHERE ...) — cú pháp ANSI cho conditional aggregation, đọc tự nhiên hơn CASE WHEN.

-- PostgreSQL, SQLite 3.30+
SELECT
    customer_id,
    COUNT(*)                              AS n_total,
    COUNT(*) FILTER (WHERE status='paid') AS n_paid,
    SUM(total) FILTER (WHERE status='paid') AS revenue_paid
FROM orders
GROUP BY customer_id;

6.1 LeetCode 596 — Classes With at Least 5 Students

Schema

Yêu cầu

Trả về tên các lớp có ít nhất 5 học sinh.

Phân rã

1. Phân hoạch theo lớp → GROUP BY class
2. Đếm học sinh mỗi lớp → COUNT(*) hoặc COUNT(DISTINCT student)
3. Lọc nhóm có count ≥ 5 → HAVING

Lời giải

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

6.2 LeetCode 1741 — Find Total Time Spent

Schema

Yêu cầu

Với mỗi (emp_id, event_day), tính tổng số phút làm việc. Một nhân viên có thể vào/ra nhiều lần trong ngày.

Phân rã

1. Mỗi dòng = một session, thời gian = (out_time − in_time)
2. Phân hoạch theo (emp_id, event_day) → GROUP BY 2 cột
3. Tổng thời gian mỗi nhóm → SUM

Lời giải

SELECT
    event_day AS day,
    emp_id,
    SUM(out_time - in_time) AS total_time
FROM Employees
GROUP BY event_day, emp_id;

6.3 LeetCode 1393 — Capital Gain/Loss (Medium)

Schema

Yêu cầu

Với mỗi stock, tính tổng lãi/lỗ = (tổng tiền Sell) − (tổng tiền Buy).

Phân rã

Với mỗi stock: cần một số dương từ Sell và một số âm từ Buy, sau đó tổng. Dùng conditional aggregation:

Lời giải

-- Cách 1: SUM(CASE WHEN)
SELECT
    stock_name,
    SUM(CASE WHEN operation = 'Sell' THEN  price
             WHEN operation = 'Buy'  THEN -price
        END) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name;

-- Cách 2: tách hai SUM rồi trừ
SELECT
    stock_name,
    SUM(CASE WHEN operation = 'Sell' THEN price ELSE 0 END)
  - SUM(CASE WHEN operation = 'Buy'  THEN price ELSE 0 END) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name;

Knowledge Check — Tuần 3

8.1 Bài tập LeetCode (5 problem)

1. 596. Classes With at Least 5 Students (Easy) — đã giải tại lớp.
2. 1741. Find Total Time Spent by Each Employee (Easy) — đã giải tại lớp.
3. 1393. Capital Gain/Loss (Medium) — conditional aggregation, đã giải tại lớp.
4. 1729. Find Followers Count (Easy) — GROUP BY + COUNT, sắp xếp theo user_id.
5. 1907. Count Salary Categories (Medium) — bài này có một bẫy: category không có nhân viên nào vẫn phải xuất hiện trong kết quả với count = 0. Gợi ý: GROUP BY không giúp được; nghĩ đến UNION 3 query, mỗi query một category.

Bonus — phân tích bug

Cho query sau, dự đoán kết quả trên dữ liệu có NULL và giải thích:

SELECT customer_id,
       COUNT(*)        AS a,
       COUNT(country)  AS b,
       COUNT(DISTINCT country) AS c,
       SUM(CASE WHEN country IS NULL THEN 1 ELSE 0 END) AS d
FROM customers
GROUP BY customer_id;

Quan hệ giữa a, b, c, d? Cái nào có thể bằng 0? Cái nào có thể NULL?

8.2 Đọc thêm và tự kiểm tra

Đọc

• SQL Cookbook (Anthony Molinaro) — Chương 5 (Metadata Queries) và Chương 7 (Working with Numbers).
• SQL Performance Explained (Markus Winand) — section "Indexing for GROUP BY".
• PostgreSQL docs: 9.20 Aggregate Functions và 9.21 Window Functions (preview tuần 6).

Tự kiểm tra (không nhìn slide)

• Bạn nói được khác biệt 4 phiên bản của COUNT trong 30 giây?
• Bạn vẽ được sơ đồ thứ tự thực thi FROM → WHERE → GROUP → HAVING → SELECT → ORDER → LIMIT từ trí nhớ?
• Bạn viết được conditional aggregation tính ratio (paid/total) phòng integer division và phòng chia 0?
• Bạn liệt kê được 3 tình huống dùng pivot pattern (SUM(CASE WHEN ...))?