【MySQL】聚合函数：汇总、分组数据

学习目标

• 掌握常用的聚合函数：COUNT, MAX, MIN, SUM, AVG
• 掌握GROUP BY和HAVING子句的用法
• 掌握Where和HAVING的区别
  • where用在group by之前，having用在group by之后
• 带GROUP BY的SQL怎么优化？
  • 未查询到，日后补充
• COUNT(1), COUNT(*), COUNT(字段)那种效率是最好的？
  • 结论：count(*) = count(1) > count(主键字段) > count(字段)

MAX()、MIN()、AVG()、SUM()、COUNT()

SELECT MAX(invoice_total) AS highest,MIN(invoice_total) AS lowest,AVG(invoice_total) AS average,SUM(invoice_total) AS total,COUNT(invoice_total) AS num
FROM invoices

COUNT(*) 得到所有记录条目

• 聚合函数只运行非空行，如果列中有空值，不会被算在函数内
• 如果想得到表格中的所有记录条目，使用COUNT(*)
• select count（name） from t_order，意思是统计t_order表中，name字段不为null的记录有多少个，如果某条记录的name字段为null，就不会被统计进去。
• select count(1) from t_order，意思是1这个表达式不为null的记录有多少个。1这个表达式就是单纯数字，它永远都不是null，所以这条语句，就是在统计t_order表中有多少个记录

-- 一个没有空行，一个有空行，结果不同
SELECT MAX(payment_date) AS latest,COUNT(invoice_total) AS num,COUNT(payment_date) AS count_of_paymentsCOUNT(*) AS total_records
FROM invoices

DISTINCT去重

SELECT MAX(invoice_total) AS highest,MIN(invoice_total) AS lowest,AVG(invoice_total) AS average,SUM(invoice_total * 1.1) AS total,-- client_id中有重复的，结果7COUNT(client_id) AS num-- 可用distinc去重，结果3COUNT(DISTINCT  client_id) AS num
FROM invoices
WHERE invoice_date > '2019-07-01';

练习1（使用UNION ， SUM， BETEEN AND）

• 练习：汇总2019上半年、下半年以及整年的数据。
• 使用UNION ， SUM， BETEEN AND

SELECT'First half of 2019' AS date_range,SUM(invoice_total) AS total_sales,SUM(payment_total) AS total_payments,SUM(invoice_total - 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 - 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 - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31'

运行结果：

GROUP BY子句

• 按列分组数据
• GROUP BY子句永远在from和where子句之后，在order by之前
• st的口诀：select， from， where， having, group by, order by

把sum按照client_id分组
还可以排序
还可以添加筛选条件
selectclient_id,sum(invoice_total) as total_sales
from invoices
where invoice_date >= '2019-07-01'
group by client_id
order by total_sales desc

• 多列分组数据

-- 多列分组
selectstate,city,sum(invoice_total) as total_sales
-- 连接两个表
from invoices i
JOIN clients using (client_id)
-- 每个state和city的组合
group by state, city

练习2（使用sum，group by， join on， join using）

-- 按支付日期、支付方式分组计算payment_total的总值
select p.date,pm.name,sum(payment_total) as 'total_payments'
from invoices i
join payments p using (invoice_id)
join payment_methods pm on p.payment_method = pm.payment_method_id
group by p.date, payment_method

运行结果

HAVING子句分组筛选

• 使用场景

-- 按clientid把totalsales分组后，想获得total大于500的客户。怎么办呢？
-- 此时不能在from后面用where totalsales> 500，因为此时total_sales的结果还没有
select client_id,sum(invoice_total) as total_sales
from invoices
group by client_id

• 用HAVING子句，在分组之后筛选数据

select client_id,sum(invoice_total) as total_sales
from invoices
group by client_id
-- 在group by后用having，此时把大于500的筛选出来了
having total_sales > 500

运行结果

• having子句的复合搜索，用and写一个复合搜索条件

select client_id,sum(invoice_total) as total_sales,count(*) as number_of_invoices
from invoices
group by client_id
-- 想筛选total_sales大于500且发票数量大于5的，用and连接
having total_sales > 500 and number_of_invoices > 5

运行结果

• having子句中筛选的列，一定是在select中出现的。而where则没有这样的限制。

• 练习
  找到位于VA的，消费总额大于100的顾客

use sql_store;
select customer_id,sum(unit_price * quantity) as total_price
from customers cjoin orders o using (customer_id)join order_items using (order_id)
where state = 'VA'
group by customer_id
having total_   price > 100

WITH ROLLUP运算符

• 对group by的结果再进行汇总

select client_id,sum(invoice_total) as total_sales
from invoices
group by client_id with rollup

运行结果

• 多列分组用rollup时，会得到每个组和整个结果集的汇总值

select state,city,sum(invoice_total) as total_sales
from invoices i
join clients c  using  (client_id)
group by state, city with rollup

运行结果

• 练习
  按照支付方式分组，获取每种支付方式支付的总额，并进行结果汇总。

use sql_invoicing;
select pm.name,sum(amount) as total
from payments pjoin payment_methods pm on payment_method_id = payment_method
group by name with rollup

查询结果