淺談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數說