005-数据透视表与GROUP BY
2019-05-02 本文已影响0人
Sayalic_2484
数据透视表
我们希望把数据按照一定的规则聚合起来，再去做统计。比如说我们已经拥有订单记录：
- 订单id，用户id，创建时间， 订单金额
但我们想知道以下问题的答案：
- 每个用户下了多少单
- 每个月有多少订单金额
这两个问题的共同点在于，需要把原始的记录按照一定规则分组（按用户，按时间），然后再对每个分组做运算。这个时候我们就需要用到Excel里面的数据透视表了：
原始数据
生成数据透视表：
插入数据透视表
数据透视表选择字段：
每月订单金额
举例来说，对于图中圈中的格子，表示创建时间为3月的所有订单，订单金额求和为300。
Excel还支持按多个维度聚合，比如我们可以在行里面增加用户ID字段：
多维度聚合
对于圈中的格子来说，表示创建时间为4月，且用户ID为4的所有订单，订单金额求和为700。
数据透视表的聚合字段可以放到行上，还可以放到列上：
多维度聚合
对于圈中的格子来说，表示创建时间为4月，且用户ID为4的所有订单，订单金额求和为700。
GROUP BY
假设数据库里面有一个订单表orders，字段为：
id, user_id, created_at, order_amount
分别表示：订单id，用户id，创建时间， 订单金额。
我们想知道以下问题的答案：
- 每个用户下了多少单
- 每个月有多少订单金额
分别对应的SQL为：
SELECT user_id, count(*) FROM orders GROUP BY user_id;
SELECT date_format(created_at, "%Y-%m"), sum(order_amount) FROM orders GROUP BY date_format(created_at, "%Y-%m");
对于第一条SQL而言：
GROUP BY user_id表示按照user_id去分组。count(*)是一个聚合函数，表示求在这个分组下所有记录的条数，也就是订单数。
对于第二条SQL而言：
date_format(created_at, "%Y-%m")表示把精确到秒的时间，转化为年-月的形式，然后再去分组，这样才是我们想要的按月分组。sum(order_amount)是一个聚合函数，表示求在这个分组下所有记录的order_amount字段的和，也就是订单金额总和。
我们这里多次遇到了聚合函数这个概念。聚合函数是把GROUP BY分组后的若干记录聚合成一个数值的函数，常见的有：
-
count(*)，统计每个分组的记录数量 -
sum(<表达式>)，每个分组的每条记录，按照表示求值之后的总和 -
avg(<表达式>)，每个分组的每条记录，按照表示求值之后的平均值
本质
对于一条SQL而言：
- 先按照
WHERE语句筛选记录，筛选剩下的记录进入GROUP BY流程 - 对每条记录，按照
GROUP BY后面的表达式求值，相同值的划分为一组，进入SELECT流程 - 把分组后的记录放入到
SELECT，对SELECT后面的每个表达式求值，得到最终结果。
假设orders表有如下数据：
id, user_id, created_at, order_amount
1, 1, 2019-01-01 00:00:00, 100
2, 1, 2019-02-01 00:00:00, 200
3, 2, 2019-02-01 00:00:00, 300
4, 2, 2019-03-01 00:00:00, 400
我们执行以下SQL：
SELECT date_format(created_at, "%Y-%m"), sum(order_amount), count(*)
FROM orders
WHERE id <= 3
GROUP BY date_format(created_at, "%Y-%m")
执行流程如下：
- 执行
WHERE筛选，剩下3条记录：
id, user_id, created_at, order_amount
1, 1, 2019-01-01 00:00:00, 100
2, 1, 2019-02-01 00:00:00, 200
3, 2, 2019-02-01 00:00:00, 300
- 执行
GROUP BY date_format(created_at, "%Y-%m")，对于每条记录的date_format(created_at, "%Y-%m")分别为
id, date_format(created_at, "%Y-%m")
1, 2019-01
2, 2019-02
3, 2019-02
聚合之后，整个结果变成一个两行的表：
2019-01, <1条记录>
2019-02, <2条记录>
- 将这个只有两行的表代入
SELECT求值。
对于第一行，date_format(created_at, "%Y-%m")就是聚合字段，得到2019-01，sum(order_amount)是该分组下所有记录的和，得到100，count(*)是该分组下记录的数量，得到1。
对于第二行，date_format(created_at, "%Y-%m")就是聚合字段，得到2019-02，sum(order_amount)是该分组下所有记录的和，200+300，得到500，count(*)是该分组下记录的数量，得到2。
所以最终我们得到：
date_format(created_at, "%Y-%m"), sum(order_amount), count(*)
2019-01, 100, 1
2019-02, 500, 2
注意事项
- SELECT语句的顺序是
SELECT开头，接着是FROM，然后是WHERE，接着是GROUP BY，然后是ORDER BY，最后是LIMIT BY。规定就是规定，入乡随俗。
