SQL-连续n天登录

问题

有一份登录表（user_login）, 里面包含id（用户id，int）， time(用户登录时间， timestamp)。 现在需要在里面找出连续三天登录的用户。

| id | time               |
|---|---------------------|
| 1 | 2024-07-16 03:46:23 |
| 1 | 2024-07-18 03:46:23 |
| 1 | 2024-07-18 05:46:23 |
| 1 | 2024-07-19 03:46:23 |
| 1 | 2024-07-20 03:46:23 |
| 2 | 2024-07-17 03:46:23 |
| 2 | 2024-07-18 03:46:23 |
| 2 | 2024-07-20 03:46:23 |

思路

首先，表里记录了用户的所有记录，现在发现用户一天登录多于一次，于是我们需要去重。

select distinct id, time from user_login;

输出

 id |        time
----+---------------------
  2 | 2024-07-20 03:46:23
  2 | 2024-07-17 03:46:23
  1 | 2024-07-18 05:46:23
  1 | 2024-07-16 03:46:23
  1 | 2024-07-20 03:46:23
  2 | 2024-07-18 03:46:23
  1 | 2024-07-19 03:46:23
  1 | 2024-07-18 03:46:23

第二步，需要给去重的数据按照id分组然后按时间排序，这样可以知道用户的登录情况。但是只是这样做的话只能用肉眼判断用户是否有连续登录，结果本身无法体现，于是我就想到了用窗口函数。我们使用row_number给登录日期排序，然后利用登录日期减去row number看看是否为同一天来判断是否是连续登录。比如用户在2024-07-19 和2024-07-20两天连续登录，那2024-07-19 - 1 day 和202-07-20 - 2 day结果是一样的。

select id, login_date, login_date - (row_number() over(partition by id order by login_date))::int as first_login
from (
  select distinct id, date(time) as login_date 
  from user_login
) user_login_date;

输出

 id | login_date | first_login
----+------------+-------------
  1 | 2024-07-16 | 2024-07-15
  1 | 2024-07-18 | 2024-07-16
  1 | 2024-07-19 | 2024-07-16
  1 | 2024-07-20 | 2024-07-16
  2 | 2024-07-17 | 2024-07-16
  2 | 2024-07-18 | 2024-07-16
  2 | 2024-07-20 | 2024-07-17

最后，我们简单group by id和first login就能知道结果了。

 select id 
 from (
  select id, login_date, login_date - (row_number() over(partition by id order by login_date))::int as first_login
  from (
    select distinct id, date(time) as login_date 
    from user_login
  ) user_login_date
  ) first_login
group by id, first_login
having count(1) >= 3;

输出

 id
----
  1

至此，我们的题目已经解答完毕

变种

这里还有一种变种，力扣180连续出现的数字

180. 连续出现的数字 - 力扣（LeetCode）

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
id 是一个自增列。

找出所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

思路其实也是一样的，甚至更简单。这里不再重复了，直接贴sql

select distinct num as consecutivenums 
from (
    select num, diff, count(num) as counts
    from (
        select id, num, id - row_number() over(partition by num order by id) as diff
        from logs) l 
group by num, diff
having count(num) >= 3) l2;