做用户行为分析这件事，很多团队一开始并不缺数据，缺的是把数据变成结论的SQL思路。我见过不止一个业务同学，手里握着一张几千万行的埋点日志表，最常问的却是：用户来了以后到底做了什么？到哪一步就不往下走了？第二天还回不回来？这些问题听起来不大，真要落到SQL上，第一步就会卡住——表结构对不对、统计口径清不清楚、能不能直接跑。下面就从一张最普通的用户行为事件表讲起，把PV/UV、会话切分、漏斗、留存和路径分析这五类高频取数需求，用可复制的SQL模板拆开讲，最后再分享几个我在真实数据环境里踩过的坑。内容不挑行业，做App、做网站、做小程序运营的同学都能参考，关键是你要有一张带事件名的用户行为表。

1. 先搞懂数据源：用户行为原始表到底该记录什么

1.1 一张能用的用户行为事件表至少要有的字段

我拿到一张行为表，一般先看五个字段：user_id、event_name、event_time、session_id、page_url。user_id决定后续的UV、留存都以谁为主体；event_name告诉你是“点击”还是“加购”；event_time是精确到秒的时间戳，最好入库前统一时区，否则后面按天统计会错位；session_id用来区分一次访问会话；page_url在路径分析和访问深度里有用。多数埋点平台还会带item_id、channel、device_type等属性，这些字段可以留着，后面做分群时经常要用。

这里提醒一句，不同团队对事件名的定义差别非常大。一个叫app_click的事件，可能是按钮点击，也可能是整个App的首页曝光；同一个“支付成功”，有的端上报payment_success，有的端上报pay_ok。所以拿到表先花半天时间确认事件字典，把这些别名梳理成统一的event_name，后面所有分析才站得住脚。如果连事件字典都没有，我也建议先做一次探查SQL，看看每个event_name对应的page_url和参数长什么样，再开始建模型。

1.2 原始埋点日志不适合直接跑分析，先做聚合再做分析

为什么不能拿原始日志直接分析？第一个原因是数据量太大。一次促销活动可能产生上亿条事件，每条事件都带几十个参数，直接SELECT会扫全表，跑一次报表能把数仓拖得够呛。第二个原因是事件里混着大量无关数据，包括前后端重复上报、测试流量、爬虫请求，直接统计会严重污染指标。第三个原因是业务分析通常按天、按周看趋势，不需要每次重算明细，一些中间结果完全可以提前算好。

我一般的做法是先建一张轻量的汇总表，把常见指标按user_id和dt提前跑出来。日常90%的问题都在汇总表上查，速度能快很多。下面这段SQL就是典型例子，按动作次数做了初步聚合：

CREATE TABLE dws_user_action_daily AS SELECT user_id, to_date(event_time) AS dt, count(1) AS all_event_cnt, count(CASE WHEN event_name = 'page_view' THEN 1 END) AS pv, count(DISTINCT session_id) AS session_cnt, count(CASE WHEN event_name = 'add_cart' THEN 1 END) AS add_cart_cnt, count(CASE WHEN event_name = 'order_submit' THEN 1 END) AS order_cnt FROM dwd_user_action_log WHERE dt >= '2024-01-01' GROUP BY user_id, to_date(event_time);

注意，这张表不存event_name明细，只存聚合好的计数，因此只适合回答“某个用户某天产生了哪些行为多少次数”这类问题。你要是想统计两个事件之间隔了多久、要走哪些页面路径，还是得回到明细表，但中间层可以大幅减少日常分析扫描的数据量。

1.3 用户维度和事件字典：分析前的两张辅助表

除了行为明细表本身，我还会建两张辅助表，一张是用户维度表，一张是事件字典表。用户维度表至少包含user_id、user_type、register_time、channel、first_dt；事件字典表包含event_name、event_name_standard、event_category、page_type。分析时经常要按用户所属渠道、新老类型或注册时间做过滤和分群，如果没有这两张表，后面每次都去行为表里用SQL推断first_dt，逻辑重复且慢。

事件字典的意义在于把杂乱的上报名称归一成标准事件名，再用唯一编码关联。这样算漏斗或留存时，只需要在汇总SQL里JOIN字典表过滤标准事件，不用每个报表复制一份CASE WHEN的映射关系。虽然是一张很小的表，但能把分析SQL维护成本降一个档次。建中间表时还要把user_id统一为字符串，避免int和string类型在JOIN时匹配不到，这个问题在跨系统取数时特别容易出现。

