Hive 刷题—— 每年的在校人数

问题描述

 year表示学生入学年度，num表示对应年度录取学生人数，stu_len表示录取学生的学制；说明：例如录取年度2018学制是3年，表示该批学生在校年份为2018~2019、2019~2020、2020-2021，在算每年的在校人数时，2018/2019/2020/2021年份都需要算上。

示例数据 

id    year    num    stu_len
1    2018    2000        3
2    2019    2000        3
3    2020    1000        4
3    2020    2000        3

参考实现

with temp as (select 2018 as year, 3 as stu_len, 2000 as num
              union all
              select 2019 as year, 3 as stu_len, 2000 as num
              union all
              select 2020 as year, 4 as stu_len, 1000 as num
              union all
              select 2020 as year, 3 as stu_len, 2000 as num)
select t.year,sum(t1.num) num
from (select min_year + pos as year
      from (select min(year) min_year, max(year + stu_len) max_year
            from temp) t
               lateral view posexplode(split(repeat(',', max_year - min_year), ',')) tbl as pos, val) t
         join temp t1 on t.year between t1.year and t1.year + t1.stu_len
group by t.year
order by 1;