Mosh Mysql学习笔记4

4 汇总统计

4.1 聚合函数

小结

聚合函数：输入一系列值并聚合为一个结果的函数

实例

USE sql_invoicing;
​
SELECT
   MAX(invoice_date) AS latest_date,  
   — SELECT选择的不仅可以是列，也可以是数字、列间表达式、列的聚合函数
   MIN(invoice_total) lowest,
   — AS 可省略
   AVG(invoice_total) average,
   SUM(invoice_total * 1.1) total,
   COUNT(*) total_records,
   COUNT(invoice_total) number_of_invoices,
   — 和上一个相等
   COUNT(payment_date) number_of_payments,  
   — 【聚合函数会忽略空值】，得到的支付数少于发票数
   COUNT(DISTINCT client_id) number_of_distinct_clients
   — DISTINCT client_id 筛掉了该列的重复值，再COUNT计数，会得到不同顾客数
FROM invoices
WHERE invoice_date > '2019-07-01'  — 想只统计下半年的结果

练习

目标：

USE sql_invoicing;
​
   SELECT
       '1st_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
       '2st_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'

4.2 GROUP BY子句

小结

按一列或多列分组，注意语句的位置

案例1：按一个字段分组

在发票记录表中按不同顾客分组统计下半年总销售额并降序排列

USE sql_invoicing;
​
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子句里出现的非聚合字段，必须全部写到GROUP BY后面；只有聚合函数（SUM/COUNT/MAX/MIN/AVG）不用进 GROUP BY，即GROUP BY子句选择的字段是client_id是满足规则的。

— 错误！invoice_total没做聚合，又没放进GROUP BY
SELECT client_id, invoice_total, SUM(invoice_total)
FROM invoices
GROUP BY client_id

这里invoice_total既不是聚合，又不在 GROUP BY，直接报错（新版 MySQL 默认严格模式，不允许）。

另外注意ORDER BY后面不能用invoice_total排序，分组后每行是客户总销售额，没有单独的 invoice_total 明细。

案例2：按多个字段分组

算各州各城市的总销售额

USE sql_invoicing;
​
SELECT
  state,
  city,
   SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY state, city  
ORDER BY state

这里GROUP BY后按正常理解其实去掉state语义上是没问题，但由于MySQL 默认严格模式，还是把字段都加上比较好。

练习

在 payments 表中，按日期和支付方式分组统计总付款额

USE sql_invoicing;
​
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, payment_method
— 用的是 SELECT 里的列别名
ORDER BY date

4.3 HAVING子句

小结

HAVING 和 WHERE 都是是条件筛选语句，条件的写法相通，数学、比较（包括特殊比较）、逻辑运算都可以用（如 AND、REGEXP 等等）

两者本质区别:

• WHERE 是对 FROM JOIN 里原表中的列进行 事前筛选，所以WHERE可以对没选择的列进行筛选，但必须用原表列名而不能用SELECT中确定的列别名

• 相反 HAVING …… 对 SELECT …… 查询后（通常是分组并聚合查询后）的结果列进行 事后筛选，若SELECT里起了别名的字段则必须用别名进行筛选，且不能对SELECT里未选择的字段进行筛选。唯一特殊情况是，当HAVING筛选的是聚合函数时，该聚合函数可以不在SELECT里显性出现

案例

筛选出总发票金额大于500且总发票数量大于5的顾客

USE sql_invoicing;
​
SELECT
  client_id,
   SUM(invoice_total) AS total_sales,
   COUNT(*/invoice_total/invoice_date) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5
— 均为 SELECT 里的列别名

若写：WHERE total_sales > 500 AND number_of_invoices > 5，会报错：Error Code: 1054. Unknown column 'total_sales' in 'where clause'

练习

在 sql_store 数据库（有顾客表、订单表、订单项目表等）中，找出在 'VA' 州且消费总额超过100美元的顾客（这是一个面试级的问题，还很常见）

思路：

USE sql_store;
​
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
   SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING (customer_id)  — 别忘了括号，特容易忘
JOIN order_items oi USING (order_id)
WHERE state = 'VA'
GROUP BY
  c.customer_id,
  c.first_name,
  c.last_name
HAVING total_sales > 100

4.4 ROLLUP运算符

GROUP BY …… WITH ROLL UP 自动汇总型分组，若是多字段分组的话汇总也会是多层次的，注意这是MySQL扩展语法，不是SQL标准语法

案例

分组查询各客户的发票总额以及所有人的总发票额

USE sql_invoicing;

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

多字段分组 例1：分组查询各州、市的总销售额（发票总额）以及州层次和全国层次的两个层次的汇总额

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

多字段分组 例2：分组查询特定日期特定付款方式的总支付额以及单日汇总和整体汇总

USE sql_invoicing;

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 WITH ROLLUP

练习

分组计算各个付款方式的总付款 并汇总

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

总结

各子句执行先后顺序（GROUP BY 和其他子句的关系）

SQL 书写顺序：SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

真实底层执行顺序（关键！决定各子句能做什么）