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

2023-12-29 17:30:11

问题:

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

文章来源:https://blog.csdn.net/weixin_43597208/article/details/135293681
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。