SQL如何计算各环节的转化率_LAG函数前置步骤比对-SQL-PHP中文网

lag函数不能直接用于计算转化率，必须先按漏斗阶段和时间维度聚合各环节完成量，再通过cte或子查询对齐为宽表，最后用列间除法（如click_cnt*1.0/nullif(pv_cnt,0)）计算比率，避免跨用户、跨时段误比及整数除法截断。

sql如何计算各环节的转化率_lag函数前置步骤比对

LAG函数不能直接算转化率，必须先聚合出各环节的完成数

转化率是“本环节完成量 / 上一环节进入量”，不是原始行为流水的简单行间差。直接对用户点击日志用 LAG() 取上一行，拉出来的可能是不同用户的动作，毫无业务意义。

实操建议：

先按漏斗阶段（如 step = 'pv'、'click'、'submit'）和时间维度（如 dt）分组聚合，得到每阶段每日的去重人数或事件数
再用 CTE 或子查询把各阶段结果对齐到同一行（例如用 MAX(CASE WHEN step = 'pv' THEN cnt END) AS pv_cnt）
最后在该宽表上对各环节字段用 LAG() —— 但更常见、更安全的做法是直接列间除法：click_cnt * 1.0 / NULLIF(pv_cnt, 0)

为什么非要用LAG对比“上一环节”而不是直接列除

只有当漏斗是严格线性、且每个用户只走一次路径时，“上一环节进入量”才等于“本环节前序环节的完成量”。但现实中存在回退、重复提交、跨天行为等，这时需要真正取“上一环节在相同时间窗口下的完成值”，而非静态列。

比如计算“当日注册用户中，次日登录率”，就得用：

SELECT
  t1.dt AS reg_date,
  t1.reg_cnt,
  t2.login_cnt AS next_day_login,
  t2.login_cnt * 1.0 / NULLIF(t1.reg_cnt, 0) AS login_rate
FROM (SELECT dt, COUNT(DISTINCT uid) AS reg_cnt FROM events WHERE event = 'register' GROUP BY dt) t1
LEFT JOIN (SELECT dt, COUNT(DISTINCT uid) AS login_cnt FROM events WHERE event = 'login' GROUP BY dt) t2
  ON t2.dt = DATE_ADD(t1.dt, INTERVAL 1 DAY)

这里没用 LAG()，因为“上一环节”是跨表、跨时间的逻辑，不是窗口内相邻行。

真要用LAG做环节比对，PARTITION BY 和 ORDER BY 必须严格按漏斗顺序定义

若你已把各环节压成单表单行（如 stage = 1→pv, 2→click, 3→pay），且保证每个 uid 在每个 dt 最多出现一次，则可考虑用 LAG() 拉前序环节值。但前提是：

PARTITION BY uid, dt：确保只在同用户同日内比较，避免跨用户污染
ORDER BY stage：漏斗阶段必须是数值型有序字段（不能是字符串 'click'），否则排序错乱
必须用 LAG(cnt, 1) OVER (PARTITION BY uid, dt ORDER BY stage)，不能省略 offset 参数——默认为 1，但显式写出更防误读

否则会出现：A 用户的 pay 行拉到 B 用户的 click 行，或者 'confirm' 排在 'submit' 前面导致倒置。

NULLIF 和乘 1.0 是硬性要求，不是可选项

漏斗首环节（如 pv）没有“上一环节”，LAG() 返回 NULL；而整数列相除（如 click_cnt / pv_cnt）在 PostgreSQL/SQL Server 中会截断为 0，在 MySQL 中可能静默转为 0 或 NULL。这两种都掩盖真实问题。

正确写法只有一条路径：

分母一律套 NULLIF(…, 0)，防止除零中断或返回 Infinity
分子显式乘 1.0 或用 CAST(… AS DECIMAL(5,4))，确保结果为小数
不依赖 COALESCE(LAG(), 0) 填充首环节——那会让转化率变成 click_cnt / 0 = NULL → COALESCE(NULL, 0) = 0，即“未进入环节却有 0% 转化”，这是误导

真正该空着的地方，就让它空着。转化率分析里，第一环节无上环节、新上线环节无历史数据、某日无流量——这些 NULL 都是有效信号，不是缺陷。