Nhóm dữ liệu: GROUP BY, HAVING, hàm tổng hợp

Nhóm dữ liệu

← Kết hợp nhiều bảng - JOIN | → Phép toán tập hợp - UNION, INTERSECT, EXCEPT

Bạn sẽ học được

Gom dòng thành nhóm bằng GROUP BY, tính toán trên từng nhóm bằng hàm tổng hợp, và lọc nhóm bằng HAVING.

---

1. Hàm tổng hợp (aggregate functions)

Hàm tổng hợp nhận nhiều dòng và trả về một giá trị.

Hàm	Công dụng	Bỏ qua NULL?
count(*)	Đếm số dòng	Không (đếm cả dòng toàn NULL)
count(cot)	Đếm giá trị không NULL	✅ Có
count(DISTINCT cot)	Đếm giá trị khác nhau	✅ Có
sum(cot)	Tổng	✅ Có
avg(cot)	Trung bình	✅ Có
min(cot) / max(cot)	Nhỏ nhất / lớn nhất	✅ Có
string_agg(cot, ',')	Nối chuỗi	✅ Có
array_agg(cot)	Gom thành mảng	Không
bool_and() / bool_or()	Và / hoặc logic	✅ Có

1.1. Không có GROUP BY = toàn bộ bảng là một nhóm

SELECT count(*)      AS so_phim,
       avg(length)   AS thoi_luong_tb,
       min(length)   AS ngan_nhat,
       max(length)   AS dai_nhat,
       sum(length)   AS tong_phut
FROM film;

 so_phim |    thoi_luong_tb     | ngan_nhat | dai_nhat | tong_phut
---------+----------------------+-----------+----------+-----------
    1000 | 115.2720000000000000 |        46 |      185 |    115272

1.2. Phân biệt count(*) với count(cột)

Đây là điểm gây nhầm lẫn thường xuyên nhất:

SELECT
   count(*)           AS tong_luot_thue,   -- 16044
   count(return_date) AS da_tra,           -- 15861 (bỏ qua 183 dòng NULL)
   count(*) - count(return_date) AS chua_tra
FROM rental;

count(*) đếm dòng; count(cot) đếm giá trị không NULL của cột đó.

Hệ quả với LEFT JOIN

-- ❌ SAI: khách chưa thuê lần nào bị đếm thành 1
SELECT c.customer_id, count(*) AS so_luot
FROM customer c
LEFT JOIN rental r ON r.customer_id = c.customer_id
GROUP BY c.customer_id;
 
-- ✅ ĐÚNG: đếm cột của bảng bên phải
SELECT c.customer_id, count(r.rental_id) AS so_luot
FROM customer c
LEFT JOIN rental r ON r.customer_id = c.customer_id
GROUP BY c.customer_id;

Với LEFT JOIN, khách không khớp vẫn sinh ra 1 dòng (với các cột phải là NULL), nên count(*) trả về 1 thay vì 0.

1.3. avg và phép chia số nguyên

SELECT avg(length) FROM film;
-- 115.2720000000000000   ← đúng, avg tự trả về numeric
 
SELECT sum(length) / count(*) FROM film;
-- 115                    ← ⚠️ SAI! chia hai số nguyên → cắt cụt phần thập phân
 
SELECT sum(length)::numeric / count(*) FROM film;
-- 115.272...             ← đúng

PostgreSQL chia số nguyên cho số nguyên ra số nguyên. Luôn ép kiểu khi cần thập phân. → Biểu thức điều kiện - CASE, COALESCE, NULLIF, CAST

1.4. Lọc bên trong hàm tổng hợp: FILTER

PostgreSQL cho phép áp điều kiện riêng cho từng hàm tổng hợp:

SELECT
   count(*)                                  AS tong,
   count(*) FILTER (WHERE rating = 'R')      AS phim_R,
   count(*) FILTER (WHERE rating = 'PG')     AS phim_PG,
   avg(length) FILTER (WHERE length > 100)   AS tb_phim_dai
FROM film;

 tong | phim_R | phim_PG |     tb_phim_dai
