SQL 入门 7：SQL 聚合与分组：函数、GROUP BY 与 ROLLUP

以下为 SQL 中聚合函数、分组查询及汇总的用法解析。需要相关数据可私信。

学习内容

1. 聚合函数
• COUNT()：统计行数，忽略空值；COUNT(*) 计数所有行。
• SUM()：计算总和。
• AVG()：求平均值。
• MAX()：返回最大值。
• MIN()：返回最小值。
• GROUP_CONCAT()：合并多行为字符串。
• 处理列数据，返回单一结果。
• 常见函数：
• 支持表达式（如 SUM(invoice_total * 1.4)），可用于日期。
• DISTINCT 去除重复值。
2. GROUP BY 子句
• 按指定列分组，结合聚合函数计算每组结果。
• 顺序：SELECT → FROM → WHERE → GROUP BY → ORDER BY。
• 多列分组用逗号分隔，无需括号。
3. HAVING 子句
• 过滤分组后的结果，解决 WHERE 无法处理聚合值的问题。
• 置于 GROUP BY 后，支持多条件。
4. WITH ROLLUP
• 在 GROUP BY 后添加，生成分组汇总行。
• 多列分组时，逐级汇总至总计。

---

示例代码与讲解

1. 聚合函数

SELECT MAX(invoice_total)
FROM invoices;

• 返回 invoice_total 最大值。

SELECT
    MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS AVG,
    SUM(invoice_total * 1.4) AS SUM,
    COUNT(invoice_total) AS COUNT
FROM invoices;

• 多聚合查询，含表达式，重命名结果为五列。

SELECT
    MAX(payment_date) AS highest,
    MIN(payment_date) AS lowest,
    COUNT(payment_date) AS COUNT
FROM invoices;

• 应用于日期，COUNT 不计空值。

SELECT COUNT(*) AS number_invoices
FROM invoices;

• 统计所有行，含空值。

SELECT
    MAX(payment_date) AS highest,
    MIN(payment_date) AS lowest,
    COUNT(payment_date) AS COUNT
FROM invoices
WHERE payment_date >= '2018-01-01';

• 筛选后聚合。

SELECT COUNT(DISTINCT client_id) AS number_client
FROM invoices;

• 统计唯一 client_id，避免重复计数。

2. GROUP BY

SELECT client_id, SUM(invoice_total) AS Total_sales
FROM invoices
GROUP BY client_id;

• 按 client_id 分组，计算每组销售总额。

SELECT client_id, SUM(invoice_total) AS Total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY Total_sales DESC;

• 筛选后分组，按总额降序。

SELECT c.city, c.state, SUM(i.invoice_total) AS Total_sales
FROM invoices i
JOIN clients c USING (client_id)
GROUP BY c.state, c.city;

• 连接表后，按州和城市分组，逗号分隔条件。

3. HAVING

SELECT client_id, SUM(invoice_total) AS Total_sales
FROM invoices
GROUP BY client_id
HAVING Total_sales >= 800;

• 筛选销售总额 ≥ 800 的组。

SELECT client_id, SUM(invoice_total) AS Total_sales, COUNT(*) AS number_invoices
FROM invoices
GROUP BY client_id
HAVING Total_sales >= 800 AND number_invoices > 5;

• 多条件筛选：总额 ≥ 800 且发票数 > 5。

4. WITH ROLLUP

SELECT client_id, SUM(invoice_total) AS Total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP;

• 添加汇总行，显示所有 client_id 的总计。

SELECT state, city, SUM(invoice_total) AS Total_sales
FROM invoices
JOIN clients c USING (client_id)
GROUP BY state, city WITH ROLLUP;

• 按州和城市分组，逐级汇总至总计。

---

作业

1. 聚合与 UNION

SELECT
    'First half of 2019' AS date_range,
    SUM(invoice_total) AS Total_sales,
    SUM(payment_total) AS Total_payments,
    SUM(invoice_total) - SUM(payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT
    'Second half of 2019' AS date_range,
    SUM(invoice_total) AS Total_sales,
    SUM(payment_total) AS Total_payments,
    SUM(invoice_total) - SUM(payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT
    'Total' AS date_range,
    SUM(invoice_total) AS Total_sales,
    SUM(payment_total) AS Total_payments,
    SUM(invoice_total) - SUM(payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31';

• 计算 2019 年上下半年及全年的销售、付款和预期差额。

2. 分组查询

SELECT date, pm.name AS payment_method, SUM(amount) AS Total_payments
FROM payments p
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id
GROUP BY date, pm.name
ORDER BY date;

• 按日期和支付方式分组，计算付款总额。

3. HAVING 筛选

USE sql_store;
SELECT c.customer_id, SUM(oi.quantity * oi.unit_price) AS total_buy
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE c.state = 'VA'
GROUP BY c.customer_id
HAVING total_buy >= 100;

• 查询 VA 州客户总购买额，筛选 ≥ 100 的结果。

4. ROLLUP 汇总

USE sql_invoicing;
SELECT pm.name, SUM(p.amount) AS SUM
FROM payments p
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP;

• 按支付方式分组，含总计。

---

总结

本次解析了聚合函数（COUNT、SUM 等）、GROUP BY 分组、HAVING 过滤及 ROLLUP 汇总。基于 sql_invoicing 和 sql_store 数据库。