roi窗口计算必须按促销周期分区聚合，避免明细行重复计算和全局均摊；需统一分子分母口径、正确映射订单到周期、显式定义窗口帧，并用nullif防除零。
ROI转化率的窗口函数计算逻辑必须绑定时间分区
直接用 AVG() 或 SUM() 聚合整个表会抹平促销周期边界，导致 ROI（(收入 - 成本) / 成本）被错误均摊。必须用 PARTITION BY promotion_id 或 PARTITION BY DATE_TRUNC('week', order_time) 显式切分周期——否则窗口函数算出来的不是“该周期内”的转化率，而是全局漂移值。
常见错误现象：SELECT *, (SUM(revenue) OVER (PARTITION BY promotion_id) - SUM(cost) OVER (PARTITION BY promotion_id)) / NULLIF(SUM(cost) OVER (PARTITION BY promotion_id), 0) AS roi 看似合理，但若单条记录是订单明细（非汇总行），就会重复计算同一笔成本多次，ROI 失真。
- 正确做法：先按
promotion_id和关键粒度（如用户、订单）聚合出每周期的总revenue和总cost，再在此结果集上用窗口函数做跨周期比较（如环比）、或直接计算单周期 ROI - 若需保留明细行并打上周期 ROI 标签，必须确保
revenue和cost是该周期的聚合值，而非当前行原始值；可用子查询或 CTE 预聚合 - 注意
NULLIF(..., 0)必须包裹分母，避免除零错误；PostgreSQL/BigQuery 支持，MySQL 需改用CASE WHEN cost = 0 THEN NULL ELSE ... END
跨促销周期对比 ROI 要用 RANGE 或 ROWS 显式定义窗口帧
只写 AVG(roi) OVER (PARTITION BY campaign_type ORDER BY start_date) 默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，它会把所有历史周期都卷进来，无法表达“近 3 个促销期平均 ROI”这种业务需求。
使用场景：运营想看某类促销（如满减、折扣券）最近 N 期的 ROI 趋势稳定性，而不是从第一期累加到当前期。
- 要限定最近 3 期：用
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW（注意是 2 PRECEDING，因为包含当前行共 3 行） - 若周期长度不固定（如有的促销 5 天、有的 12 天），优先用
RANGE BETWEEN INTERVAL '14 days' PRECEDING AND CURRENT ROW（BigQuery/PostgreSQL 支持），但需确保ORDER BY字段是日期类型且无重复值，否则 RANGE 可能吞掉多行 - MySQL 8.0+ 不支持
INTERVAL在窗口帧中，只能退回到按序号ROW_NUMBER()标记后自连接或用 LAG/LEAD 拉取前 N 行
促销周期起止时间不一致时，不能直接用事件时间做 ORDER BY
错误写法：ORDER BY order_time —— 用户下单时间 ≠ 促销生效时间。一个促销周期内可能有用户提前下单（预售）、或延迟履约（发货滞后），导致窗口排序错位，ROI 计算被污染。
真实数据场景：电商大促「618」主活动期为 6.1–6.18，但部分优惠券 5.20 就开始发放，用户 5.25 下单、6.10 发货。这笔订单应归属 618 周期，但按 order_time 排序会被挤到 5 月窗口里。
- 必须引入明确的周期标识字段，例如
promo_period_id（业务侧维护的枚举）或effective_date_range（DATERANGE类型） - 若只有起止时间字段（如
promo_start,promo_end），需在 JOIN 或 WHERE 中先将订单映射到对应周期：ON o.order_time BETWEEN p.promo_start AND p.promo_end，再以p.promo_start为ORDER BY键 - 警惕时区问题：
promo_start是 UTC 还是本地时间？数据库TIMESTAMP列是否带时区？不一致会导致跨天订单归期错误
ROI 分子分母口径不匹配是静默错误高发区
最隐蔽的问题：窗口函数跑不出报错，但数值完全不可信。比如用 SUM(revenue) 作为分子，却用 COUNT(DISTINCT user_id) 当分母算“人均 ROI”，逻辑断裂；或成本用了含税价，收入用了净额，ROI 虚高。
性能影响：在大宽表上对高基数字段（如 user_id）频繁做 COUNT(DISTINCT) 窗口计算，Spark SQL 和 Hive 会触发严重 shuffle，BigQuery 可能超内存。
- 务必统一 ROI 定义：公司级口径应明确是“订单维度 ROI”“用户维度 ROI”还是“促销费用 ROI”；不同口径必须用不同字段参与计算，不能混用
- 避免在窗口函数中嵌套
COUNT(DISTINCT)；如需去重统计，先用GROUP BY promotion_id, user_id聚合用户级指标，再在外层窗口中聚合 - 测试时用极小数据集（如 2 个促销期、各 3 条订单）手算验证：取出原始 revenue/cost，人工加总、相除，和 SQL 输出逐行比对
实际落地时，90% 的 ROI 偏差来自周期映射错误或分子分母颗粒度不一致，而不是窗口函数语法本身。先花半小时厘清“这笔钱属于哪个周期”“这个 ROI 是算给谁看的”，再写 SQL。
