一、题目

一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下 student 表中。

+---------+------------+
|  name   | continent  |
+---------+------------+
| Jack    | America    |
| Pascal  | Europe     |
| Xi      | Asia       |
| Jane    | America    |
+---------+------------+

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

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

+----------+-------+---------+
| America  | Asia  | Europe  |
+----------+-------+---------+
| Jack     | Xi    | Pascal  |
| Jane     | NULL  | NULL    |
+----------+-------+---------+

**进阶：**如果不能确定哪个大洲的学生数最多，你可以写出一个查询去生成上述学生报告吗？

二、分析

本题属于行转列的题目，但是本题有意思的点在于没有行id，也不关注顺序。因为没有id所以没有办法关联，这也是这个题目中的难点

三、SQL

1.常规解法-开窗函数

使用row_number开窗，给出每个大洲不同学生一个id，每个大洲从1开始计数,这个id也是行id。根据该id分组可以计算出所有的行。对于进阶问题 则可以顺利解决。人数最多的大洲id最大。按照rn分组，进行行转列，但不查询rn即可得到最终结果 执行SQL

select max(if(continent = 'America', name, null)) America,
       max(if(continent = 'Asia', name, null))    Asia,
       max(if(continent = 'Europe', name, null))  Europe
from (select *, row_number() over (partition by continent order by name) rn
      from t1_student) t
group by rn

SQL结果

+----------+-------+---------+
| America  | Asia  | Europe  |
+----------+-------+---------+
| Jack     | Xi    | Pascal  |
| Jane     | NULL  | NULL    |
+----------+-------+---------+

2.pivot解法

与上面类似，需要使用row_number开窗得到行id，但是行转列的时候使用透视表pivot的方式，不是if或者case when的方式。

这里pivot中非判断条件、非聚合的列即group by的列。

执行SQL

select America,Asia,Europe
from (select *, row_number() over (partition by continent order by name) rn
      from t1_student) t pivot(
max(name) for continent in('America' as America,'Asia' as Asia,'Europe' as Europe)
)

SQL结果

+----------+-------+---------+
| America  | Asia  | Europe  |
+----------+-------+---------+
| Jack     | Xi    | Pascal  |
| Jane     | NULL  | NULL    |
+----------+-------+---------+

四、建表语句和数据插入

--建表语句
CREATE TABLE t1_student(
name string,
continent string
) COMMENT '学生信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
-- 插入数据
insert into t1_student(name,continent)
values
('Jack','America'),
('Pascal','Europe'),
('Xi','Asia'),
('Jane','America');

相关推荐

1. （文末有福利）使用collec_list、collect_set函数进行行转列
2. 行转列-有序行转列
3. 行转列-多列一一对应
4. 行转列-多行转多列（竖表转横表)
5. 列转行-多列转多行（横表变竖表）
6. 列转行-lateral view explode列转行
7. 列转行-explode_outer及lateral view outer
8. 列转行-posexplode多列对应转行
9. 列转行-lateral view outer posexplode及posexplode_outer多列对应转行
10. sparksql-pivot子句介绍
11. sparksql-unpivot子句介绍

本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文: 同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎你的加入，社群免费。