数据库的透视

在力扣做到这个题的时候，了解到了透视的概念，下面记录一下我对透视的理解，以及透视需要解决的一个很关键的问题。

一、题目描述

这个题要求重新格式化当前的表
表 Department：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+

在 SQL 中，(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 [“Jan”,“Feb”,“Mar”,“Apr”,“May”,“Jun”,“Jul”,“Aug”,“Sep”,“Oct”,“Nov”,“Dec”]。
题目要求重新格式化表格，使得 每个月 都有一个部门 id 列和一个收入列。
比如：表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        |
+------+-------------+-------------+-------------+-----+-------------+

新表中包括了id和12个月，每个月的数据来源于旧表的revenue，这种变换方式叫做透视。

二、透视

相关定义：

一在数据库和电子表格中，透视是一种将行转换成列的操作。
比如，如果你有一张表记录了每个月的收入，透视就可以帮你把每个月作为一个单独的列显示，这样每一行就代表一个特定的ID，而每一列就表示不同月份的收入。
👆也就是说，重新整理这张表，新表的每一行整合旧表的多行数据，放在了不同列上。

会出现的问题：

在初始接触这个题时，我的写法是这样的：

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

然后结果是这样的：

| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | Apr_Revenue | May_Revenue | Jun_Revenue | Jul_Revenue | Aug_Revenue | Sep_Revenue | Oct_Revenue | Nov_Revenue | Dec_Revenue |
| -- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- |
| 1  | 8000        | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        |
| 2  | 9000        | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        |
| 3  | null        | 10000       | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        |

可以看到，每一行id，只有一个月的结果，结果出错。

原因：

在一般写法（不使用聚合函数）中，新表的一行数据只能来源于旧表的一行数据，所以会出现只有一列被正确赋值的情况，要解决的话，一定要让新表的一行数据来源于旧表的一组数据（多行数据），所以需要使用group by和聚合函数。

解决

正确代码如下：

SELECT 
    id,
    MAX(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
    MAX(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
    MAX(CASE WHEN month = 'Mar' THEN revenue END) AS Mar_Revenue,
    MAX(CASE WHEN month = 'Apr' THEN revenue END) AS Apr_Revenue,
    MAX(CASE WHEN month = 'May' THEN revenue END) AS May_Revenue,
    MAX(CASE WHEN month = 'Jun' THEN revenue END) AS Jun_Revenue,
    MAX(CASE WHEN month = 'Jul' THEN revenue END) AS Jul_Revenue,
    MAX(CASE WHEN month = 'Aug' THEN revenue END) AS Aug_Revenue,
    MAX(CASE WHEN month = 'Sep' THEN revenue END) AS Sep_Revenue,
    MAX(CASE WHEN month = 'Oct' THEN revenue END) AS Oct_Revenue,
    MAX(CASE WHEN month = 'Nov' THEN revenue END) AS Nov_Revenue,
    MAX(CASE WHEN month = 'Dec' THEN revenue END) AS Dec_Revenue
FROM Department
GROUP BY id;

说明：使用max不是真的为了得到什么最大值，而是为了利用聚合函数，使得当前行的数据能来源于旧表的多行。

结果展示：

| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | Apr_Revenue | May_Revenue | Jun_Revenue | Jul_Revenue | Aug_Revenue | Sep_Revenue | Oct_Revenue | Nov_Revenue | Dec_Revenue |
| -- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- |
| 1  | 8000        | 7000        | 6000        | null        | null        | null        | null        | null        | null        | null        | null        | null        |
| 2  | 9000        | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        |
| 3  | null        | 10000       | null        | null        | null        | null        | null        | null        | null        | null        | null        | null        |