面试SQL题 计算每年在校生人数
**题目均来自社区同学遇到面试题**
有一个录取学生人数表,记录的是每年录取学生人数和入学学生的学制,然后计算每年在校生人数
with data as ( -- 主键 入学年份 人数 学制 select 1 as id , 2001 as year , 1200 as stu_num , '3' as stu_len union all select 2 as id , 2000 as year , 1300 as stu_num , '5' as stu_len union all select 3 as id , 2003 as year , 1400 as stu_num , '4' as stu_len union all select 4 as id , 2002 as year , 1500 as stu_num , '3' as stu_len union all select 5 as id , 2002 as year , 1600 as stu_num , '2' as stu_len )
select year, sum(stu_num) -- 聚合在校生 from ( select id, (year + pos) as year, stu_num, stu_len, val from data lateral view posexplode( split(space( cast(stu_len as int) ) , '(?!$)' ) --产生学制数个空格并切割 --split(space( cast(stu_len as int) - 1 ) , ' ' ) --不用正则的写法,效果同上 ) -- posexplode 炸裂函数 返回带下标的表 t as pos,val -- posexplode 炸裂函数 返回 下标和具体值 ) t -- 派生表别名 group by year -- 按照年份分组,聚合在校生
答案如下
炸裂函数是面试常考的知识点,也是区分候选人SQL思维高低的好办法,建议熟练掌握
更多实战题目和解法思路尽在社区,还有视频讲解哦
#数据人的面试交流地##SQL面试#