如何在SQL中使用窗口函数计算广告投放效果的加权归因模型？-SQL-PHP中文网

窗口函数不能直接用row_number()或rank()实现时间衰减权重，必须用exp()或power()基于时间差构造衰减系数（如exp(-(t_convert-t_click)/86400)），再通过sum(decay_weight) over(partition by convert_id)计算单次转化的归因总分，注意避免溢出、去重及数据库语法差异。

如何在sql中使用窗口函数计算广告投放效果的加权归因模型？

窗口函数怎么给点击事件打时间衰减权重

直接用 ROW_NUMBER() 或 RANK() 无法表达“越靠近转化的点击越重要”这个业务逻辑。得靠 EXP() 或 POWER() 配合时间差做衰减，再用窗口函数按用户+会话聚合。关键不是排序，而是构造一个随时间递减的权重系数。

常见错误是把时间差算成绝对秒数——数值太大导致 EXP(-t) 溢出为 0。建议统一转成小时或天，并缩放（比如除以 24）。示例：

SELECT
  user_id,
  click_time,
  convert_time,
  EXP(-(UNIX_TIMESTAMP(convert_time) - UNIX_TIMESTAMP(click_time)) / (24 * 3600.0)) AS decay_weight
FROM ad_clicks
WHERE convert_time IS NOT NULL

注意：MySQL 8.0+、PostgreSQL、BigQuery 支持 EXP()；SQLite 不支持，得用 POWER(0.99, t) 类近似替代。

如何用 `SUM() OVER()` 算单次转化的归因总分

加权归因不是只看单个点击，而是把同一转化路径下所有点击的 decay_weight 加起来，作为该转化的“总归因分”，再按权重比例分摊效果。这里必须用 SUM(decay_weight) OVER(PARTITION BY convert_id)，而不是 GROUP BY —— 否则会丢失原始点击粒度。

典型陷阱：漏写 PARTITION BY 或错写成 PARTITION BY user_id。归因单位是转化事件（convert_id），不是用户。如果一个用户一天有 3 次下单，就得分 3 组计算。

ORDER BY click_time DESC 不需要加——归因权重只依赖时间差，不依赖点击顺序
若存在重复点击（如刷新），建议先去重：DISTINCT ON (user_id, click_time, convert_id)（PostgreSQL）或 ROW_NUMBER() OVER(PARTITION BY user_id, click_time, convert_id ORDER BY id) = 1

为什么 `LAG()` 和 `LEAD()` 在归因链里容易误用

有人想用 LAG(click_time) 算前一次点击间隔来建“点击序列权重”，这在多触点归因中风险很高：实际用户行为不是线性链条，可能跨设备、跨会话、甚至跳过中间环节。窗口函数按物理顺序排，但归因要按因果关系排。

coding-agent

一款AI开发辅助工具，主要用于通过后台进程将编码任务委托给 Codex、Claude Code 或 Pi 智能体。适用场景：（1）构建或创建新功能/应用，（2）审查 PR，适合需要提升相关任务效率的用户。

下载

更稳妥的做法是：先用 convert_id 关联所有前置点击，再统一计算各自到转化的时间差。不要假设点击 A → B → C 是严格时序链，尤其当数据源含 App + Web + 微信小程序时。

如果真要建路径，用字符串拼接比 LAG/LEAD 更可控：

STRING_AGG(utm_source, ' > ' ORDER BY click_time) 
  OVER(PARTITION BY convert_id)

PostgreSQL vs BigQuery 的 `UNBOUNDED PRECEDING` 兼容性差异

写 SUM(weight) OVER(PARTITION BY convert_id ORDER BY click_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 时，BigQuery 默认支持；PostgreSQL 要求显式指定 ROWS 或 RANGE，否则报错“frame clause required”。而 MySQL 8.0 对 UNBOUNDED PRECEDING 支持良好，但不支持 RANGE 时间帧（只能用 ROWS）。

实际建议：统一用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，避免依赖数据库特有语法。如果要做“最近 7 天点击归因”，就别用窗口帧，改用子查询过滤时间范围后再聚合——更清晰，也更容易调试。

归因模型真正难的不是 SQL 写法，而是如何定义“有效点击窗口”和“衰减底数”。这些参数没法从 SQL 里自动推导，得靠 AB 实验验证。写完窗口函数后，务必拿几条真实转化路径手工验算权重是否符合业务直觉。