浅谈SQL中的循环

在日常的数据分析中，经常会遇到一类问题：计算从某个时间点开始的累计数据，但在某些时间点又是没有数据的，需得到的结果是每个时间点的累计数据。
比如以下情况，左边是原始数据，右边是期望输出数据：

因为大部分数据库不像其他编程语言，没有函数式编程，不能直接for循环。如果支持游标循环，则比较容易实现。
下面介绍游标循环的思路以及在不支持游标的情况下该如何处理。
方法1：游标循环（Cursor For Loops）

具体逻辑如下：

• 按一定顺序遍历时间date；
  
• where条件的时间范围为[本月第一天，date]
  
• 以date分组，这就把在这个时间范围内的数据聚合起来了
  

1. FOR date IN list_of_dates
2. LOOP
3.      INSERTINTO final_table(date, revenue_mtd)
4.      SELECT @dateasdate, sum(revenue) as revenue_mtd
5.      FROM sales
6.      WHERE sales.dt between date_trunc('month',@date) and @date;
7. ENDLOOP;

复制代码

所以支持游标循环的数据库是比较方便做一些函数式编程的。
方法2：构造辅助列

inner join

1. /* FABRICATE SOME EXAMPLES */
2. WITH fake_sales              AS (
3.     select'2020-12-01'::date dt, 100.00 revenue unionall
4.     select'2020-12-02'::date dt, 200.00 revenue unionall
5.     select'2020-12-08'::date dt, 300.00 revenue unionall
6.     select'2020-12-09'::date dt, 400.00 revenue unionall
7.     select'2020-12-10'::date dt, 500.00 revenue
8. )
9.    , fake_dates              AS (
10.     SELECT'2020-12-01'::date + SEQ4() dt
11.     FROMTABLE (GENERATOR(ROWCOUNT => 31)) v
12. )
13.    ,
14. /* THE ACTUAL CODE */
15. monthly_mtd_window AS (
16.     SELECT dt pivot_date, date_trunc(MONTH, dt) dt_from, dt dt_to
17.     FROM fake_dates
18.     WHERE dt < '2020-12-13'::date
19. )
20. SELECT pivot_date asdate, sum(sales.revenue) as revenue_mtd
21. FROM fake_sales
22. INNERJOIN monthly_mtd_window
23.     ON sales.dt BETWEEN dt_from and dt_to
24. groupby pivot_date

复制代码

思路拆解：

• 先通过连续时间列，构造出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 ，该窗口代表首行到当前行，这样就能实现在一个范围内聚合。

1. /* FABRICATE SOME EXAMPLES */
2. WITH fake_sales              AS (
3.     select'2020-12-01'::date dt, 100.00 revenue unionall
4.     select'2020-12-02'::date dt, 200.00 revenue unionall
5.     select'2020-12-08'::date dt, 300.00 revenue unionall
6.     select'2020-12-09'::date dt, 400.00 revenue unionall
7.     select'2020-12-10'::date dt, 500.00 revenue
8. )
9.    , fake_dates              AS (
10.     SELECT'2020-12-01'::date + SEQ4() dt
11.     FROMTABLE (GENERATOR(ROWCOUNT => 31)) v
12. )
14. SELECT fake_dates.dt asdate,
15.     sum(sales.revenue) over (orderby fake_dates.dt
16.                              ROWSBETWEENUNBOUNDEDPRECEDING
17.                                       ANDCURRENTROW)   as revenue_mtd
18. FROM fake_dates
19. LEFTJOIN fake_sales sales
20.     ON sales.dt = fake_dates.dt

复制代码

比如，还可以join on辅助日期表，日期不等条件也可以。
还有啥方法，欢迎各位补充~
欢迎关注个人公众号：DS数说

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！