0%

MySQL笔记(4)--数据汇总

发表于 分类于 Valine：

1 聚合函数（Aggregate Functions）

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS total,
    SUM(invoice_total * 1.1) AS total2,
    COUNT(invoice_total) AS number_of_invoices,
    COUNT(payment_date) AS count_of_payments,		-- COUNT只计算非空值个数
    COUNT(*) AS total_records,
    COUNT(DISTINCT client_id) AS total_clients 		-- 去除重复值
FROM invoices
WHERE invoice_date > '2019-07-01'

Exercise

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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'
date_range total_sales total_payments what_we_expect
First half of 2019 1539.07 212.97 1326.1
Second half of 2019 1051.53 148.41 903.12
Total 2590.6 361.38 2229.22

2 GROUP BY 子句

Demo 1

1
2
3
4
5
6
7
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

⚠ 各个子句的顺序不能错，比如不能将WHERE移到ORDER BY 下面

得出每个client在2019年下半年的订单总金额

client_id total_sales
5 489.52
3 427.54
1 134.47

Demo 2

1
2
3
4
5
6
7
SELECT
    state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client_id)
GROUP BY state, city

得到每个（state, city）组合的总金额：

state city total_sales
NY Syracuse 802.89
WV Huntington 101.79
CA San Francisco 705.90
OR Portland 980.02

Exercise

1
2
3
4
5
6
7
8
9
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
ORDER BY date

根据交易日期和交易方式求总金额。

date payment_method total_payments
2019-01-03 Credit Card 74.55
2019-01-08 Credit Card 32.77
2019-01-08 Cash 10.00
2019-01-11 Credit Card 0.03
2019-01-15 Credit Card 148.41
2019-01-26 Credit Card 87.44
2019-02-12 Credit Card 8.18

3 HAVING 子句

👉 使用WHERE子句可以在行分组之前过滤数据
👉 使用HAVING子句可以在行分组之后过滤数据

1
2
3
4
5
6
SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500  		-- 必须是在select子句中的column
client_id total_sales
1 802.89
3 705.90
5 980.02

复合搜索条件

1
2
3
4
5
6
7
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

获取金额超过500并且多于5个订单的client的结果

client_id total_sales number_of_invoices
5 980.02 6

Exercise

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Get the customers located in VG
-- 		who have spent more than $100
USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.state,
    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
customer_id first_name last_name state total_sales
2 Ines Brushfield VA 157.92

4 ROLLUP 运算符

Demo 1

1
2
3
4
5
SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP

WITH ROLLUP 自动计算总数

client_id total_sales
1 802.89
2 101.79
3 705.90
5 980.02
2590.60

Demo 2

1
2
3
4
5
6
7
8
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

注意: GROUP BY 子句中由于使用了 WITH ROLLUP, 就不能使用别名 payment_method

得到每个组及整个结果集的汇总集:

date payment_method total_payments
2019-01-03 Credit Card 74.55
2019-01-03 74.55
2019-01-08 Cash 10.00
2019-01-08 Credit Card 32.77
2019-01-08 42.77
2019-01-11 Credit Card 0.03
2019-01-11 0.03
2019-01-15 Credit Card 148.41
2019-01-15 148.41
2019-01-26 Credit Card 87.44
2019-01-26 87.44
2019-02-12 Credit Card 8.18
2019-02-12 8.18
361.38

注意到 2019-01-08 对 Cash 和 Credit Card 有一个汇总值.