在上篇連續(xù) N 天登錄用戶中，我們其實(shí)埋下了一個有趣的引子 —— 如何計(jì)算每個用戶的連續(xù)登錄最長天數(shù)？這個看似簡單的需求，實(shí)則蘊(yùn)含著 SQL 窗口函數(shù)與日期處理的巧妙結(jié)合。今天我們就來深入拆解這個問題，從技術(shù)實(shí)現(xiàn)到業(yè)務(wù)價值，帶你全方位理解這一經(jīng)典數(shù)據(jù)分析場景。

一、核心查詢：用戶連續(xù)登錄最長天數(shù)

原理： 通過窗口函數(shù)和日期運(yùn)算識別連續(xù)登錄記錄，并找出每個用戶的最長連續(xù)登錄天數(shù)、起始日期、終止日期。

1.多層嵌套子查詢—抽絲剝繭看邏輯

-- 方法1：嵌套查詢
-- 取rn=1每個用戶連續(xù)登錄最長的天數(shù)
-- 同一用戶可能有多個連續(xù)登錄周期，若存在最長周期相同，取距今最近的起始日期和終止日期
SELECT
	user_id,
	consecutive_days AS '連續(xù)登錄最長天數(shù)',
	start_date AS '起始日期',
	end_date AS '終止日期' 
FROM
	(
    -- 用戶連續(xù)登錄天數(shù)降序排列，加序號
	SELECT
		*,
		ROW_NUMBER ( ) OVER ( PARTITION BY user_id ORDER BY consecutive_days DESC, nd_date DESC) AS rn
	FROM
		(
        -- 用戶連續(xù)登錄天數(shù)：可能有多個，只要斷開就重新計(jì)算，最小為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;

• 計(jì)算每個用戶的連續(xù)登錄天數(shù)consecutive_days
  • 同一用戶登錄周期可能有多個，只要中間有斷開就重新計(jì)算，最小周期為1天
  • 取周期內(nèi)的起始日期和終止日期

• 對每個用戶的多個連續(xù)登錄周期進(jìn)行排序
  • 先按連續(xù)天數(shù)consecutive_days降序，為查詢最長連續(xù)登錄天數(shù)做準(zhǔn)備
  • 再按截止日期end_date降序，最長登錄天數(shù)相同時，選取距今最近的周期

• 保留每個用戶最大連續(xù)天數(shù)的記錄rn=1

2.WITH 語句（CTE）—更優(yōu)雅的語法結(jié)構(gòu)

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 '連續(xù)登錄最長天數(shù)',
    start_date AS '起始日期',
    end_date AS '終止日期'
FROM (
    SELECT 
        *,
        -- 對每個用戶，標(biāo)記其最大連續(xù)天數(shù)的記錄
        ROW_NUMBER ( ) OVER ( PARTITION BY user_id ORDER BY consecutive_days DESC, nd_date DESC) AS rn
    FROM ConsecutiveCTE
) ranked
WHERE rn = 1; -- 只保留每個用戶最大連續(xù)天數(shù)的記錄

WITH 語句（CTE）查詢其實(shí)和上面的邏輯是一樣的，只是在SQL語法結(jié)構(gòu)上不同，創(chuàng)建了臨時命名結(jié)果集ConsecutiveCTE，再標(biāo)記了每個用戶連續(xù)天數(shù)的記錄命名為ranked表，最后通過rn=1只保留每個用戶最大連續(xù)天數(shù)的記錄。

相較而言CTE查詢邏輯更簡單，嵌套層級不深，且代碼更具可讀性。下面我們來介紹這個新函數(shù)。

• 在 SQL 中，WITH語句用于定義公共表表達(dá)式（Common Table Expression，簡稱 CTE），它允許你創(chuàng)建一個臨時的命名結(jié)果集，這個結(jié)果集可以在后續(xù)的SELECT、INSERT、UPDATE或DELETE語句中使用。

WITH [recursive] cte_name [(column_alias1, column_alias2,...)] AS (
    -- CTE的查詢語句，通常是一個SELECT查詢
    SELECT_statement
)
-- 使用CTE的主查詢語句
SELECT * FROM cte_name;

• 基本語法
  • recursive（可選）：表示該CTE是遞歸的，用于處理遞歸數(shù)據(jù)結(jié)構(gòu)，比如樹形結(jié)構(gòu)的數(shù)據(jù)
  • cte_name：給CTE起的名字，在后續(xù)查詢中引用這個名字來使用該CTE
  • column_alias1,column_alias2,…（可選）：為CTE查詢結(jié)果中的列指定別名
  • SELECT_statement：具體的查詢邏輯，用于生成CTE的結(jié)果集

二、業(yè)務(wù)價值：最長登錄天數(shù)的打開方式

SQL查詢用戶連續(xù)最長登錄天數(shù)，其實(shí)是分析用戶黏性的重要指標(biāo)，主要體現(xiàn)在以下幾方面：

• 用戶分層管理： 通過連續(xù)登錄時長將用戶劃分為高、中、低黏性群體，比如連續(xù)登錄超15天的用戶可重點(diǎn)維護(hù)，低于7天的則需針對性喚醒。
• 產(chǎn)品優(yōu)化參考： 若發(fā)現(xiàn)多數(shù)用戶連續(xù)登錄天數(shù)集中在某區(qū)間（如3 - 5天），可分析該階段產(chǎn)品功能是否存在斷層，比如是否在第4天缺乏引導(dǎo)用戶繼續(xù)登錄的激勵機(jī)制。
• 運(yùn)營活動評估： 對比活動前后用戶連續(xù)最長登錄天數(shù)的變化，能直觀判斷活動對提升用戶黏性的效果。例如某簽到活動后，用戶平均連續(xù)登錄天數(shù)從7天提升至15天，說明活動有效。
• 預(yù)測流失風(fēng)險： 當(dāng)用戶連續(xù)登錄天數(shù)明顯縮短或中斷時，可能是流失預(yù)警信號，可及時推送召回消息。
• 商業(yè)價值挖掘： 高連續(xù)登錄天數(shù)的用戶通常對產(chǎn)品依賴度高，更可能轉(zhuǎn)化為付費(fèi)用戶，或成為品牌傳播的種子用戶。

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

最新評論