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