浅谈SQL中的循环

在日常的数据分析中，经常会遇到一类问题：计算从某个时间点开始的累计数据，但在某些时间点又是没有数据的，需得到的结果是每个时间点的累计数据。

比如以下情况，左边是原始数据，右边是期望输出数据：

因为大部分数据库不像其他编程语言，没有函数式编程，不能直接for循环。如果支持游标循环，则比较容易实现。

下面介绍游标循环的思路以及在不支持游标的情况下该如何处理。

方法1：游标循环（Cursor For Loops）

具体逻辑如下：

1. 按一定顺序遍历时间date；

2. where条件的时间范围为[本月第一天，date]

3. 以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
 

思路拆解：

1. 先通过连续时间列，构造出monthly_mtd_window--pivot_date(统计日)，dt_from日期起点（月初第一天），dt_to日期终点（统计日）。也就是，统计日对应的当月的时间范围；

2. 通过inner join 把每个统计日所在的时间区间内所对应的原始数据找出来，比如说统计统计12-04的月累计数据，在12-01~12-04范围内的原始数据有2条；

3. 根据统计日，聚合步骤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数说