SQL面试题挑战14：每年的在校人数

问题：

year表示学生入学年度，num表示对应年度录取学生人数，stu_len表示录取学生的学制；说明：例如录取年度2018学制是3年，表示该批学生在校年份为2018_2019、20192020、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

根据以上示例计算出每年的在校人数

SQL解答：

由于需要计算每年的在校人数，所以先要造出连续的年份。然后与源表进行关联，关联条件保证年份在入学年份和结束年份之间即可。

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
t1.year
,sum(t2.num) as stu_num
from
(
select
    t1.min_year+tab.pos as year
    from
    (
        select
        min(year) as min_year
        ,max(year+stu_len) as max_year
        from temp
    )t1
    lateral view posexplode(split(repeat(',',max_year-min_year),',')) tab as pos,val
)t1
inner join temp t2
on t1.year between t2.year AND t2.year + t2.stu_len
group by t1.year