SQL_SQL_求连续登录天数

高达一号

已于 2023-07-21 00:26:04 修改

阅读量200

点赞数

分类专栏： SQL 文章标签： java 开发语言

于 2023-07-21 00:16:24 首次发布

原文链接：https://blog.csdn.net/sinat_41207450/article/details/126709185

版权

SQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

该文章提供了一个SQL查询方案，用于从包含uid和login_time字段的登陆表中计算每个用户的最大连续登陆天数。首先构造测试数据，然后通过排序、日期差计算和分组聚合操作找出连续登录的天数，最后确定每个用户的最大连续登录天数。

摘要由CSDN通过智能技术生成

转载自：SQL 求最大连续登陆天数_连续登录天数sql_机灵小布衣的博客-CSDN博客

问题描述：

已知登陆表中有 uid、login_time，求每个用户的最大连续登陆天数

uid、login_time

A | 2020-08-01 10:00:00

主要思路：连续登录的第N天，当前日期-第N天，都为同一天

1.构造测试数据

WITH user_login as (
    select 'A' uid,
        from_unixtime(
            unix_timestamp('2020-08-01 10:00:00', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'A' uid,
        from_unixtime(
            unix_timestamp('2020-08-02 10:01:00', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'A' uid,
        from_unixtime(
            unix_timestamp('2020-08-03 10:02:03', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'A' uid,
        from_unixtime(
            unix_timestamp('2020-08-06 10:04:03', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'A' uid,
        from_unixtime(
            unix_timestamp('2020-08-07 10:03:10', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'B' uid,
        from_unixtime(
            unix_timestamp('2020-08-12 10:03:11', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'B' uid,
        from_unixtime(
            unix_timestamp('2020-08-13 10:04:00', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'B' uid,
        from_unixtime(
            unix_timestamp('2020-08-14 12:04:06', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'A' uid,
        from_unixtime(
            unix_timestamp('2020-08-15 12:04:10', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'B' uid,
        from_unixtime(
            unix_timestamp('2020-08-16 13:04:12', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
    union all
    select 'B' uid,
        from_unixtime(
            unix_timestamp('2020-08-17 15:05:10', "yyyy-MM-dd HH:mm:ss")
        ) as login_time
)

2. 将记录按照每个用户分区，对登录时间进行升序排列，得到数据集 user_log_rank

user_log_rank as
(
select uid,
    login_time,
    row_number() over(
        partition by uid
        order by login_time
    ) as sort
from (
        select uid,
            date(login_time) as login_time
        from user_login
        group by uid,
            date(login_time)
    ) as rs_user_login
)

3、每条记录的登录时间减去排序的数字，得到一个组 date_group，对用户及date_group 做聚合，continue_days 即为连续登录天数

select uid,
    date_sub(login_time, sort) as date_group,
    min(login_time) as start_dt,
    max(login_time) as end_dt,
    count(1) as continue_days 
from user_log_rank a
group by uid,
    date_sub(login_time, sort)

4、以 uid 分组，max（continue_days ）即为每个用户的最大连续登录天数

select uid,
    max(continue_days) as max_continue_days
from (
        select uid,
            date_sub(login_time, sort) as date_group,
            min(login_time) as start_dt,
            max(login_time) as end_dt,
            count(1) as continue_days
        from user_log_rank a
        group by uid,
            date_sub(login_time, sort)
    ) as rs_continue_days
group by uid