最强最全⾯的⼤数据SQL⾯试题和答案（由31位⼤佬共同协作完成）
本套SQL题的答案是由许多⼤佬共同贡献，1+1的⼒量是远远⼤于2的，有不少题⽬都采⽤了⾮常巧妙的解法，也有不少题⽬有多种解法。本套⼤数据SQL题不仅题⽬丰富多样，答案更是精彩绝伦！
⾮常感谢五分钟学⼤数据读者以下⼤佬的贡献：Mr.S、后会⽆期、我就是我、涛声依旧、徐明、 蛋⽩、只争朝⼣、魏明、⼗六画⽣、John.Xiong、南⽆、。、⽠⽪、Camino 、认真的⼩眼睛、热爱⽣活Hello World！、⽆语梦醒、学不动了、life、执笔者、⽠⽪、清风明⽉、焕溪沙、Dragon·、惟吾德馨LY、长夜未央、欧哈呦、姜明松、乔⼀、⼩强、情深@骚明因时间及⽔平有限，且避免不了因疏忽等情况导致答案出错，如您发现答案有错误或您有更优解，欢迎加我
(yuan_more)告知，感激不尽！
注：以下参考答案都经过简单数据场景进⾏测试通过，但并未测试其他复杂情况。本⽂档的SQL主要使⽤Hive 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
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 8sql查询面试题及答案
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
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 _min = 2 then ret.c else null end) as min_c
,max(case _max = 2 then ret.c else null end) as max_c from (
select
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
_min = 2
_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 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;
四、窗⼝⼤⼩控制
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论