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

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

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

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

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

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

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

2.WITH 語(yǔ)句（CTE）—更優(yōu)雅的語(yǔ)法結(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ù)登錄最長(zhǎng)天數(shù)',
    start_date AS '起始日期',
    end_date AS '終止日期'
FROM (
    SELECT 
        *,
        -- 對(duì)每個(gè)用戶，標(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; -- 只保留每個(gè)用戶最大連續(xù)天數(shù)的記錄

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

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

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

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

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

二、業(yè)務(wù)價(jià)值：最長(zhǎng)登錄天數(shù)的打開(kāi)方式

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

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

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

最新評(píng)論