sql：
用到函数 space() ：生产空格 split 切分空格
posexplode 炸裂函数 返回下标和具体指
with data as (
-- 主键 入学年份 人数 学制
select 1 as id , 2001 as yy , 1200 as stu_num , '3' as stu_len union all
select 2 as id , 2000 as yy , 1300 as stu_num , '5' as stu_len union all
select 3 as id , 2003 as yy , 1400 as stu_num , '4' as stu_len union all
select 4 as id , 2002 as yy , 1500 as stu_num , '3' as stu_len union all
select 5 as id , 2002 as yy , 1600 as stu_num , '4' as stu_len union all
select 6 as id , 2014 as yy , 1600 as stu_num , '4' as stu_len union all
select 7 as id , 2015 as yy , 1600 as stu_num , '4' as stu_len union all
select 8 as id , 2015 as yy , 1600 as stu_num , '4' as stu_len union all
select 9 as id , 2015 as yy , 1600 as stu_num , '4' as stu_len union all
select 10 as id , 2015 as yy , 1600 as stu_num , '4' as stu_len union all
select 11 as id , 2016 as yy , 1600 as stu_num , '4' as stu_len union all
select 12 as id , 2016 as yy , 1600 as stu_num , '4' as stu_len union all
select 13 as id , 2016 as yy , 1600 as stu_num , '4' as stu_len union all
select 14 as id , 2016 as yy , 1600 as stu_num , '4' as stu_len union all
select 15 as id , 2016 as yy , 1600 as stu_num , '4' as stu_len
)
SELECT
endyy,
SUM(stu_num)
FROM
(
SELECT
id,
(yy + pos) AS endyy,
stu_num,
stu_len,
val
FROM
data LATERAL view posexplode(split(space(CAST(stu_len AS INT) -1),' ')) t AS pos,
val
) t
GROUP BY
endyy结果：
