GROUP BY và HAVING trong SQLite: lọc kết quả sau khi gộp nhóm

GROUP BY gộp các hàng thành từng nhóm

Các hàm tổng hợp như COUNT, SUM và AVG thu gọn nhiều hàng thành một con số. GROUP BY cho phép bạn làm điều đó theo từng loại: một con số cho mỗi khách hàng, mỗi tháng, mỗi trạng thái. Mỗi giá trị riêng biệt (hoặc tổ hợp giá trị) trở thành một hàng trong kết quả.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer;

Ba khách hàng, ba hàng kết quả. Sáu hàng ban đầu đã biến mất: chúng được gộp thành các nhóm theo khách hàng, với COUNT(*) và SUM(amount) được tính trong từng nhóm.

Cách hình dung: GROUP BY customer nghĩa là "coi mọi hàng có cùng khách hàng là một nhóm". Sau đó các hàm tổng hợp chạy riêng trên từng nhóm.

Được đặt gì trong danh sách SELECT

Đây là chỗ nhiều người vấp. Khi dùng GROUP BY, mọi cột trong danh sách SELECT phải nằm trong mệnh đề GROUP BY hoặc nằm bên trong một hàm tổng hợp. Nếu không, giá trị sẽ mơ hồ: nó nên lấy từ hàng nào trong nhóm?

CREATE TABLE sales (
    region TEXT,
    rep TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Ada', 100),
    ('North', 'Boris', 200),
    ('South', 'Cleo', 150);

-- This works: region is grouped, amount is aggregated.
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

Nếu bạn viết SELECT region, rep, SUM(amount) với GROUP BY region, SQLite vẫn vui vẻ chạy (nó dễ dãi ở chỗ các cơ sở dữ liệu khác sẽ từ chối), nhưng rep sẽ được chọn tùy ý từ nhóm. Bạn nhận được một tên nhân viên cho mỗi khu vực mà không biết chắc là ai. Đừng dựa vào điều đó: hãy gộp nhóm theo mọi cột không tổng hợp mà bạn hiển thị.

HAVING lọc các nhóm sau khi tổng hợp

WHERE lọc các hàng trước khi gộp nhóm. HAVING lọc các nhóm sau khi gộp nhóm. Đó là toàn bộ sự khác biệt, và cũng là lý do bạn không thể đặt COUNT(*) > 1 trong mệnh đề WHERE: lúc WHERE chạy, số đếm đó chưa tồn tại.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada', 50), ('Ada', 30),
    ('Boris', 80), ('Boris', 20), ('Boris', 15),
    ('Cleo', 200);

SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;

Cleo chỉ đặt một đơn hàng, nên nhóm của cô bị loại. Ada và Boris được giữ lại. Điều kiện được áp dụng trên giá trị tổng hợp của từng nhóm, không phải trên từng hàng.

Bạn có thể dùng trực tiếp bí danh cột từ danh sách SELECT trong HAVING, SQLite cho phép điều đó:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING total >= 100;

Cách này thường dễ đọc hơn việc lặp lại SUM(amount) trong mệnh đề HAVING.

WHERE và HAVING: dùng cả hai cùng lúc

Hai mệnh đề này không loại trừ nhau. WHERE thu hẹp những hàng tham gia gộp nhóm; HAVING thu hẹp những nhóm xuất hiện trong kết quả. Hầu hết truy vấn thực tế dùng cả hai.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada', 50, 'paid'),
    ('Ada', 30, 'refunded'),
    ('Boris', 80, 'paid'),
    ('Boris', 20, 'paid'),
    ('Cleo', 200, 'paid'),
    ('Cleo', 50, 'refunded');

SELECT customer, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 75;

Đọc từ trên xuống theo thứ tự thực thi:

1. WHERE status = 'paid': loại bỏ hoàn toàn các hàng đã hoàn tiền.
2. GROUP BY customer: gộp phần còn lại theo khách hàng.
3. SUM(amount) chạy trên từng nhóm.
4. HAVING SUM(amount) > 75: chỉ giữ các nhóm thỏa điều kiện.

Boris (80 + 20 = 100) và Cleo (200) được giữ lại. Đơn hàng đã thanh toán duy nhất của Ada là 50, không đạt ngưỡng.

Nhiều điều kiện và nhiều cột gộp nhóm

HAVING nhận các toán tử logic giống WHERE (AND, OR, NOT), và bạn có thể gộp nhóm theo nhiều cột để có các nhóm con:

CREATE TABLE sales (
    region TEXT,
    quarter TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Q1', 100), ('North', 'Q1', 50),
    ('North', 'Q2', 300),
    ('South', 'Q1', 80),
    ('South', 'Q2', 120), ('South', 'Q2', 60);

SELECT region, quarter, SUM(amount) AS total, COUNT(*) AS deals
FROM sales
GROUP BY region, quarter
HAVING SUM(amount) > 100 AND COUNT(*) >= 2;

Mỗi cặp (region, quarter) là một nhóm riêng. Mệnh đề HAVING yêu cầu tổng lớn hơn 100 và có ít nhất hai giao dịch. Chỉ ('North', 'Q1') và ('South', 'Q2') đạt yêu cầu.

Một mẫu thực tế: tìm giá trị trùng lặp

Truy vấn GROUP BY ... HAVING COUNT(*) > 1 là cách chuẩn để tìm các giá trị trùng lặp trong một cột:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO users (email) VALUES
    ('ada@example.com'),
    ('boris@example.com'),
    ('ada@example.com'),
    ('cleo@example.com'),
    ('boris@example.com');

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Hai giá trị trùng lặp hiện ra. Từ đây bạn thường sẽ quyết định gộp tài khoản, thêm ràng buộc UNIQUE hay dọn dẹp dữ liệu, nhưng truy vấn để phát hiện thì lần nào cũng có cùng dạng.

HAVING không có GROUP BY

Cách này hiếm gặp nhưng hợp lệ. Không có GROUP BY, toàn bộ tập kết quả được coi là một nhóm duy nhất, và HAVING lọc nó như một khối: bạn nhận được tất cả giá trị tổng hợp hoặc không nhận được gì:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (50), (30), (80);

SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue
FROM orders
HAVING SUM(amount) > 100;

Hàng kết quả duy nhất xuất hiện vì tổng là 160. Đổi ngưỡng thành > 200 và truy vấn sẽ không trả về hàng nào. Trên thực tế, bạn gần như luôn dùng HAVING cùng GROUP BY, nhưng nên biết rằng ngôn ngữ không bắt buộc điều đó.

Tóm tắt nhanh

• GROUP BY gộp các hàng thành từng nhóm theo khóa; các hàm tổng hợp chạy bên trong mỗi nhóm.
• Mọi cột không tổng hợp trong SELECT nên xuất hiện trong GROUP BY.
• WHERE lọc hàng trước khi gộp nhóm; HAVING lọc nhóm sau đó.
• Các hàm tổng hợp như COUNT(*) và SUM(...) thuộc về HAVING, không bao giờ thuộc về WHERE.
• HAVING nhận điều kiện kết hợp và có thể dùng bí danh từ SELECT.

Tiếp theo: khóa ngoại

Tổng hợp trên một bảng đã hữu ích, nhưng hầu hết cấu trúc dữ liệu thực tế trải dữ liệu ra nhiều bảng: đơn hàng ở đây, khách hàng ở kia, sản phẩm ở một nơi khác. Khóa ngoại là cách bạn nối các bảng đó với nhau để các mối quan hệ luôn nhất quán. Đó là nội dung của chương tiếp theo.