问题描述
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;标签:len,stu,num,2020,Hive,year,在校,select,刷题 From: https://www.cnblogs.com/wdh01/p/18097007