3，SQL专题训练之：连续登录天数，间隔一天也算一天（困难）

凡梦_leo

于 2024-07-26 09:58:28 发布

阅读量956

点赞数 47

分类专栏： SQL训练文章标签： sql 数据库 hdfs 大数据 python 后端 mysql

本文链接：https://blog.csdn.net/qq_55006020/article/details/140707433

版权

SQL训练专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一，原题链接

经典面试题，连续登录天数的升级版，间隔一天也算连续。求连续登录天数的用户id和用户等级，以及连续登录的次数，和连续登录的天数。

注意注意：期间间隔一天，也算连续比如 7号，8号，10号，12号。这算做连续登录4次，连续登录6天。

二，建表语句

 
 
drop table if exists author_tb;
CREATE TABLE author_tb
(
    author_id    int(10) NOT NULL,
    author_level int(10) NOT NULL,
    sex          char(10) NOT NULL
);
INSERT INTO author_tb
VALUES
    (101, 6, 'm'),
    (102, 1, 'f'),
    (103, 1, 'm'),
    (104, 3, 'm'),
    (105, 4, 'f'),
    (106, 2, 'f'),
    (107, 2, 'm'),
    (108, 5, 'f'),
    (109, 6, 'f'),
    (110, 5, 'm');
 
drop table if exists answer_tb;
CREATE TABLE answer_tb
(
    answer_date date     NOT NULL,
    author_id   int(10) NOT NULL,
    issue_id    char(10) NOT NULL,
    char_len    int(10) NOT NULL
);
 
INSERT INTO answer_tb
VALUES
    ('2021-11-1', 101, 'E001', 150),
    ('2021-11-2', 101, 'E002', 200),
    ('2021-11-3', 101, 'E002', 201),
    ('2021-11-5', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 102, 'C003', 50),
    ('2021-11-1', 103, 'P001', 35),
    ('2021-11-1', 104, 'C003', 120),
    ('2021-11-1', 105, 'P001', 125),
    ('2021-11-1', 102, 'P002', 105),
    ('2021-11-2', 101, 'P001', 201),
    ('2021-11-3', 101, 'P002', 211),
    ('2021-11-2', 101, 'P001', 211),
        ('2021-11-2', 115, 'P001', 211),
        ('2021-11-4', 115, 'P001', 211),
         ('2021-11-6', 115, 'P001', 211),
          ('2021-11-8', 115, 'P001', 211),
          ('2021-11-7', 116, 'P001', 211),
          ('2021-11-8', 116, 'P001', 211),
          ('2021-11-9', 116, 'P001', 211),
          ('2021-11-12', 116, 'P001', 211),
    ('2021-11-2', 110, 'C003', 200),
    ('2021-11-2', 110, 'C001', 225),
    ('2021-11-2', 110, 'C002', 220),
    ('2021-11-3', 101, 'C002', 180),
    ('2021-11-4', 109, 'E003', 130),
    ('2021-11-4', 109, 'E001', 123),
    ('2021-11-5', 108, 'C001', 160),
    ('2021-11-5', 108, 'C002', 120),
    ('2021-11-5', 110, 'P001', 180),
    ('2021-11-5', 106, 'P002', 45),
    ('2021-11-5', 107, 'E003', 56),
    ('2021-11-6', 107, 'E003', 56),
        ('2021-11-7', 120, 'E003', 56),
        ('2021-11-8', 120, 'E003', 56),
        ('2021-11-10', 120, 'E003', 56),
        ('2021-11-14', 120, 'E003', 56),
        ('2021-11-12', 120, 'E003', 56);
 
 
select * from author_tb;
 
select * from answer_tb;

三，分析

分析表数据我们得知答案:

120 连续登陆(期间中断1天也算一天) ,一共连续登陆了5次 7号, 8号, 10号 , 12号 ,14号他的连续登陆天数是 7号到14号一共是8天

116 连续登陆(期间中断1天也算一天), 一共连续登陆了 3次 7号,8号,9号他的12号是不算的; 所以他的连续登陆天数是 3天

115 这个人一共连续登陆了(期间中断1天也算一天) 一共连续登陆了 4次 2号,4号,

,6号,8号他的连续登陆天数是 2号到8号所以共计:7天

101 一共连续登陆了(期间中断1天也算一天) 一共连续登陆了 4次分别是:

1号 2号 3号 5号他的连续登陆天数是 1号到5号共计: 5天

1，去重

2，排序取值

3，取相差值，得到相同的天数，则连续登录；得到连续的天数，则为间隔一天登录

4，再次对新列排序取值

5，再次取相差值，得到相同的天数，则为连续登录（包含间隔一天的登录）

6，记录连续登录的次数，记录不同用户的连续登录2天之后的最小天数，和最大天数

7，取到真实的登录天数最大-最小 +1