------+--------+---------+---------------------
 1000 |    195 |     194 | 138.6...

Cách viết cũ (vẫn dùng được, dài hơn):

count(CASE WHEN rating = 'R' THEN 1 END) AS phim_R

FILTER rõ ràng hơn và là cú pháp chuẩn SQL:2003. → Biểu thức điều kiện - CASE, COALESCE, NULLIF, CAST

1.5. string_agg và array_agg

SELECT f.title,
       string_agg(a.first_name || ' ' || a.last_name, ', '
                  ORDER BY a.last_name) AS dan_dien_vien
FROM film f
JOIN film_actor fa ON fa.film_id = f.film_id
JOIN actor a       ON a.actor_id = fa.actor_id
WHERE f.film_id = 1
GROUP BY f.film_id, f.title;

      title       |                 dan_dien_vien
------------------+-------------------------------------------------
 Academy Dinosaur | Rock Dukakis, Mary Keitel, Penelope Guiness, ...

ORDER BY bên trong hàm tổng hợp quyết định thứ tự nối chuỗi — rất hữu ích.

---

2. GROUP BY

GROUP BY chia các dòng thành nhóm theo giá trị của một hoặc nhiều cột.

SELECT cot_nhom, ham_tong_hop(cot_khac)
FROM ten_bang
GROUP BY cot_nhom;

2.1. Nhóm theo một cột

SELECT rating, count(*) AS so_phim
FROM film
GROUP BY rating
ORDER BY so_phim DESC;

 rating | so_phim
--------+---------
 PG-13  |     223
 NC-17  |     210
 R      |     195
 PG     |     194
 G      |     178
(5 rows)

2.2. Nhóm theo nhiều cột

SELECT rating,
       CASE WHEN length > 120 THEN 'Dài' ELSE 'Ngắn' END AS do_dai,
       count(*) AS so_phim,
       round(avg(rental_rate), 2) AS gia_tb
FROM film
GROUP BY rating, do_dai
ORDER BY rating, do_dai;

Mỗi tổ hợp giá trị tạo thành một nhóm.

2.3. GROUP BY với JOIN

SELECT c.name AS the_loai,
       count(*) AS so_phim,
       round(avg(f.length), 1) AS thoi_luong_tb
FROM category c
JOIN film_category fc ON fc.category_id = c.category_id
JOIN film f           ON f.film_id = fc.film_id
GROUP BY c.category_id, c.name
ORDER BY so_phim DESC;

  the_loai   | so_phim | thoi_luong_tb
-------------+---------+---------------
 Sports      |      74 |         128.2
 Foreign     |      73 |         121.7
 Family      |      69 |         114.8
 Documentary |      68 |         108.8
 Animation   |      66 |         111.0
...
(16 rows)

Vì sao GROUP BY c.category_id, c.name mà không chỉ c.name?

Nhóm theo khóa chính rồi thêm các cột phụ thuộc là thói quen tốt: nếu hai thể loại trùng tên, nhóm theo name sẽ gộp nhầm chúng lại.

Thực ra PostgreSQL đủ thông minh để cho phép GROUP BY c.category_id rồi SELECT c.name (vì category_id là khóa chính nên name được xác định duy nhất). Tính năng này gọi là functional dependency và chỉ PostgreSQL 9.1+ mới có.

2.4. Quy tắc bắt buộc

Mọi cột trong SELECT phải hoặc:

1. Xuất hiện trong GROUP BY, hoặc
2. Nằm trong một hàm tổng hợp.

-- ❌ LỖI
SELECT rating, title, count(*)
FROM film
GROUP BY rating;
-- ERROR: column "film.title" must appear in the GROUP BY clause
--        or be used in an aggregate function

Lý do hiển nhiên khi nghĩ kỹ: nhóm rating = 'R' có 195 phim với 195 tựa đề khác nhau — Postgres biết chọn tựa nào?

Cách sửa tùy ý định:

-- Muốn một tựa bất kỳ đại diện
SELECT rating, min(title), count(*) FROM film GROUP BY rating;
 
