SQL进阶技巧：每年在校人数统计？

会飞的一十六 2024-09-18

210

—

场景描述

有一个录取学生人数表 in_school_stu，记录的是每年录取学生的人数及录取学生的学制，计算每年在校学生人数。

—

数据准备

create table in_school_stu as
(
select stack(
    5,
  1,2001,2,1200,
  2,2000,5,1300,
  3,2003,4,1400,
  4,2002,3,1500,
  5,2002,2,1600
) as(id,year,stu_num,stu_len)
);

—

问题分析

本题实际上属于区间重叠问题，我们将入年份与学制加起来得到结束时间

select id
           , year               year_start
           , year + stu_len - 1 year_end
           , stu_num
      from in_school_stu

通过上面简单的分析可以看到该问题为典型的区间重叠问题，将时间展开后，具体分析可参考如下图。

求解上面的时序图，可以按照如下步骤：

第一步：利用posexplode()先将数据展开，转换为明细表

select id
     , pos
     , year       year_start
     , year + pos year_end
     , stu_num
from in_school_stu stu
         lateral view posexplode(split(space(stu_len - 1), '(?!&)')) tmp as pos, val

第二步：基于步骤1的明细表，按时间汇总即可。SQL如下：

select year + pos   year
     , sum(stu_num) stu_num
from in_school_stu stu
         lateral view posexplode(split(space(stu_len - 1), '(?!&)')) tmp as pos, val
group by year + pos;

第三步：用上述结果关联数据表中入学年份得到最终结果。注意数据表中要去重。

select t1.year year
     , stu_num
from (select year + pos   year
           , sum(stu_num) stu_num
      from in_school_stu stu
               lateral view posexplode(split(space(stu_len - 1), '(?!&)')) tmp as pos, val
      group by year + pos) t1
join
(
select year  from in_school_stu group by year
)t2
on t1.year = t2.year
;

—

小结

本文对在校学生人数统计这一问题进行了分析，如果对本专栏区间重叠问题这一技巧和方法比较熟的，解决该问题会非常简单。

区间重叠问题

SQL进阶技巧：如何计算重叠区间合并问题？

SQL进阶技巧：重叠区间问题分析之品牌打折日期问题？

SQL进阶技巧：如何按任意时段分析时间区间问题？| 分区间讨论【左、中、右】

sql

文章转载自会飞的一十六，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL进阶技巧：每年在校人数统计 ？

评论

SQL进阶技巧：每年在校人数统计？