最大连续登录天数

pyq@2001

已于 2023-10-27 21:25:51 修改

阅读量920

点赞数 6

分类专栏： SQL专栏文章标签： sql 面试大数据数据库

于 2023-10-26 21:26:16 首次发布

本文链接：https://blog.csdn.net/weixin_55015548/article/details/134063687

版权

SQL专栏专栏收录该内容

6 篇文章 0 订阅

订阅专栏

最大连续登录天数问题是SQL面试题目中的一个经典题目。

通用解决思路：

先要考虑数据是否需要去重操作。比如一个人可能一天登录多回。
通过 row_number() over() 进行分组开窗排序编号。按照什么字段分组就要看题目求什么的最大连续登录天数就按照什么分组；排序一般根据登录的时间进行升序排列。
通过日期字段减去分组排序的编号字段。此时我们会发现连续登录的天对应的差值相同。
按照分组字段和差值字段进行分组进行count()计数。此时就求出了所有连续登录的天数。
最后按照分组字段分组，对连续登录的天数求最大值，再按照题目要求该咋排序输出就排序，就得出了最后的答案。

例子1：求每个用户最大连续登录天数

题目及数据：

现在有一张表，记录了每个用户每天是否登录。fdate字段代表日期，fuser_id代表用户id， fis_sign_in代表是否登录（1代表登录， 0代表未登录），求每个用户最大连续登录天数，按照用户id升序排序。一直未登录就记作0。

# 建表语句
CREATE TABLE IF NOT EXISTS tb_user_attendence(
   fdate DATE NOT NULL,
   fuser_id INT,
   fis_sign_in INT NOT NULL
)
;

# 插入数据
INSERT INTO tb_user_attendence VALUES
            ('2020-07-06', 101, 1),
            ('2020-07-06', 102, 1),
            ('2020-07-06', 103, 1),
            ('2020-07-07', 101, 1),
            ('2020-07-07', 102, 1),
            ('2020-07-07', 103, 0),
            ('2020-07-08', 101, 1),
            ('2020-07-08', 102, 0),
            ('2020-07-08', 103, 1),
            ('2020-07-09', 101, 1),
            ('2020-07-09', 102, 0),
            ('2020-07-09', 103, 1),
            ('2020-07-10', 101, 1),
            ('2020-07-10', 102, 1),
            ('2020-07-10', 103, 1),
            ('2020-07-13', 101, 1),
            ('2020-07-13', 102, 1),
            ('2020-07-13', 103, 1),
            ('2020-07-14', 101, 1),
            ('2020-07-14', 102, 0),
            ('2020-07-14', 103, 1),
            ('2020-07-15', 101, 0),
            ('2020-07-15', 102, 1),
            ('2020-07-15', 103, 0),
            ('2020-07-16', 101, 1),
            ('2020-07-16', 102, 0),
            ('2020-07-16', 103, 1),
            ('2020-07-17', 101, 1),
            ('2020-07-17', 102, 1),
            ('2020-07-17', 103, 0),
            ('2020-07-18', 104, 0),
            ('2020-07-19', 104, 0)
;

数据如下图所示：

解题步骤：

1、因为数据记录的是每个用户每天的登录情况，无重复的数据，可以不用去重。（当然按照用户id和日期去重也一样）。

2、筛选出登录的数据，按照用户id进行开窗分组，按照日期字段升序排序。

3、通过日期字段与排序编号字段做差值。从图中可看出，连续的天差值相同。

4、按照用户id和差值进行分组计数。count()计数就是连续登录的天数。

5、按照用户id进行分组求出用户最大登录天数。（）

我们发现少了104用户，因为在筛选登录数据的时候104用户被筛选掉了，因为104用户一次都没登录过。（因此在做这道题的时候也需要考虑到这一种情况，可以先拿到所有用户再与上述结果进行左连接，所有用户为主表，于是就可以解决此问题。）

最终答案结果如下图所示：

mysql 求解代码：

