当前位置：首页 > news >正文

横扫SQL面试——连续性登录问题

news 来源：原创 2025/7/5 11:01:52

横扫SQL面试

📌 连续性登录问题

在这里插入图片描述

在互联网公司的SQL面试中，连续性问题堪称“必考之王”。💻🔍

用户连续登录7天送优惠券🌟，服务器连续报警3次触发熔断⚠️，图书馆连续3天人流破百开启限流⚡” …

既考察你对窗口函数的灵活运用，又考验你能否将业务场景抽象为数学模型。

博主总结一些经典题型，帮列位小伙伴拿下这类题目🤣 🤣 🤣 🤣

Tips：
暴力解法（如自连接、逐行遍历）在数据量小时勉强可用，但面对百万级📈数据时：

性能灾难：自连接时间复杂度达O(n²)，1万行数据需1亿次计算 🔥
逻辑漏洞：简单lag/lead无法处理连续多天的复杂中断

而真正的工业级解法，只需一行窗口函数 + 虚拟分组标记，就能以O(n)时间复杂度解决问题！ 🚀

🌟 连续问题通用解法框架

步骤	核心操作 🔑	适用场景
生成连续标记	`date - row_number() over(...)`	映射连续日期到同一虚拟组
分组统计	`group by 虚拟组标记`	计算连续天数/次数
结果筛选	`having count(*) >= N`	过滤满足条件的连续事件

话不多说——直接上题：🎈🎈🎈🎈🎈🎈🎈

🎯1. 最长连续登录天数

你正在搭建一个用户活跃度的画像，其中一个与活跃度相关的特征是“最长连续登录天数”

表名	字段名	描述	数据类型
`tb_dau`	`fdate`	登录日期	DATE
	`user_id`	用户唯一标识	INT

计算用户在指定时间段内的最长连续登录天数。例如：统计用户2023年1月的最长连续登录记录。

fdate	user_id
2023-01-01	10000
2023-01-02	10000
2023-01-04	10000

预期结果🔑

user_id	max_consec_days
10000	2

博主按照解题框架一步一步带大家看哈~🤣🤣🤣

步骤1：生成连续标记（CTE `t1`）🧩

为每个用户的登录日期生成序号，标记连续登录的潜在分组。

with t1 as (selectuser_id,fdate,-- 默认大家都是有基础的哈 窗口函数应该都会哈row_number() over(partition by user_id order by fdate) as rnfrom tb_dauwhere fdate between '2023-01-01' and '2023-01-31'
)

临时表 t1：✅

user_id	fdate	rn
10000	2023-01-01	1
10000	2023-01-02	2
10000	2023-01-04	3

在这里插入图片描述

rn 表示用户按日期排序后的登录次数序号。
连续日期的 rn 差值等于日期差值（例如：2023-01-02 是第2次登录，日期差为1天）。

步骤2：计算虚拟起始点（CTE `t2`）🧩

通过 date_sub(fdate, interval rn day) 将连续日期映射到同一虚拟起始点。

t2 as (selectuser_id,fdate,date_sub(fdate, interval rn day) as start_datefrom t1
)

临时表 t2：✅

user_id	fdate	start_date
10000	2023-01-01	2022-12-31
10000	2023-01-02	2022-12-31
10000	2023-01-04	2023-01-01

在这里插入图片描述

连续日期的 start_date 相同（如1月1日和1月2日均映射到2022-12-31）。
非连续日期的 start_date 不同（如1月4日映射到2023-01-01）。

步骤3：统计连续天数（CTE `t3`）🧩

按用户和虚拟起始点分组，统计每组中的记录数（即连续天数）。

t3 as (selectuser_id,start_date,count(*) as cntfrom t2group by user_id, start_date
)

临时表 t3：✅

user_id	start_date	cnt
10000	2022-12-31	2
10000	2023-01-01	1

在这里插入图片描述

cnt 表示每个虚拟起始点对应的连续登录天数。
用户10000有两个连续区间：2天和1天。

最终结果取每个用户的最大连续天数。🧩

selectuser_id,max(cnt) as max_consec_days
from t3
group by user_id;

输出结果：✅

在这里插入图片描述

user_id	max_consec_days
10000	2

技术本质🧩

通过 date_sub(fdate, interval rn day)，将连续日期的差值抵消，映射到同一虚拟起始点：

连续日期：fdate - rn 恒定✨（如 1月1日-1天=12月31日，1月2日-2天=12月31日）。
非连续日期：fdate - rn 不同（如1月4日-3天=1月1日）。

