统计每年在校人数

统计每年在校人数

问题:
有一张招生人数信息表, year_str:招生年度; num:招生人数; year_ins:招生年制,几年制学习
从这张表中统计每年在校人数分别是多少
说明: 2020年 招生2000人,年制是3年, 该批学生在校时间为, 2020~2023年, 统计每年在校人数时, 2020、2021、2022、2023 每年都要统计上

1- 数据准备

招生信息表:
id:数据id; year_str:招生年度; num:招生人数; year_ins:招生年制,几年制学习

WITH t_enroll_info AS (
SELECT * FROM (
  VALUES 
      (1001, 2019, 1000, 3)
    , (1002, 2020, 2000, 4)
    , (1003, 2020, 1000, 3)
    , (1004, 2021, 1500, 3)
    , (1005, 2021, 2100, 4)
    , (1006, 2022, 2000, 4)
) AS table_name(id, year_str, num, year_ins)
)

id	year_str	num	year_ins
1001	2019	1000	3
1002	2020	2000	4
1003	2020	1000	3
1004	2021	1500	3
1005	2021	2100	4
1006	2022	2000	4

2- 代码实现

-- 1. 计算出每批招生在校的所有年份 year_part
SELECT 
      id, year_str, num, year_ins
    , lv.pos
    , year_str + lv.pos AS year_part
FROM t_enroll_info t1 
LATERAL VIEW POSEXPLODE(split(repeat('#,',year_ins + 1),',')) lv AS pos,val
;

id	year_str	num	year_ins	pos	year_part
1001	2019	1000	3	0	2019
1001	2019	1000	3	1	2020
1001	2019	1000	3	2	2021
1001	2019	1000	3	3	2022
1002	2020	2000	4	0	2020
1002	2020	2000	4	1	2021
1002	2020	2000	4	2	2022
1002	2020	2000	4	3	2023
1002	2020	2000	4	4	2024
1003	2020	1000	3	0	2020
1003	2020	1000	3	1	2021
…	…	…	…	…	…

-- 2. 计算出每年在校的总人数
SELECT 
      year_part AS yaers 
    , SUM(num) AS stu_num
FROM (
SELECT 
      id, year_str, num, year_ins
    , lv.pos
    , year_str + lv.pos AS year_part
FROM t_enroll_info t1 
LATERAL VIEW POSEXPLODE(split(repeat('#,',year_ins + 1),',')) lv AS pos,val
) a 
GROUP BY year_part
;

yaers	stu_num
2019	1000
2020	4000
2021	7600
2022	9600
2023	8600
2024	7600
2025	4100
2026	2000

3- 总结

posexplode()函数: 该函数可以将index和数据都取出来。
可以使用 index 作为值和招生年份相加, 得出在校年份, 因为index是从0 开始的, 所以需要将年制 加1 。

SELECT tab.pos, val
lateral view posexplode(split(repeat('1,',4),',')) tab as pos,val ;

pos	val
0	1
1	1
2	1
3	1

end