-- Muốn tất cả tựa
SELECT rating, string_agg(title, ', '), count(*) FROM film GROUP BY rating;
 
-- Muốn tựa của phim dài nhất mỗi nhóm → dùng DISTINCT ON
SELECT DISTINCT ON (rating) rating, title, length
FROM film ORDER BY rating, length DESC, film_id;

2.5. GROUP BY không cần hàm tổng hợp

SELECT customer_id FROM payment GROUP BY customer_id;
-- tương đương
SELECT DISTINCT customer_id FROM payment;

→ Truy vấn dữ liệu - SELECT, alias, ORDER BY, DISTINCT

2.6. GROUP BY gom mọi NULL vào một nhóm

Khác với toán tử =, GROUP BY coi tất cả NULL là bằng nhau và tạo một nhóm NULL duy nhất:

SELECT return_date IS NULL AS chua_tra, count(*)
FROM rental
GROUP BY return_date IS NULL;

 chua_tra | count
----------+-------
 f        | 15861
 t        |   183

---

3. HAVING

HAVING lọc nhóm sau khi GROUP BY đã chạy.

SELECT cot_nhom, ham_tong_hop(x)
FROM ten_bang
GROUP BY cot_nhom
HAVING dieu_kien_tren_nhom;

3.1. WHERE vs HAVING

	WHERE	HAVING
Lọc cái gì	Từng dòng	Từng nhóm
Chạy khi nào	Trước GROUP BY	Sau GROUP BY
Dùng hàm tổng hợp	❌ Không	✅ Có
Dùng bí danh cột	❌ Không	✅ Có (đặc cách PostgreSQL)

-- Tìm khách hàng chi tiêu trên $200
SELECT customer_id, sum(amount) AS tong_chi
FROM payment
GROUP BY customer_id
HAVING sum(amount) > 200
ORDER BY tong_chi DESC;

 customer_id | tong_chi
-------------+----------
         148 |   211.55
         526 |   208.58
         178 |   194.61   ← không, dòng này bị loại

Không thể viết WHERE sum(amount) > 200 — tại thời điểm WHERE chạy, các nhóm chưa tồn tại.

3.2. Dùng cả hai

-- Lọc dòng bằng WHERE (trước), lọc nhóm bằng HAVING (sau)
SELECT customer_id,
       count(*)    AS so_lan_tt,
       sum(amount) AS tong_chi
FROM payment
WHERE payment_date >= '2007-01-01'    -- ① lọc dòng
GROUP BY customer_id                  -- ② gom nhóm
HAVING count(*) > 30                  -- ③ lọc nhóm
ORDER BY tong_chi DESC;               -- ④ sắp xếp

Ưu tiên WHERE hơn HAVING khi có thể

-- ❌ Chậm: gom nhóm tất cả rồi mới bỏ đi
GROUP BY rating HAVING rating <> 'NC-17'
 
-- ✅ Nhanh: loại bỏ dòng trước khi gom nhóm
WHERE rating <> 'NC-17' GROUP BY rating

WHERE giảm số dòng phải xử lý; HAVING chỉ vứt bỏ kết quả đã tính xong. Với bảng lớn, chênh lệch rất đáng kể.

3.3. HAVING không cần GROUP BY

Khi không có GROUP BY, toàn bảng là một nhóm:

SELECT count(*) FROM film HAVING count(*) > 500;
-- trả về 1000
 
SELECT count(*) FROM film HAVING count(*) > 5000;
-- trả về 0 dòng (không phải giá trị 0!)

Ít dùng nhưng đôi khi tiện để kiểm tra điều kiện tồn tại.

3.4. Ví dụ tổng hợp thực tế

Tìm các thể loại có trên 60 phim và giá thuê trung bình trên $2.90:

SELECT c.name AS the_loai,
       count(*) AS so_phim,
       round(avg(f.rental_rate), 2) AS gia_tb,
       round(avg(f.length), 1) AS thoi_luong_tb
