SQL语句查询连续N天登录用户(解决方案)
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、建表：还原场景问题</a></li><li><a href="#_label1">二、查询：多种方法实现</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">1.自连接查询</a></li><li><a href="#_lab2_1_1">2.窗口函数</a></li><li><a href="#_lab2_1_2">3.日期差值分组</a></li></ul><li><a href="#_label2">三、总结：三种方法对比与拓展</a></li><ul class="second_class_ul"></ul></ul></div><p>前几天刷手机时看到一道有趣的<strong> </strong><a href="https://www.jb51.net/database/3503262d8.htm" target="_blank">SQL 题：</a><strong><a href="https://www.jb51.net/database/3503262d8.htm" target="_blank">查询连续 3 天登录的用户</a></strong>。这让我联想到之前讨论过的开窗函数，深入思考后发现其实还有多种实现方式。今天就来和大家分享几种解决方案，欢迎一起讨论！</p><p class="maodian"><a name="_label0"></a></p><h2>一、建表：还原场景问题</h2>
<p>1.创建用户登录记录表<code>t_login_records</code>，包含用户 ID 和登录日期两个核心字段</p>
<div class="jb51code"><pre class="brush:sql;">DROP TABLE IF EXISTS t_login_records;#若表存在删除
-- 创建用户登录记录表
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='用户登录记录表';</pre></div>
<p>2.插入测试数据</p>
<ul><li><strong>查看表结构</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">-- 查看表结构
DESC t_login_records;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609513174.jpg" /></p>
<ul><li><strong>数据预览</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">INSERT INTO t_login_records (user_id, login_date) VALUES
(1, '2023-01-01'),
(1, '2023-01-02'),
(1, '2023-01-03'),-- 用户1连续3天登录
(1, '2023-01-05'),
(2, '2023-01-01'),
(2, '2023-01-02'),
(2, '2023-01-04'),-- 用户2不连续
(3, '2023-01-01'),
(3, '2023-01-02'),
(3, '2023-01-03'),-- 用户3连续3天登录
(3, '2023-01-04'),-- 用户3连续4天登录
(4, '2023-01-01'),
(4, '2023-01-03'),
(4, '2023-01-05'),-- 用户4不连续
(5, '2023-01-01'),
(5, '2023-01-02'),
(5, '2023-01-03'),-- 用户5连续3天登录
(5, '2023-01-04'),-- 用户5连续4天登录
(5, '2023-01-05'), -- 用户5连续5天登录
(5, '2023-02-01'), -- 断开
(5, '2023-02-02'),-- 用户5再连续2天登录
(5, '2023-02-03');-- 用户5再连续3天登录
SELECT * FROM t_login_records;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609513228.jpg" /></p>
<p class="maodian"><a name="_label1"></a></p><h2>二、查询：多种方法实现</h2>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>1.自连接查询</h3>
<ul><li><strong>原理：</strong> 通过三次连接同一张表，强制匹配同一用户的三条登录记录，且日期依此相差 1 天</li></ul>
<div class="jb51code"><pre class="brush:sql;">-- 方法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;</pre></div>
<ul><li><strong>查询结果：</strong><code>user_id</code>为1、3、5的用户</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609513139.jpg" /></p>
<ul><li><strong>执行步骤：</strong> 自连接将表t1（基准记录）与t2（次日记录）、t3（第三日记录）连接，确保</li><li>用户相同<ul><li><code>t2.login_date=t1.login_date+1</code></li><li><code>t3.login_date=t1.login_date+2</code></li><li><code>DISTINCT</code>过滤重复的<code>user_id</code></li></ul></li><li><strong>示例数据验证：</strong><ul><li><code>user_id=1</code>的用户在 2023-01-01、2023-01-02、2023-01-03 连续登录：</li><li><code>t1(2023-01-01)</code> → <code>t2(2023-01-02)</code> → <code>t3(2023-01-03)</code>，匹配成功。</li></ul></li><li><code>user_id=2</code>的用户仅在 2023-01-01 和 2023-01-02连续登录：<ul><li>无法找到连续三天的记录，匹配失败。</li></ul></li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609513250.jpg" /></p>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>2.窗口函数</h3>
<ul><li><strong>原理：</strong> 使用窗口函数<code>LEAD()</code>获取每个用户后续的登录日期，直接判断是否连续。</li></ul>
<div class="jb51code"><pre class="brush:sql;">-- 方法2：窗口函数（适用于支持LEAD函数的数据库，如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;</pre></div>
<ul><li><strong>查询结果：</strong><code>user_id</code>依然为1、3、5的用户</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609513199.jpg" /></p>
<ul><li><strong>执行步骤：</strong> 窗口函数和条件过滤
<ul><li><code>LEAD(login_date, 1)</code>：获取当前记录的下一条日期。</li><li><code>LEAD(login_date, 2)</code>：获取当前记录的下两条日期。</li><li>确保<code>next_day = login_date + 1</code>且<code>next_2_days = login_date + 2</code>。</li></ul></li><li><strong>示例数据验证：</strong><ul><li>用户1的第一条记录（2023-01-01）：</li><li>next_day=2023-01-02（差值1天）</li><li>next_2_days=2023-01-03（差值2天）</li><li>满足条件，用户1被选中。</li><li>用户4的第一条记录（2023-01-01）：</li><li>next_day=2023-01-03</li><li>next_2_days=2023-01-05</li></ul></li></ul>
<p>不满足条件，用户4未被选中。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609513258.jpg" /></p>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>3.日期差值分组</h3>
<ul><li><strong>原理：</strong> 将每个登录日期减去其在用户组内的排序序号，连续日期会得到相同的差值，通过分组统计差值出现次数即可。</li></ul>
<div class="jb51code"><pre class="brush:sql;">-- 方法3：日期差值分组（适用于支持ROW_NUMBER的数据库）
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;</pre></div>
<ul><li><strong>查询结果：</strong><code>user_id</code>依然为1、3、5的用户</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609513257.jpg" /></p>
<ul><li><strong>执行步骤：</strong><code>计算分组标识grp</code>和<code>分组统计</code>
<ul><li><code>ROW_NUMBER()</code>为每个用户的登录记录分配连续序号（1,2,3…）。</li><li><code>DATE_SUB(login_date,ROW_NUMBER())</code>：将日期减去序号，连续日期会得到相同的结果。</li><li>按<code>user_id</code>和<code>grp</code>分组，统计每组的日期数量，若≥3则为连续登录。</li></ul></li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609513260.jpg" /></p>
<ul><li><strong>DATE_SUB()函数：</strong><code>DATE_SUB(date, INTERVAL expr unit)</code>是 SQL 中的一个日期函数，用于从指定日期中减去一个时间间隔。
<ul><li><code>INTERVAL expr unit</code>：指定要减去的时间间隔</li><li><code>INTERVAL</code>：固定关键字，表示时间间隔</li><li><code>expr</code> 是一个数值</li><li><code>unit</code> 是时间单位（如 DAY、MONTH、YEAR 等）</li></ul></li></ul>
<p class="maodian"><a name="_label2"></a></p><h2>三、总结：三种方法对比与拓展</h2>
<table><thead><tr><th>方法</th><th>优点</th><th>缺点</th></tr></thead><tbody><tr><td><strong>自连接</strong></td><td>简单直接，兼容性强</td><td>性能差（多次扫描表）</td></tr><tr><td><strong>窗口函数</strong></td><td>逻辑清晰，一步到位</td><td>需数据库支持窗口函数</td></tr><tr><td><strong>日期差值</strong></td><td>性能最优，逻辑巧妙</td><td>理解难度较高</td></tr></tbody></table>
<p>这道题虽然仅要求查询连续 3 天登录的用户，但通过这三种方法我们可以举一反三。如果要查询连续 4 天、5 天甚至 N 天登录的用户，第三种日期差值分组法更具优势，只需修改 <code>HAVING COUNT(DISTINCT login_date) >= N</code> 即可实现 “一力破万法” 的效果！</p>
頁:
[1]