SQL - 如何用开窗函数查询连续登录天数大于N的用户？

💡思路概述

1. 去重：对每个用户的登录日期去重。

2. 生成row_number记录登录次数：用 ROW_NUMBER() 为每个用户的每次登录记录编号

3. 构造分组依据：“登录日期 - row_number”这个差值在连续登录时是相同的，可用于分组。

4. 分组并统计：按 (user_id, login_date - row_number) 分组，统计每组的连续天数。

5. 筛选：取连续天数 > N 的用户记录。

 

CREATE TABLE login_log (
    user_id INT,
    login_date DATE
);

INSERT INTO login_log (user_id, login_date) VALUES
(1, '2025-07-01'),
(1, '2025-07-02'),
(1, '2025-07-03'),
(1, '2025-07-05'),
(1, '2025-07-06'),
(2, '2025-07-01'),
(2, '2025-07-02'),
(2, '2025-07-03'),
(2, '2025-07-04'),
(2, '2025-07-05'),
(3, '2025-07-02'),
(3, '2025-07-04'),
(3, '2025-07-06');

 

WITH login_data AS (
    SELECT DISTINCT user_id, login_date::date  -- 去重
    FROM login_log
),
numbered_logs AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM login_data
)
SELECT * FROM numbered_logs;

 

user_id	login_date	rn
1	2025-07-01T00:00:00.000Z	1
1	2025-07-02T00:00:00.000Z	2
1	2025-07-03T00:00:00.000Z	3
1	2025-07-05T00:00:00.000Z	4
1	2025-07-06T00:00:00.000Z	5
2	2025-07-01T00:00:00.000Z	1
2	2025-07-02T00:00:00.000Z	2
2	2025-07-03T00:00:00.000Z	3
2	2025-07-04T00:00:00.000Z	4
2	2025-07-05T00:00:00.000Z	5
3	2025-07-02T00:00:00.000Z	1
3	2025-07-04T00:00:00.000Z	2
3	2025-07-06T00:00:00.000Z	3

 

WITH login_data AS (
    SELECT DISTINCT user_id, login_date::date  -- 去重
    FROM login_log
),
numbered_logs AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM login_data
),
grouped_logs AS (
    SELECT
        user_id,
        login_date,
        rn,
        login_date - rn * INTERVAL '1 day' AS grp  -- 关键：构造分组依据
    FROM numbered_logs
)
SELECT * FROM grouped_logs;

 

user_id	login_date	rn	grp
1	2025-07-01T00:00:00.000Z	1	2025-06-30T00:00:00.000Z
1	2025-07-02T00:00:00.000Z	2	2025-06-30T00:00:00.000Z
1	2025-07-03T00:00:00.000Z	3	2025-06-30T00:00:00.000Z
1	2025-07-05T00:00:00.000Z	4	2025-07-01T00:00:00.000Z
1	2025-07-06T00:00:00.000Z	5	2025-07-01T00:00:00.000Z
2	2025-07-01T00:00:00.000Z	1	2025-06-30T00:00:00.000Z
2	2025-07-02T00:00:00.000Z	2	2025-06-30T00:00:00.000Z
2	2025-07-03T00:00:00.000Z	3	2025-06-30T00:00:00.000Z
2	2025-07-04T00:00:00.000Z	4	2025-06-30T00:00:00.000Z
2	2025-07-05T00:00:00.000Z	5	2025-06-30T00:00:00.000Z
3	2025-07-02T00:00:00.000Z	1	2025-07-01T00:00:00.000Z
3	2025-07-04T00:00:00.000Z	2	2025-07-02T00:00:00.000Z
3	2025-07-06T00:00:00.000Z	3	2025-07-03T00:00:00.000Z

 

WITH login_data AS (
    SELECT DISTINCT user_id, login_date::date  -- 去重
    FROM login_log
),
numbered_logs AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM login_data
),
grouped_logs AS (
    SELECT
        user_id,
        login_date,
        rn,
        login_date - rn * INTERVAL '1 day' AS grp  -- 关键：构造分组依据
    FROM numbered_logs
),
grouped_counts AS (
    SELECT
        user_id,
        MIN(login_date) AS start_date,
        MAX(login_date) AS end_date,
        COUNT(*) AS consecutive_days
    FROM grouped_logs
    GROUP BY user_id, grp
)
SELECT * FROM grouped_counts;

 

user_id	start_date	end_date	consecutive_days
2	2025-07-01T00:00:00.000Z	2025-07-05T00:00:00.000Z	5
3	2025-07-04T00:00:00.000Z	2025-07-04T00:00:00.000Z	1
3	2025-07-06T00:00:00.000Z	2025-07-06T00:00:00.000Z	1
1	2025-07-01T00:00:00.000Z	2025-07-03T00:00:00.000Z	3
3	2025-07-02T00:00:00.000Z	2025-07-02T00:00:00.000Z	1
1	2025-07-05T00:00:00.000Z	2025-07-06T00:00:00.000Z	2

 

WITH login_data AS (
    SELECT DISTINCT user_id, login_date::date  -- 去重
    FROM login_log
),
numbered_logs AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM login_data
),
grouped_logs AS (
    SELECT
        user_id,
        login_date,
        rn,
        login_date - rn * INTERVAL '1 day' AS grp  -- 关键：构造分组依据
    FROM numbered_logs
),
grouped_counts AS (
    SELECT
        user_id,
        MIN(login_date) AS start_date,
        MAX(login_date) AS end_date,
        COUNT(*) AS consecutive_days
    FROM grouped_logs
    GROUP BY user_id, grp
)
SELECT *
FROM grouped_counts
WHERE consecutive_days > 3  -- 替换为你需要的 N

 

user_id	start_date	end_date	consecutive_days
2	2025-07-01T00:00:00.000Z	2025-07-05T00:00:00.000Z	5