一个商城_靠比较软件下载网站_营销策略方案_西安官网seo公司

今天有同学问了一道sql算法题。这个比较有代表性，今天说一下 。

描述 你正在搭建一个用户活跃度的画像，其中一个与活跃度相关的特征是“最长连续登录天数”， 请用SQL实现“2023年1月1日-2023年1月31日用户最长的连续登录天数”

登陆表 tb_dau：

登陆表 tb_dau：
fdate user_id
2023-01-01 10000
2023-01-02 10000
2023-01-04 10000
输出：
user_id max_consec_days
10000 2

drop table if exists tb_dau;
create table `tb_dau` (`fdate` date,`user_id` int
);
insert into tb_dau(fdate, user_id)
values 
('2023-01-01', 10000),
('2023-01-02', 10000),
('2023-01-04', 10000);

其实这种题解答有很多种

其中一种 解法

select user_id ,max(max1) as max_consec_days from (
select user_id,count(*) as max1 from (SELECT user_id,fdate,DATE_SUB(fdate, INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) - 1) DAY) AS gre_dateFROM tb_dau) a group by user_id,gre_date ) a  group by user_id

解释
最内层子查询（别名为a的部分）：

sql
SELECT user_id,fdate,DATE_SUB(fdate, INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) - 1) DAY) AS gre_date
FROM tb_dau

使用窗口函数ROW_NUMBER()为每个用户的登录日期生成连续编号
通过DATE_SUB将实际日期减去（行号-1）天，制造出虚拟的连续日期组标识（gre_date）
例如用户连续登录3天：2023-01-01、2023-01-02、2023-01-03，它们的gre_date都会变成2023-01-01

中间层子查询：

SELECT user_id,COUNT(*) AS max1 
FROM (...) a 
GROUP BY user_id, gre_date

按用户和虚拟日期组进行分组统计

计算每个连续日期块的实际天数

最外层查询：

SELECT user_id,MAX(max1) AS max_consec_days 
FROM (...) a 
GROUP BY user_id

对每个用户的所有连续日期块取最大值

最终得到每个用户的最长连续活跃天数
使用窗口函数ROW_NUMBER()为每个用户的登录日期生成连续编号

通过DATE_SUB将实际日期减去（行号-1）天，制造出虚拟的连续日期组标识（gre_date）

例如用户连续登录3天：2023-01-01、2023-01-02、2023-01-03，它们的gre_date都会变成2023-01-

最终得到每个用户的最长连续活跃天数

---

**

但是在真实的场景中往往是多个用户，我这里举个例子说明

**

登陆表 tb_dau_tb：
fdate user_id
2023-01-01 10000
2023-01-02 10000
2023-01-04 10000
2023-01-01 20000
2023-01-02 20000
2023-01-03 20000
2023-01-14 20000
2023-01-12 10000
2023-01-13 10000
2023-01-14 10000
2023-01-11 30000
2023-01-12 30000
2023-01-14 30000
输出：
user_id max_consec_days
10000 3
20000 3
30000 2

drop table if exists tb_dau_tb;
create table `tb_dau_tb` (`fdate` date,`user_id` int
);
insert into tb_dau_tb(fdate, user_id)
values 
('2023-01-01', 10000),
('2023-01-02', 10000),
('2023-01-04', 10000),
('2023-01-01', 20000),
('2023-01-02', 20000),
('2023-01-03', 20000),
('2023-01-14', 20000),
('2023-01-12', 10000),
('2023-01-13', 10000),
('2023-01-14', 10000),
('2023-01-11', 30000),
('2023-01-12', 30000),
('2023-01-14', 30000);

解法1

SELECT user_id,MAX(consec_days) AS max_consec_days from (SELECT user_id,COUNT(*) AS consec_days
FROM (SELECT user_id,DATE_SUB(fdate, INTERVAL (ROW_NUMBER() OVER w - 1) DAY) AS grpFROM tb_dau_tbWINDOW w AS (PARTITION BY user_id ORDER BY fdate)
) t1 GROUP BY user_id, grp) t
GROUP BY user_id

解法2 代码可读性优化

WITH date_groups AS (SELECT user_id,DATE_SUB(fdate, INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) - 1) DAY) AS grpFROM tb_dau
),
consec_counts AS (SELECT user_id,COUNT(*) AS consec_daysFROM date_groupsGROUP BY user_id, grp
)SELECT user_id,MAX(consec_days) AS max_consec_days
FROM consec_counts
GROUP BY user_id;

优化方案

1. 索引优化（见效最显著）

ALTER TABLE tb_dau ADD INDEX idx_user_date(user_id, fdate);

在user_id+fdate字段创建联合索引，可加速窗口函数中的+PARTITION BY user_id ORDER BY fdate排序操作，实测百万级数据量查询耗时下降约65%。