SQL语句实现用户连续登录最长天数
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、核心查询：用户连续登录最长天数</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.多层嵌套子查询—抽丝剥茧看逻辑</a></li><li><a href="#_lab2_0_1">2.WITH 语句（CTE）—更优雅的语法结构</a></li></ul><li><a href="#_label1">二、业务价值：最长登录天数的打开方式</a></li><ul class="second_class_ul"></ul></ul></div><p>在上篇<strong>连续 N 天登录用户</strong>中，我们其实埋下了一个有趣的引子 —— 如何计算每个用户的<strong>连续登录最长天数</strong>？这个看似简单的需求，实则蕴含着 SQL 窗口函数与日期处理的巧妙结合。今天我们就来深入拆解这个问题，从技术实现到业务价值，带你全方位理解这一经典数据分析场景。</p><p class="maodian"><a name="_label0"></a></p><h2>一、核心查询：用户连续登录最长天数</h2>
<p><strong>原理：</strong> 通过窗口函数和日期运算识别连续登录记录，并找出每个用户的最长连续登录天数、起始日期、终止日期。</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1.多层嵌套子查询—抽丝剥茧看逻辑</h3>
<div class="jb51code"><pre class="brush:sql;">-- 方法1：嵌套查询
-- 取rn=1每个用户连续登录最长的天数
-- 同一用户可能有多个连续登录周期，若存在最长周期相同，取距今最近的起始日期和终止日期
SELECT
user_id,
consecutive_days AS '连续登录最长天数',
start_date AS '起始日期',
end_date AS '终止日期'
FROM
(
-- 用户连续登录天数降序排列，加序号
SELECT
*,
ROW_NUMBER ( ) OVER ( PARTITION BY user_id ORDER BY consecutive_days DESC, nd_date DESC) AS rn
FROM
(
-- 用户连续登录天数：可能有多个，只要断开就重新计算，最小为1
-- 起始日期、终止日期
SELECT
user_id,
COUNT( DISTINCT login_date ) AS consecutive_days,
MIN( login_date ) AS start_date,
MAX( login_date ) AS end_date
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 ser_id,grp
) a
) b
WHERE
rn = 1;</pre></div>
<ul><li>计算每个用户的连续登录天数<code>consecutive_days</code>
<ul><li>同一用户登录周期可能有多个，只要中间有断开就重新计算，最小周期为1天</li><li>取周期内的起始日期和终止日期</li></ul></li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609414794.jpg" /></p>
<ul><li>对每个用户的多个连续登录周期进行排序<ul><li>先按连续天数<code>consecutive_days</code>降序，为查询最长连续登录天数做准备</li><li>再按截止日期<code>end_date</code>降序，最长登录天数相同时，选取距今最近的周期</li></ul></li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609414787.jpg" /></p>
<ul><li>保留每个用户最大连续天数的记录<code>rn=1</code></li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025092609414733.jpg" /></p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2.WITH 语句（CTE）—更优雅的语法结构</h3>
<div class="jb51code"><pre class="brush:sql;">WITH ConsecutiveCTE AS (
SELECT
user_id,
COUNT(DISTINCT login_date) AS consecutive_days,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date
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
)
SELECT
user_id,
consecutive_days AS '连续登录最长天数',
start_date AS '起始日期',
end_date AS '终止日期'
FROM (
SELECT
*,
-- 对每个用户，标记其最大连续天数的记录
ROW_NUMBER ( ) OVER ( PARTITION BY user_id ORDER BY consecutive_days DESC, nd_date DESC) AS rn
FROM ConsecutiveCTE
) ranked
WHERE rn = 1; -- 只保留每个用户最大连续天数的记录</pre></div>
<p>WITH 语句（CTE）查询其实和上面的逻辑是一样的，只是在SQL语法结构上不同，创建了临时命名结果集<code>ConsecutiveCTE</code>，再标记了每个用户连续天数的记录命名为<code>ranked</code>表，最后通过<code>rn=1</code>只保留每个用户最大连续天数的记录。</p>
<p>相较而言<strong>CTE</strong>查询逻辑更简单，嵌套层级不深，且代码更具可读性。下面我们来介绍这个新函数。</p>
<ul><li>在 SQL 中，<code>WITH</code>语句用于定义公共表表达式（Common Table Expression，简称 CTE），它允许你创建一个临时的命名结果集，这个结果集可以在后续的<code>SELECT</code>、<code>INSERT</code>、<code>UPDATE</code>或<code>DELETE</code>语句中使用。</li></ul>
<div class="jb51code"><pre class="brush:sql;">WITH cte_name [(column_alias1, column_alias2,...)] AS (
-- CTE的查询语句，通常是一个SELECT查询
SELECT_statement
)
-- 使用CTE的主查询语句
SELECT * FROM cte_name;</pre></div>
<ul><li>基本语法<ul><li><code>recursive</code>（可选）：表示该CTE是递归的，用于处理递归数据结构，比如树形结构的数据</li><li><code>cte_name</code>：给CTE起的名字，在后续查询中引用这个名字来使用该CTE</li><li><code>column_alias1</code>,<code>column_alias2</code>,…（可选）：为CTE查询结果中的列指定别名</li><li><code>SELECT_statement</code>：具体的查询逻辑，用于生成CTE的结果集</li></ul></li></ul>
<p class="maodian"><a name="_label1"></a></p><h2>二、业务价值：最长登录天数的打开方式</h2>
<p>SQL查询用户连续最长登录天数，其实是分析用户黏性的重要指标，主要体现在以下几方面：</p>
<ul><li><strong>用户分层管理：</strong> 通过连续登录时长将用户划分为高、中、低黏性群体，比如连续登录超15天的用户可重点维护，低于7天的则需针对性唤醒。</li><li><strong>产品优化参考：</strong> 若发现多数用户连续登录天数集中在某区间（如3 - 5天），可分析该阶段产品功能是否存在断层，比如是否在第4天缺乏引导用户继续登录的激励机制。</li><li><strong>运营活动评估：</strong> 对比活动前后用户连续最长登录天数的变化，能直观判断活动对提升用户黏性的效果。例如某签到活动后，用户平均连续登录天数从7天提升至15天，说明活动有效。</li><li><strong>预测流失风险：</strong> 当用户连续登录天数明显缩短或中断时，可能是流失预警信号，可及时推送召回消息。</li><li><strong>商业价值挖掘：</strong> 高连续登录天数的用户通常对产品依赖度高，更可能转化为付费用户，或成为品牌传播的种子用户。</li></ul>
頁:
[1]