心中一直对group by 有疑惑
明明有多个数据,为啥分组后就剩一个数据了,这个数据是如何选择出来的
今天做到这道力扣的简(kun)单(nan)题,好家伙,整不会了
部门表 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,这里深入讲解一下。后面会用javascript代码对比举例,请做好心理准备。
对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,该表中的数据被分为三组,即
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
+------+---------+-------+
| 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,否则忽略。
写成功能对应的javascript代码就是这样:
result.jan_revenue = departments.reduce(total, department => total + (department.month == 'Jan' ? department.revenue : 0)
其它月份同理:
result.feb_revenue = departments.reduce(total, department => total + (department.month == 'Feb' ? department.revenue : 0)
result.mar_revenue = departments.reduce(total, department => total + (department.month == 'Mar' ? department.revenue : 0)
……
那为什么也可以用max来处理呢?
因为根据题目描述我们可以知道,每个月份最多只会出现一次,所以用max取出那个唯一值就可以了。
完整代码
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
太强了 这一通解析使我茅塞顿开 这下真变成简单题了
上文解析作者:loverxp
链接:https://leetcode-cn.com/problems/reformat-department-table/solution/group-byben-zhi-lun-by-loverxp-7mgy/
来源:力扣(LeetCode)