在日常的資料分析中，經常會遇到一類問題：計算從某個時間點開始的累計資料，但在某些時間點又是沒有資料的，需得到的結果是每個時間點的累計資料，
比如以下情況，左邊是原始資料，右邊是期望輸出資料：
因為大部分資料庫不像其他編程語言，沒有函式式編程，不能直接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
思路拆解：
-
先通過連續時間列，構造出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數說
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/502770.html
標籤：大數據
上一篇：從Multirepo到Monorepo 袋鼠云數堆疊前端研發效率提升探索之路
下一篇：SPL：跑批有這么難么？