SQL笔试题（1）：求连续时间问题（必考难题）

更新一下，自春招以来，已经有超过十几个朋友和学弟学妹反应面试的时候考到了这类题的原题，大家一定要重视起来哈，在看懂的同时自己也动手写一写

SQL题听起来是个人就会，然而面试一面就挂。其实难得不是语法，而是对于操作关系型数据库逻辑的欠缺。这种欠缺可以通过多尝试解决不同类型的问题来解决

相关系列：

小帅比：SQL笔试题（2）：求时间间隔问题（必考难题）

小帅比：SQL笔试题（3）：时间相关题目（难题解析）

你也可以添加作者个人vx ：heyifan258，和作者一对一交流行业知识及面试技巧。添加备注：知乎，好友上限即将满员，随缘通过

今天分享一个非常常考，同时也不太容易的sql题目类型， 求连续时间问题。

这类题的核心是：分组排序，用时间减去排序，如果连续的话他们的差会是相同值

记住下面的 表，按照提供的三步思路理清楚里面的数据逻辑，连续时间问题再不会难到你

思路：

求解连续天数是面试时非常常考的问题，这类问题有非常巧妙的统一解法，分为3步：

1、为日期排序

• - row number() over (partition by use_id order by date) as rank

2、求日期和排序的差值（diff）

• - 因为日期存在周期，会存在跨月、跨年的情况，故使用datediff最保险
• date_diff(date, rank) as diff

3、求diff出现最多的次数

• - max(count(diff))

<问题1>

• 日活表求每个用户一周活跃天数
• 日活表求每个用户一周内最大连续活跃天数

-- 日活表求每个用户一周活跃天数
select user_id, count(1)
from(
select user_id
    ,date
from table
where TIMESTAMPDIFF(day, time, now()) <= 7
group by user_id
    ,date)
group by user_id
-- 日活表求每个用户一周内最大连续活跃天数
-- 求出现次数最多的diff
select user_id
    ,max(count)
    -- 算出登陆日期与排序的diff
    select user_id
        ,date_sub(date, rank) as gap
        ,count(1) as count
        -- 为每个用户的登录，按时间排序
        select user_id
            ,date
            ,row_number() over (partition by user_id order by date) as rank
        from (
              -- 选出每个用户最近7天的记录，同时每个用户每天只保留一条记录
                select distinct user_id, date
                    from table
                where TIMESTAMPDIFF(day, now(), date) <=7
    ) group by user_id
        ,date_sub(date, rank)
) group by user_id

这里用到了row_number() over ( partition by [] order by [] )函数、TIMESTAMPDIFF（）函数和date_sub()函数，解释如下：

• row_number() over ( partition by [] order by [] )是一种非常常用的分组排序函数，可以将排序的结果输出成一个新的列，partition by []是指按什么字段分组，order by [] 用于设置按什么顺序，row_number() 用于设置排序的方式，除了row_number()外还有rank和dense_rank
• TIMESTAMPDIFF（）语法规则是(unit，begin，end)，返回begin-end的结果，其中begin和end是DATE或DATETIME表达式，unit参数是确定(end-begin)的结果的单位，表示为整数，取值可以是秒second，分钟minute，小时hour和天day等
• date_sub() 语法规则 (time，int)，将时间减去n天

<问题2>

给定一张用户签到表 user_attendence，表中包含三个字段，分别是用户ID：【user_id】，日期：【date】，是否签到：【is_sign_in】，0否1是

• 计算截至当前，每个用户已经连续签到的天数

select user_id, TIMESTAMPDIFF(day,el, now()) as contDays