将连续性问题转化为分组计数问题，时间复杂度仅为 O(n)。✨

完整代码 ~

-- 定义第一个公共表表达式 (CTE) t1，用于计算每个用户登录日期的排序
with t1 as (selectuser_id,  -- 用户IDfdate,    -- 登录日期row_number() over(partition by user_id order by fdate) as rn  -- 为每个用户的登录日期生成排序编号from tb_dauwhere fdate between '2023-01-01' and '2023-01-31'  -- 选择指定日期范围内的记录
), -- 定义第二个公共表表达式 (CTE) t2，用于计算每个登录日期的起始日期
t2 as (selectuser_id,  -- 用户IDfdate,    -- 登录日期date_sub(fdate, interval rn day) as start_date  -- 计算起始日期：将登录日期减去排序编号天数from t1
), -- 定义第三个公共表表达式 (CTE) t3，用于计算每个用户在相同起始日期下的连续登录天数
t3 as (selectuser_id,  -- 用户IDcount(*) as cnt  -- 计算连续登录天数from t2group by user_id, start_date  -- 按用户和起始日期分组
)-- 从 t3 表中选择用户ID和其最大连续登录天数
selectuser_id,  -- 用户IDmax(cnt) as max_consec_days  -- 最大连续登录天数
from t3
group by user_id;  -- 按用户ID分组

🎯2. 连续出现的数字

从数字序列中找出至少连续出现3次的数字。例如：[1, 1, 1, 2, 2, 3] 中，1 连续出现3次。

表名	字段名	描述	数据类型
`logs`	`id`	记录序号	INT
	`num`	数字值	INT

id	num
1	1
2	1
3	1
4	2
5	2
6	3

ConsecutiveNums
1

这题同理~连续性问题解法框架：
1.💡 标记连续性：使用 row_number() 生成序号。
2. 🔍生成虚拟组：通过差值（如 id - rn）抵消连续增量。
3. 🛠️分组统计：按虚拟组聚合，筛选满足条件的结果。

步骤1：生成连续标记（CTE `t1`）🚀

为每个数字按 id 排序生成行号，标记连续出现的潜在分组。

with t1 as (selectnum,id,row_number() over(partition by num order by id) as rnfrom logs
)

临时表 t1：✅

num	id	rn
1	1	1
1	2	2
1	3	3
2	4	1
2	5	2
3	6	1

在这里插入图片描述

rn 表示相同数字（num）按 id 排序后的出现次数序号。
连续相同数字的 id 与 rn 的差值恒定（例如：num=1 时，id - rn = 0）。

步骤2：计算虚拟分组标记（CTE `t2`）🚀

通过 id - rn 生成分组标记 group_id，将连续相同数字映射到同一虚拟组。

t2 as (selectnum,id - rn as group_idfrom t1
)

临时表 t2：✅

num	group_id
1	0
1	0
1	0
2	3
2	3
3	5

在这里插入图片描述

连续相同数字的 group_id 相同（如 num=1 的3条记录均为 group_id=0）。
非连续或不同数字的 group_id 不同（如 num=2 和 num=3）。

步骤3：统计连续出现次数（最终查询）🚀

按 num 和 group_id 分组，筛选出出现次数≥3的组，并去重输出结果。

select distinct num as ConsecutiveNums
from t2
group by num, group_id
having count(*) >= 3;

分组统计结果：✅

num	group_id	count(*)
1	0	3
2	3	2
3	5	1

在这里插入图片描述

最终输出：✅

ConsecutiveNums
1

技术本质🚀

通过 id - row_number()，将连续相同数字的差值抵消，映射到同一虚拟分组：

连续相同数字：id - rn 恒定🔥（如 num=1 时，id=1,2,3 → 1-1=0, 2-2=0, 3-3=0）。
非连续或不同数字：id - rn 不同（如 num=2 时，id=4,5 → 4-1=3, 5-2=3，但次数不足）。

如果题目要求连续出现4次，只需修改 having 条件：

having count(*) >= 4  -- 筛选连续出现4次的数字

🎯3. 新注册用户连续登录不少于3天

筛选出新注册用户在注册后至少连续登录3天的用户列表。例如：用户注册后连续登录了2023-01-01、01-02、01-03三天。

表名	字段名	描述	数据类型
`tb_users`	`user_id`	用户唯一标识	INT
	`reg_date`	用户注册日期	DATE
