MySQL课程笔记---第四章：聚合函数，GROUP BY，HAVING，ROLLUP

 主讲教师：Mosh;课程链接：【【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！-哔哩哔哩】 https://b23.tv/MqVRzdk

-- 第四章
-- 聚合函数
SELECT 
	MAX(payment_date) AS highest, -- 当应用于日期时，返回最近的日期
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total*1.1) AS total, -- 对象可以是数学表达式
    COUNT(invoice_total) AS number_of_invoices, -- 返回有效的总记录条数，有效指非空值
    COUNT(payment_date) AS count_of_payments, -- 此处有空值，故返回值小于上面的
    COUNT(*) AS total_records, -- 不管是不是空值，返回所有记录条数,但会有重复值
    COUNT(DISTINCT client_id) AS distinct_totaol_records -- distinct语句可以去除重复值
FROM invoices
-- 也可以加上筛选条件
WHERE invoice_date > '2019-07-01'

-- !!!!!!!!!!!SELECT语句不加括号！！！！！！！！！！！！
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 < '2019-07-01'
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 >= '2019-07-01'
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

-- GROUP BY 子句
-- 对单列分组
SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id -- 根据用户id进行分组,group by 在from和where之后，在order by之前
ORDER BY total_sales DESC
-- 对多个列分组
SELECT 
	state,
    city,
	SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client_id)
GROUP BY state,city

SELECT -- 练习题
	date,
    pm.name AS payment_metod,
    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
ORDER BY date

-- HAVING 子句
SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 -- HAVING子句用于在分组后筛选数据，where用在分组之前

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中有的

SELECT -- 练习题
	c.state,
    c.customer_id,
    c.first_name AS customer,
    SUM(oi.unit_price*oi.quantity) AS total_spent
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,
    c.first_name,
    c.last_name -- 写三个更保险
Having total_spent > 100

-- ROLLUP 运算符（只在MYsql里有）
SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP -- 新添一个汇总行，汇总数据，不能应用于id列
-- 多列分组汇总
SELECT 
	state,
    city,
	SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients c USING (client_id)
GROUP BY state,city WITH ROLLUP -- 会得到每个组和整个结果集的汇总
-- 注意使用with rollup时，不能使用列别名

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