如何使用SQL实现筛选出连续3天登录用户与窗口函数的示例代码

1. 筛选出连续3天登录用户

为了筛选出连续3天登录的用户，我们需要找到那些在连续3天内都有登录记录的用户。这就需要使用SQL中的窗口函数。

1.1 窗口函数的作用

窗口函数是一种特殊的函数，它可以用来聚合某些列的数据。它的优势在于，它可以在不破坏原始数据的情况下，对数据进行聚合处理。

窗口函数有以下作用：

对被分组的数据进行聚合计算

每一行数据都可以访问整个分组数据的聚合结果

可以获得每一行数据相对于整个分组数据的排名、行号等

1.2 筛选连续3天登录用户的具体实现

假设我们有一个用户登录记录表user_login，其中包含以下字段：

user_id: 用户ID

login_time: 登录时间（格式为yyyy-MM-dd HH:mm:ss）

我们可以使用窗口函数，结合GROUP BY语句，来进行筛选：

SELECT user_id
FROM (
  SELECT user_id, login_time, 
         DATE_SUB(login_time, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_time) - 1 DAY) AS date_group
  FROM user_login
  GROUP BY user_id, date_group
) AS t
GROUP BY user_id
HAVING COUNT(*) >= 3

这里的意思是，先把登录记录按用户ID（PARTITION BY user_id）和日期（按login_time排序后，利用ROW_NUMBER函数生成日期序号，再用DATE_SUB函数计算出日期）进行分组（GROUP BY user_id, date_group）。然后在子查询中，对每个用户在每个日期分组内的登录记录进行计数，最后在外层查询中，筛选出登录次数大于等于3的用户（HAVING COUNT(*) >= 3）。

2. 窗口函数实现的示例代码

下面是一个完整的示例代码，包含创建表、插入数据和查询连续3天登录的用户：

-- 创建用户登录记录表
CREATE TABLE user_login (
  user_id INT NOT NULL,
  login_time DATETIME NOT NULL,
  PRIMARY KEY (user_id, login_time)
);
-- 插入测试数据
INSERT INTO user_login (user_id, login_time)
VALUES
  (1, '2021-01-01 10:00:00'),
  (1, '2021-01-02 10:00:00'),
  (1, '2021-01-03 10:00:00'),
  (1, '2021-01-04 10:00:00'),
  (1, '2021-01-05 10:00:00'),
  (2, '2021-01-01 10:00:00'),
  (2, '2021-01-02 10:00:00'),
  (2, '2021-01-03 10:00:00'),
  (3, '2021-01-01 10:00:00'),
  (3, '2021-01-02 10:00:00'),
  (3, '2021-01-04 10:00:00'),
  (4, '2021-01-01 10:00:00'),
  (4, '2021-01-02 10:00:00'),
  (4, '2021-01-03 10:00:00'),
  (5, '2021-01-01 10:00:00'),
  (5, '2021-01-02 10:00:00'),
  (5, '2021-01-03 10:00:00'),
  (5, '2021-01-04 10:00:00');
-- 查询连续3天登录的用户
SELECT user_id
FROM (
  SELECT user_id, login_time, 
         DATE_SUB(login_time, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_time) - 1 DAY) AS date_group
  FROM user_login
  GROUP BY user_id, date_group
) AS t
GROUP BY user_id
HAVING COUNT(*) >= 3;

运行上述代码，可以得到如下结果：

+---------+
| user_id |
+---------+
|       1 |
|       4 |
|       5 |
+---------+

可以看到，只有用户1、4和5在连续3天内都有登录记录。