面试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思维高低的好办法，建议熟练掌握

更多实战题目和解法思路尽在社区，还有视频讲解哦