用户行为路径分析：桑基图背后的 SQL 数据准备与清洗

可视化很漂亮，但更让人头秃的是可视化背后的那堆 SQL。这篇文章带你看看一张桑基图背后，数据需要经过怎样的"淬炼"才能变成一条条流畅的行为路径。

一、业务场景：用户到底是怎么逛的？

运营同学跑来问我："大喜，能不能帮我看看用户从首页进来之后，都去了哪些页面？最后在哪一步流失了？"

简单来说，就是要做用户行为路径分析，用桑基图（Sankey Diagram）来直观展示用户在各页面之间的流转情况。看起来只画一张图，但数据的准备和清洗可能占了 80% 的工作量。

我们有一个埋点日志表user_event_log，结构大致如下：

二、数据清洗：埋点数据有多脏？

埋点数据的质量，一言难尽。同一个用户同一秒能上报 3 次相同的点击事件；有些事件的时间戳比服务器收到的时间还晚（客户端时间校准问题）；还有"幽灵事件"——页面已经退出了还在上报。清洗是绕不开的。

为什么不直接按user_id, session_id, event_time排序去重，而要按秒级别去重？客户端网络重试机制会在丢包后自动补报——同一个点击事件因为 TCP 超时重传被报了 2-3 次，但每次的event_time可能差 200-500ms（补报时间等于原始时间 + 重试延迟），如果按毫秒级event_time去重，这三个"重复"事件会被当成不同事件保留。按秒去重（DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s')）把窗口放宽到 1 秒，保证同一秒内同一用户对同一页面的重复事件被合并——代价是可能误杀 1 秒内真实发生两次相同操作的情况（概率极低，用户不太可能在 1 秒内点两次同一个按钮），收益是去重准确率从 60% 提升到 95%+。