FROM category c
JOIN film_category fc ON fc.category_id = c.category_id
JOIN film f           ON f.film_id = fc.film_id
WHERE f.rating <> 'NC-17'
GROUP BY c.category_id, c.name
HAVING count(*) > 40
   AND avg(f.rental_rate) > 2.90
ORDER BY gia_tb DESC;

---

4. Hàm cửa sổ — khi GROUP BY là không đủ

GROUP BY thu gọn nhiều dòng thành một. Nếu bạn muốn giữ nguyên số dòng nhưng vẫn có giá trị tổng hợp, dùng hàm cửa sổ (window function).

SELECT
   title,
   rating,
   length,
   avg(length) OVER (PARTITION BY rating)         AS tb_cung_rating,
   length - avg(length) OVER (PARTITION BY rating) AS chenh_lech,
   rank() OVER (PARTITION BY rating ORDER BY length DESC) AS thu_hang
FROM film
WHERE rating = 'G'
ORDER BY thu_hang
LIMIT 5;

So sánh trực quan:

	GROUP BY	OVER (PARTITION BY ...)
Số dòng kết quả	Bằng số nhóm	Bằng số dòng gốc
Giữ chi tiết từng dòng	❌ Không	✅ Có

Các hàm cửa sổ hay dùng:

row_number() OVER (ORDER BY length DESC)     -- 1, 2, 3, 4  (luôn khác nhau)
rank()       OVER (ORDER BY length DESC)     -- 1, 2, 2, 4  (nhảy số khi hòa)
dense_rank() OVER (ORDER BY length DESC)     -- 1, 2, 2, 3  (không nhảy số)
lag(length)  OVER (ORDER BY film_id)         -- giá trị dòng trước
lead(length) OVER (ORDER BY film_id)         -- giá trị dòng sau
sum(amount)  OVER (ORDER BY payment_date)    -- tổng lũy kế

Ví dụ — top 3 phim dài nhất mỗi thể loại:

WITH xep_hang AS (
   SELECT c.name AS the_loai, f.title, f.length,
          row_number() OVER (PARTITION BY c.category_id
                             ORDER BY f.length DESC, f.film_id) AS hang
   FROM film f
   JOIN film_category fc ON fc.film_id = f.film_id
   JOIN category c       ON c.category_id = fc.category_id
)
SELECT the_loai, title, length
FROM xep_hang
WHERE hang <= 3
ORDER BY the_loai, hang;

Không lọc hàm cửa sổ trong WHERE

Hàm cửa sổ được tính sau WHERE và GROUP BY, nên WHERE row_number() <= 3 sẽ báo lỗi. Phải bọc trong CTE hoặc subquery như ví dụ trên. → CTE và truy vấn đệ quy

---

5. Bài tập

1. Đếm số phim theo từng rating, chỉ hiện các rating có trên 200 phim.
2. Tính tổng doanh thu (sum(amount)) theo từng nhân viên (staff_id).
3. Tìm 5 khách hàng thuê nhiều phim nhất, hiển thị cả họ tên.
4. Với mỗi thể loại, đếm số phim thuộc từng rating — dùng FILTER.
5. Đếm số lượt thuê theo từng tháng của năm 2005.
6. Tìm các diễn viên đóng trên 35 phim.
7. Giải thích vì sao câu sau bị lỗi và đưa ra 2 cách sửa khác nhau:

   SELECT rating, title, count(*) FROM film GROUP BY rating;

8. Với mỗi khách hàng, tính tổng chi tiêu và tỷ lệ phần trăm so với tổng doanh thu toàn hệ thống (gợi ý: hàm cửa sổ).

Đáp án

1.

SELECT rating, count(*) AS so_phim
FROM film
GROUP BY rating
HAVING count(*) > 200
ORDER BY so_phim DESC;

Kết quả: PG-13 (223) và NC-17 (210).

2.

SELECT s.staff_id,
       s.first_name || ' ' || s.last_name AS nhan_vien,
       count(*)    AS so_giao_dich,
       sum(p.amount) AS doanh_thu
