一、业务背景

在经营分析场景里，我们经常需要回答：

• “截至今天，过去 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 提供毫秒级查询。