SQL连续登录统计：窗口函数与日期分组实战

SQL连续登录统计：窗口函数与日期分组实战

发布时间：2026/10/19 6:28:31
SQL连续登录统计：窗口函数与日期分组实战 最近几年只要和数据相关的岗位面试十次里有八次会遇到这道题给一张用户登录记录表统计连续登录N天及以上的用户数。我第一次看到这题时第一反应是这有什么好考的后来真正处理过用户活跃分析才发现这道题能在一百个候选人里筛出九十个——它考的不只是会不会写一个窗口函数而是能不能把连续这个词翻译成可计算的分组逻辑。这篇文章我准备从一道具体需求出发把建表、造数、Hive/MySQL两个版本的解法、最容易踩的坑以及从连续登录扩展到连续活跃的思路全部过一遍。无论你是刚接触SQL的数据新人还是已经写过不少统计的数仓开发应该都能从中找到有用的东西。1. 这道经典题在考什么连续登录的定义与需求拆解先看需求的原形。假设有一张登录流水表里面记录了每个用户的登录日期现在要找出连续登录3天及以上的用户有哪些如果只给最终结论只需要输出用户id和对应的连续天数。严格来说很多业务方还会要求输出用户数比如上个月连续打卡7天的用户有多少那就在这个基础上再套一层去重计数。连续这个词在SQL里没有天然的表达方式必须自己构造。常见定义是按自然日连续也就是说用户A在1月1日、1月2日、1月3日这三天每天都登录就算连续3天如果中间隔了一天比如1月1日和1月3日登录但1月2日没来就不算连续。更严格一点还要满足每天至多只算一次登录毕竟用户一天登十次活跃度也不该按十次算。1.1 需求先拆成三层再动手写SQL拿到这种需求我习惯先把它拆成三个子问题去重、排序、分组。去重解决同一天多条登录记录的问题排序解决每个用户日期先后顺序的问题分组则是核心要决定哪些日期属于同一个连续段。很多初学者犯的错误是上来就写SELECT user_id, COUNT(DISTINCT login_date)这样只能算出总登录天数算不出连续天数。因为COUNT(DISTINCT)会把断掉的日子也拼在一起比如1月1日、1月3日、1月5日统计出来是3天但显然不连续。1.2 一张能说清需求的样例数据假设我们有四个用户登录记录长这样user_idlogin_date1012024-01-011012024-01-021012024-01-031012024-01-061022024-01-011022024-01-011022024-01-021022024-01-041032024-01-011032024-01-021032024-01-031032024-01-041042023-12-291042023-12-301042023-12-311042024-01-011042024-01-02从肉眼看101有1月1日到3日连续3天102虽然1月1日有两条记录但1月1日到2日只有2天达不到3天103连续4天104从2023年12月29日一路跨到2024年1月2日连续5天。所以最终需要输出的用户是101、103、104。这里的102就是典型的同一天多次登录陷阱如果不去重直接算1月1日会占两个排序位置本来连续的两天也会被拆得乱七八糟。1.3 表结构只需要四个字段做这个分析一张简单的登录流水表就够了。实际业务里可能还有设备id、登录渠道、登录时长这些字段但统计连续登录只关心三样用户id、登录日期、以及是否还有更多信息需要追溯。如果需要看登录时间明细可以再加一个login_time如果没有这张表最小可用的结构就是user_id加login_date。后面我会给完整的建表语句这里先把模型在脑子里搭好。2. 核心解法日期减序号为什么这一招能识别连续登录网上关于这题的解法很多但绕来绕去都离不开同一个核心技巧把登录日期按用户排序编上号然后用日期减去编号得到一个分组标记。日期连续时这个标记相同日期一旦断了标记就会跳变。2.1 从数学上理解为什么能分组连续登录的日期序列本质上是一个公差为1的等差数列。比如1月1日、1月2日、1月3日每两个相邻日期之间差1天。如果我们给这些日期从1开始依次编号编号序列也是公差为1的等差数列1、2、3。同一个等差数列减去另一个公差相同的等差数列结果是一个常数。所以日期减编号得到的是同一个值。反过来一旦中间断了一天日期仍然按年份递增但编号已经被跳过的日子隔开相当于日期序列还是连续的公差1编号序列却没有对应上两者的差值就会发生变化。2.2 排队发号码牌的生活类比可以想象用户每天去签到每天签到的队伍里都有固定的一天位置。用户连续登录3天就是这3天每天都在队伍里日子每天往后挪一天用户的签到次数也加1所以日期这一列减去次数这一列的差值始终固定。如果用户隔了一天再来日期往后跳了两天但签到次数只加了1差值就被拉大了一下子就能看出这是两段。这个思路和很多字符串匹配里的滑动窗口不太一样它不需要窗口只需要一次排序和一次减法SQL表达非常简洁。2.3 手把手算一遍101用户的过程以101为例他登录了1月1日、1月2日、1月3日、1月6日。先按日期排序编上序号rn再计算date_sub(login_date, rn)MySQL写法是DATE_SUB(login_date, INTERVAL rn DAY)结果如下login_daterngroup_flag2024-01-0112023-12-312024-01-0222023-12-312024-01-0332023-12-312024-01-0642024-01-02看到没有前三行日期连续减出来的group_flag都是2023-12-31第四行单独是2024-01-02。这样按user_id和group_flag分组count一下就能得到101有连续3天和连续1天两个段。这里rn从1开始所以组标记比实际起始日期早了一天但这完全不影响分组。2.4 分组后如何筛选连续N天有了group_flag之后事情就简单了GROUP BY user_id, group_flag然后用COUNT(*)数每个连续段的天数。想要连续3天及以上加一句HAVING COUNT(*) 3就行。如果想要最大连续登录天数去掉HAVING直接取MAX(continuous_days)。这里有个小细节很容易被忽略连续天数就是该组里的记录条数因为我们已经按天去重过了。如果没去重count出来的是登录次数而不是天数后面会专门讲这个坑。3. 完整实战建表、造数据Hive/MySQL双版本跑通这个部分直接给能跑的东西。我尽量让Hive和MySQL的建表、插入、查询都齐整方便你本地复现。3.1 MySQL建表与测试数据我见过很多建模方式是把user_id和login_date设成联合主键表示一个用户一天只能有一条登录记录。但真实流水表往往是多端登录、多渠道上报同一天同一用户有多条记录很正常。所以更稳妥的建模是保留自增id把user_id和login_date建一个联合索引查询时在SQL里去重。CREATE TABLE user_login_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL COMMENT 用户ID, login_date DATE NOT NULL COMMENT 登录日期YYYY-MM-DD, login_time DATETIME DEFAULT NULL COMMENT 登录时间, KEY idx_user_date (user_id, login_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户登录流水表;插入测试数据其中102在1月1日故意插了两条INSERT INTO user_login_log (user_id, login_date, login_time) VALUES (101, 2024-01-01, 2024-01-01 09:12:00), (101, 2024-01-02, 2024-01-02 10:01:00), (101, 2024-01-03, 2024-01-03 08:20:00), (101, 2024-01-06, 2024-01-06 20:55:00), (102, 2024-01-01, 2024-01-01 08:00:00), (102, 2024-01-01, 2024-01-01 12:30:00), (102, 2024-01-02, 2024-01-02 09:05:00), (102, 2024-01-04, 2024-01-04 11:11:00), (103, 2024-01-01, 2024-01-01 07:45:00), (103, 2024-01-02, 2024-01-02 18:30:00), (103, 2024-01-03, 2024-01-03 10:10:00), (103, 2024-01-04, 2024-01-04 21:00:00), (104, 2023-12-29, 2023-12-29 08:00:00), (104, 2023-12-30, 2023-12-30 09:00:00), (104, 2023-12-31, 2023-12-31 10:00:00), (104, 2024-01-01, 2024-01-01 11:00:00), (104, 2024-01-02, 2024-01-02 12:00:00);3.2 Hive建表与数据写入Hive的表结构和MySQL基本一致差异主要在存储格式和类型上。为了演示方便我直接用非分区表生产环境建议按日期字段加分区后面会讲分区裁剪怎么用。CREATE TABLE user_login_log ( user_id BIGINT COMMENT 用户ID, login_date STRING COMMENT 登录日期格式YYYY-MM-DD, login_time STRING COMMENT 登录时间 ) COMMENT 用户登录流水表 STORED AS ORC;插入数据和MySQL类似INSERT INTO TABLE user_login_log VALUES (101, 2024-01-01, 2024-01-01 09:12:00), (101, 2024-01-02, 2024-01-02 10:01:00), (101, 2024-01-03, 2024-01-03 08:20:00), (101, 2024-01-06, 2024-01-06 20:55:00), (102, 2024-01-01, 2024-01-01 08:00:00), (102, 2024-01-01, 2024-01-01 12:30:00), (102, 2024-01-02, 2024-01-02 09:05:00), (102, 2024-01-04, 2024-01-04 11:11:00), (103, 2024-01-01, 2024-01-01 07:45:00), (103, 2024-01-02, 2024-01-02 18:30:00), (103, 2024-01-03, 2024-01-03 10:10:00), (103, 2024-01-04, 2024-01-04 21:00:00), (104, 2023-12-29, 2023-12-29 08:00:00), (104, 2023-12-30, 2023-12-30 09:00:00), (104, 2023-12-31, 2023-12-31 10:00:00), (104, 2024-01-01, 2024-01-01 11:00:00), (104, 2024-01-02, 2024-01-02 12:00:00);3.3 核心查询SQLHive和MySQL的细微差别先看Hive版本我习惯用子查询一层套一层这样在各个版本里都相对稳妥。第一步去重第二步开窗排序第三步算group_flag第四步分组统计SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, CAST(rn AS INT)) AS group_flag FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ) dedup ) seq ) t GROUP BY user_id, group_flag HAVING COUNT(*) 3 ORDER BY user_id;MySQL版本几乎一样唯一的差别是日期减天数的写法Hive用date_sub(login_date, n)MySQL用DATE_SUB(login_date, INTERVAL n DAY)。SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ) dedup ) seq ) t GROUP BY user_id, group_flag HAVING COUNT(*) 3 ORDER BY user_id;这里用的是MySQL 8.0的窗口函数。如果你的生产环境还是MySQL 5.7别急第5章会专门讲变量写法。3.4 结果解析和最终用户数跑出来的结果应该是user_idcontinuous_days101310341045和手工看数据得出的结论完全一致。102虽然1月1日有两条记录但因为先去重了他最大连续段只有2天被HAVING过滤掉。如果要输出连续登录3天及以上的用户数需要在上面再包一层SELECT COUNT(DISTINCT user_id) AS user_cnt FROM ( SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ) dedup ) seq ) t GROUP BY user_id, group_flag HAVING COUNT(*) 3 ) cnt;这个嵌套的目的是先算出每个用户的连续段再横向去重统计用户数。你可能会问直接在外面SELECT COUNT(*)不行吗不行因为一个用户可能有多个连续3天以上的时间段比如连续3天、隔两天又连续3天直接count会把同一个用户计多次。业务上问用户在统计周期内是否连续登录过3天需要先按用户维度折叠成一个布尔值再计数。4. 那些容易踩的坑重复登录、空值、跨年、统计口径这道题看似简单真正跑数的时候坑非常多。我见过不少人笔试写得头头是道一到生产环境查出来的数对不上问题大多出在这一章。4.1 同一天多次登录必须先去重这是第一号坑如果表里一个用户一天登录了多次直接开窗排序1月1日会拿到两个不同的rn。比如102在1月1日有两条记录排序后rn可能是1和21月2日是3。此时计算date_sub(login_date, rn)1月1日两条分别得到2023-12-31和2023-12-30本来连续的1月1日到1月2日也被拆成了两个组统计结果彻底错乱。所以无论原始数据是不是一天多条第一步必须做GROUP BY user_id, login_date或SELECT DISTINCT user_id, login_date。我在真实数据环境中还会再加一点先确认时间字段没有带时分秒或者至少要CAST(login_time AS DATE)否则2024-01-01 08:00和2024-01-01 23:00也会被当成两条。4.2 排序函数选哪个row_number用在前rank/dense_rank用在哪去重之后每个用户在同一个日期只有一条记录所以日期之间不存在并列ROW_NUMBER()是最自然的选择。有些文章喜欢用DENSE_RANK()但那是处理同一天多条记录时的补救方案不是标准解法。这里顺便说一个面试经常追问的点如果不先去重直接用DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date)同一天的记录会拿到同一个排名但这会让后续date_sub(login_date, rn)中的rn代表不同日期的个数虽然能变相做到去重效果可一旦日期带分组外的其他维度或者要输出明细很容易让逻辑变得混乱。与其在排序函数上绕弯子不如老老实实先去重。4.3 字符串日期和跨年跨月的坑Hive里如果日期字段是STRING一定要保证所有值都是YYYY-MM-DD格式。我在实际处理中遇到过20240101、2024-1-1、2024/01/01混在一起的情况date_sub处理不出来排序也可能错乱。稳妥做法是统一regexp_replace或from_unixtime(unix_timestamp(...))转成同一格式再参与计算。跨年跨月本身不是问题因为日期函数是按日历日计算的。比如104用户从2023年12月29日连续到2024年1月2日login_daterngroup_flag2023-12-2912023-12-282023-12-3022023-12-282023-12-3132023-12-282024-01-0142023-12-282024-01-0252023-12-28group_flag依然一样不会被月份或年份的边界打断。但如果有人自作聪明用SUBSTR(login_date, 1, 7)按月截串再去做日期减序号那跨年必出问题。4.4 空值和脏数据必须在预处理阶段干掉login_date为NULL时ROW_NUMBER()排序后通常会在最前面date_sub结果也是NULLGROUP BY之后变成一个以NULL为分组标记的组会污染统计结果。user_id为空同理多个空用户会被并到同一个NULL组里。我在写生产脚本时第一步就会加上WHERE user_id IS NOT NULL AND login_date IS NOT NULL不要指望后续逻辑帮你去兜脏数据。4.5 统计口径连续N天、恰好N天、还是近N天连续登录3天及以上和恰好连续3天的写法略有不同。前者用HAVING COUNT(*) 3后者用 3。但恰好连续3天有个隐藏问题用户可能有一个连续4天的段按恰好3天算他就不该被统计可业务往往想的是至少3天。所以拿到需求第一件事是跟业务确认口径。还有一个容易踩的是近30天连续登录用户。很多人直接WHERE login_date date_sub(current_date, 29)然后按连续段分组但这样会把刚好发生在30天窗口边缘的连续段截断比如一个用户从30天前第一天开始连续登录5天窗口里其实只有4天到底算不算近30天连续5天不同业务有不同的答案。我在交付前一般会把口径写成一句话附在结果表描述里避免后续扯皮。5. 不止窗口函数MySQL 5.x变量方案与lag方案的取舍MySQL 8.0发布之前窗口函数是没有的。但这道题出现的时间远早于MySQL 8.0所以面试官经常追一句如果你们库不支持窗口函数怎么写下面这两个思路值得掌握。5.1 为什么还要学非窗口函数方案一方面面试里问这个问题是在考察你到底是背模板还是真懂原理另一方面有些公司确实还在用MySQL 5.6/5.7或者Hive版本比较老真要落地时窗口函数不一定都开了。这时候变量方案或者自连接方案就是保底手段。5.2 MySQL 5.x用户变量写法用户变量的核心是按user_id和login_date排序后用变量记住上一个用户和上一个日期。如果当前用户和上一个用户相同且当前日期正好比上一个日期多1天说明连续复用之前的组标记否则重新开一组。SET prev_user : NULL; SET prev_date : NULL; SET grp_start : NULL; SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, grp_start : IF( prev_user user_id AND DATEDIFF(login_date, prev_date) 1, grp_start, login_date ) AS grp_start, prev_user : user_id, prev_date : login_date FROM ( SELECT DISTINCT user_id, login_date FROM user_login_log ORDER BY user_id, login_date ) t ) s GROUP BY user_id, grp_start HAVING COUNT(*) 3 ORDER BY user_id;这个写法依赖MySQL在SELECT子句中从左到右计算用户变量的行为属于实现细节不是SQL标准。数据量不大时可以用数据量大或者要长期维护的报表我不建议把变量写到线上任务里因为它太容易出现环境差异导致的结果漂移。面试展示思路没问题生产还是优先升级到8.0或换Hive。5.3 lag窗口函数用上一次登录日期构造分组除了日期减序号还有一种很直观的解法计算每个用户上一次登录日期如果当前日期和上一次登录日期相差不是1天说明这是一个新连续段的开始。然后把是否新段做窗口累加得到段编号。SELECT user_id, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, SUM(is_new_group) OVER ( PARTITION BY user_id ORDER BY login_date ) AS group_id FROM ( SELECT user_id, login_date, CASE WHEN prev_login_date IS NULL OR DATEDIFF(login_date, prev_login_date) 1 THEN 1 ELSE 0 END AS is_new_group FROM ( SELECT user_id, login_date, LAG(login_date, 1) OVER ( PARTITION BY user_id ORDER BY login_date ) AS prev_login_date FROM ( SELECT DISTINCT user_id, login_date FROM user_login_log ) t ) pre ) mark ) t GROUP BY user_id, group_id HAVING COUNT(*) 3 ORDER BY user_id;这个思路的好处是逻辑更接近人怎么理解连续而且后续如果要判断断档天数直接在CASE里改条件就行。缺点是比日期减序号多一层窗口聚合数据量大时开销稍高。5.4 三种方案怎么选方案适用场景优点缺点日期减序号Hive/Spark/MySQL 8.0简单直观两轮计算结束需要窗口函数用户变量MySQL 5.x不依赖窗口函数依赖赋值顺序难维护lag判断新段Hive/Spark/MySQL 8.0逻辑直接扩展性强多一层窗口聚合如果环境允许我一般首选日期减序号如果面试官追问拿出lag方案聊思路如果对方坚持要兼容MySQL 5.7再给变量方案。6. 从一道题到一类业务连续登录统计的扩展连续登录这个连续模型能延伸出很多玩法。掌握核心思路之后很多需求其实都是同一套打法换皮。6.1 连续区间明细和最大连续天数只输出连续天数还不够很多运营活动要看具体起止日期。比如要奖励连续打卡7天以上的用户最好把每个连续段的开始日期和结束日期都拉出来。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 rn DAY) AS group_flag FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT user_id, login_date FROM user_login_log GROUP BY user_id, login_date ) dedup ) seq ) t GROUP BY user_id, group_flag HAVING COUNT(*) 3 ORDER BY user_id, start_date;最大连续天数就是在这个结果上取MAX(continuous_days)按用户折叠。注意一个用户可能有多个连续段所以要先在段粒度聚合再在用户粒度折叠两步不能省。6.2 从按天连续到按周/月连续有些业务说的连续活跃不是每天都来而是每周至少来一次。这时候可以把登录日期先归一到周再判断周序列是否连续。Hive里可以用TRUNC(login_date, MM)或DATE_FORMAT(login_date, yyyy-MM)MySQL里可以用DATE_FORMAT(login_date, %Y-%m)然后对月粒度日期做同样的日期减序号操作。这里有个新坑如果按周算周一拉出来的用户可能周五周六活跃周日没来下周一来一次中间隔了3天但在周粒度上是连续的。要先定义好一周的起点是哪一天再跨周判断。按周连续和按天连续的SQL结构完全一样只是日期字段先做了粒度归并。6.3 生产环境下的性能优化思路先说Hive。窗口函数ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)在计算时需要把所有同用户的数据分到同一个Reduce里如果某个超高频用户的数据量特别大这个Reduce就会很慢形成数据倾斜。常见的缓解办法是先按user_id和login_date做GROUP BY去重减少数据量再到外部过滤掉明显异常的超级用户比如每天登录几十次的测试账号。如果倾斜依然严重可以对user_id做哈希散列把同一个用户拆到多个桶里最后再把结果合并。再说MySQL。给表加上(user_id, login_date)联合索引非常关键因为第一步去重和窗口函数排序都依赖这个索引。数据量在百万级以内这套SQL基本是毫秒到秒级如果到千万级以上还是考虑把数据同步到数仓用Hive/Spark跑不要让在线库承担这种分析型查询。还有一个小技巧业务如果只需要看最近N天的连续登录可以在最内层先加时间条件。比如只需要看近30天就WHERE login_date DATE_SUB(CURDATE(), INTERVAL 29 DAY)把数据量提前砍掉再开窗。但前面说的口径问题要自己把握好跨窗口边界的连续段会不会被截断一定要和业务确认。最后说一点个人体会这道题我前前后后给不同业务写过很多版本最大的感受是答案不难难的是问清楚连续到底意味着什么。是先按天去重还是留着重复登录次数连续3天是自然日还是活跃日跨周怎么算这些细节不确认SQL写得再漂亮产出的数字也是废的。我现在遇到类似需求都会先把这两件事写在需求文档第一行按什么粒度去重、连续按什么口径切分。确认完再写SQL基本不会被返工。这个习惯也是这道经典题真正教会我的东西。