group by本质论与groupby多个字段

一、group by本质论

转载自：https://leetcode-cn.com/problems/reformat-department-table/solution/group-byben-zhi-lun-by-loverxp-7mgy/

1179. 重新格式化部门表

SQL架构

部门表 Department：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。

 

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

查询结果格式如下面的示例所示：

Department 表：
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

查询得到的结果表：
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

解题思路

发现很多童鞋不理解group by，以及为什么要使用sum或max,这里深入讲解一下。

对group by理解不透彻有可能是MySQL用户专有的问题，因为在MySQL中，允许下面这样的写法

select id, revenue
from Department
group by id

即在select子句中出现了group by子句中没有出现的列名revenue，而这种写法在SQL标准中是没有的，在MySQL以外的大部分数据库中也是不支持的，因为逻辑上没有意义。

要理解为什么这种写法没有意义，我们来看一看题目本身提供的样例数据。（注意，其中的id并不是真正的id，只是部门编号)

+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

通过group by id，该表中的数据被分为三组，即

+------+---------+-------+
| 1    | 8000    | Jan   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+
+------+---------+-------+
| 2    | 9000    | Jan   |
+------+---------+-------+
+------+---------+-------+
| 3    | 10000   | Feb   |
+------+---------+-------+

此时在第一组中，有三条记录，也就是说有三个revenue，那么此时select id, revenue就无法判定应该取哪一个revenue，所以这样的操作在标准SQL中是不允许的，只能通过聚合函数来处理。而MySQL在这里提供了一种便利的方式，却让理解它的工作方式变得更加困难。

聚合函数本质上就是常见编程语言里的reduce，也就是把一组数据处理成一个单一数据，所以可以这样写：

select id, sum(revenue) as total
from Department
group by id

来表示一个部门的总收入。

这个操作在javascript中类似于这样：

departments.reduce(total, department => total + department.revenue)

max等其它聚合函数同理，都可以找到用reduce表示的等价形式。

那么代码

sum(case month when 'Jan' then revenue end) as Jan_Revenue,

的意思就是说把所有的revenue聚合处理，处理的方法是，如果month的值是Jan，那么结果就是revenue，否则忽略。
 

select id,
    sum(case month when 'Jan' then revenue end) as Jan_Revenue,
    sum(case month when 'Feb' then revenue end) as Feb_Revenue,
    sum(case month when 'Mar' then revenue end) as Mar_Revenue,
    sum(case month when 'Apr' then revenue end) as Apr_Revenue,
    sum(case month when 'May' then revenue end) as May_Revenue,
    sum(case month when 'Jun' then revenue end) as Jun_Revenue,
    sum(case month when 'Jul' then revenue end) as Jul_Revenue,
    sum(case month when 'Aug' then revenue end) as Aug_Revenue,
    sum(case month when 'Sep' then revenue end) as Sep_Revenue,
    sum(case month when 'Oct' then revenue end) as Oct_Revenue,
    sum(case month when 'Nov' then revenue end) as Nov_Revenue,
    sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id

二、groupby多个字段

案例：

在平时的开发任务中我们经常会用到MYSQL的GROUP BY分组， 用来获取数据表中以分组字段为依据的统计数据。比如有一个学生选课表，表结构如下：

Table: Subject_Selection

Subject   Semester   Attendee
---------------------------------
ITB001    1          John
ITB001    1          Bob
ITB001    1          Mickey
ITB001    2          Jenny
ITB001    2          James
MKB114    1          John
MKB114    1          Erica

我们想统计每门课程有多少个学生报名，应用如下SQL：

SELECT Subject, Count(*)
FROM Subject_Selection
GROUP BY Subject

得到如下结果：

Subject    Count
------------------------------
ITB001     5
MKB114     2

因为表里记录了有5个学生选择ITB001，2个学生选择了MKB114。

产生这个结果的原因是：
GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。
那么GROUP BY X, Y呢？
GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。

我们下面再接着要求统计出每门学科每个学期有多少人选择，应用如下SQL：

SELECT Subject, Semester, Count(*)
FROM Subject_Selection
GROUP BY Subject, Semester

上面SQL的意思是，对Subject_Selection表中的数据进行分组，将具有相同Subject和Semester字段值的记录放到同一个分组里去， 然后对每个分组中的数据应用聚合函数（COUNT，SUM， AVG，etc）。

得到的结果是：

Subject    Semester   Count
------------------------------
ITB001     1          3
ITB001     2          2
MKB114     1          2

从表中的记录我们可以看出这个分组结果是正确的有3个学生在第一学期选择了ITB001, 2个学生在第二学期选择了ITB001,
还有两个学生在第一学期选择了MKB114, 没人在第二学期选择MKB114。

再比如有一个订单表，表中记录了所有支付过的订单

Table: Order

Product   Buyer       Spending
---------------------------------
PD001     Todd          12.00
PD001     Todd          12.00
PD001     Todd          12.00
PD001     Lily          12.00
PD001     Lily          12.00
PD002     Todd          20.00
PD002     Todd          20.00

现在我们想统计每个用户在每种商品上总共花了多少钱，执行如下SQL

SELECT Product，Buyer, SUM(Spending)
FROM `Order`
GROUP BY  Buyer,Product

获得的结果如下：

总结：

在MYSQL中使用GROUP BY对表中的数据进行分组时，
GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里，
GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。