前幾天刷手機(jī)時(shí)看到一道有趣的 SQL 題：查詢連續(xù) 3 天登錄的用戶。這讓我聯(lián)想到之前討論過(guò)的開(kāi)窗函數(shù)，深入思考后發(fā)現(xiàn)其實(shí)還有多種實(shí)現(xiàn)方式。今天就來(lái)和大家分享幾種解決方案，歡迎一起討論！

一、建表：還原場(chǎng)景問(wèn)題

1.創(chuàng)建用戶登錄記錄表t_login_records，包含用戶 ID 和登錄日期兩個(gè)核心字段

DROP TABLE IF EXISTS t_login_records;#若表存在刪除
-- 創(chuàng)建用戶登錄記錄表
CREATE TABLE t_login_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    login_date DATE NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='用戶登錄記錄表';

2.插入測(cè)試數(shù)據(jù)

• 查看表結(jié)構(gòu)

-- 查看表結(jié)構(gòu)	
DESC t_login_records;

• 數(shù)據(jù)預(yù)覽

INSERT INTO t_login_records (user_id, login_date) VALUES
(1, '2023-01-01'),
(1, '2023-01-02'),
(1, '2023-01-03'),  -- 用戶1連續(xù)3天登錄
(1, '2023-01-05'),
(2, '2023-01-01'),
(2, '2023-01-02'),
(2, '2023-01-04'),  -- 用戶2不連續(xù)
(3, '2023-01-01'),
(3, '2023-01-02'),
(3, '2023-01-03'),  -- 用戶3連續(xù)3天登錄
(3, '2023-01-04'),  -- 用戶3連續(xù)4天登錄
(4, '2023-01-01'),
(4, '2023-01-03'),
(4, '2023-01-05'),  -- 用戶4不連續(xù)    
(5, '2023-01-01'),
(5, '2023-01-02'),
(5, '2023-01-03'),  -- 用戶5連續(xù)3天登錄
(5, '2023-01-04'),  -- 用戶5連續(xù)4天登錄
(5, '2023-01-05'),	-- 用戶5連續(xù)5天登錄
(5, '2023-02-01'),	-- 斷開(kāi)
(5, '2023-02-02'),  -- 用戶5再連續(xù)2天登錄
(5, '2023-02-03');  -- 用戶5再連續(xù)3天登錄
SELECT * FROM t_login_records;

二、查詢：多種方法實(shí)現(xiàn)

1.自連接查詢

• 原理： 通過(guò)三次連接同一張表，強(qiáng)制匹配同一用戶的三條登錄記錄，且日期依此相差 1 天

-- 方法1：自連接查詢
SELECT DISTINCT t1.user_id
FROM t_login_records t1
JOIN t_login_records t2 
  ON t1.user_id = t2.user_id 
  AND DATEDIFF(t2.login_date, t1.login_date) = 1
JOIN t_login_records t3 
  ON t1.user_id = t3.user_id 
  AND DATEDIFF(t3.login_date, t1.login_date) = 2;

• 查詢結(jié)果：user_id為1、3、5的用戶

• 執(zhí)行步驟： 自連接將表t1（基準(zhǔn)記錄）與t2（次日記錄）、t3（第三日記錄）連接，確保
• 用戶相同
  • t2.login_date=t1.login_date+1
  • t3.login_date=t1.login_date+2
  • DISTINCT過(guò)濾重復(fù)的user_id
• 示例數(shù)據(jù)驗(yàn)證：
  • user_id=1的用戶在 2023-01-01、2023-01-02、2023-01-03 連續(xù)登錄：
  • t1(2023-01-01) → t2(2023-01-02) → t3(2023-01-03)，匹配成功。
• user_id=2的用戶僅在 2023-01-01 和 2023-01-02連續(xù)登錄：
  • 無(wú)法找到連續(xù)三天的記錄，匹配失敗。

2.窗口函數(shù)

