mysql建立数据透视表_SQL复习笔记 —— 数据透视表的妙用

在SQL面试题中，数据透视表也是一个热门考点之一。SQL内置了数据透视表的函数，但是其使用不够灵活，在许多情况下需要自己根据情况写出相应的查询逻辑。下面给出两个具体的例子，来分析在不同场景下数据透视表的查询方法：

基本逻辑：case+group by语句

Leetcode 1179是一个典型制作数据透视表的题目编写一个 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 | ... | Dec_Revenue |

+---+-------------+---+--------------+

| 1 | 8000 | ... | null |

| 2 | 9000 | ... | null |

| 3 | null | ... | null |

+---+-------------+---+--------------+

该题的思路是以部门ID为行标签，以月份为列标签来显示出相应部门在某月产生的收入。首先通过分析，题目需要以month列为依据对数据进行分类，因此这里需要使用case语句将不同的情况分成不同的列。其次我们需要使用group by聚合函数，将行标签归为三个部门的ID，这里值得注意的是，虽然本题中每个月每个部门的收入在原表中是唯一的，并不存在实际的求和需求，但我们依然可以使用sum函数对每一部门下的收入进行聚合，从而使得表格符合最终期望得到的格式。一个可能的MySQL实现如下所示：

select

id,

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

sum(case when month='Feb' then revenue else null end) as Feb_Revenue,

sum(case when month='Mar' then revenue else null end) as Mar_Revenue,

sum(case when month='Apr' then revenue else null end) as Apr_Revenue,

sum(case when month='May' then revenue else null end) as May_Revenue,

sum(case when month='Jun' then revenue else null end) as Jun_Revenue,

sum(case when month='Jul' then revenue else null end) as Jul_Revenue,

sum(case when month='Aug' then revenue else null end) as Aug_Revenue,

sum(case when month='Sep' then revenue else null end) as Sep_Revenue,

sum(case when month='Oct' then revenue else null end) as Oct_Revenue,

sum(case when month='Nov' then revenue else null end) as Nov_Revenue,

sum(case when month='Dec' then revenue else null end) as Dec_Revenue

from department

group by id;

leetcode 1179是最基本和最典型的数据透视表查询格式，对于所有SQL查询数据透视表的题目，其基本框架都是使用case语句建立列标签，进而选用group by+合适的聚合函数整理行标签，最终获得所需的数据透视表。

进阶：灵活选取行标签和聚合函数

leetcode 618是一个非常巧妙的题，该题可以看作是数据透视表的一个变种一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下 student表中。

| name | continent |

|-------|----------|

| Jack | America |

| Pascal | Europe |

| Xi | Asia |

| Jane | America |

写一个查询语句实现对大洲(continent)列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲(America)、亚洲(Asia)和欧洲(Europe)。数据保证来自美洲的学生不少于来自亚洲或者欧洲的学生。

对于样例输入，它的对应输出是：

| America | Asia | Europe |

|---------|------|--------|

| Jack | Xi | Pascal |

| Jane | | |

该题与上一题最大的不同之处在于最终结果表并没有明确的行标签，仅仅存在作为国家的列标签，以及列标签下罗列的不同国籍的学生。因此需要通过自己对建立一个虚拟的行标签对透视表的每一行进行格式化。使用窗口函数row_number是一个很自然的想法，我们队不同国家的学生分别从1开始进行编号，因此从1到n(n为学生最多国家的学生总数，表中为美国)作为该透视表的每一行的“虚拟标签”。聚合函数可以选择max函数，同样这里并没有比较需求，而是用过max函数对学生的名字进行聚合，使得非空的位置填上学生的名字。下面是MySQL的一个实现：

select

max(case when continent='America' then name else null end) as America,

max(case when continent='Asia' then name else null end) as Asia,

max(case when continent='Europe' then name else null end) as Europe

from(

select

*,row_number() over(partition by continent order by name asc) as rk

from student

) as g

group by rk;

这个思路非常巧妙，本身题目和数据透视表并没有直接关联，且用常规想法去做会有比较大的障碍，因为需要以学生最多的一列作为行数，因此在格式调整上会十分的繁琐和不自然。然而运用数据透视表的思想，为其添加虚拟行标签转化为数据透视表，再利用合适的聚合函数将其格式化，该题的思路则显得简单而明了。许多分类和表格格式化的面试题，均能通过数据透视表的思路得到通俗简洁的答案。

参考