MySQL基础进阶：汇总数据

聚合函数

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

1. MAX()：求最大值
2. MIN()：求最小值
3. AVG()：求平均值
4. SUM()：求和
5. 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 number_of_invioces,
    COUNT(*) AS total_records,   -- 表中共有多少条记录
COUNT(DISTINCT client_id) AS number_of_clients
-- DISTINCT 用于删除重复的属性值，然后再计数
FROM invoices
WHERE invoice_date > '2019-07-01' 

GROUP BY子句

按一列或多列中记录利用某种规则重新汇总，生成新的记录。
语法：GROUP BY …… 用于将某个属性列下相同的值进行合并，一般和函数公用，函数放在SELECT 后，GROUP BY放在WHERE后
NOTE:

1. 注意语句的位置顺序。
2. 解答复杂问题时，学会先分解拆分为简单的小问题或小步骤逐个击破。合理运用分解组合和IPO思想。
3. 当有多个统计分类限制条件时，依次分类细化，多个限制条件也是逐步细化，从大到小的包含关系。

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.state,
    c.city,
    SUM(i.invoice_total) AS total_sales
-- SELECT通常都是选择分组依据列+目标统计列的聚合函数。选别的列没有意义
FROM invoices i
JOIN clients c
    USING (client_id)
WHERE invoice_date > '2019-07-01' 
GROUP BY c.state, c.city
-- 虽然用了两个属性值为分组依据，但是从数据来说仍然是一个城市的
SELECT
    p.date,
    pm.name,
    SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
    ON P.payment_method = pm.payment_method_id
GROUP BY p.date, p.payment_method
ORDER BY p.date
-- 用了两个属性值为统计依据，再满足第一个汇总依据基础上再满足第二个汇总依据

HAVING子句

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

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

SELECT
    client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices >5
-- HAVING子句用于统计后的数据筛选
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 c.state = 'VA'
GROUP BY c.customer_id
HAVING total_sales > 100
NOTE: 当HAVING筛选的是聚合函数时，该聚合函数可以不在SELECT里显性出现。

ROLLUP运算符

GROUP BY …… WITH ROLLUP 自动汇总型分组（对 SUM 之类的聚合值进行分组后的汇总），若是多字段分组的话汇总也会是多层次的，注意这是MySQL扩展语法，不是SQL标准语法。
WITH ROLLUP前的属性名一定要在SELECT中呈现出来，如果只是GROUP BY就不一定要在SELECT中表现出来。

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