SQL窗口函数计算用户购买路径转化率

先说一个关键点：窗口函数写对路径，核心就一句话——ROW_NUMBER()或RANK()必须配PARTITION BY user_id，ORDER BY created_at必须精确到秒，且最好加二级排序（比如event_type）。然后用FIRST_VALUE和LAST_VALUE给首尾行为打标，注意LAST_VALUE必须显式指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，否则结果可能全是NULL。这个底层逻辑搞清楚了，后面才能聊转化率。

窗口函数怎么写才能正确排序购买路径

转化率计算的前提，是路径顺序必须严格按时间对齐。很多人纠结选ROW_NUMBER()还是RANK()，其实关键不在函数本身，而在ORDER BY里是否包含了精确到秒的时间字段（比如created_at），同时所有用户行为必须落在同一个分区。

• 别直接用event_time排序——如果它只有日期没有时分秒，同一天内的多次行为会随机排序，路径直接乱掉。
• 用户级路径必须用PARTITION BY user_id，漏掉就是全量混排，转化率完全失真。
• 同一毫秒内多个事件怎么办？加二级排序，比如ORDER BY created_at, event_type，把'click'排在'cart_add'前面。

如何用窗口函数标记每个用户的首尾行为

转化率本质是“从第一步走到最后一步的人数占比”，所以先得识别每个用户的路径起点和终点。不用MIN()/MAX()聚合再关联，那样中间步骤会丢失；得靠窗口函数直接打标。

• 用FIRST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY created_at)标出首行为（通常是'view'）。
• 用LAST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)标出末行为。注意ROWS子句必须加，否则默认只看到当前行及之前，结果就是当前行本身。
• 如果某个用户只有'view'没有'pay'，那他的末行为就是'view'——这类人天然计入分母但不进分子。

转化率分母为什么不能直接 count(user_id)

分母不是总用户数，而是完成路径起点的用户数。假设你定义的路径是view → cart_add → pay，那么只有触发过view的人才算入分母——没浏览就下单的属于异常流量，硬算进去会拉低转化率。

• 先过滤出所有起点行为：WHERE event_type = 'view'，再对user_id去重计数，这才是真实分母。
• 分子是同时满足起点和终点的用户：需要用EXISTS或JOIN关联该用户后续是否有pay行为，不能只查末行为等于'pay'（因为可能view → pay → refund，末行为是'refund'）。
• 窗口函数自己不管分子分母的聚合逻辑，它只帮我们把每行归属组织好，最终还得套一层GROUP BY或子查询。

MySQL 8.0 和 PostgreSQL 的兼容性坑

LAST_VALUE()在MySQL 8.0的默认行为和PostgreSQL不一致：MySQL需要显式写ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，而PostgreSQL默认就是全窗口。如果在MySQL里漏写，结果只返回当前行，看起来像全是NULL。

• PostgreSQL的FRAME clause更灵活，但MySQL 8.0.2+才完整支持，低于这个版本会报错。
• SQL Server的LAG()/LEAD()对路径断点检测更方便（比如查用户是否跳过了cart_add），但MySQL不支持IGNORE NULLS，遇到空值容易中断链路。
• 别依赖WINDOW命名复用——MySQL 8.0支持，PostgreSQL也支持，但某些BI工具解析器不认，建议直接展开写。

话说回来，真正卡住人的往往不是函数语法，而是路径定义本身。你认定的“标准路径”是否覆盖了真实用户行为？比如“微信小程序直接支付”绕过了view，这种流量要不要剔除？先跟业务对齐口径，再写SQL。