浅谈SQL中的循环
在日常的数据分析中，经常会遇到一类问题：计算从某个时间点开始的累计数据，但在某些时间点又是没有数据的，需得到的结果是每个时间点的累计数据。比如以下情况，左边是原始数据，右边是期望输出数据：
https://mmbiz.qpic.cn/mmbiz_png/qxkPHOklnS6qraWEUdIV5OXh6VVN5WGicQ8SGawbETP1bcicNWu0MkOWp78TYZicibjCUAkoqg3upqtNO4SC4w8UMQ/640?wx_fmt=png
因为大部分数据库不像其他编程语言，没有函数式编程，不能直接for循环。如果支持游标循环，则比较容易实现。
下面介绍游标循环的思路以及在不支持游标的情况下该如何处理。
方法1：游标循环（Cursor For Loops）
具体逻辑如下：
[*]按一定顺序遍历时间date；
[*]where条件的时间范围为[本月第一天，date]
[*]以date分组，这就把在这个时间范围内的数据聚合起来了
FOR date IN list_of_dates
LOOP
INSERTINTO final_table(date, revenue_mtd)
SELECT @dateasdate, sum(revenue) as revenue_mtd
FROM sales
WHERE sales.dt between date_trunc('month',@date) and @date;
ENDLOOP;所以支持游标循环的数据库是比较方便做一些函数式编程的。
方法2：构造辅助列
inner join
/* FABRICATE SOME EXAMPLES */
WITH fake_sales AS (
select'2020-12-01'::date dt, 100.00 revenue unionall
select'2020-12-02'::date dt, 200.00 revenue unionall
select'2020-12-08'::date dt, 300.00 revenue unionall
select'2020-12-09'::date dt, 400.00 revenue unionall
select'2020-12-10'::date dt, 500.00 revenue
)
, fake_dates AS (
SELECT'2020-12-01'::date + SEQ4() dt
FROMTABLE (GENERATOR(ROWCOUNT => 31)) v
)
,
/* THE ACTUAL CODE */
monthly_mtd_window AS (
SELECT dt pivot_date, date_trunc(MONTH, dt) dt_from, dt dt_to
FROM fake_dates
WHERE dt < '2020-12-13'::date
)
SELECT pivot_date asdate, sum(sales.revenue) as revenue_mtd
FROM fake_sales
INNERJOIN monthly_mtd_window
ON sales.dt BETWEEN dt_from and dt_to
groupby pivot_date思路拆解：
https://mmbiz.qpic.cn/mmbiz_png/qxkPHOklnS6qraWEUdIV5OXh6VVN5WGic9U2h5w5Kwy4hrffbFFEfBWulDCWf0hPfDWUrdoibBpmuiaGyLSUykmDA/640?wx_fmt=png
[*]先通过连续时间列，构造出monthly_mtd_window--pivot_date(统计日)，dt_from日期起点（月初第一天），dt_to日期终点（统计日）。也就是，统计日对应的当月的时间范围；
[*]通过inner join 把每个统计日所在的时间区间内所对应的原始数据找出来，比如说统计统计12-04的月累计数据，在12-01~12-04范围内的原始数据有2条；
[*]根据统计日，聚合步骤2中找出的原始数据。
方法3：开窗函数
这应该是最佳解决方案了。
构造表：以连续日期为主表关联原始数据，不连续的日期位置上的统计量为空。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，该窗口代表首行到当前行，这样就能实现在一个范围内聚合。
/* FABRICATE SOME EXAMPLES */
WITH fake_sales AS (
select'2020-12-01'::date dt, 100.00 revenue unionall
select'2020-12-02'::date dt, 200.00 revenue unionall
select'2020-12-08'::date dt, 300.00 revenue unionall
select'2020-12-09'::date dt, 400.00 revenue unionall
select'2020-12-10'::date dt, 500.00 revenue
)
, fake_dates AS (
SELECT'2020-12-01'::date + SEQ4() dt
FROMTABLE (GENERATOR(ROWCOUNT => 31)) v
)
SELECT fake_dates.dt asdate,
sum(sales.revenue) over (orderby fake_dates.dt
ROWSBETWEENUNBOUNDEDPRECEDING
ANDCURRENTROW) as revenue_mtd
FROM fake_dates
LEFTJOIN fake_sales sales
ON sales.dt = fake_dates.dt比如，还可以join on辅助日期表，日期不等条件也可以。
还有啥方法，欢迎各位补充~
欢迎关注个人公众号：DS数说
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]