SQL语句实现用户连续登录最长天数_MsSqlserver

在上篇连续 n 天登录用户中，我们其实埋下了一个有趣的引子 —— 如何计算每个用户的连续登录最长天数？这个看似简单的需求，实则蕴含着 sql 窗口函数与日期处理的巧妙结合。今天我们就来深入拆解这个问题，从技术实现到业务价值，带你全方位理解这一经典数据分析场景。

一、核心查询：用户连续登录最长天数

原理： 通过窗口函数和日期运算识别连续登录记录，并找出每个用户的最长连续登录天数、起始日期、终止日期。

1.多层嵌套子查询—抽丝剥茧看逻辑

-- 方法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;

计算每个用户的连续登录天数consecutive_days
- 同一用户登录周期可能有多个，只要中间有断开就重新计算，最小周期为1天
- 取周期内的起始日期和终止日期

对每个用户的多个连续登录周期进行排序
- 先按连续天数consecutive_days降序，为查询最长连续登录天数做准备
- 再按截止日期end_date降序，最长登录天数相同时，选取距今最近的周期

保留每个用户最大连续天数的记录rn=1

2.with 语句（cte）—更优雅的语法结构

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; -- 只保留每个用户最大连续天数的记录

with 语句（cte）查询其实和上面的逻辑是一样的，只是在sql语法结构上不同，创建了临时命名结果集consecutivecte，再标记了每个用户连续天数的记录命名为ranked表，最后通过rn=1只保留每个用户最大连续天数的记录。

相较而言cte查询逻辑更简单，嵌套层级不深，且代码更具可读性。下面我们来介绍这个新函数。

在 sql 中，with语句用于定义公共表表达式（common table expression，简称 cte），它允许你创建一个临时的命名结果集，这个结果集可以在后续的select、insert、update或delete语句中使用。

with [recursive] cte_name [(column_alias1, column_alias2,...)] as (
    -- cte的查询语句，通常是一个select查询
    select_statement
)
-- 使用cte的主查询语句
select * from cte_name;

基本语法
- recursive（可选）：表示该cte是递归的，用于处理递归数据结构，比如树形结构的数据
- cte_name：给cte起的名字，在后续查询中引用这个名字来使用该cte
- column_alias1,column_alias2,…（可选）：为cte查询结果中的列指定别名
- select_statement：具体的查询逻辑，用于生成cte的结果集

二、业务价值：最长登录天数的打开方式

sql查询用户连续最长登录天数，其实是分析用户黏性的重要指标，主要体现在以下几方面：

用户分层管理： 通过连续登录时长将用户划分为高、中、低黏性群体，比如连续登录超15天的用户可重点维护，低于7天的则需针对性唤醒。
产品优化参考： 若发现多数用户连续登录天数集中在某区间（如3 - 5天），可分析该阶段产品功能是否存在断层，比如是否在第4天缺乏引导用户继续登录的激励机制。
运营活动评估： 对比活动前后用户连续最长登录天数的变化，能直观判断活动对提升用户黏性的效果。例如某签到活动后，用户平均连续登录天数从7天提升至15天，说明活动有效。
预测流失风险： 当用户连续登录天数明显缩短或中断时，可能是流失预警信号，可及时推送召回消息。
商业价值挖掘： 高连续登录天数的用户通常对产品依赖度高，更可能转化为付费用户，或成为品牌传播的种子用户。

到此这篇关于sql用户连续登录最长天数的文章就介绍到这了,更多相关sql用户连续登录最长天数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！