暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 面试官：“如何用 SQL 查询每个用户最大连续登录日期？”

面试官：“如何用 SQL 查询每个用户最大连续登录日期？”

大数据的奇妙冒险 2022-12-06
255

前言：“如何用 SQL 查询每个用户最大连续登录日期？”

这道题是数据开发面试经常问到的题目，因为其比较考验候选人的 SQL 能力，今天就来简单讲讲其思路。

样例数据如下 login_log：

user_idlogin_time
12022-11-28
12022-12-01
12022-12-02
12022-12-03
22022-12-01
22022-12-04

(1) 先开个窗

不了解或不熟悉开窗函数的，可以看下这篇：

通俗易懂：窗口函数 | 全是案例，看完不懂算我输

    select user_id, login_time,
    row_number() over(partition by user_id order by login_time) num 
    from login_log;

    开完窗的结果如下：

    user_idlogin_timenum
    12022-11-281
    12022-12-012
    12022-12-023
    12022-12-034
    22022-12-011
    22022-12-042

    (2) 利用等差数列的特性

    若是连续登录，login_time - num 则相等。

    因此可以这样写：

      select t.user_id, 
          t.login_time, 
          date_sub(login_time, INTERVAL t.num DAY) date_rslt
      from 
      (
          select user_id, login_time, 
          row_number() over(partition by user_id order by login_time) num 
          from login_log
      ) t;

      注：INTERVAL 关键字可以用于计算时间间隔， date_sub(login_time, INTERVAL t.num DAY)表示登录时间减去 num 天；若 DAY 改成 HOUR 表示减去 num 小时。

      以上 SQL 执行后得到：

      user_idlogin_timedate_rslt
      12022-11-282022-11-27
      12022-12-012022-11-29
      12022-12-022022-11-29
      12022-12-032022-11-29
      22022-12-012022-11-30
      22022-12-042022-12-02

      (3) 分组后可获得结果

        select a.user_id,
            a.date_rslt,
            count(1) as cnt
        from(
            select 
                t.user_id, 
                t.login_time,
                date_sub(login_time, INTERVAL t.num DAY) date_rslt
            from 
                (
                select user_id, 
                login_time, 
                row_number() over(partition by user_id order by login_time) num 
                from login_log
                ) t
        ) a
        group by a.user_id, a.date_rslt;

        以上就是全部内容啦，想学习更多大数据相关知识，关注大数据的奇妙冒险。

        点赞关注不迷路，转载请注明出处。


        sqltime函数select
        文章转载自大数据的奇妙冒险，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

        评论