连续登录用户统计：窗口函数与SQL核心实现全解析

统计连续登录用户数这个题目在我做数据开发的这几年前前后后遇到了不下十次。不管是用Hive还是MySQL面试官只要想考察窗口函数、考察你把业务逻辑翻译成SQL的能力大概率就会把这道题抛出来。我自己也从最初照抄网上的答案到后来逐步理解背后的数学规律再到在生产环境里处理各种脏数据算是把这题的每个角落都摸了一遍。这篇文章不绕弯直接把这题完整拆开。我会把建表语句、测试数据、核心SQL、进阶写法、常见坑点全部过一遍Hive和MySQL两个版本都会给出可跑的代码。适合刚接触窗口函数的人系统性学习也适合已经会写但想搞明白“为什么这样写就能算出连续”的同学。看完你不仅能抄作业还能知道作业为什么这么写。1. 连续登录统计的问题拆解先把“连续”翻译成SQL1.1 这题到底在问什么三种常见需求变体先别急着写SQL需求没搞清楚就写代码是这题最容易翻车的地方。“统计连续登录的用户数”这个说法其实很模糊我见过至少三种不同的问法第一种统计每个用户每个连续登录区间的天数输出像“用户A在1月1日到1月4日连续登录了4天1月6日到1月7日连续登录了2天”这样的结果。第二种只关心“连续登录超过N天的用户”比如运营活动要求连续签到7天的用户发奖励这时候要筛选出满足条件的用户列表。第三种统计“连续登录N天及以上到底有多少人”这个更激进直接要一个人数通常用来衡量活动效果或者用户粘性。这三种需求看起来差不多但SQL的写法是有差异的。第一种要输出区间第二种要加HAVING过滤第三种要在第二种的基础上再套一层COUNT DISTINCT。很多人在面试时只背了一种写法面试官一变形就懵了。所以我在后文会把三种都实现一遍你拿去就能直接用。1.2 核心数学规律日期减去行号连续日期会收敛到同一个值这题最优雅的地方在于它有一个非常漂亮的数学规律。假设一个用户连续登录了1月1日、1月2日、1月3日你把这三条记录按日期排序给它们编上行号会得到这样的结果1月1日行号1日期减1天 12月31日1月2日行号2日期减2天 12月31日1月3日行号3日期减3天 12月31日看到了吗连续的三天分别减去自己的行号竟然收敛到了同一天。这是因为连续日期本身就是等差递增的步长是1而行号也是等差递增的步长也是1。两个等差序列相减差值恒定不变。一旦中间断了一天比如1月5日它减去行号4假设前面有四条记录得到的是1月1日和前面的12月31日不一样了。这个规律就是我们分组的钥匙把日期减行号的结果当作分组ID同一个用户、同一个分组ID内的记录就是一段连续登录。这条思路实现起来极其简洁而且天然把“连续”具象化了是我在实际工作中用得最多的一种方案。1.3 为什么窗口函数是解题标配没有窗口函数之前这个问题解决起来非常痛苦。你要么用自连接逐日对比要么用变量游标式地逐行判断SQL写出来又长又容易错。窗口函数出现之后这套逻辑变得异常清晰。这题涉及的核心窗口函数是ROW_NUMBER()作用是在每个用户的登录日期排序后给出递增序号。PARTITION BY user_id意思就是按用户分组每个用户内部重新从1开始编号ORDER BY login_date则保证了编号是按照时间顺序来的。它和RANK()、DENSE_RANK()的区别在于ROW_NUMBER()永远是连续整数哪怕日期重复也不会并列。这一点很关键——如果同一天有重复登录记录ROW_NUMBER会给出不同的行号导致分组错乱这个坑我在第5章会专门讲。窗口函数还有一个好处它不减少行数。普通GROUP BY会把多行压成一行而窗口函数是在每一行旁边加一列计算结果所以能在保留原始日期信息的同时计算分组ID这一步是后续聚合的前提。2. 建表语句与测试数据Hive、MySQL双版本一次到位2.1 Hive建表从生产分区表到演示版先看生产环境里比较规范的Hive建表方式。实际业务中登录日志通常按天分区存储格式会用ORC或者Parquet因为查询性能和压缩比都比纯文本好很多CREATE TABLE IF NOT EXISTS login_log ( user_id BIGINT COMMENT 用户ID, login_date DATE COMMENT 登录日期 ) COMMENT 用户登录日志表 PARTITIONED BY (dt STRING COMMENT 日期分区) STORED AS ORC;写数据的时候分区字段就是业务日期这样每天的增量数据放到对应的分区里。查询时用WHERE dt 2024-01-01这样的条件就能快速裁剪分区不用全表扫描。不过为了演示方便本文后面所有SQL都基于下面的简化版本没有分区纯文本存储方便你在本地Hive或集群上直接跑CREATE TABLE IF NOT EXISTS login_log ( user_id BIGINT COMMENT 用户ID, login_date DATE COMMENT 登录日期 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t STORED AS TEXTFILE;需要注意login_date字段我特意用了DATE类型而不是STRING。如果项目里登录时间是STRING类型的“2024-01-01 10:23:45”那后续SQL里必须做截断处理这个坑详见第5章。2.2 MySQL建表索引设计决定后续查询效率MySQL这边我用InnoDB引擎字符集utf8mb4。最核心的一点是建一个联合索引(user_id, login_date)因为SQL里所有操作都是先按user_id分组、再按login_date排序有这个索引MySQL就能按索引顺序扫描避免额外的filesortCREATE TABLE login_log ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL COMMENT 用户ID, login_date DATE NOT NULL COMMENT 登录日期, KEY idx_user_date (user_id, login_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;如果你用的是MySQL 8.0及以上窗口函数直接支持如果是5.7或更早的版本就要用用户变量来模拟这个我放在4.4节单独讲。建表时把DATE类型用清楚能省掉后面大量CAST的麻烦。2.3 测试数据故意埋几个坑测试数据我准备了4个用户故意涵盖了多种边界情况用户1有两段连续登录1月1日到1月4日连续4天1月6日到1月7日连续2天用户2有两段连续登录1月1日到1月2日连续2天1月4日到1月6日连续3天用户3只有1月10日一天登录属于“连续1天”的边界情况用户41月1日登录了两次用来演示重复登录的坑Hive里插入数据INSERT INTO login_log (user_id, login_date) VALUES (1, 2024-01-01), (1, 2024-01-02), (1, 2024-01-03), (1, 2024-01-04), (1, 2024-01-06), (1, 2024-01-07), (2, 2024-01-01), (2, 2024-01-02), (2, 2024-01-04), (2, 2024-01-05), (2, 2024-01-06), (3, 2024-01-10), (4, 2024-01-01), (4, 2024-01-01);MySQL的插入语句一样同样可以直接执行。生产环境里我强烈建议你额外插入一些脏数据做验证比如带时分秒的时间戳、NULL日期这样能把很多隐藏问题提前暴露出来。2.4 数据准备阶段的注意事项插入测试数据时有几个细节值得留意。第一月份一定要补零写成2024-01-01而不是2024-1-1。虽然很多引擎能自动识别但一旦某个引擎不识别日期转换就会出错表现为分组结果完全错乱。第二建议把“单日登录”和“重复登录”的用户都放进去这两个是新手最容易忽略的边界。第三如果是用HiveINSERT INTO VALUES的方式在小数据量下没问题但生产环境导入数据通常走LOAD DATA这里仅为验证逻辑就不展开了。3. 核心SQL实现两种主流解法逐行拆解3.1 解法一等差序列法row_number date_sub先看Hive版本SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp FROM login_log ) t GROUP BY user_id, grp ORDER BY user_id;MySQL 8.0版本长这样SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp FROM login_log ) t GROUP BY user_id, grp ORDER BY user_id;这两个版本唯一的语法差异在日期减函数Hive的date_sub接受整数作为第二个参数而MySQL要求显式写成INTERVAL n DAY。执行逻辑完全一样。我们来逐层看它是怎么运行的。最内层子查询给每个用户的登录日期按时间排序并编上行号同时算出一个grp字段。以用户1为例下面是中间结果1月1日行号1grp 1月1日减1天 2023-12-311月2日行号2grp 1月2日减2天 2023-12-311月3日行号3grp 1月3日减3天 2023-12-311月4日行号4grp 1月4日减4天 2023-12-311月6日行号5grp 1月6日减5天 2024-01-011月7日行号6grp 1月7日减6天 2024-01-01对外层查询来说grp相同的记录自然归为同一组再用COUNT(*)数行数恰好就是连续的天数MIN和MAX给出这个连续区间的起止日期。结果就很清晰了用户1起止2024-01-01到2024-01-044天另一段起止2024-01-06到2024-01-072天用户2两段分别2天和3天用户3仅2024-01-10一天1天用户4注意了因为1月1日登录了两次行号分别编成1和2grp就变成了2023-12-31和2023-12-30被分成了两组每组计1天用户4的结果就是那棵雷如果你没在数据准备阶段做去重统计出来的连续天数就会虚高或错裂。解决方案我在第5章专门说。3.2 解法二差值标记法lag 累计求和等差序列法的确简洁但有的面试官会追问如果不用行号还能怎么算这时候你就要拿出第二种思路——用LAG函数比较相邻日期。LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)能取到当前行之前一行的登录日期。如果当前日期比上一行日期大1天说明和前一条记录在同一个连续段里如果上一条记录不存在或者日期差不是1天说明从这里开始新的一段。把“是否新开一段”记作1和0再对这段标记做累计求和就能得到和grp等价的分组ID。完整SQL如下WITH t1 AS ( SELECT user_id, login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date FROM login_log ), t2 AS ( SELECT user_id, login_date, CASE WHEN prev_date IS NULL OR DATEDIFF(login_date, prev_date) ! 1 THEN 1 ELSE 0 END AS is_new_group FROM t1 ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY login_date) AS grp FROM t2 ) t GROUP BY user_id, grp ORDER BY user_id;用用户1的数据模拟一下t2的中间结果。第一条记录prev_date是NULL标记为11月2日比1月1日大1天标记为01月3日比1月2日大1天标记为01月4日比1月3日大1天标记为0。到这里累计和为1。到了1月6日上一行是1月4日差2天标记为1累计和变成21月7日比1月6日大1天标记为0累计和仍为2。于是第一段连续的分组ID是1第二段是2GROUP BY之后的效果和等差序列法完全一致。这两种解法我建议你都掌握。等差序列法写起来更短逻辑更精巧差值标记法的每一步都更贴近“连续”的自然语义遇到更复杂的规则比如允许迟到1天也算连续时改起来更直观。3.3 两个引擎的语法差异对照与选型为了让你不踩语法坑我把两个引擎的关键函数差异整理成了一张表功能HiveMySQL日期减天数date_sub(date, n)date_sub(date, interval n day)日期加天数date_add(date, n)date_add(date, interval n day)两个日期相差天数datediff(date1, date2)datediff(date1, date2)窗口函数全面支持8.0及以上支持5.7不支持CTE语法支持8.0及以上支持选型上我个人的经验是能看懂等差序列法就优先用它因为它结构最紧凑排查问题时看中间结果也方便。差值标记法适合在规则多变的时候使用比如业务方说“用户当天没登录但前一天登录且后一天也登录算作连续”这种弹性规则用LAG法更容易改。4. 从“连续几天”到“连续分析”高频进阶写法4.1 筛选连续登录N天以上的用户第3章的输出结果包含了每个用户每一段的连续天数。现在如果业务要“连续登录3天及以上的用户”直接套一层过滤就行SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp FROM login_log ) t GROUP BY user_id, grp HAVING COUNT(*) 3 ORDER BY user_id;这里HAVING是在分组后过滤COUNT(*)算出的就是连续天数。这个结果对运营特别有价值用户2的第二段连续3天会被筛出来用户1虽然有一段4天长连续但还有一段只有2天这条2天的记录会被过滤掉——注意这是正确的行为因为“连续3天”只关心满足条件的那些连续段。4.2 每个用户的最大连续天数如果只是想知道“每个用户在这段时间里最长连续登录了几天”那就在上面结果的基础上再按用户聚合一次取MAXSELECT user_id, MAX(continuous_days) AS max_continuous_days FROM ( SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp FROM login_log ) t GROUP BY user_id, grp ) t2 GROUP BY user_id;这里不需要MIN和MAX日期了直接取最大天数就好。结果就是用户1最大连续4天用户2最大连续3天用户3最大1天。这个指标在会员体系里很常见比如判断重度用户、衡量活动期间活跃峰值。4.3 输出完整连续区间明细除了天数有时候还需要把区间本身导出来给下游或者做可视化。只需要把MIN(login_date)、MAX(login_date)、COUNT(*)三个字段同时保留前文3.1的SQL本身就是完整的区间明细结果SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp FROM login_log ) t GROUP BY user_id, grp ORDER BY user_id, start_date;这种结果可以直接拼上业务维度的信息比如给每个连续区间打上“哪次活动期间产生”的标签或者计算区间结束后多久用户流失。做用户留存分析时这种“活跃段”数据特别有用。4.4 旧版MySQL的兼容替代用户变量方案如果你的环境还是MySQL 5.7窗口函数用不了只能用用户变量来模拟行号和分组。下面这套写法是兼容5.7的SET prev_user : NULL; SET prev_date : NULL; SET grp : 0; SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, grp : IF(prev_user user_id AND prev_date DATE_SUB(login_date, INTERVAL 1 DAY), grp, grp 1) AS grp, prev_user : user_id, prev_date : login_date FROM ( SELECT user_id, login_date FROM login_log ORDER BY user_id, login_date ) a ) b GROUP BY user_id, grp ORDER BY user_id;这段代码的原理是先把数据按用户和时间排好序然后用prev_user和prev_date记录上一行的值逐行判断当前行是否和上一行连续。如果连续分组ID不变如果不连续分组ID加1。变量方案有坑最典型的是同一个SELECT中变量读取和赋值的顺序在不同版本中行为不完全一致严谨的做法是在生产环境先小规模验证。总之能用8.0就别折腾5.7这个方案我定位成“救急”不算最优解。5. 常见问题与避坑实录5.1 同一天重复登录最容易被坑的细节测试数据里用户4的坑你肯定还记着。只要login_date字段里同一用户同一天出现两次ROW_NUMBER会给两条记录分配不同行号date_sub后的grp就不同原本连续1天被错分成两个“连续1天”。最直接的解决方案是在查询最内层先去重SELECT user_id, login_date FROM login_log GROUP BY user_id, login_date;用GROUP BY去重后再套连续登录的逻辑。这个动作成本很低但能救回大量错误结果。如果表特别大去重后再进入窗口函数环节还能顺带减少shuffle的数据量。实际上在真实生产环境登录表天然可能有重复比如用户一天内多次打开App每打开一次记一条所以这个去重步骤几乎必须写进你的模板里。5.2 日期带时间、格式不统一、NULL值日期字段是datetime类型时DATEDIFF算出来会带上小时分钟直接拿datetime去比较基本都会失败。我在实际项目中常用的处理办法是查询前先把时间截断成日期-- 处理datetime类型的时间戳先转成DATE再参与计算 SELECT user_id, TO_DATE(login_time) AS login_date FROM login_log GROUP BY user_id, TO_DATE(login_time);MySQL里对应的是DATE()函数或者DATE_FORMAT(login_time, %Y-%m-%d)。此外日期格式必须统一2024-01-01和2024-1-1混用会导致某些引擎比较失败最稳妥的做法是ETL层就把日期规范成固定的yyyy-MM-dd格式。至于NULL日期直接拿去做date_sub会得到NULL然后GROUP BY会把它们塞进同一个分组看起来像是连续几天的脏数据建议在最初的查询里就把login_date IS NOT NULL条件带上。5.3 边界语义“断一天”和“连续一天”业务方说的“连续”有时候不是指自然日连续而是指“相隔不超过N天”。比如一个用户1月1日和1月3日各登录一次中间1月2日没登录如果规则是“隔一天也算连续”那么1月1日和1月3日应该归为同一段。处理这种规则解法一就不太好改了解法二只要把DATEDIFF(login_date, prev_date) ! 1改成DATEDIFF(login_date, prev_date) 2就行。另外“连续1天”算不算连续也要提前确认。很多运营活动要求“连续签到3天”那你HAVING COUNT(*) 3毫无争议。但如果是要做用户分层把只有单日登录的用户也算进“连续活跃”则1天也算数。语义没对齐统计结果就会和业务预期差一大截。写之前先花一分钟问清楚比写完再返工省得多。5.4 大表性能优化建议最后聊性能。几百万行数据这套SQL跑起来毫无压力但到了几亿行甚至更大规模就要留个心眼了。第一先裁剪日期范围。登录日志这种表通常有分区WHERE dt 2024-01-01 AND dt 2024-01-31能直接砍掉大量无关分区。第二先去重后开窗。窗口函数虽然好用但PARTITION BY user_id意味着每个用户的数据会被分到一起本质上是按用户的shuffle数据量大的时候这一步的代价不低。先把重复数据去掉、把用户范围缩小再进入窗口函数效率会好很多。第三如果业务上只需要“连续N天以上”的用户不一定要跑完整个明细。可以先在子查询里用优化过的规则直接过滤掉明显不可能的用户。比如要连续7天那么总登录次数小于7的用户直接可以剔除这个用GROUP BY HAVING COUNT(*) 7就能在早期把数据量压下去。我实际跑过的最常见场景是一个中等规模的App用户表几千万、登录明细表几亿行。只要分区裁剪做好、去重前置、窗口函数只跑必要的用户范围Hive任务通常在几分钟内能出结果。真正的问题往往不是SQL本身而是上游数据没清洗干净——重复数据、脏日期、格式混杂每一样都能让你的正确结果变成错误结果。最后再分享一个这条SQL的变体用法吧。我在做会员活跃分析时经常需要把“连续登录段”和“流失预警”结合起来。做法很简单在得到每个用户每个连续区间的起止日期后用LAG函数算一下相邻两个区间的间隔天数如果间隔小于某个阈值就标记成“可能流失又召回”的用户。这算是在基础连续登录统计之上长出来的新能力但支撑它的核心还是这套分组逻辑。SQL这东西基础弄通了变化真的很多但万变不离其宗关键就是理解“日期减行号”这个规律再加上对边界情况的嗅觉。希望这篇文章能帮你把这题彻底吃透。