FROM staff s
JOIN payment p ON p.staff_id = s.staff_id
GROUP BY s.staff_id, s.first_name, s.last_name
ORDER BY doanh_thu DESC;

3.

SELECT c.customer_id,
       c.first_name || ' ' || c.last_name AS khach_hang,
       count(r.rental_id) AS so_luot_thue
FROM customer c
JOIN rental r ON r.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY so_luot_thue DESC, c.customer_id
LIMIT 5;

4.

SELECT c.name AS the_loai,
       count(*) AS tong,
       count(*) FILTER (WHERE f.rating = 'G')     AS g,
       count(*) FILTER (WHERE f.rating = 'PG')    AS pg,
       count(*) FILTER (WHERE f.rating = 'PG-13') AS pg13,
       count(*) FILTER (WHERE f.rating = 'R')     AS r,
       count(*) FILTER (WHERE f.rating = 'NC-17') AS nc17
FROM category c
JOIN film_category fc ON fc.category_id = c.category_id
JOIN film f           ON f.film_id = fc.film_id
GROUP BY c.category_id, c.name
ORDER BY c.name;

5.

SELECT date_trunc('month', rental_date)::date AS thang,
       count(*) AS so_luot
FROM rental
WHERE rental_date >= '2005-01-01'
  AND rental_date <  '2006-01-01'
GROUP BY thang
ORDER BY thang;

date_trunc cắt timestamp về đầu tháng — cách chuẩn để nhóm theo thời gian. → Kiểu dữ liệu chuyên sâu

6.

SELECT a.actor_id,
       a.first_name || ' ' || a.last_name AS dien_vien,
       count(*) AS so_phim
FROM actor a
JOIN film_actor fa ON fa.actor_id = a.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name
HAVING count(*) > 35
ORDER BY so_phim DESC;

7. Lỗi vì title không nằm trong GROUP BY cũng không nằm trong hàm tổng hợp — mỗi nhóm rating có hàng trăm tựa khác nhau.

Cách 1 — gom tựa thành chuỗi:

SELECT rating, string_agg(title, ', ' ORDER BY title), count(*)
FROM film GROUP BY rating;

Cách 2 — nhóm theo cả hai cột (mỗi nhóm chỉ 1 dòng, count luôn = 1, ít hữu ích nhưng hợp lệ):

SELECT rating, title, count(*) FROM film GROUP BY rating, title;

Cách 3 (thường là ý người viết muốn) — dùng hàm cửa sổ để giữ chi tiết:

SELECT rating, title, count(*) OVER (PARTITION BY rating) FROM film;

8.

SELECT customer_id,
       sum(amount) AS tong_chi,
       round(100.0 * sum(amount) / sum(sum(amount)) OVER (), 3) AS phan_tram
FROM payment
GROUP BY customer_id
ORDER BY tong_chi DESC
LIMIT 10;

sum(sum(amount)) OVER () trông lạ nhưng hợp lệ: hàm tổng hợp bên trong chạy trước (theo nhóm), hàm cửa sổ bên ngoài chạy sau (trên toàn bộ kết quả đã nhóm).

---

6. Tóm tắt

• Hàm tổng hợp bỏ qua NULL, trừ count(*).
• Với LEFT JOIN, dùng count(cot_bang_phai) chứ không phải count(*).
• FILTER (WHERE ...) cho phép mỗi hàm tổng hợp có điều kiện riêng.
• Mọi cột trong SELECT phải nằm trong GROUP BY hoặc trong hàm tổng hợp.
• WHERE lọc dòng (trước nhóm), HAVING lọc nhóm (sau nhóm). Ưu tiên WHERE để nhanh hơn.
• Chia số nguyên cho số nguyên bị cắt cụt — nhớ ép ::numeric.
• Cần giữ chi tiết từng dòng mà vẫn có giá trị tổng hợp → dùng hàm cửa sổ OVER (PARTITION BY ...).

---

← Kết hợp nhiều bảng - JOIN | → Phép toán tập hợp - UNION, INTERSECT, EXCEPT