8，分组，聚合，拿到连续的次数，连续的天数

9，拼接表，拿到最终数据

四，SQL解答

-- 连续登录3天 不足1天也算
with t1 as (
    #去重
    select distinct answer_date,author_id from  answer_tb
),
    # 排序 以id分组  以answer_date升序排序  取时间日期的排序编码
    t2 as (
        select  answer_date, author_id,
                row_number() over (partition by author_id order by answer_date) 用户的答复时间排序值
                from t1
    ),
    #取差值 为1的 算作连续登陆天数 即 日期减去日期的排序值得到的日期的列
    t3 as (
        select answer_date, author_id, 用户的答复时间排序值,
        date_add(answer_date,interval -用户的答复时间排序值 day ) 日期减去日期的排序值得到的日期
        from t2
    ),
    #在取差值 对  日期减去日期的排序值得到的日期  继续排序  用dense排序 允许并列
    t4 as (
        select answer_date, author_id, 用户的答复时间排序值, 日期减去日期的排序值得到的日期,
               dense_rank() over (partition by author_id order by 日期减去日期的排序值得到的日期) 排序2
            from t3
),
    #拿到连续 连续登陆的人
    t5 as (
        select answer_date, author_id, 用户的答复时间排序值, 日期减去日期的排序值得到的日期, 排序2,
               date_add(日期减去日期的排序值得到的日期,interval -排序2 day) 连续登陆天数2天

        from t4
    ),
#     在这个表里面 分组 统计天数   以id 分组  以相同天数次数聚合
    t6 as (
        select author_id,count(*) 不同id连续登陆2天的次数,
               max(answer_date) 最大日期,min(answer_date) 最小日期
        from t5 group by author_id,连续登陆天数2天
),# 根据连续登录人的人 取出 连续登陆3天的人
    t7 as (
    select author_id,不同id连续登陆2天的次数,
           timestampdiff(day,t6.最小日期,t6.最大日期)+1 as 连续天数   # 时间差 +1天 刚好是连续登陆的天数(间隔一天也算一天)
           from t6
),
    #拼接成最终的结果
    t8 as (
        select author_id,max(不同id连续登陆2天的次数) as 连续天数的次数,连续天数
        from t7
         group by author_id,连续天数 having max(不同id连续登陆2天的次数) >=3
    ),
    t9  as (
        select author_id,
               (select a.author_level from author_tb a where a.author_id in(t8.author_id) ) as author_level,
               连续天数的次数,
               连续天数
        from t8
    )
select * from t9;

五，验证

分析表数据我们得知答案:

120 连续登陆(期间中断1天也算一天) ,一共连续登陆了5次 7号, 8号, 10号 , 12号 ,14号他的连续登陆天数是 7号到14号一共是8天

116 连续登陆(期间中断1天也算一天), 一共连续登陆了 3次 7号,8号,9号他的12号是不算的; 所以他的连续登陆天数是 3天

115 这个人一共连续登陆了(期间中断1天也算一天) 一共连续登陆了 4次 2号,4号,

,6号,8号他的连续登陆天数是 2号到8号所以共计:7天

101 一共连续登陆了(期间中断1天也算一天) 一共连续登陆了 4次分别是:

1号 2号 3号 5号他的连续登陆天数是 1号到5号共计: 5天

六，知识点总结

知识点总结连续登录天数间隔一天也算连续思路步骤：

第一步，去重；

第二步，排序取值。开窗，以id分组，以时间排序，拿到时间的排序值；

第三步去差值，如果时间减去排序值得到的天数是相同的表示连续登录;

        [比如第一条是2024-10-1 他减去他的排序值1 2024-9-30

        第二条是 2024-10-2 他减去他的排序值2 2024-9-30 ]

        如果得到的天数是连续的表示间隔1天登录；

第四步，再次排序取值。为求连续登录哪怕间隔一天登录也连续，所以继续开窗对新日期列排序；

第五步，再取差值。再取一个新的日期列，拿到如果新日期列-新的排序列得到是相同的那么表示他们是连续登录的天（间隔一天也算一天）；用dense_rank() 因为有相同的所以允许并列

第六步，实际上我们统计相同的天数的次数就是连续登录的次数。

          但是为了拿到我们连续登录的真实天数。所以需要记录下来以id分组，

                连续登录2天的        相同日期 ,分组取出该组：最小的天数和最大的天数；

第七步，拿到真实的登录天数最大日期和最小日期的时间差+1

第八步，去重采用[分组，count聚合] 拿到满足条件大于3天连续登录次数（间隔一天也算一天）；

第九步，拼接表以最终的是数据呈现给用户； id ,等级,连续登陆的次数,连续登陆的天数是个列

总结：开窗函数的运用，取插值，用时间-排序的数值如果是相等，则为连续登录天数

如果减去之后还是连续的天数，那么他们就是间隔1天连续登录的