2. 三步算清PV/UV、会话数和访问深度

2.1 页面浏览PV和独立访客UV的SQL口径

PV就是Page View，一次页面浏览计一次，统计时一般只取event_name = 'page_view'的事件，而不是所有事件。UV是独立访客，指在统计周期内至少发生一次页面浏览的去重用户数。很多人直接把COUNT(DISTINCT user_id)套在整个表上，结果把那些只点了按钮、没打开页面的用户也算了进去，口径就不对了。正确写法是先过滤页面浏览事件，再基于过滤后的数据算PV和UV：

SELECT to_date(event_time) AS dt, count(1) AS pv, count(DISTINCT user_id) AS uv FROM dwd_user_action_log WHERE event_name = 'page_view' AND dt = '2024-01-01' GROUP BY to_date(event_time);

如果产品有App、H5、小程序多个端，建议把platform作为分组维度，分端统计，不然汇总值会被重复计算或掩盖某个端的波动。这里还有一个容易忽略的细节：同一个用户在App上浏览10次，算UV时只能算1次，但在PV里算10次，所以两个指标放在同一张表里没问题，但导出到报表时一定要写清楚单位，避免业务方把它们相加。

2.2 用窗口函数切分会话：30分钟阈值不再是难题

会话切分是用户行为分析的基础，很多进阶指标都建立在会话之上。如果埋点端已经生成了可靠的session_id，直接用即可；但很多项目里session_id并不存在，或者是用用户最后一次操作时间计算的，这时就要自己根据行为时间切分。常见规则是：同一个用户相邻两条行为的时间间隔超过30分钟，算一个新会话。用窗口函数比自连接简单得多，也更不容易出错：

