网站导航
> 技术文档 > 向前滚动累加SQL 实现思路

向前滚动累加SQL 实现思路

网友: 技术文档 2025-07-26 19:41:47

一、业务背景

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

  • “截至今天，过去 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

SELECT a.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_5dFROM fact_daily_loan aLEFT JOIN fact_daily_loan b ON ( 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 BY a.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_start FROM fact_order_detail),-- 2. 为每个月生成 6 个月窗口windowed AS ( SELECT m.month_start, generate_series( m.month_start - INTERVAL \'5 month\', m.month_start, INTERVAL \'1 month\' )::date AS window_month FROM months m),-- 3. 预聚合：按月去重订单base AS ( SELECT dealer_name, dealer_id, date_trunc(\'month\', confirm_dt) AS month_start, order_no FROM fact_order_detail),-- 4. 把窗口拼到 base 上agg AS ( SELECT w.month_start AS report_month, b.dealer_name, b.dealer_id, COUNT(DISTINCT b.order_no) AS cnt_6m FROM windowed w JOIN base b ON b.month_start = w.window_month GROUP BY w.month_start, b.dealer_name, b.dealer_id)-- 5. 最终输出SELECT cnt_6m, dealer_name, dealer_id, to_char(report_month, \'YYYY-MM-DD\') AS report_month_strFROM aggORDER BY dealer_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 提供毫秒级查询。

网络标签：

相关问题