-- 第一步：去重 —— 同一用户同一秒内对同一页面的重复事件只保留最早的一条 WITH deduplicated_events AS ( SELECT user_id, event_type, page_id, event_time, session_id, -- 窗口函数：按事件分组，同一秒内的重复取第一条 ROW_NUMBER() OVER ( PARTITION BY user_id, event_type, page_id, DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') ORDER BY event_time ASC ) AS rn FROM user_event_log WHERE dt BETWEEN '2026-06-20' AND '2026-07-21' -- 分析窗口：一个月 ) SELECT * FROM deduplicated_events WHERE rn = 1;

接下来要识别"幽灵事件"——用户已经退出了，但客户端还在"补报"。通过判断事件之间的时间间隔来识别。

-- 第二步：识别异常时间间隔 —— 单次会话超过 30 分钟无操作视为新会话 WITH event_with_lag AS ( SELECT *, LAG(event_time, 1) OVER ( PARTITION BY user_id, session_id ORDER BY event_time ) AS prev_event_time FROM deduplicated_clean -- 上一步的去重数据 ), session_segmented AS ( SELECT *, -- 间隔超过 30 分钟，标记为新会话段的起点 CASE WHEN prev_event_time IS NULL THEN 1 WHEN TIMESTAMPDIFF(MINUTE, prev_event_time, event_time) > 30 THEN 1 ELSE 0 END AS is_new_segment FROM event_with_lag ) SELECT user_id, session_id, -- 累积求和生成会话段编号 SUM(is_new_segment) OVER ( PARTITION BY user_id, session_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS session_segment, event_time, page_id, event_type FROM session_segmented;

三、行为路径构建：把"点"串成"线"

清洗完数据，接下来要把每个用户在一次会话中的行为序列拼接成路径。比如：首页 → 搜索页 → 商品详情 → 购物车 → 下单页 → 支付成功，这就是一条完整的转化路径。

-- 第三步：构建行为路径 —— 用 LEAD 窗口函数串联相邻页面 WITH behavior_path AS ( SELECT user_id, session_id, session_segment, page_id AS from_page, -- LEAD 获取用户下一步去了哪个页面 LEAD(page_id, 1) OVER ( PARTITION BY user_id, session_id, session_segment ORDER BY event_time ) AS to_page, event_time, -- 计算在当前页面的停留时长（秒） TIMESTAMPDIFF(SECOND, event_time, LEAD(event_time, 1) OVER ( PARTITION BY user_id, session_id, session_segment ORDER BY event_time ) ) AS dwell_seconds FROM cleaned_events -- 上一步清洗后的数据 WHERE event_type = 'page_view' -- 只分析页面浏览事件 ) SELECT * FROM behavior_path WHERE to_page IS NOT NULL -- 过滤掉最后一步（没有"下一步"的事件） AND dwell_seconds > 0; -- 过滤停留时长为负值或零的异常数据

这里有个关键细节：停留时长的计算。很多教程直接用两个事件的时间差，但别忘了用户可能在后台挂机。我们设定了 30 分钟的阈值，超过这个时长的停留不纳入分析。

为什么停留时长超过 30 分钟的数据必须剔除？停留时长的计算公式是LEAD(event_time) - event_time——这个公式假设用户从页面 A 跳转到页面 B 之间一直在 A 上。但如果用户在 A 页面把浏览器切到后台看视频看了 20 分钟再切回来，这 20 分钟的"停留"其实是看视频不是看你的页面。不剔除的话，你的数据里会有一堆 1800 秒、3600 秒的"超级停留"——这些极端值拉高了均值、扭曲了分位点分析、让"平均停留 45 秒"变成"平均停留 3 分钟"，运营看到这份数据做出的动作会是错的。30 分钟阈值并非放之四海而皆准——资讯类产品可能设 10 分钟，视频类/游戏类可能需要 60 分钟——核心是结合你的产品形态来确定"一个用户连续操作的最大合理间隔"。

四、桑基图数据聚合

桑基图需要的数据格式是(source, target, value)三元组，表示从页面 A 到页面 B 的流转人数（或人次）。到了汇总这一步，SQL 反而比较简单了：

-- 第四步：聚合流转关系 —— 生成桑基图的 source-target-value 数据 SELECT from_page AS source, to_page AS target, COUNT(DISTINCT user_id) AS user_count, -- 流转用户数 COUNT(1) AS flow_count, -- 流转总次数 -- 筛选条件：只保留用户数 >= 100 的主要路径 ROUND(COUNT(DISTINCT user_id) * 100.0 / SUM(COUNT(DISTINCT user_id)) OVER(), 2 ) AS pct -- 路径占比 FROM behavior_path GROUP BY from_page, to_page HAVING COUNT(DISTINCT user_id) >= 100 -- 过滤长尾路径，避免桑基图过于杂乱 ORDER BY user_count DESC;

到这里，数据已经可以直接喂给 ECharts 或 D3.js 的桑基图组件了。关键链路的流转数据一清二楚：

从这个数据就能看出，"商品详情→购物车"是最大的流失漏斗，超过一半的用户看了详情页但没有加购，这是运营需要重点优化的环节。

-- 第五步：流失分析 —— 哪些页面是"断头路"？ WITH last_pages AS ( SELECT page_id AS exit_page, COUNT(DISTINCT user_id) AS exit_users FROM cleaned_events WHERE (user_id, session_id, session_segment, event_time) IN ( -- 找出每个会话段的最后一个浏览页面 SELECT user_id, session_id, session_segment, MAX(event_time) FROM cleaned_events WHERE event_type = 'page_view' GROUP BY user_id, session_id, session_segment ) GROUP BY page_id ) SELECT exit_page, exit_users, ROUND(exit_users * 100.0 / SUM(exit_users) OVER(), 2) AS exit_rate FROM last_pages ORDER BY exit_users DESC LIMIT 10;

🚨 踩坑提醒

1. LAG和LEAD窗口函数在数据量超过 1000 万行时，PARTITION BY user_id, session_id会导致 Shuffle 量爆炸：每个user_id + session_id组合的数据都要落在同一个 Executor 上才能计算LAG/LEAD，大促期间 1000 万用户 × 2 个 session = 2000 万个分区键，Hash Shuffle 的数据传输量可能达到几百 GB。先过滤分析时间窗口（如只分析 7 天数据），再限制 Top N 活跃用户（如日活 > 5 次的高频用户），最后对长尾用户做随机采样——不是所有用户都需要纳入路径分析。

2. 桑基图的HAVING COUNT(DISTINCT user_id) >= 100会隐藏长尾但重要的转化路径：某个新上线的小程序入口虽然只有 50 个用户从那里进来，但其中 40 个完成了全链路转化（转化率 80%）——这条路径因为用户数不到 100 被过滤掉了，你错失了一个发现最优转化路径的机会。过滤条件是"绝对用户数"和"转化率"的并集——用户数 > 100 或转化率 > 60% 且用户数 > 20 的路径都保留，这样不会漏掉小而重要的发现。

3. 多端行为数据（APP + 小程序 + H5）在做路径分析时，session_id可能跨端不一致：同一个用户在 APP 和小程序之间跳转时，如果两端的session_id生成逻辑不同（一个用 UUID、一个用自增 ID），用户从 APP 的商品详情页跳到小程序的支付页，在你的数据里被切成了两条独立的路径[商品详情]和[支付]，之间丢失了"跳转"这个关键节点。必须用业务 UID 做跨端关联——在生成路径前，用user_id + timestamp做跨端数据的时序合并，把 APP 事件和小程序事件按时间排序后合成一条完整路径。

一张桑基图从无到有，SQL 写了大半天。事后复盘，最费时的不是写聚合查询，而是数据清洗——去重、会话切分、异常识别这三步消耗了约 60% 的时间。这也提醒我们：数据分析项目中，前期数据治理的投入永远值得。好的数据质量才能支撑起好的分析结论，否则再漂亮的桑基图也只是"garbage in, garbage out"。另外，桑基图虽然好看，但路径超过 15 个节点后就很难阅读了，实际生产环境中建议截取 Top 10 路径展示。

五、总结

本文介绍的方案在实际项目中需要经过充分验证后再全量推广。建议先在灰度环境中观察关键指标的变化，确认无异常后再逐步放量。技术在不断演进，保持学习和实践的心态，才能在架构设计上走得更远。如果在实际落地过程中遇到问题，欢迎在评论区交流讨论。