LeetCode面试SQL-学生地理信息报告

数据仓库晨曦

发布于 2024-09-24 10:45:35

9700

代码可运行

文章被收录于专栏：数据仓库技术数据仓库技术

运行总次数：0

代码可运行

一、题目

一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下 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');