【SQL】查询连续登陆7天以上的用户

http://www.taoyouhuipin.top/

查询7天连续登陆用户这个问题很经典，解决方法也有很多，这里我讲一下我的处理方法，希望对大家有帮助。

具体思路1: 因为是要求解连续登陆的，那么如果对日期（需要先对日期distinct 去重）进行排序的话，可以知道 日期是连续递增，序号也是，例如：

当我们用date - 序号天数的时候，如果存在连续登陆的日期，那么得出来的日期，应该是相同的

new_date = 2020-11-15 减去 N = 2020-11-16 减去 （N+1）=  2020-11-17 减去 （N+2）= 2020-11-18 减去 （N+3）......

我们再对new_date 分组 ，则可知道连续登陆的有多少次

select

new_date ,count(1)

from (

2020-11-15 减去 N = 2020-11-16 减去 （N+1）=  2020-11-17 减去 （N+2）= 2020-11-18 减去 （N+3）......

)

;

综上思路，我们再来理解解答题目：

with orde as (
select '2019-12-28' as date, 1 id
union all
select '2019-12-29' as date, 1 id
union all
select '2019-12-30' as date, 1 id
union all
select '2019-12-31' as date, 1 id
union all
select '2020-01-01' as date, 1 id
union all
select '2020-01-02' as date, 1 id
union all
select '2020-01-03' as date, 1 id
union all
select '2020-01-05' as date, 1 id
union all
select '2020-01-06' as date, 1 id
union all
select '2020-01-07' as date, 1 id
union all
select '2020-01-08' as date, 1 id
union all
select '2020-01-01' as date, 2 id
union all
select '2020-01-01' as date, 2 id
union all
select '2020-01-11' as date, 2 id
union all
select '2020-01-12' as date, 2 id
union all
select '2020-01-13' as date, 2 id
union all
select '2020-01-14' as date, 2 id
union all
select '2020-01-15' as date, 2 id
union all
select '2020-01-16' as date, 2 id
union all
select '2020-01-17' as date, 2 id
union all
select '2020-01-18' as date, 2 id
)
select id,count(1) 
from 
(
select *,date_sub(date(日期) ,interval cum day) as 结果 
from (
        select *,row_number() over(PARTITION by id order by 日期) as cum 
    from 
        (select DISTINCT date(date) as 日期,id from orde ) a
        )  b
    ) c 
GROUP BY id,结果 
having count(1)>7;

ps: 题目只是要求我们再1~30日之间计算，而上述脚本亦可计算 跨月、跨年，如果跨月跨年不算符合要求，可以根据下列逻辑计算（直接用日期 减去 数值，会有隐式数据类型转换）：

with orde as (
select '2019-12-28' as date, 1 id
union all
select '2019-12-29' as date, 1 id
union all
select '2019-12-30' as date, 1 id
union all
select '2019-12-31' as date, 1 id
union all
select '2020-01-01' as date, 1 id
union all
select '2020-01-02' as date, 1 id
union all
select '2020-01-03' as date, 1 id
union all
select '2020-01-05' as date, 1 id
union all
select '2020-01-06' as date, 1 id
union all
select '2020-01-07' as date, 1 id
union all
select '2020-01-08' as date, 1 id
union all
select '2020-01-01' as date, 2 id
union all
select '2020-01-01' as date, 2 id
union all
select '2020-01-11' as date, 2 id
union all
select '2020-01-12' as date, 2 id
union all
select '2020-01-13' as date, 2 id
union all
select '2020-01-14' as date, 2 id
union all
select '2020-01-15' as date, 2 id
union all
select '2020-01-16' as date, 2 id
union all
select '2020-01-17' as date, 2 id
union all
select '2020-01-18' as date, 2 id
)
select id,count(1) 
from 
(
select *,date(日期) - cum  as 结果 
from (
        select *,row_number() over(PARTITION by id order by 日期) as cum 
    from 
        (select DISTINCT date(date) as 日期,id from orde ) a
        )  b
    ) c 
GROUP BY id,结果 
having count(1)>=7;

最后

以上就是淡淡白猫为你收集整理的【SQL】查询连续登陆7天以上的用户【SQL】查询连续登陆7天以上的用户的全部内容，希望文章能够帮你解决【SQL】查询连续登陆7天以上的用户【SQL】查询连续登陆7天以上的用户所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。