SQL进阶技巧：如何使数组中的固定参数动态化？ | SQL中的滑动窗口如何实现？
目录
如果觉得本文对你有帮助，想进一步学习SQL语言这门艺术的，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：
专栏 原价99，现在活动价59.9，按照阶梯式增长，直到恢复原价。
0 场景描述
在表中有一堆学生记录。
school stduent_id start_date end_date
222 123 2019-02-03 2023-02-03
222 345 2015-02-03 2019-02-03
222 567 2021-02-03 2024-02-03
（1）我想计算每所学校每年有多少学生，比如从 2017 年一直到 2024 年。
（2） 我希望在年份移动时自动更新，例如，现在应该是 2017-2024，明年应该是 2018-2025。
1 数据准备
create table student as
(
select stack(
3,
222 , 123, '2019-02-03', '2023-02-03',
222 , 345, '2015-02-03', '2019-02-03',
222 , 567, '2021-02-03', '2024-02-03'
) as(school,student,start_date,end_date)
);2 实现思路
问题1分析
第一步：利用lateral view explode展成明细数据
SELECT *
FROM student
LATERAL VIEW EXPLODE(ARRAY(2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024)) a AS yrs
;2221232019-02-032023-02-032017
2221232019-02-032023-02-032018
2221232019-02-032023-02-032019
2221232019-02-032023-02-032020
2221232019-02-032023-02-032021
2221232019-02-032023-02-032022
2221232019-02-032023-02-032023
2221232019-02-032023-02-032024
2223452015-02-032019-02-032017
2223452015-02-032019-02-032018
2223452015-02-032019-02-032019
2223452015-02-032019-02-032020
2223452015-02-032019-02-032021
2223452015-02-032019-02-032022
2223452015-02-032019-02-032023
2223452015-02-032019-02-032024
2225672021-02-032024-02-032017
2225672021-02-032024-02-032018
2225672021-02-032024-02-032019
2225672021-02-032024-02-032020
2225672021-02-032024-02-032021
2225672021-02-032024-02-032022
2225672021-02-032024-02-032023
2225672021-02-032024-02-032024
（2） 按学校、年份分组，获取每年的在校生人数
SELECT school
, yrs
, COUNT(DISTINCT student) AS cnt
FROM student
LATERAL VIEW EXPLODE(ARRAY(2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024)) a AS yrs
WHERE 1 = 1
AND YEAR(start_date) <= yrs
AND YEAR(end_date) >= yrs
group by school, yrs
问题2：如何动态获取年份，年份能够自动更新？
注意问题1的求解方式中，数组中采用固定的常数值，这种属于硬编码，实际需求当中，不可能只是固定的年份，而是随着年份的增长，年份的时间也在变动，对于这种变动的时间应该如何求解或者我应该随时间推移，依然能够保持代码的鲁棒性，而不用手动修改？
我们可以借助于posexplode函数生成序列，利用当前时间函数current_date进行范围的移动。
步骤1：利用posexplode函数生成移动指针范围（序列）
select posexplode(split(space(7),' ')) as (i,x)
第二步：利用当前时间进行时间上移动
7年前截止到当前时间
公式：year(current_date) - 7 + i
具体SQL如下：
select year(current_date) - 7 + i as yr
from (select posexplode(split(space(7), ' ')) as (i, x)) s
最终完整的SQL如下：
SELECT school
, year(current_date) - 7 + i years
, COUNT(DISTINCT student) AS cnt
FROM student
LATERAL VIEW POSEXPLODE(SPLIT(SPACE(7), space(1))) tmp AS i, x
WHERE 1 = 1
AND YEAR (start_date) <= year (current_date) - 7 + i
AND YEAR (end_date) >= year (current_date) - 7 + i
group by school, year (current_date) - 7 + i
3 小结
本文依据实际场景中的案例，分析了SQL中如何使数组中的固定参数动态化的解决方案。根据本文中的案例可进一步抽象为特定条件下的滑动窗口的实现，如本文按照条件生成窗口范围为7，移动步长为1，注意此处需要与窗口函数中order by后的滑动窗口的区别。
如果觉得本文对你有帮助，想进一步学习SQL语言这门艺术的，那么不妨也可以选择去看看我的博客专栏 ，部分内容如下：
数字化建设通关指南
专栏 原价99，现在活动价59.9，按照阶梯式增长，直到恢复原价。
专栏主要内容：
（1）SQL进阶实战技巧
可以参考如下教程，具体链接如下
上面链接中的文章及技巧会不定期更新。
（2）数仓建模实战技巧和个人心得
1）新人入职新公司后应如何快速了解业务？
2）以业务视角看宽表化建设？
3) 维度建模 or 关系型建模？
4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？
5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系 该由谁来搭建？
6）如何优雅设计DWS层？DWS层模型好坏该如何评价？
7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？
8） 数据架构的选择，mpp or hadoop?
9）数仓团队应如何体现自己的业务价值，讲好数据故事？
10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关 系？
11）数据部门如何与业务部门沟通，并规划指引业务需求？
文章不限于以上内容，有新的想法也会及时更新到该专栏。
数字化建设通关指南_莫叫石榴姐的博客-CSDN博客
原文地址：https://blog.csdn.net/godlovedaniel/article/details/143004364
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！