一、業務背景

在經營分析場景里，我們經常需要回答：

• “截至今天，過去 N 天/月/周累計發生了多少？”

• “把維度切到省、市、房型、項目經理、代理商等，結果又是什么？”

本文用兩個真實需求做演示：

1. 以天為粒度，計算過去 7 天 的放款單量、放款金額；并順便給出過去 5 天 的口徑作為對比。

2. 以月為粒度，計算過去 6 個月（含當月）的累計訂單數，維度為代理商。

為了便于閱讀，下文所有表名、字段名都做了脫敏混淆，但邏輯 100 % 保留。

---

二、需求 1：過去 7 天放款（天粒度，多維度）

2.1 期望輸出

data_date	省	市	…（其它維度）	loan_cnt	loan_amt	ys_fst_apply_cnt_7sum	delivery_go_capacity_sum	…

其中：

• ys_fst_apply_cnt_7sum = 過去 7 天（不含當日）的同維度放款單量之和

• delivery_go_capacity_sum = 過去 7 天（不含當日）的同維度放款金額之和

2.2 實現思路

采用 自關聯（self-join）：

• 主表 A：取當日維度切片；

• 從表 B：在時間窗口 [A.data_date-7, A.data_date-1] 且維度完全一致的數據；

• 用 SUM() 聚合即可完成累加。

2.3 關鍵 SQL

SELECTa.data_date,a.dim_province,a.dim_city,a.dim_town,a.dim_roof1,a.dim_roof2,a.dim_pm,a.dim_dealer,a.dim_dealer_team,a.dim_settle,a.cnt_當天單量,a.amt_當天金額,/* 過去 7 天（不含當日）累計 */SUM(COALESCE(b.cnt_當天單量,0)) AS cnt_7d,SUM(COALESCE(b.amt_當天金額,0)) AS amt_7d,/* 過去 5 天（不含當日）累計，僅作演示 */SUM(CASE WHEN a.data_date - b.data_date <= 5 THEN COALESCE(b.cnt_當天單量,0) ELSE 0 END) AS cnt_5d,SUM(CASE WHEN a.data_date - b.data_date <= 5 THEN COALESCE(b.amt_當天金額,0) ELSE 0 END) AS amt_5d
FROM fact_daily_loan a
LEFT JOIN fact_daily_loan bON (COALESCE(a.dim_province,' ')   = COALESCE(b.dim_province,' ')AND COALESCE(a.dim_city,' ')       = COALESCE(b.dim_city,' ')AND COALESCE(a.dim_town,' ')       = COALESCE(b.dim_town,' ')AND COALESCE(a.dim_roof1,' ')      = COALESCE(b.dim_roof1,' ')AND COALESCE(a.dim_roof2,' ')      = COALESCE(b.dim_roof2,' ')AND COALESCE(a.dim_pm,' ')         = COALESCE(b.dim_pm,' ')AND COALESCE(a.dim_dealer,-1)      = COALESCE(b.dim_dealer,-1)AND COALESCE(a.dim_dealer_team,-1) = COALESCE(b.dim_dealer_team,-1)AND COALESCE(a.dim_settle,' ')     = COALESCE(b.dim_settle,' ')AND a.data_date - b.data_date BETWEEN 1 AND 7   -- 關鍵：滑窗 7 天
)
GROUP BYa.data_date,a.dim_province,a.dim_city,a.dim_town,a.dim_roof1,a.dim_roof2,a.dim_pm,a.dim_dealer,a.dim_dealer_team,a.dim_settle,a.cnt_當天單量,a.amt_當天金額;

2.4 注意點

1. 維度對齊：所有維度都要 COALESCE 以防 NULL 匹配不上。

2. 時間區間：a.data_date - b.data_date BETWEEN 1 AND 7 等價于“前 7 天不含當日”。

3. 性能：如果數據量大，建議把日期過濾下推、或在從表加索引 (date, 維度組合)。

---

三、需求 2：過去 6 月訂單（月粒度，僅代理商維度）

3.1 期望輸出

report_month	dealer_name	dealer_id	cnt_6m

3.2 實現思路

采用 生成月份序列 + 預聚合 的經典寫法：

1. 先把事實表出現的所有月份抽出來（去重）。

2. 為每個月生成一個 6 個月窗口（含自己 + 前 5 個月）。

3. 預先把訂單按月去重，得到 (dealer, month, order_no) 的干凈集合。

4. 用窗口月把“干凈集合”掛上去，再 COUNT(DISTINCT order_no) 即可。

3.3 關鍵 SQL

-- 1. 提取事實表所有月份
WITH months AS (SELECT DISTINCT date_trunc('month', confirm_dt) AS month_startFROM fact_order_detail
),-- 2. 為每個月生成 6 個月窗口
windowed AS (SELECTm.month_start,generate_series(m.month_start - INTERVAL '5 month',m.month_start,INTERVAL '1 month')::date AS window_monthFROM months m
),-- 3. 預聚合：按月去重訂單
base AS (SELECTdealer_name,dealer_id,date_trunc('month', confirm_dt) AS month_start,order_noFROM fact_order_detail
),-- 4. 把窗口拼到 base 上
agg AS (SELECTw.month_start      AS report_month,b.dealer_name,b.dealer_id,COUNT(DISTINCT b.order_no) AS cnt_6mFROM windowed wJOIN base bON b.month_start = w.window_monthGROUP BYw.month_start,b.dealer_name,b.dealer_id
)-- 5. 最終輸出
SELECTcnt_6m,dealer_name,dealer_id,to_char(report_month, 'YYYY-MM-DD') AS report_month_str
FROM agg
ORDER BYdealer_id,report_month;

3.4 注意點

• 用 generate_series 生成月份序列，天然避開了閏月、大小月問題。

• 預先把訂單按月去重，避免后面 COUNT DISTINCT 時掃大表。

• 如果窗口更大（如 12 個月），可把 5 改成 11 即可。

---

四、兩種方案對比與選型建議

維度	7 天放款（自關聯）	6 月訂單（生成序列）
粒度	天	月
窗口	7 天	6 個月
維度	多（省/市/房型…）	少（僅代理商）
數據量	百萬/千萬級	千萬級
主要算子	Self-Join + SUM	generate_series + JOIN + COUNT DISTINCT
性能敏感點	維度組合基數高導致笛卡爾放大	月份序列膨脹有限，可接受
適用場景	任意維度、短周期滾動累加	維度單一、長周期滾動累加

一句話總結：

• 短周期 + 高維度 → 自關聯 + 時間過濾；

• 長周期 + 低維度 → 預聚合 + 生成序列。

---

五、擴展思考

1. Presto/Trino 可用 RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND 1 DAY PRECEDING 的窗口函數，邏輯更簡潔，但引擎需要支持。

2. ClickHouse 可用 GROUP BY (date, dim...) WITH ROLLUP + runningAccumulate 實現實時累加。

3. 實時場景 可以把窗口結果寫到 Redis / Druid，再通過 API 提供毫秒級查詢。