2，SQL专题训练之：最低连续登录3天 （困难）

目录

一，原题链接

二，题干

三，建表语句

四，分析

1，去重

2，排序取值

3，取相差值，得到同一个日期的则是连续登录的天数

4，分组，聚合，拿到登录天数

5，拼接另外一个表，拿到最终呈现给用户的数据​编辑

五，SQL解答

六，验证

七，知识点总结

---

一，原题链接

经典面试题，链接无

二，题干

经典面试题。统计连续登录的天数，用户id，和用户的等级。最低连续登录3天。

有两张表，分别是用户表和用户登录信息表。

三，建表语句

 
 
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;

四，分析

1,查看表

2,查看表二

分析结果：

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

120 连续登陆 7号, 8号, 他的连续登陆天数是 2天  不足3天

116 连续登陆, 一共连续登陆了 3次 7号,8号,9号 他的12号是不算的; 所以他的连续登陆天数是 3天

101 一共连续登陆了  1号 2号 3号 他的连续登陆天数是 3天

1，去重

2，排序取值

3，取相差值，得到同一个日期的则是连续登录的天数

4，分组，聚合，拿到登录天数

5，拼接另外一个表，拿到最终呈现给用户的数据

五，SQL解答

-- 连续登录3天
 
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
    ),
    #取连续登录的天数  以id和 相同的日期 分组  统计次数
    t4 as (
        select  author_id, count(日期减去日期的排序值得到的日期) as 连续登录的天数
            from t3 group by author_id,日期减去日期的排序值得到的日期 having count(日期减去日期的排序值得到的日期)>=3
),
    t5 as (
        select  author_id,
                (select a.author_level from author_tb a where a.author_id in(t4.author_id))  author_level,
                 连续登录的天数
            from t4
    )
 
select * from t5;

六，验证

七，知识点总结

• with上下文管理器的运用 让代码运行的更加好维护
• 可读性也更强
• 逻辑也更加清晰

连续登录的天数

• 第一步，去重
• 第二步 开窗，统计时间日期 排序 1234……这种 记录下来
• 第三步 用时间差函数  date_add 当下一行的日期与排序的数值 如果得到相同的日期 则表示这是连续连续的，因为相差值其实是1 也就是行数是1  类似于矩阵：
• 第四步，分区，聚合，拿到连续登录的天数
• 第五步，拼接表 得到题目中其他的字段，呈现给用户最终结果