连续登录统计这道SQL题我愿称之为数据岗位的全民必考题。这几年不管是在面试候选人的简历筛选里还是在日常业务分析中我见过它以各种面目出现面试官让你现场写统计连续3天登录的用户业务方突然来一句能不能拉一下最近连续7天活跃的用户名单甚至偶尔还有运营同事拿着Excel跑来问这种连续日期咋算的。本质上都是同一个问题给定一张用户登录记录表怎么算出每个用户出现过连续日期段。这类问题之所以经典是因为它考察的并非某个冷门函数而是你对SQL处理序列关系的核心理解。你不需要复杂的数据结构不需要存储过程只需要抓住一个关键数学事实连续的日期减去一个等差序列会落在同一个桶里。这个日期减行号的套路一套出来问题就变得极其简单。这篇文章我会把完整的建表语句、测试数据、Hive和MySQL双版本的核心SQL、常见的面试变体、以及我实际踩过的坑全部交代清楚保证你看完能直接抄作业也能理解背后每一步的为什么。1. 这道经典题到底在考什么1.1 问题定义与业务场景先把题目说清楚。假设有一张登录记录表大致长这样用户ID登录日期1012024-01-011012024-01-021022024-01-011022024-01-03现在要回答的问题是每个用户历史上最长连续登录了多少天或者哪些用户存在过连续3天及以上登录的行为甚至更进一步把每个用户的所有连续登录区间都列出来各自持续多少天。这个需求在真实业务里特别常见。运营要做连续签到N天送积分的活动需要先算出哪些用户满足连续N天产品要看用户粘性想知道有多少用户连续一周每天都使用产品风控要看异常注册连续多天密集登录的小号也经常靠这个来识别。可以说判断日期是否连续是数据分析里最基础、最高频的日期序列操作之一。而连续这个性质恰恰是SQL比较难直接表达的东西。SQL擅长的是等值判断、范围判断、分组聚合但不擅长表达序列中相邻元素的差值是否为1这种关系。标题里说的经典经典就经典在这里它逼着你找到一种方法把不是等值关系的连续性转化成可以用GROUP BY处理的等值关系。1.2 核心破题思路把连续变成分组我来用一个极其直观的例子说说这个转换的数学本质。假设你有一串日期1号、2号、3号、5号、6号、10号。这是一串中间断开的日期。现在你给每个日期按从小到大的顺序编号1号排第12号排第23号排第35号排第46号排第510号排第6。然后你计算日期减去编号这个差值1号减1得到2023-12-312号减2得到2023-12-313号减3得到2023-12-315号减4得到2024-01-016号减5得到2024-01-0110号减6得到2024-01-04发现没有凡是连续的日期它们的日期减编号结果完全相等一旦出现断档结果就跳变了。1号到3号是连续的三行减出来都是同一天到5号因为跳过了4号减出的结果突然变大形成了新的组。这不是巧合。因为连续日期本身就是按天为步长的等差序列而编号也是按1递增的等差序列。两个等差序列相减差的还是一个等差序列而且步长抵消后步距变成0也就是恒定的常数。一旦中间断了一天日期序列的递增量从1变成了2而编号仍然只加1两者差就会产生一个跳跃。这个数学原理就是整个解法最核心的魔法。看懂这一层你就不需要死记SQL而是可以灵活应对无数变体。比如把天换成月判断连续月份把日期换成第几周判断连续周思路完全一样只是把具体单位换掉而已。2. 建表与测试数据先把地基打好2.1 MySQL与Hive通用建表语句好原理讲完我们动手写SQL。第一步是建一张登录表。我用最简洁的结构一个用户ID一个登录日期。-- 适用于MySQL / Hive CREATE TABLE user_login ( user_id INT, login_date DATE );如果是Hive生产环境里这张表大概率是分区表按日期分区。但做逻辑验证和学习不分区反而更直观先把核心逻辑跑通再套到分区表上不迟。实际项目中你通常还需要考虑登录来源、设备类型等维度但和连续登录这个命题无关这里一律省略。2.2 造一批能覆盖各种情况的测试数据空表没法验证逻辑我给你一组精心设计的测试数据。这组数据不是随便造的它覆盖了几种关键情况同一用户有连续段、有断开点、有单日孤点、有多个用户、以及有同一天重复登录的记录。INSERT INTO user_login (user_id, login_date) VALUES (101, 2024-01-01), (101, 2024-01-02), (101, 2024-01-03), (101, 2024-01-05), (101, 2024-01-06), (101, 2024-01-10), (102, 2024-01-01), (102, 2024-01-02), (102, 2024-01-04), (102, 2024-01-05), (102, 2024-01-06), (102, 2024-01-07), (103, 2024-01-01), (103, 2024-01-05), (104, 2024-01-02), (104, 2024-01-02), (104, 2024-01-03);我来说说这组数据里埋的伏笔。用户101有1-3号连续3天然后5-6号连续2天最后10号单独一天适合验证多段连续区间拆分。用户102有1-2号连续2天然后4-7号连续4天适合验证最长连续天数的计算。用户103完全没连续全是孤日。用户104是最阴险的情况2号出现了两条记录同一天登录了两次如果不做去重直接算一定出问题。后面第五节我会专门讲这个坑你先记住104这个伏笔。2.3 日期字段的类型与格式陷阱建表时login_date用DATE类型这是最标准、最省心的选择。但实际工作中我从没见过哪张业务表里日期是干干净净的DATE类型更多的可能是这样几种情况时间戳BIGINT、带时分秒的DATETIME、或者干脆是字符串2024-01-01。这几种类型都会给连续判断带来额外麻烦。时间戳需要先做FROM_UNIXTIME或者DATE(FROM_UNIXTIME(...))转成日期再做DATE_FORMAT截断到天DATETIME需要用DATE()函数去掉时分秒字符串则要保证格式统一最好直接用DATE()函数做一次显式转换让后续的日期加减函数能正确识别。这里我给出一个经验法则进入计算之前先统一把所有日期字段处理成纯日期即DATE类型并且归一化到天。这一步不做后面DATE_SUB和DATEDIFF算出来的结果可能完全不可信。我自己吃过亏曾经有一张表日期字段是DATETIME同一时间的多条记录、跨天边缘的记录算出来的连续天数莫名其妙差一天排查了半天最后发现是时分秒在捣鬼。3. 核心解法日期减行号的分组魔法3.1 第一步用ROW_NUMBER给每个用户的日期排序编号进入正题。核心SQL分两步第一步先使用窗口函数ROW_NUMBER()按用户分区、按日期排序生成一个从1开始递增的序号。SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login;这个seq就是我在第一节说的编号。PARTITION BY user_id的意思是每个用户独立编号互不影响。ORDER BY login_date保证编号顺序和日期顺序一致这一点极其重要如果日期从小到大排序编号也从小到大才是严格的两个等差序列。跑一下上面的查询用户101的结果应该是这样user_idlogin_dateseq1012024-01-0111012024-01-0221012024-01-0331012024-01-0541012024-01-0651012024-01-106注意看101的日期不是连续的但seq仍然是连续的1到6。这就是整个解法的关键伏笔日期断档编号不断档。3.2 第二步DATE_SUB让连续日期落进同一个组接着把编号从日期里减掉。MySQL的写法是DATE_SUBHive的写法是date_sub两者的参数略有差异这个下一节细说。SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp -- MySQL写法 FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t;这段子查询里我们把第一步的结果当成一张新表然后对每一行计算日期减编号。减完的结果我习惯给它起名叫grp含义就是连续分组标识。用户101的结果user_idlogin_dateseqgrp1012024-01-0112023-12-311012024-01-0222023-12-311012024-01-0332023-12-311012024-01-0542024-01-011012024-01-0652024-01-011012024-01-1062024-01-04grp这一列的规律已经肉眼可见了1-3号三行的grp完全相同正好对应那段连续3天的区间5-6号两行grp相同对应连续2天的区间10号单独一行是一个长度为1的区间。连续与不连续全部被grp这个字段区分开了。3.3 第三步GROUP BY收尾统计连续天数有了grp这个字段剩下的就是最简单的GROUP BY聚合了。SELECT user_id, grp, COUNT(*) AS continuous_days, MIN(login_date) AS start_date, MAX(login_date) AS end_date FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t1 ) t2 GROUP BY user_id, grp;最终结果里每一行就是一个连续登录区间。101用户会得到三段连续3天1-3号、连续2天5-6号、连续1天10号。COUNT(*)就是区间长度MIN和MAX分别是这个连续段的起止日期业务上往往也需要这两个日期所以一次算出来不亏。这个三层嵌套看着有点啰嗦但每一层职责清晰最内层生成编号中间层计算分组标识最外层聚合统计。如果是Hive或者MySQL 8.0以上你可以用CTEWITH语法把三层拆成顺序步骤可读性会好很多我后面演示。提示DATE_SUB(login_date, INTERVAL seq DAY) 的本质就是在日期上减去seq天。登录日期是2024-01-05seq是4减完得到2024-01-01。如果你理解成把日期倒回去几天就容易直觉上理解为什么连续的日期落在同一个点了。3.4 Hive与MySQL的语法差异对照Hive和MySQL在核心逻辑上一模一样差别主要在函数签名上。我直接给一份对照步骤MySQL8.0Hive行号函数ROW_NUMBER() OVER (...)完全相同日期减天数DATE_SUB(login_date, INTERVAL seq DAY)date_sub(login_date, seq)日期差DATEDIFF(d1, d2)datediff(d1, d2)Hive的date_sub只接受两个参数直接写天数就行不需要INTERVAL关键字。另外Hive的日期运算在很多版本里要求参数是DATE或STRING类型如果传入的是时间戳先得用from_unixtime转一次这个细节经常有人踩。MySQL 8.0已经完全支持窗口函数所以上面的写法可以直接跑如果你还在用MySQL 5.7及以下窗口函数是没有的我第四节专门给一个替代方案。完整的Hive写法SELECT user_id, grp, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, seq) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t1 ) t2 GROUP BY user_id, grp;Hive里如果你还要返回起止日期也可以用MIN(login_date)和MAX(login_date)逻辑和MySQL一致。注意Hive对GROUP BY的字段有严格限制grp必须出现在GROUP BY里这个和MySQL的ONLY_FULL_GROUP_BY开启时的规则一样。4. 场景扩展面试官最爱追问的几个变体只写出基础版还不够面试和真实业务里几乎都会在基础版上做变形。我把高频的变体整理了一下每个都给出可直接跑的SQL。4.1 只想要连续登录N天以上的用户业务上最常见的是找出连续登录超过3天的用户。在基础版上面加一层HAVING就行。SELECT user_id, grp, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t1 ) t2 GROUP BY user_id, grp HAVING COUNT(*) 3;注意HAVING过滤的是每个连续区间的长度不是用户的总登录天数。同一个用户有多个区间只要其中一段达到3天就会被查出来。如果你要的是全部连续区间都小于N天的用户除外那就需要在用户维度上再做一次MAX聚合把区间长度取最大值后再过滤。这两种口径差别很大接需求的时候一定要先和业务方确认清楚。4.2 计算每个用户的最长连续登录天数另一个高频问题是给我每个用户最长连续登录过多少天。这个直接在区间聚合的基础上再按用户聚合一次。SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM ( SELECT user_id, grp, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t1 ) t2 GROUP BY user_id, grp ) t3 GROUP BY user_id;这一步其实就是连续区间的长度再取一次MAX。实际跑下来101用户的结果是3102用户的结果是4103是1104是2。这个口径在很多报表里叫做最长连续活跃天数是用户粘性分析的关键指标比月活天数更能反映用户习惯的稳定性。4.3 同一天多次登录必须先去重我之前在测试数据里埋了一个雷用户104在2024-01-02有两条记录。如果直接按上面的SQL跑104的seq会变成1号排第12号的重复记录排第2和排第33号排第4。这会导致什么后果1号减1、2号减2、2号减3、3号减4算出来的grp五花八门原本连续的两天会被拦腰截断甚至可能算出来一个根本不存在的连续3天。解法非常简单先对原始表做一次DISTINCT去重把每个用户每天只保留一条记录再做后续计算。这一步不是可选项是必选项。WITH distinct_login AS ( SELECT DISTINCT user_id, login_date FROM user_login ), t1 AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM distinct_login ), t2 AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp FROM t1 ) SELECT user_id, grp, COUNT(*) AS continuous_days, MIN(login_date) AS start_date, MAX(login_date) AS end_date FROM t2 GROUP BY user_id, grp;这段我也顺便演示了CTE写法在Hive和MySQL 8.0里都能跑。逻辑和嵌套子查询完全一样但阅读顺序是从上往下的明显舒服很多。我在实际写复杂查询时只要引擎支持CTE基本都用这种写法后期维护的人会感谢你。4.4 MySQL 5.x老版本的变量替代方案窗口函数是MySQL 8.0才有的。如果你的环境还在5.7甚至更老就要用用户变量模拟窗口函数。思路是按用户和日期排序后用变量记录上一个用户ID、上一个日期然后判断当前行和上一行是否构成连续关系从而生成分组编号。SELECT user_id, grp, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, grp : IF( prev_user user_id AND DATEDIFF(login_date, prev_date) 1, grp, grp 1 ) AS grp, prev_user : user_id, prev_date : login_date FROM ( SELECT user_id, login_date FROM user_login ORDER BY user_id, login_date ) t CROSS JOIN (SELECT grp : 0, prev_user : NULL, prev_date : NULL) r ) t2 GROUP BY user_id, grp;这个写法要注意一个巨大的坑MySQL对SELECT中变量的赋值顺序没有硬性保证特别是同一SELECT列表里多个变量互相依赖的时候结果可能不稳定。我在MySQL 5.6上跑通过换到5.7某些版本就出现上一行数据滞后的问题。所以这个方案我只建议在无法升级环境的紧急场景下临时用而且必须把数据量控制小一点在真实生产环境里用它做全量统计风险很高。有条件的该升级就升级。4.5 变体连续月份怎么算把天换成月核心思路不变但细节有坑。如果某用户在2024-01-31登录了一次2024-02-28登录了一次这两次是连续月份吗从日期上算差了28天但从月份上算差了1个月。处理方式有两种一是用DATE_FORMAT(login_date, %Y-%m)把日期截断到月份然后对月份序号做ROW_NUMBER用月份差来减二是直接用PERIOD_DIFF之类的函数判断月份差是否为1。但在做日期减编号时不能直接用DATE_SUB减天数因为月不是固定天数。我的建议是先把日期转成月份字符串月份序号两个字段用一个连续的月份编号比如从2024年1月到2024年12月对应1到12参与加减运算。这样跳脱了日期函数的限制逻辑反而更清晰。这种把自然时间单位映射为连续编号的思想能处理周、月、季度甚至财年。本质上你只要想清楚什么单位的连续性就把对应的编号序列构造出来。5. 常见问题与排查技巧实录这一节全是我的实战经验。下面的问题我全部在真实环境中遇到过有些甚至排查到凌晨。5.1 算出的连续天数比肉眼看的少优先怀疑重复数据现象某个用户明明连续登录了5天但SQL算出来最长只有3天。这种通常就是原始登录表里某些日期有多条记录。比如用户某天用App登录了一次、又用网页登录了一次业务日志里记录了两行。加了ROW_NUMBER之后重复日期破坏了日期与编号等差对应的关系。排查手法很直接先对原始表按用户、日期做GROUP BY看看有没有COUNT(*)大于1的分组SELECT user_id, login_date, COUNT(*) FROM user_login GROUP BY user_id, login_date HAVING COUNT(*) 1;如果查出来有重复就在计算前的CTE里加上DISTINCT一劳永逸。但这里我要多提醒一句DISTINCT去重之前先确认你对同一天多次登录算不算连续的业务口径。签到场景通常算1次但某些计费、时长分析的场景可能需要保留多次记录并另行处理不要盲目去重。5.2 日期字段是字符串隐式转换带来的偏差很多时候登录日期是STRING类型比如2024/01/01或者2024-01-01 09:30:00。DATE_SUB和DATEDIFF遇到格式不标准的字符串会出现两种情况要么直接报错要么隐式转换后得出完全错误的日期。最大的坑是2024/01/01这种斜杠格式MySQL在严格模式下会报错非严格模式下会转成NULL而NULL参与运算后整个分组全乱。防御办法是在预处理阶段统一日期格式SELECT user_id, DATE(STR_TO_DATE(login_date, %Y/%m/%d)) AS login_date FROM raw_login;如果是带时分秒的就先转成DATETIME再截断到天。我建议把日期清洗单独做成一个前置步骤不要让核心分组逻辑去容忍脏数据。核心逻辑保持干净出问题才好排查。5.3 数据量大时先看清执行计划窗口函数ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)在数据量大时主要成本在PARTITION BY和ORDER BY底层往往对应一次排序或哈希分发。在Hive上如果这张表按登录日期分区而你只想统计最近30天的登录行为应先加分区过滤条件缩小数据扫描范围再跑窗口函数。在MySQL上则可以给(user_id, login_date)建一个联合索引让排序走索引避免文件排序。判断是否走了索引MySQL可以用EXPLAINHive可以用EXPLAIN查看执行计划重点看Sort Operator和Reduce阶段的数据量。我曾经在一个千万级登录表上用这套SQL统计全量用户的最长连续登录天数最初跑了十几分钟加上分区裁剪和去重优化后压到了两分钟以内。对于这种非高频的批量分析任务能省则省。5.4 结果永远多出一段长度为1的连续不少新手第一次跑完发现每个用户都有一段连续1天的记录比如101用户多出一行连续1天看起来突兀。这不是Bug而是单日登录本身也是一个合法的连续区间。如果你只想关心真正有意义的多日连续在最终外层加HAVING COUNT(*) 2即可。但如果你想统计最长连续登录天数单日段不应该被忽略因为有些用户历史最长连续就是1天业务上代表从未有过连续登录行为。口径选择要跟业务对齐不要自己拍脑袋。5.5 排查思路速查表异常现象可能原因优先排查方向连续天数偏小同一天重复记录按用户日期做GROUP BY查COUNT结果出现NULL分组日期字段含空值或转格式失败检查DATE()转换与原表数据质量日期差一天错乱日期字段含时分秒未截断到天DATE()函数归一化后再计算部分用户数据缺失用户ID分区字段有NULL过滤或补充user_id查询极慢无索引、扫描全表、排序成本高加索引、分区裁剪、限制数据范围注意我在实际排查时永远先从数据质量入手而不是先怀疑SQL语法。这套逻辑本身不复杂绝大多数问题都出在数据比想象中脏。6. 个人操作心得与最后一点建议关于连续登录统计我最后分享几个自己的操作习惯。第一我会把去重无条件写在最前面哪怕当前数据看起来没有重复因为接手的表经常在别处被重复写入宁可多一次DISTINCT也不要让结果带着隐患。第二我会用CTE把步骤拆成命名清晰的子块比如distinct_login、sorted_login、grouped_login这样三个月后再回来看还能一眼看懂当时在干什么。第三结果输出时我在业务报表里习惯带上start_date和end_date这比单纯输出一个连续天数有用得多业务方经常后续还要按区间筛选、圈人。另外给一个扩展方向假如业务要的是每周连续活跃或者每个自然月内连续工作日活跃你只需要把日期映射到对应的周序号或工作日序号再套同一套编号减序号逻辑。我在一个项目里甚至用它处理过连续交易周的渠道商统计问题形态一模一样换成周粒度之后直接复用。真理解了我们说的这个数学原理这套解法能覆盖至少七八种看起来完全不同的连续性问题。如果这篇文章能让你从背过这个SQL进阶到理解为什么要这么算我觉得就值了。下一次遇到类似需求你不需要再翻笔记直接照着这个思路切片、编号、相减、分组四步走完。
连续登录统计这道SQL题我愿称之为数据岗位的全民必考题。这几年不管是在面试候选人的简历筛选里还是在日常业务分析中我见过它以各种面目出现面试官让你现场写统计连续3天登录的用户业务方突然来一句能不能拉一下最近连续7天活跃的用户名单甚至偶尔还有运营同事拿着Excel跑来问这种连续日期咋算的。本质上都是同一个问题给定一张用户登录记录表怎么算出每个用户出现过连续日期段。这类问题之所以经典是因为它考察的并非某个冷门函数而是你对SQL处理序列关系的核心理解。你不需要复杂的数据结构不需要存储过程只需要抓住一个关键数学事实连续的日期减去一个等差序列会落在同一个桶里。这个日期减行号的套路一套出来问题就变得极其简单。这篇文章我会把完整的建表语句、测试数据、Hive和MySQL双版本的核心SQL、常见的面试变体、以及我实际踩过的坑全部交代清楚保证你看完能直接抄作业也能理解背后每一步的为什么。1. 这道经典题到底在考什么1.1 问题定义与业务场景先把题目说清楚。假设有一张登录记录表大致长这样用户ID登录日期1012024-01-011012024-01-021022024-01-011022024-01-03现在要回答的问题是每个用户历史上最长连续登录了多少天或者哪些用户存在过连续3天及以上登录的行为甚至更进一步把每个用户的所有连续登录区间都列出来各自持续多少天。这个需求在真实业务里特别常见。运营要做连续签到N天送积分的活动需要先算出哪些用户满足连续N天产品要看用户粘性想知道有多少用户连续一周每天都使用产品风控要看异常注册连续多天密集登录的小号也经常靠这个来识别。可以说判断日期是否连续是数据分析里最基础、最高频的日期序列操作之一。而连续这个性质恰恰是SQL比较难直接表达的东西。SQL擅长的是等值判断、范围判断、分组聚合但不擅长表达序列中相邻元素的差值是否为1这种关系。标题里说的经典经典就经典在这里它逼着你找到一种方法把不是等值关系的连续性转化成可以用GROUP BY处理的等值关系。1.2 核心破题思路把连续变成分组我来用一个极其直观的例子说说这个转换的数学本质。假设你有一串日期1号、2号、3号、5号、6号、10号。这是一串中间断开的日期。现在你给每个日期按从小到大的顺序编号1号排第12号排第23号排第35号排第46号排第510号排第6。然后你计算日期减去编号这个差值1号减1得到2023-12-312号减2得到2023-12-313号减3得到2023-12-315号减4得到2024-01-016号减5得到2024-01-0110号减6得到2024-01-04发现没有凡是连续的日期它们的日期减编号结果完全相等一旦出现断档结果就跳变了。1号到3号是连续的三行减出来都是同一天到5号因为跳过了4号减出的结果突然变大形成了新的组。这不是巧合。因为连续日期本身就是按天为步长的等差序列而编号也是按1递增的等差序列。两个等差序列相减差的还是一个等差序列而且步长抵消后步距变成0也就是恒定的常数。一旦中间断了一天日期序列的递增量从1变成了2而编号仍然只加1两者差就会产生一个跳跃。这个数学原理就是整个解法最核心的魔法。看懂这一层你就不需要死记SQL而是可以灵活应对无数变体。比如把天换成月判断连续月份把日期换成第几周判断连续周思路完全一样只是把具体单位换掉而已。2. 建表与测试数据先把地基打好2.1 MySQL与Hive通用建表语句好原理讲完我们动手写SQL。第一步是建一张登录表。我用最简洁的结构一个用户ID一个登录日期。-- 适用于MySQL / Hive CREATE TABLE user_login ( user_id INT, login_date DATE );如果是Hive生产环境里这张表大概率是分区表按日期分区。但做逻辑验证和学习不分区反而更直观先把核心逻辑跑通再套到分区表上不迟。实际项目中你通常还需要考虑登录来源、设备类型等维度但和连续登录这个命题无关这里一律省略。2.2 造一批能覆盖各种情况的测试数据空表没法验证逻辑我给你一组精心设计的测试数据。这组数据不是随便造的它覆盖了几种关键情况同一用户有连续段、有断开点、有单日孤点、有多个用户、以及有同一天重复登录的记录。INSERT INTO user_login (user_id, login_date) VALUES (101, 2024-01-01), (101, 2024-01-02), (101, 2024-01-03), (101, 2024-01-05), (101, 2024-01-06), (101, 2024-01-10), (102, 2024-01-01), (102, 2024-01-02), (102, 2024-01-04), (102, 2024-01-05), (102, 2024-01-06), (102, 2024-01-07), (103, 2024-01-01), (103, 2024-01-05), (104, 2024-01-02), (104, 2024-01-02), (104, 2024-01-03);我来说说这组数据里埋的伏笔。用户101有1-3号连续3天然后5-6号连续2天最后10号单独一天适合验证多段连续区间拆分。用户102有1-2号连续2天然后4-7号连续4天适合验证最长连续天数的计算。用户103完全没连续全是孤日。用户104是最阴险的情况2号出现了两条记录同一天登录了两次如果不做去重直接算一定出问题。后面第五节我会专门讲这个坑你先记住104这个伏笔。2.3 日期字段的类型与格式陷阱建表时login_date用DATE类型这是最标准、最省心的选择。但实际工作中我从没见过哪张业务表里日期是干干净净的DATE类型更多的可能是这样几种情况时间戳BIGINT、带时分秒的DATETIME、或者干脆是字符串2024-01-01。这几种类型都会给连续判断带来额外麻烦。时间戳需要先做FROM_UNIXTIME或者DATE(FROM_UNIXTIME(...))转成日期再做DATE_FORMAT截断到天DATETIME需要用DATE()函数去掉时分秒字符串则要保证格式统一最好直接用DATE()函数做一次显式转换让后续的日期加减函数能正确识别。这里我给出一个经验法则进入计算之前先统一把所有日期字段处理成纯日期即DATE类型并且归一化到天。这一步不做后面DATE_SUB和DATEDIFF算出来的结果可能完全不可信。我自己吃过亏曾经有一张表日期字段是DATETIME同一时间的多条记录、跨天边缘的记录算出来的连续天数莫名其妙差一天排查了半天最后发现是时分秒在捣鬼。3. 核心解法日期减行号的分组魔法3.1 第一步用ROW_NUMBER给每个用户的日期排序编号进入正题。核心SQL分两步第一步先使用窗口函数ROW_NUMBER()按用户分区、按日期排序生成一个从1开始递增的序号。SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login;这个seq就是我在第一节说的编号。PARTITION BY user_id的意思是每个用户独立编号互不影响。ORDER BY login_date保证编号顺序和日期顺序一致这一点极其重要如果日期从小到大排序编号也从小到大才是严格的两个等差序列。跑一下上面的查询用户101的结果应该是这样user_idlogin_dateseq1012024-01-0111012024-01-0221012024-01-0331012024-01-0541012024-01-0651012024-01-106注意看101的日期不是连续的但seq仍然是连续的1到6。这就是整个解法的关键伏笔日期断档编号不断档。3.2 第二步DATE_SUB让连续日期落进同一个组接着把编号从日期里减掉。MySQL的写法是DATE_SUBHive的写法是date_sub两者的参数略有差异这个下一节细说。SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp -- MySQL写法 FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t;这段子查询里我们把第一步的结果当成一张新表然后对每一行计算日期减编号。减完的结果我习惯给它起名叫grp含义就是连续分组标识。用户101的结果user_idlogin_dateseqgrp1012024-01-0112023-12-311012024-01-0222023-12-311012024-01-0332023-12-311012024-01-0542024-01-011012024-01-0652024-01-011012024-01-1062024-01-04grp这一列的规律已经肉眼可见了1-3号三行的grp完全相同正好对应那段连续3天的区间5-6号两行grp相同对应连续2天的区间10号单独一行是一个长度为1的区间。连续与不连续全部被grp这个字段区分开了。3.3 第三步GROUP BY收尾统计连续天数有了grp这个字段剩下的就是最简单的GROUP BY聚合了。SELECT user_id, grp, COUNT(*) AS continuous_days, MIN(login_date) AS start_date, MAX(login_date) AS end_date FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t1 ) t2 GROUP BY user_id, grp;最终结果里每一行就是一个连续登录区间。101用户会得到三段连续3天1-3号、连续2天5-6号、连续1天10号。COUNT(*)就是区间长度MIN和MAX分别是这个连续段的起止日期业务上往往也需要这两个日期所以一次算出来不亏。这个三层嵌套看着有点啰嗦但每一层职责清晰最内层生成编号中间层计算分组标识最外层聚合统计。如果是Hive或者MySQL 8.0以上你可以用CTEWITH语法把三层拆成顺序步骤可读性会好很多我后面演示。提示DATE_SUB(login_date, INTERVAL seq DAY) 的本质就是在日期上减去seq天。登录日期是2024-01-05seq是4减完得到2024-01-01。如果你理解成把日期倒回去几天就容易直觉上理解为什么连续的日期落在同一个点了。3.4 Hive与MySQL的语法差异对照Hive和MySQL在核心逻辑上一模一样差别主要在函数签名上。我直接给一份对照步骤MySQL8.0Hive行号函数ROW_NUMBER() OVER (...)完全相同日期减天数DATE_SUB(login_date, INTERVAL seq DAY)date_sub(login_date, seq)日期差DATEDIFF(d1, d2)datediff(d1, d2)Hive的date_sub只接受两个参数直接写天数就行不需要INTERVAL关键字。另外Hive的日期运算在很多版本里要求参数是DATE或STRING类型如果传入的是时间戳先得用from_unixtime转一次这个细节经常有人踩。MySQL 8.0已经完全支持窗口函数所以上面的写法可以直接跑如果你还在用MySQL 5.7及以下窗口函数是没有的我第四节专门给一个替代方案。完整的Hive写法SELECT user_id, grp, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, date_sub(login_date, seq) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t1 ) t2 GROUP BY user_id, grp;Hive里如果你还要返回起止日期也可以用MIN(login_date)和MAX(login_date)逻辑和MySQL一致。注意Hive对GROUP BY的字段有严格限制grp必须出现在GROUP BY里这个和MySQL的ONLY_FULL_GROUP_BY开启时的规则一样。4. 场景扩展面试官最爱追问的几个变体只写出基础版还不够面试和真实业务里几乎都会在基础版上做变形。我把高频的变体整理了一下每个都给出可直接跑的SQL。4.1 只想要连续登录N天以上的用户业务上最常见的是找出连续登录超过3天的用户。在基础版上面加一层HAVING就行。SELECT user_id, grp, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t1 ) t2 GROUP BY user_id, grp HAVING COUNT(*) 3;注意HAVING过滤的是每个连续区间的长度不是用户的总登录天数。同一个用户有多个区间只要其中一段达到3天就会被查出来。如果你要的是全部连续区间都小于N天的用户除外那就需要在用户维度上再做一次MAX聚合把区间长度取最大值后再过滤。这两种口径差别很大接需求的时候一定要先和业务方确认清楚。4.2 计算每个用户的最长连续登录天数另一个高频问题是给我每个用户最长连续登录过多少天。这个直接在区间聚合的基础上再按用户聚合一次。SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM ( SELECT user_id, grp, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp FROM ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM user_login ) t1 ) t2 GROUP BY user_id, grp ) t3 GROUP BY user_id;这一步其实就是连续区间的长度再取一次MAX。实际跑下来101用户的结果是3102用户的结果是4103是1104是2。这个口径在很多报表里叫做最长连续活跃天数是用户粘性分析的关键指标比月活天数更能反映用户习惯的稳定性。4.3 同一天多次登录必须先去重我之前在测试数据里埋了一个雷用户104在2024-01-02有两条记录。如果直接按上面的SQL跑104的seq会变成1号排第12号的重复记录排第2和排第33号排第4。这会导致什么后果1号减1、2号减2、2号减3、3号减4算出来的grp五花八门原本连续的两天会被拦腰截断甚至可能算出来一个根本不存在的连续3天。解法非常简单先对原始表做一次DISTINCT去重把每个用户每天只保留一条记录再做后续计算。这一步不是可选项是必选项。WITH distinct_login AS ( SELECT DISTINCT user_id, login_date FROM user_login ), t1 AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS seq FROM distinct_login ), t2 AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL seq DAY) AS grp FROM t1 ) SELECT user_id, grp, COUNT(*) AS continuous_days, MIN(login_date) AS start_date, MAX(login_date) AS end_date FROM t2 GROUP BY user_id, grp;这段我也顺便演示了CTE写法在Hive和MySQL 8.0里都能跑。逻辑和嵌套子查询完全一样但阅读顺序是从上往下的明显舒服很多。我在实际写复杂查询时只要引擎支持CTE基本都用这种写法后期维护的人会感谢你。4.4 MySQL 5.x老版本的变量替代方案窗口函数是MySQL 8.0才有的。如果你的环境还在5.7甚至更老就要用用户变量模拟窗口函数。思路是按用户和日期排序后用变量记录上一个用户ID、上一个日期然后判断当前行和上一行是否构成连续关系从而生成分组编号。SELECT user_id, grp, COUNT(*) AS continuous_days FROM ( SELECT user_id, login_date, grp : IF( prev_user user_id AND DATEDIFF(login_date, prev_date) 1, grp, grp 1 ) AS grp, prev_user : user_id, prev_date : login_date FROM ( SELECT user_id, login_date FROM user_login ORDER BY user_id, login_date ) t CROSS JOIN (SELECT grp : 0, prev_user : NULL, prev_date : NULL) r ) t2 GROUP BY user_id, grp;这个写法要注意一个巨大的坑MySQL对SELECT中变量的赋值顺序没有硬性保证特别是同一SELECT列表里多个变量互相依赖的时候结果可能不稳定。我在MySQL 5.6上跑通过换到5.7某些版本就出现上一行数据滞后的问题。所以这个方案我只建议在无法升级环境的紧急场景下临时用而且必须把数据量控制小一点在真实生产环境里用它做全量统计风险很高。有条件的该升级就升级。4.5 变体连续月份怎么算把天换成月核心思路不变但细节有坑。如果某用户在2024-01-31登录了一次2024-02-28登录了一次这两次是连续月份吗从日期上算差了28天但从月份上算差了1个月。处理方式有两种一是用DATE_FORMAT(login_date, %Y-%m)把日期截断到月份然后对月份序号做ROW_NUMBER用月份差来减二是直接用PERIOD_DIFF之类的函数判断月份差是否为1。但在做日期减编号时不能直接用DATE_SUB减天数因为月不是固定天数。我的建议是先把日期转成月份字符串月份序号两个字段用一个连续的月份编号比如从2024年1月到2024年12月对应1到12参与加减运算。这样跳脱了日期函数的限制逻辑反而更清晰。这种把自然时间单位映射为连续编号的思想能处理周、月、季度甚至财年。本质上你只要想清楚什么单位的连续性就把对应的编号序列构造出来。5. 常见问题与排查技巧实录这一节全是我的实战经验。下面的问题我全部在真实环境中遇到过有些甚至排查到凌晨。5.1 算出的连续天数比肉眼看的少优先怀疑重复数据现象某个用户明明连续登录了5天但SQL算出来最长只有3天。这种通常就是原始登录表里某些日期有多条记录。比如用户某天用App登录了一次、又用网页登录了一次业务日志里记录了两行。加了ROW_NUMBER之后重复日期破坏了日期与编号等差对应的关系。排查手法很直接先对原始表按用户、日期做GROUP BY看看有没有COUNT(*)大于1的分组SELECT user_id, login_date, COUNT(*) FROM user_login GROUP BY user_id, login_date HAVING COUNT(*) 1;如果查出来有重复就在计算前的CTE里加上DISTINCT一劳永逸。但这里我要多提醒一句DISTINCT去重之前先确认你对同一天多次登录算不算连续的业务口径。签到场景通常算1次但某些计费、时长分析的场景可能需要保留多次记录并另行处理不要盲目去重。5.2 日期字段是字符串隐式转换带来的偏差很多时候登录日期是STRING类型比如2024/01/01或者2024-01-01 09:30:00。DATE_SUB和DATEDIFF遇到格式不标准的字符串会出现两种情况要么直接报错要么隐式转换后得出完全错误的日期。最大的坑是2024/01/01这种斜杠格式MySQL在严格模式下会报错非严格模式下会转成NULL而NULL参与运算后整个分组全乱。防御办法是在预处理阶段统一日期格式SELECT user_id, DATE(STR_TO_DATE(login_date, %Y/%m/%d)) AS login_date FROM raw_login;如果是带时分秒的就先转成DATETIME再截断到天。我建议把日期清洗单独做成一个前置步骤不要让核心分组逻辑去容忍脏数据。核心逻辑保持干净出问题才好排查。5.3 数据量大时先看清执行计划窗口函数ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)在数据量大时主要成本在PARTITION BY和ORDER BY底层往往对应一次排序或哈希分发。在Hive上如果这张表按登录日期分区而你只想统计最近30天的登录行为应先加分区过滤条件缩小数据扫描范围再跑窗口函数。在MySQL上则可以给(user_id, login_date)建一个联合索引让排序走索引避免文件排序。判断是否走了索引MySQL可以用EXPLAINHive可以用EXPLAIN查看执行计划重点看Sort Operator和Reduce阶段的数据量。我曾经在一个千万级登录表上用这套SQL统计全量用户的最长连续登录天数最初跑了十几分钟加上分区裁剪和去重优化后压到了两分钟以内。对于这种非高频的批量分析任务能省则省。5.4 结果永远多出一段长度为1的连续不少新手第一次跑完发现每个用户都有一段连续1天的记录比如101用户多出一行连续1天看起来突兀。这不是Bug而是单日登录本身也是一个合法的连续区间。如果你只想关心真正有意义的多日连续在最终外层加HAVING COUNT(*) 2即可。但如果你想统计最长连续登录天数单日段不应该被忽略因为有些用户历史最长连续就是1天业务上代表从未有过连续登录行为。口径选择要跟业务对齐不要自己拍脑袋。5.5 排查思路速查表异常现象可能原因优先排查方向连续天数偏小同一天重复记录按用户日期做GROUP BY查COUNT结果出现NULL分组日期字段含空值或转格式失败检查DATE()转换与原表数据质量日期差一天错乱日期字段含时分秒未截断到天DATE()函数归一化后再计算部分用户数据缺失用户ID分区字段有NULL过滤或补充user_id查询极慢无索引、扫描全表、排序成本高加索引、分区裁剪、限制数据范围注意我在实际排查时永远先从数据质量入手而不是先怀疑SQL语法。这套逻辑本身不复杂绝大多数问题都出在数据比想象中脏。6. 个人操作心得与最后一点建议关于连续登录统计我最后分享几个自己的操作习惯。第一我会把去重无条件写在最前面哪怕当前数据看起来没有重复因为接手的表经常在别处被重复写入宁可多一次DISTINCT也不要让结果带着隐患。第二我会用CTE把步骤拆成命名清晰的子块比如distinct_login、sorted_login、grouped_login这样三个月后再回来看还能一眼看懂当时在干什么。第三结果输出时我在业务报表里习惯带上start_date和end_date这比单纯输出一个连续天数有用得多业务方经常后续还要按区间筛选、圈人。另外给一个扩展方向假如业务要的是每周连续活跃或者每个自然月内连续工作日活跃你只需要把日期映射到对应的周序号或工作日序号再套同一套编号减序号逻辑。我在一个项目里甚至用它处理过连续交易周的渠道商统计问题形态一模一样换成周粒度之后直接复用。真理解了我们说的这个数学原理这套解法能覆盖至少七八种看起来完全不同的连续性问题。如果这篇文章能让你从背过这个SQL进阶到理解为什么要这么算我觉得就值了。下一次遇到类似需求你不需要再翻笔记直接照着这个思路切片、编号、相减、分组四步走完。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