with t as (
    # 因为要求每个用户的相关信息，后续要筛选登录的数据，可能有的用户一次都没有登录，所以先拿到所有用户
    select distinct fuser_id from tb_user_attendence
),t1 as (
    # 因为数据记录的是每个用户每天的登录情况，无重复的数据，可以不用去重。（当然按照用户id和日期去重也一样）
    select
        fdate, fuser_id,
        # 对用户id进行开窗分组，按照日期字段升序排序
        row_number() over (partition by fuser_id order by fdate) rn
    from tb_user_attendence
    # 筛选出登录的数据
    where fis_sign_in = 1
),t2 as (
    select
        fuser_id,fdate,rn,
        # 通过日期字段和排序编号字段做差值
        date_sub(fdate,interval rn day) diff
    from t1
),t3 as (
    # 按照用户id和差值进行分组计数
    select
        fuser_id,count(1) cnt
    from t2
    group by fuser_id,diff
),t4 as (
    # 按照用户id进行分组求出用户最大登录天数
    select
        fuser_id,max(cnt) max_day
    from t3
    group by fuser_id
)
select
    t.fuser_id,
    # 因为筛选数据的原因，有些一直没有登录的用户没有被统计出来，因此需要补上这些人。
    ifnull(max_day,0) max_day
from t left join t4 on t.fuser_id = t4.fuser_id
order by fuser_id
;

例子2：求出每张卡每个月最大连续消费天数

题目及数据：

现在有一张表，记录了卡消费的相关信息。card_nbr字段为卡的id，c_date字段为消费日期， c_type字段为消费的类型， c_atm字段为消费的金额。求每张卡每个月最大连续消费天数。

# 建表语句
create table tb_card
(
    card_nbr varchar(32),
    c_date   varchar(32),
    c_type   varchar(32),
    c_atm    int
);

# 插入数据
insert into tb_card values
               (1, '2022-01-01', '网购', 150),
               (1, '2022-01-01', '网购', 100),
               (1, '2022-01-02', '网购', 200),
               (1, '2022-01-03', '网购', 300),
               (1, '2022-01-15', '网购', 100),
               (1, '2022-01-16', '网购', 200),
               (2, '2022-01-06', '网购', 500),
               (2, '2022-01-07', '网购', 800),
               (1, '2022-02-02', '网购', 200),
               (1, '2022-02-03', '网购', 300),
               (1, '2022-02-04', '网购', 300),
               (1, '2022-02-05', '网购', 300),
               (1, '2022-02-08', '网购', 800),
               (1, '2022-02-09', '网购', 900),
               (2, '2022-02-05', '网购', 500),
               (2, '2022-02-06', '网购', 500),
               (2, '2022-02-07', '网购', 800),
               (2, '2022-02-07', '网购', 850)
;

数据如下图所示：

解题步骤：

1、去重操作。

2、分组排序求编号。因为要求每张卡每个月的最大连续消费天数，因此要按照卡id和月份进行分组。

3、做差值。dt字段为分组的月份字段，diff为日期与排序编号的差值字段。

4、按照卡id、月份和差值进行分组聚合计数。

5、按照卡id和月份分组求出每张卡每个月最大连续消费天数。

mysql 求解代码：

with t1 as (
    select
        card_nbr,c_date,
        # 在去重的基础上开窗排序，按照卡号和日期分组，按日期排序
        row_number() over (partition by card_nbr,date_format(c_date,'%Y-%m') order by c_date) rn
    from tb_card
    # 分组去重
    group by card_nbr,c_date
),t2 as (
    select
        # 做差值
        card_nbr,date_format(c_date,'%Y-%m') dt,
        date_sub(c_date,interval rn day) diff
    from t1
),t3 as (
    select
        # 按照卡号、日期和差值分组计数
        card_nbr,dt,count(1) cnt
    from t2
    group by card_nbr,dt,diff
)
# 按照卡号和日期分组，求出每个卡号每个月最大连续消费天数
select
    card_nbr,dt,max(cnt) max_cnt
from t3
group by card_nbr,dt
;