做用户行为分析这件事很多团队一开始并不缺数据缺的是把数据变成结论的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和UVSELECT 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%的抽样数据上跑通逻辑确认结果和业务口径一致后再全量跑。全量跑完之后把结果落到报表表后续按天增量刷新。这样既保证正确性也不会每次分析都重算历史全量。