`tb_login`	`user_id`	用户唯一标识	INT
	`login_date`	用户登录日期	DATE

用户表 (tb_users)✅

user_id	reg_date
10001	2023-01-01
10002	2023-01-05

登录表 (tb_login)✅

user_id	login_date
10001	2023-01-01
10001	2023-01-02
10001	2023-01-03
10002	2023-01-05
10002	2023-01-06

预期结果

user_id
10001

步骤1：关联用户与登录数据（CTE `login_sequence`）✅

筛选注册后7天内的登录记录，并为每个用户的登录日期生成行号。

with login_sequence as (selectu.user_id,l.login_date,-- 为每个用户的登录日期生成行号（按日期排序）row_number() over(partition by u.user_id order by l.login_date) as rnfrom tb_users ujoin tb_login l on u.user_id = l.user_idand l.login_date between u.reg_date and u.reg_date + interval 7 day
)

临时表 login_sequence：✅

user_id	login_date	rn
10001	2023-01-01	1
10001	2023-01-02	2
10001	2023-01-03	3
10002	2023-01-05	1
10002	2023-01-06	2

rn 表示用户按登录日期排序后的连续次数。
login_date between reg_date and reg_date + 7 day 限定注册后7天内的登录行为。

限定用户注册后7天内的登录行为，聚焦新用户关键活跃期，数据进入窗口函数前剔除无效数据，避免对全量数据排序。聚焦核心业务目标（如新用户激活率、首周留存率）

步骤2：生成虚拟分组标记（CTE `consec_groups`）🚀

计算 login_date - rn，将连续日期映射到同一虚拟起始点。

consec_groups as (selectuser_id,login_date,-- 计算虚拟分组标记（连续日期的差值为0）date_sub(login_date, interval rn day) as group_idfrom login_sequence
)

临时表 consec_groups：✅

user_id	login_date	group_id
10001	2023-01-01	2022-12-31
10001	2023-01-02	2022-12-31
10001	2023-01-03	2022-12-31
10002	2023-01-05	2023-01-04
10002	2023-01-06	2023-01-04

连续登录的日期差值相同（如用户10001的3次登录均映射到 2022-12-31）。🚀🚀🚀
非连续登录的日期差值不同（如用户10002的2次登录映射到 2023-01-04）。

步骤3：统计连续登录天数（最终查询）

按用户和虚拟分组标记统计连续天数，筛选≥3天的用户。

distinct user_id 确保用户多次满足条件时只输出一次。

select distinct user_id
from consec_groups
group by user_id, group_id
having count(*) >= 3;

分组统计结果：✅

user_id	group_id	count(*)
10001	2022-12-31	3
10002	2023-01-04	2

最终输出：✅

user_id
10001

💡 关键逻辑

虚拟分组标记：
date_sub(login_date, interval rn day) 将连续日期映射到同一虚拟起始点，本质是公式：
连续天数 = 最大登录日期 - 最小登录日期 + 1 （若连续，则 login_date - rn 恒定）

🎯4. 图书馆高峰期检测

找出图书馆连续3天及以上人流量≥100的高峰时段。例如：2023-01-02至2023-01-04连续三天人流量达标。

表名	字段名	描述	数据类型
`info`	`date`	日期	DATE
	`people`	人流量	INT

date	people
2023-01-01	70
2023-01-02	100
2023-01-03	120
2023-01-04	120
2023-01-05	90

预期结果

start_date	end_date	consecutive_days
2023-01-02	2023-01-04	3

后面博主就不再啰嗦啦大家可以发现套路是不是都一样~🤣🤣🤣 “标记→分组→过滤”✅✅✅

with valid_days as (select date,date - row_number() over(order by date) as grpfrom infowhere people >= 100
),
consec_groups as (selectmin(date) as start_date,max(date) as end_date,count(*) as consecutive_daysfrom valid_daysgroup by grphaving count(*) >= 3
)
select * from consec_groups;

筛选有效日期：过滤人流量≥100的天数。
生成连续组标记：date - row_number() 将连续有效日期映射到同一组。
统计连续时段：按组统计起止日期和持续天数。

🎯5. 用户指标检测

从订单表中筛选出连续三天及以上每天总下单金额均超过100元的用户。例如：用户A在2023-01-01至2023-01-03每天的总消费分别为120元、150元、110元，满足条件。

表名	字段名	描述	数据类型
`order_table`	`id`	订单编号	INT
	`dt`	下单日期	DATE
	`amount`	订单金额	INT

