用户行为路径分析桑基图背后的 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 的桑基图组件了。关键链路的流转数据一清二楚路径用户数占比首页→搜索125,43023.4%搜索→商品详情98,20018.3%商品详情→购物车45,6008.5%购物车→下单28,3005.3%下单→支付成功22,1004.1%从这个数据就能看出商品详情→购物车是最大的流失漏斗超过一半的用户看了详情页但没有加购这是运营需要重点优化的环节。-- 第五步流失分析 —— 哪些页面是断头路 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; 踩坑提醒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 次的高频用户最后对长尾用户做随机采样——不是所有用户都需要纳入路径分析。桑基图的HAVING COUNT(DISTINCT user_id) 100会隐藏长尾但重要的转化路径某个新上线的小程序入口虽然只有 50 个用户从那里进来但其中 40 个完成了全链路转化转化率 80%——这条路径因为用户数不到 100 被过滤掉了你错失了一个发现最优转化路径的机会。过滤条件是绝对用户数和转化率的并集——用户数 100 或转化率 60% 且用户数 20 的路径都保留这样不会漏掉小而重要的发现。多端行为数据APP 小程序 H5在做路径分析时session_id可能跨端不一致同一个用户在 APP 和小程序之间跳转时如果两端的session_id生成逻辑不同一个用 UUID、一个用自增 ID用户从 APP 的商品详情页跳到小程序的支付页在你的数据里被切成了两条独立的路径[商品详情]和[支付]之间丢失了跳转这个关键节点。必须用业务 UID 做跨端关联——在生成路径前用user_id timestamp做跨端数据的时序合并把 APP 事件和小程序事件按时间排序后合成一条完整路径。一张桑基图从无到有SQL 写了大半天。事后复盘最费时的不是写聚合查询而是数据清洗——去重、会话切分、异常识别这三步消耗了约 60% 的时间。这也提醒我们数据分析项目中前期数据治理的投入永远值得。好的数据质量才能支撑起好的分析结论否则再漂亮的桑基图也只是garbage in, garbage out。另外桑基图虽然好看但路径超过 15 个节点后就很难阅读了实际生产环境中建议截取 Top 10 路径展示。五、总结本文介绍的方案在实际项目中需要经过充分验证后再全量推广。建议先在灰度环境中观察关键指标的变化确认无异常后再逐步放量。技术在不断演进保持学习和实践的心态才能在架构设计上走得更远。如果在实际落地过程中遇到问题欢迎在评论区交流讨论。
用户行为路径分析：桑基图背后的 SQL 数据准备与清洗
用户行为路径分析桑基图背后的 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 的桑基图组件了。关键链路的流转数据一清二楚路径用户数占比首页→搜索125,43023.4%搜索→商品详情98,20018.3%商品详情→购物车45,6008.5%购物车→下单28,3005.3%下单→支付成功22,1004.1%从这个数据就能看出商品详情→购物车是最大的流失漏斗超过一半的用户看了详情页但没有加购这是运营需要重点优化的环节。-- 第五步流失分析 —— 哪些页面是断头路 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; 踩坑提醒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 次的高频用户最后对长尾用户做随机采样——不是所有用户都需要纳入路径分析。桑基图的HAVING COUNT(DISTINCT user_id) 100会隐藏长尾但重要的转化路径某个新上线的小程序入口虽然只有 50 个用户从那里进来但其中 40 个完成了全链路转化转化率 80%——这条路径因为用户数不到 100 被过滤掉了你错失了一个发现最优转化路径的机会。过滤条件是绝对用户数和转化率的并集——用户数 100 或转化率 60% 且用户数 20 的路径都保留这样不会漏掉小而重要的发现。多端行为数据APP 小程序 H5在做路径分析时session_id可能跨端不一致同一个用户在 APP 和小程序之间跳转时如果两端的session_id生成逻辑不同一个用 UUID、一个用自增 ID用户从 APP 的商品详情页跳到小程序的支付页在你的数据里被切成了两条独立的路径[商品详情]和[支付]之间丢失了跳转这个关键节点。必须用业务 UID 做跨端关联——在生成路径前用user_id timestamp做跨端数据的时序合并把 APP 事件和小程序事件按时间排序后合成一条完整路径。一张桑基图从无到有SQL 写了大半天。事后复盘最费时的不是写聚合查询而是数据清洗——去重、会话切分、异常识别这三步消耗了约 60% 的时间。这也提醒我们数据分析项目中前期数据治理的投入永远值得。好的数据质量才能支撑起好的分析结论否则再漂亮的桑基图也只是garbage in, garbage out。另外桑基图虽然好看但路径超过 15 个节点后就很难阅读了实际生产环境中建议截取 Top 10 路径展示。五、总结本文介绍的方案在实际项目中需要经过充分验证后再全量推广。建议先在灰度环境中观察关键指标的变化确认无异常后再逐步放量。技术在不断演进保持学习和实践的心态才能在架构设计上走得更远。如果在实际落地过程中遇到问题欢迎在评论区交流讨论。
