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