id	dt	amount
1001	2021-12-12	123
1002	2021-12-12	45
1001	2021-12-13	43
1001	2021-12-13	45
1001	2021-12-14	230

预期结果

user_id
1001

-- 步骤1：按用户和日期汇总金额，过滤每天金额>100的记录
with daily_summary as (selectid as user_id,dt,sum(amount) as total_amountfrom order_tablegroup by user_id, dthaving sum(amount) > 100
),
-- 步骤2：生成连续标记
sequence_marker as (selectuser_id,dt,date_sub(dt, interval row_number() over(partition by user_id order by dt) day) as grpfrom daily_summary
),
-- 步骤3：统计连续天数
consec_groups as (selectuser_id,grp,count(*) as consec_days,min(dt) as start_date,max(dt) as end_datefrom sequence_markergroup by user_id, grphaving count(*) >= 3  -- 连续3天及以上
)
-- 步骤4：输出结果
select distinct user_id
from consec_groups;

按天汇总金额：group by user_id, dt 处理一天多笔订单。
生成虚拟分组：date_sub(dt, interval row_number() day) 将连续日期映射到同一虚拟组。
统计连续天数：筛选连续≥3天的用户。

🎯6. 用户最大连续缴费次数

计算每个用户的最长连续缴费天数。例如：用户U002在2023-01-03至2023-01-05连续缴费3天，结果为3。

表名	字段名	描述	数据类型
`payment_log`	`user_id`	用户唯一标识	VARCHAR
	`pay_date`	缴费日期	DATE
	`amount`	缴费金额	INT

user_id	pay_date	amount
U001	2023-01-01	100
U001	2023-01-02	200
U001	2023-01-04	150
U002	2023-01-03	80
U002	2023-01-04	90
U002	2023-01-05	120

预期结果

user_id	max_consec_days
U001	2
U002	3

with payment_sequence as (selectuser_id,pay_date,date_sub(pay_date, interval row_number() over(partition by user_id order by pay_date) day) as grpfrom payment_log
),
consec_groups as (selectuser_id,grp,count(*) as consec_daysfrom payment_sequencegroup by user_id, grp
)
selectuser_id,max(consec_days) as max_consec_days
from consec_groups
group by user_id;

生成虚拟分组：date_sub(pay_date, interval row_number() day) 标记连续缴费序列。
统计连续天数：按用户和虚拟组计算连续缴费次数。
取最大值：max(consec_days) 获取每个用户的最大连续天数。

🧩 连续性问题的通用解法框架

步骤	核心操作	适用场景
数据清洗	按业务需求聚合数据（如按天汇总金额）	处理多笔记录/噪声数据
生成连续标记	`date - row_number()` 映射连续日期到虚拟组	统一连续序列的时空标识
分组统计	`group by 虚拟组标记`	计算连续天数/次数
结果筛选	`having` 或 `max()` 过滤目标结果	输出满足条件的用户或时段

这一套组合拳下来✅✅✅ 相信列位面试在遇到连续性登录问题丝毫不慌了😂

留个作业~ 有些难度哈——合并用户停留位置

给定用户位置停留记录，需要对同一个用户在同一个位置的连续多条记录进行合并，合并原则为开始时间取最早时间，停留时长加和。

表名	字段名	字段含义	数据类型
用户位置停留记录表	`user`	用户标识	VARCHAR
	`location`	位置标识	VARCHAR
	`start_time`	停留开始时间	DATETIME
	`stay_duration`	停留的时长（分钟）	INT

user	location	start_time	stay_duration
UserA	LocationA	2018-01-01 08:00:00	60
UserA	LocationA	2018-01-01 09:00:00	60
UserA	LocationB	2018-01-01 10:00:00	60
UserA	LocationA	2018-01-01 11:00:00	60

结果预期：

user	location	start_time	stay_duration
UserA	LocationA	2018-01-01 08:00:00	120（08:00:00 开始的 60 分钟和 09:00:00 开始的 60 分钟合并）
UserA	LocationB	2018-01-01 10:00:00	60
UserA	LocationA	2018-01-01 11:00:00	60

很熟悉的套路啦

第一步：按照用户、地点分组起始时间去排序。

在这里插入图片描述

第二步：按照连续登录问题 A列 - 排序列做一个分组标记

在这里插入图片描述

第3步分组之后 min sum聚合即可~

在这里插入图片描述