• 原理： 使用窗口函數(shù)LEAD()獲取每個(gè)用戶后續(xù)的登錄日期，直接判斷是否連續(xù)。

-- 方法2：窗口函數(shù)（適用于支持LEAD函數(shù)的數(shù)據(jù)庫(kù)，如MySQL 8.0+、PostgreSQL）
SELECT DISTINCT user_id
FROM (
  SELECT 
    user_id,
    login_date,
    LEAD(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS next_day,
    LEAD(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS next_2_days
  FROM t_login_records
) t
WHERE DATEDIFF(next_day, login_date) = 1 
  AND DATEDIFF(next_2_days, login_date) = 2;

• 查詢結(jié)果：user_id依然為1、3、5的用戶

• 執(zhí)行步驟： 窗口函數(shù)和條件過(guò)濾
  • LEAD(login_date, 1)：獲取當(dāng)前記錄的下一條日期。
  • LEAD(login_date, 2)：獲取當(dāng)前記錄的下兩條日期。
  • 確保next_day = login_date + 1且next_2_days = login_date + 2。
• 示例數(shù)據(jù)驗(yàn)證：
  • 用戶1的第一條記錄（2023-01-01）：
  • next_day=2023-01-02（差值1天）
  • next_2_days=2023-01-03（差值2天）
  • 滿足條件，用戶1被選中。
  • 用戶4的第一條記錄（2023-01-01）：
  • next_day=2023-01-03
  • next_2_days=2023-01-05

不滿足條件，用戶4未被選中。

3.日期差值分組

• 原理： 將每個(gè)登錄日期減去其在用戶組內(nèi)的排序序號(hào)，連續(xù)日期會(huì)得到相同的差值，通過(guò)分組統(tǒng)計(jì)差值出現(xiàn)次數(shù)即可。

-- 方法3：日期差值分組（適用于支持ROW_NUMBER的數(shù)據(jù)庫(kù)）
SELECT user_id
FROM (
  SELECT 
    user_id,
    login_date,
    DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM t_login_records
) t
GROUP BY user_id, grp
HAVING COUNT(DISTINCT login_date) >= 3;

• 查詢結(jié)果：user_id依然為1、3、5的用戶

• 執(zhí)行步驟：計(jì)算分組標(biāo)識(shí)grp和分組統(tǒng)計(jì)
  • ROW_NUMBER()為每個(gè)用戶的登錄記錄分配連續(xù)序號(hào)（1,2,3…）。
  • DATE_SUB(login_date,ROW_NUMBER())：將日期減去序號(hào)，連續(xù)日期會(huì)得到相同的結(jié)果。
  • 按user_id和grp分組，統(tǒng)計(jì)每組的日期數(shù)量，若≥3則為連續(xù)登錄。

• DATE_SUB()函數(shù)：DATE_SUB(date, INTERVAL expr unit)是 SQL 中的一個(gè)日期函數(shù)，用于從指定日期中減去一個(gè)時(shí)間間隔。
  • INTERVAL expr unit：指定要減去的時(shí)間間隔
  • INTERVAL：固定關(guān)鍵字，表示時(shí)間間隔
  • expr 是一個(gè)數(shù)值
  • unit 是時(shí)間單位（如 DAY、MONTH、YEAR 等）

三、總結(jié)：三種方法對(duì)比與拓展

這道題雖然僅要求查詢連續(xù) 3 天登錄的用戶，但通過(guò)這三種方法我們可以舉一反三。如果要查詢連續(xù) 4 天、5 天甚至 N 天登錄的用戶，第三種日期差值分組法更具優(yōu)勢(shì)，只需修改 HAVING COUNT(DISTINCT login_date) >= N 即可實(shí)現(xiàn) “一力破萬(wàn)法” 的效果！

到此這篇關(guān)于SQL語(yǔ)句查詢連續(xù)N天登錄用戶的文章就介紹到這了,更多相關(guān)sql連續(xù)N天登錄用戶內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！