WITH events AS ( SELECT user_id, event_name, event_time, lag(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time FROM dwd_user_action_log WHERE dt >= '2024-01-01' ), sessions AS ( SELECT user_id, event_time, sum( CASE WHEN prev_time IS NULL OR unix_timestamp(event_time) - unix_timestamp(prev_time) > 1800 THEN 1 ELSE 0 END ) OVER (PARTITION BY user_id ORDER BY event_time) AS session_seq FROM events ) SELECT user_id, count(DISTINCT session_seq) AS session_cnt FROM sessions GROUP BY user_id;

这段SQL的核心是LAG拿到上一条行为时间，再用累计SUM标记“新会话起点”。第一条行为prev_time为空，视为新会话，后来间隔超过30分钟也视为新会话，session_seq就是每个用户的会话编号。这个写法只依赖user_id和event_time，不需要额外埋点支持，适用于大量历史日志回溯。你要是用其他数据库，时间差函数名称可能不同，比如有些用datediff('second', prev_time, event_time)，换成等价的函数即可。

2.3 访问深度：一个会话里到底浏览了多少页

有了会话编号，访问深度就简单了：一个会话内统计page_view事件的数量。如果有可靠的session_id，直接按用户和会话分组：

SELECT user_id, session_id, count(*) AS page_depth FROM dwd_user_action_log WHERE event_name = 'page_view' GROUP BY user_id, session_id ORDER BY page_depth DESC;

如果session_id不准确，可以先按2.2节的办法生成会话编号，再按用户和会话编号统计。这里还有一个口径问题要提前定清楚：访问深度到底看的是“浏览页面的次数”还是“浏览过多少个不同页面”？如果用户来回刷新详情页，前者会显得很深，后者更接近真实内容消费量。我的习惯是两种都算：count(*)叫“页面浏览深度”，count(DISTINCT page_url)叫“去重页面深度”，放到报表里分别命名，业务方要哪种就取哪种。

3. 漏斗分析写对了，才知道用户在哪一步流失

3.1 先定漏斗事件和观察窗口，再写SQL

漏斗分析不能一上来就写SQL，必须先定两件事：漏斗由哪些事件组成，以及允许用户多长时间内走完。电商场景下常见漏斗是 App启动/曝光 -> 商品详情 -> 加购 -> 提交订单 -> 支付成功。如果把“曝光”定义成所有页面浏览，会把很多不相关流量塞进来；如果把事件顺序限制得太严格，又可能漏掉一些先加购再回来看详情的用户。我建议产品目标明确时，按业务定义；没有明确事件字典时，先用一组大家都认可的关键事件做一期分析，之后再调整。

观察窗口同样关键。如果一个用户上周看过详情，今天突然下单，他算不算当前漏斗的一环？如果漏斗统计的是自然日内完成，窗口就设为当天；如果统计的是拉新活动带来的用户，窗口通常是活动开始后7天。窗口设得越短，漏斗越能反映单次访问的转化效率；窗口设得越长，越能反映用户整个周期的决策链路。写SQL前先和业务确认好窗口，不然同一个代码可能今天跑出3%转化，明天跑出8%转化，问题不在代码，在定义。

3.2 按用户去重的漏斗SQL模板

我常用的一种漏斗SQL，是把每个用户在每个步骤上有没有发生过目标事件，先转成0/1字段，再做汇总。这样行数从事件数降到用户数，后续加任何维度都不会因为多步骤而膨胀。以下模板按“启动、详情、加购、下单、支付”五步展示：

WITH funnel_users AS ( SELECT user_id, MAX(CASE WHEN event_name = 'app_launch' THEN 1 ELSE 0 END) AS step1, MAX(CASE WHEN event_name = 'detail_view' THEN 1 ELSE 0 END) AS step2, MAX(CASE WHEN event_name = 'add_cart' THEN 1 ELSE 0 END) AS step3, MAX(CASE WHEN event_name = 'order_submit' THEN 1 ELSE 0 END) AS step4, MAX(CASE WHEN event_name = 'pay_success' THEN 1 ELSE 0 END) AS step5 FROM dwd_user_action_log WHERE event_time >= '2024-01-01' AND event_time < '2024-01-08' GROUP BY user_id ) SELECT sum(step1) AS step1_users, sum(step2) AS step2_users, sum(step3) AS step3_users, sum(step4) AS step4_users, sum(step5) AS step5_users FROM funnel_users;

注意，这里统计的是“在观察窗口内，该用户是否发生过这些动作中的每一个”，不要求步骤按时间顺序排列。这样做的优点是简单、快，适合先看整体转化率；缺点是当一个用户先加购后看详情时，系统仍会把这个人算进详情步骤，和真实路径不完全一致。如果要严格按顺序来，需要把每步的最早发生时间提取出来再做比较，复杂度会上升，但更符合业务漏斗定义。首次分析先用这个宽口径摸一遍，再细化也不迟。

3.3 多表JOIN做漏斗的经典误区

我刚做漏斗时踩过一个坑：把每层事件分别算一张表，再用user_id去JOIN。如果同一个用户在详情、加购、下单各产生了多次事件，多张表JOIN之后会出现行数膨胀。比如一个用户加了5次购、下了3次单，两张表JOIN后就产生15行，人数被重复放大，转化率自然算错。正确的思路和上面一样：先在子查询里把每个用户的0/1标记或最早事件时间算出来，再做JOIN或直接汇总。

还有一种更隐蔽的问题，是漏斗人数直接按事件数COUNT而不按用户去重。某一步的按钮被同一个用户反复点击，事件数看起来很多，但真正进入这一步的人可能只有几个。所以每一步的分子分母都应该是COUNT(DISTINCT user_id)，或者像上面的写法一样，先用MAX把用户在该步骤是否发生压成一个标记，再SUM。这样出的数字才经得起核对。

4. 留存率计算的完整SQL方案：次日、7日、30日

4.1 留存定义：不是“活跃天数”，而是“第N天是否回来”

留存率这名字听着简单，但口径经常打架。我见过有人把用户七天里活跃过就算留存，这是“周活跃用户”，不是“次日留存”。更准确的留存定义是：把某一天首次进入产品的用户定义为新用户，观察这批人在第N天是否又发生了关键行为（通常是有任意行为或当天有页面浏览）。分母是当天新增用户数，分子是这批人中第N天活跃的人数。比如4月1日新增100人，4月2日有30个人回来，次日留存就是30%。

这个定义的关键在于，不同人的首次活跃日不同，所以计算时要按每个用户的first_dt去对齐，而不是所有用户统一按某个自然日算。如果只统计“某天活跃且次日活跃”，那会把老用户和新用户混在一起，得出的是整体用户次日活跃率，不是留存率。业务看板里的留存，默认都应该按“群组+第N天”来算。

4.2 从首日到第N日的留存SQL

所以要先把每个用户最早的行为日期算出来，再关联后续每天的活跃明细，计算与首日的日期差。我用的是LEFT JOIN，因为要保留没有任何后续活跃的用户，这些人留存分子为0，如果直接用内连接会被丢掉，分母就错了：

WITH first_actions AS ( SELECT user_id, MIN(to_date(event_time)) AS first_dt FROM dwd_user_action_log GROUP BY user_id ), daily_actives AS ( SELECT DISTINCT user_id, to_date(event_time) AS dt FROM dwd_user_action_log ) SELECT f.first_dt, COUNT(DISTINCT f.user_id) AS new_users, COUNT(DISTINCT CASE WHEN a.dt = date_add(f.first_dt, 1) THEN f.user_id END) AS day1_users, COUNT(DISTINCT CASE WHEN a.dt = date_add(f.first_dt, 7) THEN f.user_id END) AS day7_users, COUNT(DISTINCT CASE WHEN a.dt = date_add(f.first_dt, 30) THEN f.user_id END) AS day30_users FROM first_actions f LEFT JOIN daily_actives a ON f.user_id = a.user_id AND a.dt BETWEEN f.first_dt AND date_add(f.first_dt, 30) GROUP BY f.first_dt ORDER BY f.first_dt;

跑完之后，用day1_users * 1.0 / new_users算次日留存率，7日和30日同理。如果保留两位小数，就再套一个round。这里有个细节：daily_actives先做了DISTINCT user_id, dt，所以后面的COUNT(DISTINCT ... CASE ...)不会因同一天多次活跃而重复计数，但依然建议保留DISTINCT，因为有些表里有同一个用户同一天多条日志。

4.3 新老用户分组留存的扩展写法

如果想看新用户和老用户的留存差异，先要定义“新老”。通常把first_dt = 计算日期的用户叫新用户，之前有行为的用户叫老用户。上面的first_actions已经能区分了，因为first_dt就是用户首次活跃日期。在计算某一天的次日留存时，可以在用户维度上打标签，再透视成宽表。

对于活动场景，还有一种常见做法：只关注某个活动开始之后首次活跃的用户，看他们后续N天的回访情况。这时候只需要在first_actions里加一个WHERE first_dt >= '2024-06-01'就能筛选。如果台账里有用户注册时间，用它做首日划分会更贴近业务上的“新增用户”，注册后但一段时间没来的沉默用户，不会因为某天偶然打开就算成新用户。这个口径差异直接影响留存率数值，上线报表前最好和业务方确认清楚。

5. 路径分析也能用SQL做：排序、相邻事件与Top路径

5.1 用窗口函数给每个用户的行为排序

路径分析通常有专门的可视化工具，但数据量不大或者只是临时验证时，SQL完全能顶半边天。核心思想是：以用户（或用户+会话）为分组，按时间从小到大给行为事件排序，然后用LAG或LEAD拿到相邻行为，进而统计流转关系。先看排序这一步：

SELECT user_id, event_name, event_time, row_number() OVER ( PARTITION BY user_id, session_id ORDER BY event_time ) AS seq FROM dwd_user_action_log;

这段SQL输出的每一行都带一个seq，同一个会话里第一次行为是1，第二次是2，以此类推。如果没有session_id，就先按2.2节的方法生成会话编号，再拿会话编号做分区。需要注意，如果事件表按天分区，而一个用户的行为跨了午夜，按天分区去排序会把一个连续会话拆成两段，所以能直接按明细全量排序最好，或者确保会话切分逻辑已经考虑了跨天。

5.2 相邻事件流转统计

有了排序，下一步就是把每行和上一行拼起来，统计从事件A到事件B的次数。用LAG取到上一个事件名：

WITH ordered AS ( SELECT user_id, session_id, event_name, event_time, lag(event_name) OVER ( PARTITION BY user_id, session_id ORDER BY event_time ) AS prev_event_name FROM dwd_user_action_log ) SELECT prev_event_name, event_name, count(1) AS transition_cnt FROM ordered WHERE prev_event_name IS NOT NULL GROUP BY prev_event_name, event_name ORDER BY transition_cnt DESC;

这个表回答了“从首页之后，用户最常见的下一动作是什么”。如果同一页面内多次曝光事件把路径撑得很长，可以先过滤掉无意义的曝光，或者把event_name换成page_url，只保留页面浏览事件。统计流转的时候还要注意，这里的一行代表一次“相邻转移”，不是一个人，所以同一个用户连续点5次按钮会产生5条转移记录。如果关心用户规模，要换成分组后对用户去重统计，而不是直接count(1)。

5.3 路径序列拼接：Top路径大概怎么算

要看完整的Top路径，有些数仓支持把事件聚合成列表，比如collect_list再拼接。不过直接collect_list的顺序通常不保证，必须先排序。稳妥的做法是：先在一个子查询里给事件排序，再按会话聚合拼接；如果不想处理数组，可以用LEAD提取前几步，统计“第一步->第二步->第三步”的三步路径，大多数业务问题到这个粒度已经能回答了。下面是一段用LEAD取前两步的简化例子：

WITH ordered AS ( SELECT user_id, session_id, event_name, lead(event_name, 1) OVER (PARTITION BY user_id, session_id ORDER BY event_time) AS next1, lead(event_name, 2) OVER (PARTITION BY user_id, session_id ORDER BY event_time) AS next2 FROM dwd_user_action_log ) SELECT event_name, next1, next2, count(1) AS path_cnt FROM ordered WHERE next1 IS NOT NULL GROUP BY event_name, next1, next2 ORDER BY path_cnt DESC LIMIT 50;

输出结果类似首页 -> 商品列表 -> 商品详情。如果事件名太泛，可以把event_name换成page_url或页面名称，或者用page_url和event_name一起拼出更有业务含义的路径字段。路径分析里最忌讳把所有事件都塞进去，因为用户会有大量无效点击，先按业务目标把关键节点筛选出来，路径表会干净很多。

6. 实际项目里，我最常被数据质量和性能坑到的几个点

6.1 user_id为空、event_time错乱，第一关是清洗

再漂亮的SQL，遇到脏数据也会得出荒唐结论。用户行为表里最常见的三类脏数据是：user_id为空但带有效token或设备ID，需要先映射成统一用户标识；event_time缺失，在ETL时直接把这一行丢进异常分区；event_name同一个动作有多个叫法，比如有的端上报product_detail，有的端上报detail_view，需要建事件字典做名称归一。我习惯在建汇总表之前先跑一遍探查SQL，把user_id为空的行数、时间字段为空的占比、事件名分布都拉出来看一眼，确认完再往下做。不要轻信上游说“已经清洗过了”这类话，实际项目里多半还要自己再折腾一遍。

清洗逻辑也要考虑业务侧。比如未登录用户，在落地页浏览阶段可能没有user_id，但会有一个未被覆盖的匿名设备ID。如果分析对象是“真实注册用户”，那过滤掉即可；如果分析的是“全部访客行为”，就要用匿名ID做维度，并在报表里标明口径。这个选择没有绝对对错，但必须在同一个报表周期内保持不变，否则留存和漏斗都会自相矛盾。

6.2 时区、日期边界和自然周对齐的小细节

时间口径是最容易被忽略的。客户端埋点和服务器端处理经常不在同一个时区，如果日志统一存的UTC，而业务按东八区看自然日，直接用to_date会把当天中午之后的用户行为切到第二天，导致留存和漏斗全部偏移。稳妥的做法是在ETL层把event_time统一转成业务时区的本地时间，再做dt分区；如果已经是干净的时间戳字段，也可以在执行查询时用from_unixtime做时区平移，但那样每次SQL都要写，很容易漏掉某一条。

日期边界也有一个高频小坑：如果观察窗口是6月1日0点到6月7日24点，SQL里用>= '2024-06-01' AND < '2024-06-07'会少算6月7日整整一天。正确写法是< '2024-06-08'，即用半开区间。跨年那周的weekofyear特别容易算错，建议在日历表里维护好“周一开始日期”，用关联方式代替函数计算。别小看这些问题，我曾见过报表因为边界少一天，整体转化率差了20%，排查到凌晨才发现是<后面写错了。

6.3 亿级日志表上跑分析的几条优化经验

前面所有SQL如果直接怼在几亿行的明细表上，很可能跑不动。经验第一条是分区裁剪：按天分区，写SQL务必带dt条件，即使只是汇总最近30天，也比扫全表快很多。第二条是把高频场景沉淀成中间表，比如每日用户行为汇总表、用户首日时间表、漏斗用户标记表，重复查询直接读结果。第三条是不要轻易用多维度COUNT(DISTINCT)，在高基数的大表上代价很大；先按用户粒度预聚合，再把汇总结果拿来统计，会快一个数量级。

如果确实需要快速估一下UV，可以使用近似去重函数，比如approx_count_distinct之类的算法。它的误差通常在2%以内，用来做日常监控和趋势对比完全够用，只有财务核对时需要精确值。我在真实项目里的习惯是：先把SQL放在1%的抽样数据上跑通逻辑，确认结果和业务口径一致后再全量跑。全量跑完之后把结果落到报表表，后续按天增量刷新。这样既保证正确性，也不会每次分析都重算历史全量。