1. 項目需求拆解連續(xù)登錄到底在算什么先說結(jié)論這個需求在數(shù)據(jù)分析里幾乎屬于“必考題型”它本質(zhì)是兩個問題第一個問題一個用戶歷史上最多連續(xù)多少天登錄過比如連續(xù)3天、連續(xù)7天中間斷開一天就重新計數(shù)。第二個問題用戶每次斷開后重新登錄要生成一個從1開始的序號讓后續(xù)統(tǒng)計能區(qū)分“第幾段連續(xù)區(qū)間”。比如用戶1月1日、1月2日登錄1月3日沒登錄1月4日又登錄那么前兩天的序號是1、2后一天的序號重新從1開始。這兩件事聽起來簡單但真正落到 Hive SQL 里需要想清楚“連續(xù)”的數(shù)學(xué)定義、日期去重、跨天跨月邊界以及 Hive 特有的并行和字段處理細節(jié)。我在實際項目中接過不少類似需求有的要算連續(xù)簽到、有的要算商家連續(xù)營業(yè)天數(shù)、有的要算用戶活躍連續(xù)性底層邏輯完全一致。只要把核心方法吃透任何連續(xù)性問題都能直接套。這個方案適合誰參考一類是剛接觸 Hive 開窗函數(shù)的人另一類是已經(jīng)寫了一些 SQL 但總覺得邏輯繞不清的人。我會從最基礎(chǔ)的行文思路上講把每一步拆開連 SQL 里每一步在干什么都解釋清楚最后給出完整可復(fù)制的實現(xiàn)。2. 前置知識Hive 開窗函數(shù)和下鉆的邏輯基礎(chǔ)2.1 為什么計算連續(xù)性問題必須用開窗函數(shù)先明確一個痛點普通 GROUP BY 只能做聚合算不出“連續(xù)”。因為你不知道某一行是不是和前一行日期連續(xù)而有了開窗函數(shù)后每一行都能看到它前一行、后一行的對應(yīng)字段。這就像排隊點名你不僅知道自己是誰還能看到排在自己前一位的人是誰一下就能判斷中間有沒有缺人。Hive 里常用的開窗函數(shù)有 ROW_NUMBER()、LAG()、LEAD()、SUM() OVER() 等。計算連續(xù)登錄天數(shù)的核心套路有兩個基于 LAG() 判斷與前一行是否連續(xù)再給斷開處打標(biāo)記基于日期減去行號的差值分組把連續(xù)的行歸到同一個組里。這里要特別提醒不要用自關(guān)聯(lián)去判斷連續(xù)性。數(shù)據(jù)量一上來自關(guān)聯(lián)的性能和復(fù)雜度都不可控而且 SQL 寫出來很難維護。開窗函數(shù)的方式幾乎是固定的性能和可讀性都最好。2.2 日期去重很多連續(xù)性算錯的第一步登錄記錄表可能一行一天也可能同一人同一天有很多條記錄比如用戶一天內(nèi)多次打開 App每個事件都落了一條日志。如果不去重1月1日有三條記錄就會被錯誤地認為是連續(xù)三天。所以在計算之前必須對登錄日期做去重才能進入“連續(xù)日期序列”的下一步。去重標(biāo)準寫法SELECT DISTINCT user_id, login_date FROM login_log;如果你用的是 SparkSQL也可以用 GROUP BY user_id, login_date效果一樣。這個操作看起來簡單但漏掉這一步后面全盤皆輸。2.3 日期格式與日期相減的攔路問題另一個常見坑是日期并不是標(biāo)準日期類型。有的是字符串20250101有的是y-M-d格式有的是帶時間戳的 2025-01-01 12:34:56。做日期差之前必須統(tǒng)一為標(biāo)準日期類型。Hive 里常用的日期函數(shù)to_date(字段)把時間字符串轉(zhuǎn)成日期格式date_sub(日期, n)日期往前推n天datediff(日期1, 日期2)返回日期1減日期2的天數(shù)regexp_replace把 20250101 轉(zhuǎn)成 2025-01-01寫法為 regexp_replace(dt, (\d{4})(\d{2})(\d{2}), $1-$2-$3)我見過很多同學(xué)在測試環(huán)境日期格式正常生產(chǎn)環(huán)境因為日期字段帶了時分秒導(dǎo)致連續(xù)判斷全部失效。所以第一步必須做數(shù)據(jù)探查看清楚日期字段的真實類型和格式。3. 核心方法一基于 LAG 和分組標(biāo)記實現(xiàn)最大連續(xù)登錄天數(shù)3.1 思路拆解怎么定義“同一段連續(xù)區(qū)間”假設(shè)有這樣一個用戶的登錄日期序列2025-01-01 2025-01-02 2025-01-03 2025-01-05 2025-01-06可以看到1月1日到1月3日是連續(xù)的之后斷了1月5日到1月6日又是連續(xù)的。我們需要讓 SQL 自己識別出這兩段區(qū)間并給每段區(qū)間一個組編號。識別斷點的思路如果當(dāng)前行的日期減去它上一行的日期結(jié)果大于1說明上一行和當(dāng)前行之間不連續(xù)當(dāng)前行就是一段新區(qū)間的起點。具體實現(xiàn)先用 LAG(login_date, 1, NULL) OVER (PARTITION BY user_id ORDER BY login_date) 拿到前一行日期。然后判斷 datediff(當(dāng)前日期, 前一行日期) 是否等于1。如果不等于1說明當(dāng)前行是新一段連續(xù)區(qū)間的開頭標(biāo)記為1否則標(biāo)記為0。有了標(biāo)記之后再用 SUM(標(biāo)記) OVER (PARTITION BY user_id ORDER BY login_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 做累加。這個累加結(jié)果就是分組編號。因為新區(qū)間起點為1累加后會把后續(xù)所有行都歸到同一個段數(shù)字下直到下一個斷點出現(xiàn)。拿上面的例子走一遍登錄日期 前一日 是否斷點 累加和 2025-01-01 NULL 1 1 2025-01-02 2025-01-01 0 1 2025-01-03 2025-01-02 0 1 2025-01-05 2025-01-03 1 2 2025-01-06 2025-01-05 0 2可見前三天分組為1后兩天分組為2分段完全正確。3.2 完整 SQL直接能跑的 Hive 腳本下面給出一套完整可用的 Hive SQL。假設(shè)登錄日志表名為 login_log字段為 user_id、login_datetime可能有多條重復(fù)日期也可能有時間戳。第一步做清洗去重第二步算分組第三步按組聚合算出每次連續(xù)天數(shù)第四步取最大。WITH login_dedup AS ( SELECT user_id, to_date(login_datetime) AS login_date FROM login_log GROUP BY user_id, to_date(login_datetime) ), login_group AS ( SELECT user_id, login_date, CASE WHEN datediff(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) 1 THEN 0 ELSE 1 END AS is_new_group_start, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login_date FROM login_dedup ), login_with_group_id AS ( SELECT user_id, login_date, SUM(is_new_group_start) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id FROM login_group ), login_duration AS ( SELECT user_id, group_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS continuous_days FROM login_with_group_id GROUP BY user_id, group_id ) SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM login_duration GROUP BY user_id;如果想把最大連續(xù)登錄天數(shù)對應(yīng)的起止日期也一起帶出來可以在最后一步用排序或結(jié)構(gòu)體。常見做法是把每段連續(xù)的天數(shù)、開始日期、結(jié)束日期都聚合到一個 map 中再取天數(shù)最大的那一個。但實際業(yè)務(wù)中大多數(shù)只要求一個數(shù)字所以上面這個 SQL 已經(jīng)足夠。3.3 為什么寫 CASE 而不是直接 LAG 減 1有同學(xué)會問既然 LAG 能拿到前一日日期能不能直接用一個字段標(biāo)記“是否連續(xù)”然后分組我給你一個反面示例-- 錯誤寫法示意 if(datediff(login_date, lag_date)1, group_id, group_id1)這樣寫的問題在于每一行的 group_id 都依賴前一行計算完的結(jié)果而 SQL 開窗函數(shù)中不能以這種方式迭代賦值。正確做法是把“是否新組”做成0/1的獨立標(biāo)記再通過 SUM 累加生成組號。這是 SQL 實現(xiàn)連續(xù)分組的經(jīng)典范式理解了這個后面很多問題都能解決。另外要注意 LAG 的第一個參數(shù)是列名第二個參數(shù)是偏移量第三個參數(shù)是默認值。第三個參數(shù)不寫的話默認就是 NULL。這里我們故意不寫因為第一行的前一日是 NULLdatediff(NULL) 結(jié)果是 NULLCASE 的判斷條件不成立會走 ELSE 分支得到1正好符合“第一行作為新起點”的語義。3.4 跨年、跨月、時區(qū)問題一并處理連續(xù)登錄的日期可能跨年比如 12月31日 到 1月1日。只要日期類型正確、datediff 直接用日期相減跨年不是問題Hive 的日期函數(shù)內(nèi)部會處理好絕對天數(shù)差。真正要注意的是時區(qū)問題。如果日志里的登錄時間是 UTC而業(yè)務(wù)期望的是北京時間那么直接 to_date 會使得部分用戶晚上登錄被算到前一天。比如 UTC 時間 1月1日16點北京時間已經(jīng)是1月2日0點但 to_date 后還是1月1日。這在計算連續(xù)登錄次數(shù)時會造成偏差。穩(wěn)妥的做法是在清洗層統(tǒng)一做時區(qū)轉(zhuǎn)換再 to_dateto_date(from_utc_timestamp(login_datetime, GMT8)) AS login_date具體時區(qū)名要按集群支持情況調(diào)整。如果你們公司日志埋點本身就統(tǒng)一用東八區(qū)那就不存在這個問題但建議每個項目都確認一次別想當(dāng)然。4. 核心方法二斷連后重新編號連續(xù)序號4.1 業(yè)務(wù)場景什么叫連續(xù)序號“連續(xù)序號”本質(zhì)是給用戶每一段連續(xù)登錄區(qū)間內(nèi)部的行從1、2、3……依次編號一旦登錄日期斷開則重新從1開始。也就是user_id login_date seq A 2025-01-01 1 A 2025-01-02 2 A 2025-01-03 3 A 2025-01-05 1 A 2025-01-06 2這個序號在很多場景下有用比如判斷用戶是否連續(xù)登錄第7天直接取 seq7 的記錄統(tǒng)計用戶有多少次連續(xù)3天以上的活躍期可以先按 seq 判斷生成“連續(xù)簽到獎勵”報表比如第幾段連續(xù)簽到的第幾天。如果讓排序字段直接 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)得到的將是全量序號 1、2、3、4、5而不是在斷點重置的序號。所以必須先對連續(xù)區(qū)間分組再在每組內(nèi)做編號。4.2 實現(xiàn)思路先分組再編號分組的方法和上一節(jié)完全一樣先用 LAG 判斷斷點再用 SUM(0/1) 累加成 group_id。之后組內(nèi)排序編號就非常簡單ROW_NUMBER() OVER (PARTITION BY user_id, group_id ORDER BY login_date) AS seq注意這里 PARTITION BY 是 user_id 和 group_id 兩個字段的組合。group_id 在同一個用戶內(nèi)不同連續(xù)區(qū)間取值不同所以可以準確地把每一段連續(xù)區(qū)間分出來。4.3 完整 SQL連續(xù)序號生成WITH login_dedup AS ( SELECT user_id, to_date(login_datetime) AS login_date FROM login_log GROUP BY user_id, to_date(login_datetime) ), login_with_flag AS ( SELECT user_id, login_date, CASE WHEN LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) IS NULL THEN 1 WHEN datediff(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) 1 THEN 0 ELSE 1 END AS is_new_start FROM login_dedup ), login_with_group AS ( SELECT user_id, login_date, SUM(is_new_start) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id FROM login_with_flag ) SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id, group_id ORDER BY login_date) AS seq FROM login_with_group ORDER BY user_id, login_date;這段 SQL 把1月1日、1月2日、1月3日編為1、2、31月5日因為斷點形成新組所以重新從1開始。實際運行結(jié)果和預(yù)期完全一致。4.4 小心重復(fù)日期導(dǎo)致的組內(nèi)編號錯亂上面已經(jīng)做了 user_id login_date 去重所以不會出現(xiàn)同一天多條記錄。但如果去重沒做同一登錄日期會出現(xiàn)多條相同日期記錄組內(nèi) ROW_NUMBER 會給他們編出 1、2、3看起來就像連續(xù)多天其實是誤報。還有一個隱蔽細節(jié)如果登錄時間字段是帶時分秒的直接用 to_date 后再去重是對的但如果你用的是原始時間戳字段去做 LAG 比較比如直接 LAG(login_datetime)則同一天的兩次登錄會因為時間不同而判定為斷點導(dǎo)致連續(xù)區(qū)間被錯誤切分。所以全程必須使用 date 類型的字段同一標(biāo)準從一而終。5. 兩個需求合體同時輸出最大連續(xù)天數(shù)和斷連序號5.1 一條 SQL 搞定兩個指標(biāo)的建模邏輯實際業(yè)務(wù)中經(jīng)常需要同時輸出兩種信息一個是每個人最大的連續(xù)登錄天數(shù)一個是每次登錄對應(yīng)的第幾天序號。這兩個需求的前半部分完全一致都是“去重 → 打斷點標(biāo)記 → 生成 group_id”。我強烈建議不要寫兩遍重復(fù)代碼而是把公共部分放在 WITH 子句里一步到位。這也是 Hive 開發(fā)中非常重要的習(xí)慣先做一個高質(zhì)量的明細模型再從明細模型派生不同的聚合結(jié)果。用代碼來表達就是公共部分得到 user_id、login_date、group_id基于公共部分用 ROW_NUMBER 生成組內(nèi)序號基于公共部分按 user_id、group_id 聚合求 COUNT 得到每段連續(xù)天數(shù)再按 user_id 聚合取 MAX 得到最大連續(xù)天數(shù)。如果希望最后一張表既包含明細又包含每人最大連續(xù)天數(shù)一種簡單做法是先把明細和聚合算好再做 JOIN。下面給出完整的合體 SQL。5.2 完整合體 SQL可直接復(fù)制WITH login_dedup AS ( SELECT user_id, to_date(login_datetime) AS login_date FROM login_log GROUP BY user_id, to_date(login_datetime) ), login_with_group AS ( SELECT user_id, login_date, SUM(CASE WHEN LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) IS NULL OR datediff( login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) ) 1 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id FROM login_dedup ), login_detail_with_seq AS ( SELECT user_id, login_date, group_id, ROW_NUMBER() OVER (PARTITION BY user_id, group_id ORDER BY login_date) AS seq FROM login_with_group ), login_duration AS ( SELECT user_id, group_id, COUNT(*) AS continuous_days FROM login_with_group GROUP BY user_id, group_id ), user_max_duration AS ( SELECT user_id, MAX(continuous_days) AS max_continuous_days FROM login_duration GROUP BY user_id ) SELECT d.user_id, d.login_date, d.group_id, d.seq, m.max_continuous_days FROM login_detail_with_seq d JOIN user_max_duration m ON d.user_id m.user_id ORDER BY d.user_id, d.login_date;輸出結(jié)果示例user_id login_date group_id seq max_continuous_days A 2025-01-01 1 1 3 A 2025-01-02 1 2 3 A 2025-01-03 1 3 3 A 2025-01-05 2 1 3 A 2025-01-06 2 2 3這樣用戶在明細每一行都能看到“我現(xiàn)在第幾天”也能看到“歷史最多連續(xù)幾天”對后續(xù)報表分析非常友好。5.3 性能優(yōu)化減少重復(fù) LAG 計算上面的 SQL 在 login_with_group 子句里CASE 中調(diào)用了兩次 LAG。雖然邏輯上不報錯但 Hive 執(zhí)行時會對每一行計算兩次 LAG生產(chǎn)環(huán)境大表下不夠優(yōu)雅。更穩(wěn)妥的做法是先用 LAG 把上一行日期計算出來再做判斷l(xiāng)ogin_with_prev AS ( SELECT user_id, login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date FROM login_dedup ), login_with_group AS ( SELECT user_id, login_date, SUM(CASE WHEN prev_date IS NULL OR datediff(login_date, prev_date) 1 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY login_date) AS group_id FROM login_with_prev )這個寫法更清晰每行計算一次 LAG整體性能更好。我第一次寫的時候直接套復(fù)雜 CASE數(shù)據(jù)量500萬行跑了很久改成上面的寫法之后快了一倍多。后來排查發(fā)現(xiàn)有些引擎對重復(fù)開窗函數(shù)識別不全確實會真實計算兩次。6. 常用陷阱與真實排查記錄6.1 表里存在未來時間戳導(dǎo)致連續(xù)天數(shù)虛高有次遇到一個項目某用戶的最大連續(xù)登錄天數(shù)突然從15天跳到了40天直覺上不對勁。查數(shù)據(jù)后發(fā)現(xiàn)該用戶有幾條登錄時間來自用戶本地設(shè)備時間改過系統(tǒng)時間后產(chǎn)生了未來時間戳。這類數(shù)據(jù)如果不加過濾會把未來日期也算進登錄序列導(dǎo)致連續(xù)天數(shù)被錯誤拉長。解決方案很簡單在清洗層增加過濾條件保留登錄日期小于等于當(dāng)前日期或業(yè)務(wù)允許的最大日期。WHERE to_date(login_datetime) current_date()如果是離線數(shù)倉因為跑批有延遲也可以統(tǒng)一對時間做上限控制。這一條原本不在需求里但實際開發(fā)中必須考慮臟數(shù)據(jù)對連續(xù)計算的影響。6.2 用戶在多設(shè)備登錄導(dǎo)致同一天多次記錄很多業(yè)務(wù)場景是一個賬號綁定多個設(shè)備一天內(nèi)可能在手機、平板、電腦上都有登錄事件。如果不做去重直接計算會得出user_id login_dt A 2025-01-01 A 2025-01-01 A 2025-01-01然后按 login_date 排序這里因為有三條同日期記錄LAG 判斷 datediff 為0因為不等于1所以第二個和第三個都被標(biāo)記為新組起點得出用戶有三次“1天連續(xù)登錄”的假象。正確去重方式SELECT user_id, MAX(login_date) AS login_date FROM ... GROUP BY user_id, login_date;這里用 MAX 是為了兼容可能有空值的情況其實DISTINCT 就足夠。我在實際中發(fā)現(xiàn) DISTINCT 在 Hive 某些版本對大量字段會有 shuffle 性能問題但只針對兩三個字段沒毛病。6.3 斷點標(biāo)記中的 NULL 陷阱很多人在寫斷點判斷時只判斷 datediff 是否等于1忽略了第一行的前一日期是 NULL。如果寫成CASE WHEN datediff(login_date, LAG(login_date) OVER (...)) 1 THEN 0 ELSE 1 END第一行因為 datediff 結(jié)果為 NULL判斷結(jié)果為 NULL走到 ELSE 得到1恰恰也是正確結(jié)果。所以這個寫法在實際運行中是對的。但為了可讀性我還是建議在 NULL 判斷上明確寫出來避免后續(xù)維護的人閱讀困難CASE WHEN LAG(login_date) OVER (...) IS NULL THEN 1 WHEN datediff(login_date, LAG(login_date) OVER (...)) 1 THEN 0 ELSE 1 END6.4 大表下開窗排序帶來的數(shù)據(jù)傾斜PARTITION BY user_id 時會按用戶分桶如果少數(shù)超級用戶登錄記錄特別多會導(dǎo)致單個 reducer 處理量過大形成數(shù)據(jù)傾斜。常用優(yōu)化手段有三個設(shè)置 hive.optimize.sort.dynamic.partition 等參數(shù)但有時效果有限按用戶維度判斷是否為大用戶單獨分桶處理再 union 回主流程在清洗層把明顯異常的超高頻用戶比如機器人賬號過濾掉或者按業(yè)務(wù)口徑剔除以秒級頻率登錄的異常數(shù)據(jù)。如果只是離線報表且數(shù)據(jù)量在億級以內(nèi)通常并行度調(diào)一下就能解決問題。我遇到實際傾斜時會用以下參數(shù)先試試SET hive.exec.reducers.bytes.per.reducer256000000; SET hive.exec.paralleltrue;不過每個集群版本不一樣參數(shù)名可能會有差異要根據(jù)實際環(huán)境確認。6.5 對連續(xù)天數(shù)的“自然日”定義要保持一致業(yè)務(wù)上“連續(xù)登錄7天”是不是一定要7個自然日有些產(chǎn)品的連續(xù)簽到規(guī)則是“任意7天內(nèi)有登錄即算連續(xù)”這種口徑就不能用上面的方法需要改成滾動窗口統(tǒng)計。例如用SUM(1) OVER (PARTITION BY user_id ORDER BY login_date RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW)或者先展開日期再計算。實際中業(yè)務(wù)方說“連續(xù)”通常默認自然日連續(xù)但你要主動確認一次。我吃過虧開發(fā)完后業(yè)務(wù)說他們想要的是“任意7天內(nèi)登錄過5天”完全不是一個東西返工成本很高。7. 從志經(jīng)驗談什么時候用 SQL 什么時候用腳本7.1 SQL 方案的優(yōu)缺點這套用 Hive SQL 實現(xiàn)的方案優(yōu)點是純 SQL、好維護、跑批穩(wěn)定不依賴外部 Python 或代碼環(huán)境數(shù)據(jù)量大時只要調(diào)參數(shù)能扛住。缺點在于如果業(yè)務(wù)很復(fù)雜比如需要判斷自然日還是活躍日、需要排除指定節(jié)假日、需要每個用戶有自定義起始周期SQL 表達起來會比較繞。這時用 Python、Scala 或者 Spark RDD 也可以實現(xiàn)但不一定更快。7.2 讓臨時表變成數(shù)倉模型我在實際項目中一般情況下不會讓業(yè)務(wù)查詢直接訪問原始登錄日志。要先構(gòu)建一個“用戶登錄密集模型”包含 user_id、login_date、group_id、seq、max_continuous_days 字段落在 Hive 表中后續(xù)所有報表都查詢這張表。這樣處理有幾個好處計算一次多處復(fù)用原始日志變化時只需要刷新這張表下游各種報表不需要重復(fù)計算同樣的分組邏輯。建表語句簡單示意CREATE TABLE dws_user_login_continuous ( user_id STRING COMMENT 用戶ID, login_date STRING COMMENT 登錄日期, group_id BIGINT COMMENT 連續(xù)區(qū)間分組ID, seq BIGINT COMMENT 連續(xù)區(qū)間內(nèi)序號, max_continuous_days BIGINT COMMENT 歷史最大連續(xù)登錄天數(shù) ) COMMENT 用戶登錄連續(xù)明細表 PARTITIONED BY (dt STRING COMMENT 數(shù)據(jù)分區(qū)日期) STORED AS ORC;寫入時把上面的合體 SQL 結(jié)果 insert overwrite 到表中即可。注意分區(qū)字段 dt 與 login_date 不要混淆一個是數(shù)據(jù)加工日期一個是業(yè)務(wù)登錄日期。7.3 連續(xù)問題的一招通吃日期減去行號除了 LAG SUM(標(biāo)記) 的方法還有一種非常經(jīng)典的方法用日期減去所在組內(nèi)的行號得到一個穩(wěn)定的日期基準這個基準相同則屬于同一連續(xù)區(qū)間。寫成 SQL 就是SELECT user_id, login_date, date_sub(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS group_date FROM login_dedup;舉例說明連續(xù)登錄三天行號分別是1、2、3日期分別是 2025-01-01、2025-01-02、2025-01-03那么 date_sub 后得到 2024-12-31、2024-12-31、2024-12-31完全相等。而斷掉后再出現(xiàn)的 2025-01-05行號是4date_sub 得到 2025-01-01和前一段的 2024-12-31 不同于是分到不同組。這個方法實現(xiàn)起來代碼更短WITH login_dedup AS ( SELECT user_id, to_date(login_datetime) AS login_date FROM login_log GROUP BY user_id, to_date(login_datetime) ), login_with_gap AS ( SELECT user_id, login_date, date_sub(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS group_date FROM login_dedup ) SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id, group_date ORDER BY login_date) AS seq FROM login_with_gap;這個方法被稱為“差值和行號法”比 LAG 判斷少一個開窗計算性能通常更好。但有一個前提條件登錄日期必須是嚴格連續(xù)且沒有重復(fù)的日期值。只要已經(jīng)做了去重完全可行。我個人更喜歡先用 LAG 法因為更容易理解便于別人接手但差值和行號法在某些大表場景下更快值得掌握。8. 實操場景擴展不只是登錄天數(shù)8.1 統(tǒng)計商家連續(xù)營業(yè)天數(shù)電商平臺經(jīng)常要分析商家是不是“僵尸店”或者評選“金牌店鋪”其中就有連續(xù)營業(yè)天數(shù)指標(biāo)。和用戶登錄基本一樣只要把 user_id 換成 shop_idlogin_date 換成 business_date。業(yè)務(wù)上要考慮實體店可能會因為疫情、裝修停業(yè)這種斷點自然也會重新分段。8.2 計算用戶連續(xù)簽到獎勵等級很多 App 有簽到活動連續(xù)簽到7天給獎勵。利用 seq 字段可以很輕松地篩出每個用戶當(dāng)前連續(xù)簽到的天數(shù)并判斷是否達到獎勵條件SELECT user_id, MAX(seq) AS current_continuous_days FROM login_detail_with_seq WHERE login_date current_date() GROUP BY user_id;還可以進一步統(tǒng)計每個用戶的連續(xù)簽到段數(shù)、每一段跨了多少天。這些指標(biāo)都可以基于上面已經(jīng)構(gòu)建好的模型完成。8.3 監(jiān)控數(shù)據(jù)回補導(dǎo)致的歷史連續(xù)性重算數(shù)倉里經(jīng)常有上游數(shù)據(jù)回補的情況比如某天的日志數(shù)據(jù)凌晨沒有到位第二天補了上來。如果模型表沒有做冪等處理重跑全量時會因為新增的歷史日期導(dǎo)致 group_id 和 seq 變化。這種情況下建議模型表按全量覆蓋方式刷新或者給原始日志增加過濾條件避免重復(fù)接入。我在實際中試過增量更新連續(xù)模型邏輯非常容易出 bug最簡單的就是每天重刷全量只要數(shù)據(jù)量可控這是最穩(wěn)的。9. 排障速查表遇到連續(xù)計算問題對照查看癥狀可能原因解決辦法最大連續(xù)天數(shù)比實際多很多未按 user_id login_date 去重清洗層加 GROUP BY 或 DISTINCT序號從2開始或中間亂跳分組標(biāo)記沒有正確生成檢查 LAG 是否取到上一日期斷點處序號沒有歸1group_id 沒有累加或用錯分區(qū)字段確保 SUM(標(biāo)記) 的窗口按 user_id 排序跨年數(shù)據(jù)算斷日期字符串參與 datediff 時格式不對全部轉(zhuǎn)成 date 類型再計算同一天多條記錄導(dǎo)致連續(xù)天數(shù)虛高去重不徹底按 user_id、to_date 去重分組結(jié)果全一樣date_sub 差值法與 ROW_NUMBER 聯(lián)合使用時日期未被去重先 GROUP BY 后再執(zhí)行數(shù)據(jù)傾斜、運行極慢個別用戶記錄過多調(diào)大 reducer 或?qū)Υ笥脩魡为毺幚磉B續(xù)天數(shù)與業(yè)務(wù)預(yù)期不符口徑不同自然日 vs 任意X天提前和業(yè)務(wù)確認口徑這張表的每一條我都實際踩過或幫別人排查過建議收藏。它們比任何理論都更能節(jié)省你的時間。10. 最后一個實驗性技巧用數(shù)組函數(shù)實現(xiàn)連續(xù)分組如果你所在的環(huán)境是 SparkSQL 3.0 以上還支持一些高版本的 Hive 函數(shù)比如 collect_list、sort_array?？梢韵劝岩粋€用戶的所有登錄日期用 collect_list 收集成一個數(shù)組再遍歷數(shù)組判斷連續(xù)性。但這種方式在 Hive 上寫起代碼非常啰嗦要用 explode 再打散不同版本函數(shù)支持差異很大。我不建議常規(guī)場景用它只在某些特殊場景下比如已經(jīng)有一個用戶數(shù)組字段不希望多次 join 時才考慮。這里點到為止重點還是掌握上一節(jié) SQL 法。我在實際項目中逐步形成了一套自己的模板以后只要遇到“連續(xù) x 天”類需求直接復(fù)制改字段名。核心就是四個關(guān)鍵步驟去重登錄日期用 LAG 標(biāo)記斷點用 SUM 累加生成分組聚合取最大或組內(nèi)排號。這套流程已經(jīng)被我用到用戶活躍、店鋪營業(yè)、事件觸發(fā)連續(xù)性等多個場景每次都很穩(wěn)。如果非要說一點個人體會那就是千萬不要一上來就寫復(fù)雜 SQL先把邏輯用筆畫出幾行樣例手動推一遍分組結(jié)果再寫代碼。很多問題在紙面上想清楚后代碼就是一步到位的翻譯而已。