年前，技术群组织了一场数据类的技术&面试讨论会，邀请了一些大厂朋友、23年参加社招和校招的同学来分享：新人如何入门数据和算法岗，面经/面试题经验分享、大厂在算法场景的落地项目及经验分享等热门话题。
结合讨论内容，今天我总结一下面试中最频繁被考的 SQL 面试题，喜欢几点收藏、关注、点赞。文章篇幅有限，完整版，可以文末找我们获取。
本文目录
一、行列转换 二、排名中取他值 三、累计求值 四、窗口大小控制 五、产生连续数值 六、数据扩充与收缩 七、合并与拆分 八、模拟循环操作 九、不使用distinct或group by去重 十、容器–反转内容 十一、多容器–成对提取数据 十二、多容器–转多行 十三、抽象分组–断点排序 十四、业务逻辑的分类与抽象–时效 十五、时间序列–进度及剩余 十六、时间序列–构造日期 十七、时间序列–构造累积日期 十八、时间序列–构造连续日期 十九、时间序列–取多个字段最新的值 二十、时间序列–补全数据 二十一、时间序列–取最新完成状态的前一个状态 二十二、非等值连接–范围匹配 二十三、非等值连接–最近匹配 二十四、N指标–累计去重
一、行列转换
描述：表中记录了各年份各部门的平均绩效考核成绩。 表名：t1 表结构：
a -- 年份
b -- 部门
c -- 绩效得分
表内容：
a b c
2014 B 9
2015 A 8
2014 A 10
2015 B 7
问题一：多行转多列
问题描述：将上述表内容转为如下输出结果所示：
a col_A col_B
2014 10 9
2015 8 7
参考答案：
select
a,
max(case when b="A" then c end) col_A,
max(case when b="B" then c end) col_B
from t1
group by a;
问题二：如何将结果转成源表？（多列转多行）
问题描述：将问题一的结果转成源表，问题一结果表名为t1_2。
参考答案：
select
a,
b,
c
from (
select a,"A" as b,col_a as c from t1_2
union all
select a,"B" as b,col_b as c from t1_2
)tmp;
问题三：同一部门会有多个绩效，求多行转多列结果
问题描述：2014年公司组织架构调整，导致部门出现多个绩效，业务及人员不同，无法合并算绩效，源表内容如下：
2014 B 9
2015 A 8
2014 A 10
2015 B 7
2014 B 6
输出结果如下所示：
a col_A col_B
2014 10 6,9
2015 8 7
参考答案:
select
a,
max(case when b="A" then c end) col_A,
max(case when b="B" then c end) col_B
from (
select
a,
b,
concat_ws(",",collect_set(cast(c as string))) as c
from t1
group by a,b
)tmp
group by a;
二、排名中取他值
表名：t2 表字段及内容：
a b c
2014 A 3
2014 B 1
2014 C 2
2015 A 4
2015 D 3
问题一：按a分组取b字段最小时对应的c字段
输出结果如下所示：
a min_c
2014 3
2015 4
参考答案:
select
a,
c as min_c
from
(
select
a,
b,
c,
row_number() over(partition by a order by b) as rn
from t2
)a
where rn = 1;
问题二：按a分组取b字段排第二时对应的c字段
输出结果如下所示：
a second_c
2014 1
2015 3
参考答案：
select
a,
c as second_c
from
(
select
a,
b,
c,
row_number() over(partition by a order by b) as rn
from t2
)a
where rn = 2;
问题三：按a分组取b字段最小和最大时对应的c字段
输出结果如下所示：
a min_c max_c
2014 3 2
2015 4 3
参考答案:
select
a,
min(if(asc_rn = 1, c, null)) as min_c,
max(if(desc_rn = 1, c, null)) as max_c
from
(
select
a,
b,
c,
row_number() over(partition by a order by b) as asc_rn,
row_number() over(partition by a order by b desc) as desc_rn
from t2
)a
where asc_rn = 1 or desc_rn = 1
group by a;
问题四：按a分组取b字段第二小和第二大时对应的c字段
输出结果如下所示：
a min_c max_c
2014 1 1
2015 3 4
参考答案：
select
ret.a
,max(case when ret.rn_min = 2 then ret.c else null end) as min_c
,max(case when ret.rn_max = 2 then ret.c else null end) as max_c
from (
select
*
,row_number() over(partition by t2.a order by t2.b) as rn_min
,row_number() over(partition by t2.a order by t2.b desc) as rn_max
from t2
) as ret
where ret.rn_min = 2
or ret.rn_max = 2
group by ret.a;
问题五：按a分组取b字段前两小和前两大时对应的c字段
注意：需保持b字段最小、最大排首位
输出结果如下所示：
a min_c max_c
2014 3,1 2,1
2015 4,3 3,4
参考答案：
select
tmp1.a as a,
min_c,
max_c
from
(
select
a,
concat_ws(',', collect_list(c)) as min_c
from
(
select
a,
b,
c,
row_number() over(partition by a order by b) as asc_rn
from t2
)a
where asc_rn <= 2
group by a
)tmp1
join
(
select
a,
concat_ws(',', collect_list(c)) as max_c
from
(
select
a,
b,
c,
row_number() over(partition by a order by b desc) as desc_rn
from t2
)a
where desc_rn <= 2
group by a
)tmp2
on tmp1.a = tmp2.a;
三、累计求值
表名：t3 表字段及内容：
a b c
2014 A 3
2014 B 1
2014 C 2
2015 A 4
2015 D 3
问题一：按a分组按b字段排序，对c累计求和
输出结果如下所示：
a b sum_c
2014 A 3
2014 B 4
2014 C 6
2015 A 4
2015 D 7
参考答案：
select
a,
b,
c,
sum(c) over(partition by a order by b) as sum_c
from t3;
问题二：按a分组按b字段排序，对c取累计平均值
输出结果如下所示：
a b avg_c
2014 A 3
2014 B 2
2014 C 2
2015 A 4
2015 D 3.5
参考答案：
select
a,
b,
c,
avg(c) over(partition by a order by b) as avg_c
from t3;
问题三：按a分组按b字段排序，对b取累计排名比例
输出结果如下所示：
a b ratio_c
2014 A 0.33
2014 B 0.67
2014 C 1.00
2015 A 0.50
2015 D 1.00
参考答案：
select
a,
b,
c,
round(row_number() over(partition by a order by b) / (count(c) over(partition by a)),2) as ratio_c
from t3
order by a,b;
问题四：按a分组按b字段排序，对b取累计求和比例
输出结果如下所示：
a b ratio_c
2014 A 0.50
2014 B 0.67
2014 C 1.00
2015 A 0.57
2015 D 1.00
参考答案：
select
a,
b,
c,
round(sum(c) over(partition by a order by b) / (sum(c) over(partition by a)),2) as ratio_c
from t3
order by a,b;
四、窗口大小控制
表名：t4 表字段及内容：
a b c
2014 A 3
2014 B 1
2014 C 2
2015 A 4
2015 D 3
问题一：按a分组按b字段排序，对c取前后各一行的和
输出结果如下所示：
a b sum_c
2014 A 1
2014 B 5
2014 C 1
2015 A 3
2015 D 4
参考答案：
select
a,
b,
lag(c,1,0) over(partition by a order by b)+lead(c,1,0) over(partition by a order by b) as sum_c
from t4;
问题二：按a分组按b字段排序，对c取平均值
问题描述：前一行与当前行的均值！
输出结果如下所示：
a b avg_c
2014 A 3
2014 B 2
2014 C 1.5
2015 A 4
2015 D 3.5
参考答案：
select
a,
b,
case when lag_c is null then c
else (c+lag_c)/2 end as avg_c
from
(
select
a,
b,
c,
lag(c,1) over(partition by a order by b) as lag_c
from t4
)temp;
技术交流
独学而无优则孤陋而寡闻，技术要学会交流、分享，不建议闭门造车。
建立了技术交流与面试交流群，面试真题、答案获取，均可加交流群获取，群友已超过2000人，添加时最好的备注方式为：来源+兴趣方向，方便找到志同道合的朋友。
方式①、微信搜索公众号：Python学习与数据挖掘，后台回复：sql面试题 方式②、添加微信号：dkl88194，备注：sql面试题
文章精选
滴滴风控机器学习算法岗面试题8道(含答案解析)美团营销机器学习算法岗(实习)面试题9道(含答案解析)面了滴滴的数据分析师（实习），几道面试题都是原题啊面试必备！机器学习常用十大算法的优缺点！25道机器学习面试问题（附答案）5个必考的大厂SQL面试题一文解决样本不均衡10 大经典排序算法 Python 版实现实战案例：时间序列预测代码模板(单变量、多元、多步、多元多步)实战案例：基于fasttext embedding + lightgbm 垃圾短信识别实战案例：构建基于 DSSM 双塔模型的电影推荐系统实战案例：构建基于 Transformer 建立时间序列预测模型（附完整代码）实战案例：基于LSTM的四种方法进行电影评论情感分类预测实战案例：基于LSTM的国际航空公司乘客预测实战案例：基于电商销售数据的 RFM 模型构建实战案例：一文详解数据分析经典模型RFM实战案例：基于孤立森林的信用卡欺诈检测
相关阅读
发表评论