SQL语言笔记 第四章 聚合函数+分组查询 GROUP BY\HAVING\WITH ROLLUP

使用的是MySQL数据库

1. 聚合函数 实现汇总数据查询

• 聚合函数是数据库内置函数中的一部分，其取系列值并聚合它们，导出一个单一值。
• 聚合函数只运行非空值。
• WHERE子句中无法使用聚合函数。

-- MAX() 取最大值 可以应用于数值、日期和字符串
-- MIN() 取最小值 可以应用于数值、日期和字符串
-- AVG() 取平均值
-- SUM() 求和
-- COUNT() 非空个数
-- COUNT(*) 总个数

SELECT
    -- 最近日期 MAX(payment_date) AS highest,
    MAX(invoice_total) 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_if_payments,
    -- 想要不管付没付款的都算个数，在括号里写*即可
    -- COUNT(*) AS total_records
    -- *剔除重复项，只统计不重复的个数 在括号里列名称前面加DISTINCT关键字
    -- 如：统计开发票的客户数，一个客户可能开多个发票只能算一个客户
    COUNT(DISTINCT client_id) AS count_of_clients 
FROM invoices
-- 可以添加筛选条件，再执行上面函数 显示2019-07-01之后关于发票的相关查询
-- WHERE invoice_date > '2019-07-01'

-- 练习
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'

2. GROUP BY 子句

-- 查看每个客户的发票总额
SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
-- 先执行WHERE筛选，再执行GROUP BY分组
-- 查询2019下半年的发票
WHERE invoice_date > '2019-07-01'
-- 根据客户id分组
GROUP BY client_id
-- ORDER BY不可以写在GROUP BY前面，否则会报语法错误
-- 根据total_sales倒序显示
ORDER BY total_sales DESC;

-- 多列分组
-- 根据州、市两列组合分组，连接clients表查询
SELECT
    state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY state,city;

3. HAVING 子句

• HAVING子句和WHERE子句的区别：
• （1）HAVING子句可以在分组之后筛选数据；（2）且只能使用SELECT子句中出现的列（指表中真实存在的数据列）；（3）HAVING子句中可以直接使用聚合函数，即使该聚合函数没有存在于SELECT子句中；（4）HAVING子句针对的是分组。
• （1）WHERE子句只能在分组之前筛选数据；（2）可以使用任何列；（3）WHERE子句不能使用聚合函数；（4）WHERE子句针对的是行。

-- 查询总销售高于500的客户,且发票数量超过5张
SELECT
    client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices
FROM invoices
-- 分组之前没有total_sales，无法使用WHERE子句
GROUP BY client_id
HAVING total_sales > 500
    AND number_of_invoices > 5

-- 练习：查询所在地为VA的顾客，且总消费金额超过100
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    state,
    SUM(ci.quantity*ci.unit_price) AS total_spent
FROM customers c
-- orders表与order_items表存在关联，customers表通过orders表，获取order_items表中的数据
JOIN orders o
    USING (customer_id)
-- order_items表中存放了数量和单价
JOIN order_items ci
    USING (order_id)
WHERE state = 'VA'
GROUP BY c.customer_id,
         c.first_name,
         c.last_name
HAVING total_spent > 100

4. ROLLUP 运算符 WITH ROLLUP（MySQL特有）

• 在GROUP BY子句最后添加WITH ROLLUP，且添加ROLLUP运算符，分组的列名不可以用别名。
• ROLLUP运算符只能应用于聚合值的列。
• 在现实工作中应用广泛，但不是标准的SQL语言，只有MySQL独有。

SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
-- total_sales一列最后多了数值的汇总值
GROUP BY client_id WITH ROLLUP

-- 多列分组 给出每个组及整个结果集的汇总值
SELECT
    state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY state,
         city WITH ROLLUP
         
-- 练习：查询不同付款方式的付款总额以及汇总值
SELECT
    pm.name AS payment_method,
    SUM(p.amount) AS total
FROM payment_methods pm
JOIN payments p
    ON (pm.payment_method_id = p.payment_method)
-- *使用ROLLUP运算符，不可以使用别名
GROUP BY pm.name WITH ROLLUP;