簡(jiǎn)介《數(shù)據(jù)中臺(tái)實(shí)戰(zhàn)手把手教你搭建數(shù)據(jù)中臺(tái)》電子書(shū)面向企業(yè)管理者、產(chǎn)品經(jīng)理、運(yùn)營(yíng)設(shè)計(jì)開(kāi)發(fā)人員及數(shù)據(jù)領(lǐng)域?qū)W習(xí)者聚焦數(shù)據(jù)中臺(tái)從概念認(rèn)知到落地搭建的完整路徑幫助讀者判斷企業(yè)是否需要中臺(tái)并掌握采集、存儲(chǔ)、打通、應(yīng)用四個(gè)核心環(huán)節(jié)。壓縮包為PDF格式僅含1個(gè)文件體積約11.3MB便于在電腦或移動(dòng)設(shè)備上閱讀與檢索。目前已有402人學(xué)習(xí)下載具備一定關(guān)注度。書(shū)中結(jié)合作者在大型企業(yè)參與中臺(tái)從0到1建設(shè)的實(shí)戰(zhàn)經(jīng)歷圍繞數(shù)據(jù)埋點(diǎn)、指標(biāo)管理、分層建模、標(biāo)簽平臺(tái)、用戶(hù)與商品流量交易分析、自助分析平臺(tái)及自動(dòng)化營(yíng)銷(xiāo)等模塊展開(kāi)配有較多案例與方法論可幫助讀者理解中臺(tái)建設(shè)的適用條件、實(shí)施流程和常見(jiàn)陷阱也為中臺(tái)產(chǎn)品經(jīng)理及企業(yè)數(shù)據(jù)化轉(zhuǎn)型項(xiàng)目提供可操作的實(shí)踐參考。1. 先別急著買(mǎi)引擎這份大數(shù)據(jù)中臺(tái)資料該在什么階段翻開(kāi)很多團(tuán)隊(duì)一上大數(shù)據(jù)中臺(tái)先買(mǎi)計(jì)算引擎、再拉埋點(diǎn)最后發(fā)現(xiàn)指標(biāo)口徑對(duì)不上看板越做越多決策卻越來(lái)越慢。這份數(shù)據(jù)中臺(tái)實(shí)戰(zhàn)資料的價(jià)值不在概念而在它把采集、存儲(chǔ)、打通、應(yīng)用四段拆成了能照著做的工序埋點(diǎn)表怎么設(shè)計(jì)、ODS 到 ADS 怎么分層、標(biāo)簽平臺(tái)怎么從寬表長(zhǎng)出來(lái)、交易看板怎么給不同角色用。適合正在做數(shù)據(jù)化轉(zhuǎn)型的產(chǎn)品、運(yùn)營(yíng)、開(kāi)發(fā)和準(zhǔn)備面試數(shù)據(jù)中臺(tái)崗位的人不適合只有一條產(chǎn)品線、數(shù)據(jù)量還沒(méi)起來(lái)的初創(chuàng)團(tuán)隊(duì)。讀的時(shí)候別從第一頁(yè)順讀先拿自己公司的一條業(yè)務(wù)線做對(duì)照缺哪段補(bǔ)哪段。2. 數(shù)據(jù)采集埋點(diǎn)表、業(yè)務(wù)庫(kù)同步與 T-1 鏈路的落地拆解采集做不扎實(shí)后面所有分層建模都是空中樓閣。資料里反復(fù)強(qiáng)調(diào)業(yè)務(wù)中臺(tái)和數(shù)據(jù)中臺(tái)是兩條腿前者把通用業(yè)務(wù)模塊收口后者把跨產(chǎn)品線數(shù)據(jù)收口沒(méi)有業(yè)務(wù)中臺(tái)也能做數(shù)據(jù)中臺(tái)但采集階段要多跑幾條產(chǎn)品線做調(diào)研溝通成本會(huì)高很多。落到執(zhí)行采集不是“把日志丟進(jìn)數(shù)倉(cāng)”這么簡(jiǎn)單至少要同時(shí)管住三件事埋點(diǎn)事件有沒(méi)有統(tǒng)一 schema、業(yè)務(wù)庫(kù)同步有沒(méi)有可重跑的 T-1 鏈路、采集結(jié)果有沒(méi)有驗(yàn)收標(biāo)準(zhǔn)。2.1 埋點(diǎn)事件表先定 schema再談采集埋點(diǎn)最容易翻車(chē)的地方是事件名和屬性名各寫(xiě)各的同一個(gè)“提交訂單”A 產(chǎn)品線叫submit_orderB 產(chǎn)品線叫order_commit屬性里一個(gè)用order_id一個(gè)用orderNo。資料里的做法是先定事件命名規(guī)范再讓開(kāi)發(fā)按表填報(bào)。常見(jiàn)命名是“業(yè)務(wù)域_對(duì)象_動(dòng)作”比如trade_order_submit、goods_detail_view、cart_add_click屬性分公共屬性和業(yè)務(wù)屬性公共屬性由 SDK 統(tǒng)一帶上業(yè)務(wù)屬性由埋點(diǎn)方案聲明。事件表不要一上來(lái)就寬表化先保證一行一個(gè)事件、事件 ID 可去重、時(shí)間字段統(tǒng)一到服務(wù)端時(shí)間。-- 埋點(diǎn)事件明細(xì)表按天分區(qū)properties 存業(yè)務(wù)屬性 CREATE TABLE IF NOT EXISTS ods_user_event ( event_id STRING COMMENT 事件唯一ID前端生成服務(wù)端補(bǔ)發(fā), event_name STRING COMMENT 事件名域_對(duì)象_動(dòng)作, user_id STRING COMMENT 登錄用戶(hù)ID未登錄為空, anonymous_id STRING COMMENT 設(shè)備匿名ID未登錄時(shí)必填, session_id STRING COMMENT 會(huì)話ID, page_id STRING COMMENT 頁(yè)面ID, module_id STRING COMMENT 模塊ID, platform STRING COMMENT 端web/app/mini, app_version STRING COMMENT 客戶(hù)端版本, server_ts BIGINT COMMENT 服務(wù)端接收時(shí)間戳毫秒, properties STRING COMMENT JSON屬性限制2KB以?xún)?nèi), dt STRING COMMENT 分區(qū)日期 yyyy-MM-dd ) PARTITIONED BY (dt) STORED AS PARQUET;這段建表語(yǔ)句的關(guān)鍵不是語(yǔ)法而是把“可去重”和“可分區(qū)”提前寫(xiě)進(jìn) schema。event_id用來(lái)處理網(wǎng)絡(luò)重試導(dǎo)致的重復(fù)上報(bào)user_id和anonymous_id至少有一個(gè)不為空否則后續(xù)無(wú)法做用戶(hù)行為串聯(lián)server_ts不要用客戶(hù)端時(shí)間代替客戶(hù)端時(shí)間容易被改properties限制大小避免有人把整個(gè)商品對(duì)象塞進(jìn)埋點(diǎn)。分區(qū)字段dt按服務(wù)端接收日期落跨天補(bǔ)發(fā)的事件要單獨(dú)標(biāo)記否則日?qǐng)?bào)會(huì)對(duì)不上。埋點(diǎn)方案評(píng)審時(shí)我一般會(huì)強(qiáng)迫產(chǎn)品把下面這張表填完填不完就不排期。事件名、觸發(fā)時(shí)機(jī)、必填屬性、屬性類(lèi)型、責(zé)任人、驗(yàn)收指標(biāo)這六列缺一不可。尤其是“觸發(fā)時(shí)機(jī)”要寫(xiě)到“點(diǎn)擊提交按鈕且接口返回成功后上報(bào)”不能寫(xiě)“用戶(hù)下單時(shí)”。很多轉(zhuǎn)化率異常不是數(shù)倉(cāng)算錯(cuò)而是前端在按鈕點(diǎn)擊時(shí)就上報(bào)接口失敗也算了一筆。字段示例約束事件名trade_order_submit小寫(xiě)字母下劃線最長(zhǎng) 48 字符觸發(fā)時(shí)機(jī)提交訂單接口返回成功不能寫(xiě)“用戶(hù)下單時(shí)”必填屬性order_id、pay_amount、goods_id空值率超過(guò) 1% 阻斷發(fā)布屬性類(lèi)型pay_amount為 decimal(16,2)金額統(tǒng)一單位元責(zé)任人某業(yè)務(wù)線產(chǎn)品負(fù)責(zé)人變更需同步數(shù)據(jù)團(tuán)隊(duì)驗(yàn)收指標(biāo)日事件量、UV、空值率、重復(fù)率發(fā)布后 24 小時(shí)看板核對(duì)埋點(diǎn)驗(yàn)收不要靠人眼看日志直接跑 SQL 看四個(gè)數(shù)事件量、去重后事件量、必填屬性空值率、event_id重復(fù)率。下面這段查詢(xún)按事件名聚合適合放進(jìn)每日調(diào)度超過(guò)閾值就告警。參數(shù)上dt取業(yè)務(wù)日期event_name可以先限定核心轉(zhuǎn)化事件不用全量跑重復(fù)率閾值我一般設(shè) 0.5%空值率設(shè) 1%超過(guò)就回滾 SDK 版本或修埋點(diǎn)方案不要先去改數(shù)倉(cāng)清洗邏輯。-- 埋點(diǎn)驗(yàn)收核心事件量、空值率、重復(fù)率 SELECT event_name, COUNT(1) AS pv, COUNT(DISTINCT event_id) AS pv_dedup, ROUND(1 - COUNT(DISTINCT event_id) / COUNT(1), 4) AS dup_rate, ROUND(SUM(CASE WHEN user_id IS NULL AND anonymous_id IS NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS id_null_rate, ROUND(SUM(CASE WHEN properties IS NULL OR properties THEN 1 ELSE 0 END) / COUNT(1), 4) AS prop_null_rate FROM ods_user_event WHERE dt ${biz_date} AND event_name IN (trade_order_submit, goods_detail_view, cart_add_click) GROUP BY event_name;提示埋點(diǎn)驗(yàn)收不通過(guò)時(shí)先回滾 SDK 版本再查網(wǎng)絡(luò)重試和頁(yè)面曝光重復(fù)上報(bào)不要直接改數(shù)倉(cāng)邏輯把異常藏掉。2.2 業(yè)務(wù)庫(kù)同步與日志采集的 T-1 鏈路業(yè)務(wù)庫(kù)數(shù)據(jù)是交易、訂單、商品、用戶(hù)這些“結(jié)果數(shù)據(jù)”埋點(diǎn)日志是瀏覽、點(diǎn)擊、加購(gòu)這些“過(guò)程數(shù)據(jù)”。資料里把采集鏈路分成兩條業(yè)務(wù)庫(kù)走實(shí)時(shí)同步加 T-1 全量校對(duì)日志走消息隊(duì)列落地后按天分區(qū)。常見(jiàn)做法是業(yè)務(wù)庫(kù)變更用 binlog 追增量每天凌晨再跑一次全量或增量校對(duì)防止漏數(shù)日志采集在前端埋點(diǎn)后先落消息隊(duì)列再離線落到對(duì)象存儲(chǔ)或分布式文件系統(tǒng)按dt分區(qū)。兩條鏈路最后都收斂到 ODS 層表名和責(zé)任人登記到元數(shù)據(jù)表里。調(diào)度腳本不要寫(xiě)成“一把梭”至少把業(yè)務(wù)日期、任務(wù)名、重跑標(biāo)記、失敗告警四個(gè)參數(shù)留出來(lái)。下面是一個(gè)通用 T-1 同步腳本骨架job_config指向同步任務(wù)配置biz_date由調(diào)度系統(tǒng)傳入rerun用來(lái)控制是否覆蓋分區(qū)。腳本里先做源表行數(shù)探查再執(zhí)行同步最后寫(xiě)一條元數(shù)據(jù)日志這樣出問(wèn)題時(shí)能查到是源表空了還是同步任務(wù)失敗了。#!/bin/bash set -euo pipefail BIZ_DATE${1:?業(yè)務(wù)日期不能為空格式 yyyy-MM-dd} JOB_CONFIG${2:?同步任務(wù)配置不能為空} RERUN${3:-false} # 1. 同步前探查源表行數(shù)結(jié)果寫(xiě)入臨時(shí)文件 python /opt/sync/bin/check_source.py \ --config ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --output /tmp/source_count_${BIZ_DATE}.json # 2. 執(zhí)行同步reruntrue 時(shí)覆蓋目標(biāo)分區(qū) python /opt/sync/bin/run_job.py \ --config ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --mode overwrite \ --rerun ${RERUN} # 3. 寫(xiě)元數(shù)據(jù)日志任務(wù)名、業(yè)務(wù)日期、源行數(shù)、目標(biāo)行數(shù)、狀態(tài) python /opt/sync/bin/write_meta.py \ --job ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --status success參數(shù)說(shuō)明上BIZ_DATE是業(yè)務(wù)日期不是執(zhí)行日期很多團(tuán)隊(duì)凌晨跑數(shù)時(shí)把執(zhí)行日期當(dāng)業(yè)務(wù)日期導(dǎo)致 T-1 數(shù)據(jù)錯(cuò)位一天。RERUN只在補(bǔ)數(shù)時(shí)傳true日常調(diào)度傳false避免誤覆蓋。同步任務(wù)配置里要明確源表、目標(biāo)表、主鍵、增量字段、分區(qū)字段、字段映射主鍵用于去重增量字段用于追變更分區(qū)字段用于重跑。日志采集側(cè)則要在 ODS 建外部表或分區(qū)表按dt掛載不要每天改表結(jié)構(gòu)。采集完成后必須登記元數(shù)據(jù)不然三個(gè)月后沒(méi)人知道ods_order是誰(shuí)負(fù)責(zé)、幾點(diǎn)更新、下游有誰(shuí)用。資料里把中臺(tái)團(tuán)隊(duì)拆成十類(lèi)角色從負(fù)責(zé)人、產(chǎn)品、數(shù)據(jù)開(kāi)發(fā)、分析師到測(cè)試和運(yùn)維采集階段至少要有業(yè)務(wù)責(zé)任人、數(shù)據(jù)開(kāi)發(fā)責(zé)任人、運(yùn)維責(zé)任人三方簽字。元數(shù)據(jù)表不用復(fù)雜下面這幾列就夠用表名、來(lái)源系統(tǒng)、更新頻率、SLA、主鍵、責(zé)任人、下游任務(wù)。缺元數(shù)據(jù)的表我一般不允許進(jìn) DWD寧可先卡在 ODS。元數(shù)據(jù)項(xiàng)示例用途表名ods_order唯一標(biāo)識(shí)來(lái)源系統(tǒng)某業(yè)務(wù)線訂單庫(kù)排查源端問(wèn)題更新頻率每日 T-109:00 前約定 SLA主鍵order_id去重與對(duì)賬責(zé)任人某數(shù)據(jù)開(kāi)發(fā)變更聯(lián)系人下游任務(wù)dwd_trade_order影響面分析2.3 采集驗(yàn)收量、質(zhì)、時(shí)三層卡點(diǎn)采集驗(yàn)收要卡三層量、質(zhì)、時(shí)。量看日環(huán)比和周同比核心事件量突然掉 30% 或漲 50% 都要查質(zhì)看主鍵重復(fù)、必填字段空值、枚舉值越界時(shí)看 SLA比如每天 09:00 前 ODS 分區(qū)必須就緒晚了就觸發(fā)告警。資料里很多實(shí)戰(zhàn)案例都指向一個(gè)結(jié)論采集階段放過(guò)的小問(wèn)題到應(yīng)用層會(huì)變成大問(wèn)題。比如訂單表里混入測(cè)試訂單交易看板會(huì)多算埋點(diǎn)里platform出現(xiàn)unknown流量分析會(huì)少一塊日志補(bǔ)發(fā)沒(méi)有標(biāo)記日?qǐng)?bào)和實(shí)時(shí)看板會(huì)對(duì)不上。采集驗(yàn)收 SQL 可以固定成模板每個(gè) ODS 表接進(jìn)來(lái)時(shí)改表名和主鍵即可。下面這段檢查主鍵重復(fù)、分區(qū)行數(shù)、關(guān)鍵字段空值結(jié)果輸出到質(zhì)量表調(diào)度系統(tǒng)根據(jù)status決定是否阻斷下游。參數(shù)上pk換成實(shí)際主鍵dt用業(yè)務(wù)日期min_rows設(shè)一個(gè)業(yè)務(wù)可接受的下限比如訂單表日行數(shù)不低于 1000低于就告警而不是直接阻斷避免節(jié)假日誤殺。-- 采集質(zhì)量檢查主鍵重復(fù)、行數(shù)下限、關(guān)鍵字段空值 SELECT ods_order AS table_name, ${biz_date} AS dt, COUNT(1) AS row_cnt, COUNT(1) - COUNT(DISTINCT order_id) AS pk_dup_cnt, SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS pk_null_cnt, SUM(CASE WHEN pay_amount IS NULL THEN 1 ELSE 0 END) AS amount_null_cnt, CASE WHEN COUNT(1) 1000 THEN WARN WHEN COUNT(1) - COUNT(DISTINCT order_id) 0 THEN FAIL WHEN SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) 0 THEN FAIL ELSE PASS END AS status FROM ods_order WHERE dt ${biz_date};這一層做完采集才算有“黑匣子”可查。后面指標(biāo)對(duì)不上時(shí)先看 ODS 行數(shù)和主鍵再看 DWD 清洗規(guī)則最后才懷疑匯總邏輯。順序反了會(huì)浪費(fèi)很多時(shí)間。3. 存儲(chǔ)與計(jì)算ODS 到 ADS 的分層建模和指標(biāo)口徑怎么落表分層建模最怕做成表名游戲ODS、DWD、DWS 建了一堆業(yè)務(wù)一問(wèn)“昨天支付金額為什么和財(cái)務(wù)對(duì)不上”還是答不出來(lái)。資料里的分層不是照搬概念而是把“原始數(shù)據(jù)、清洗明細(xì)、主題匯總、應(yīng)用結(jié)果”分開(kāi)讓每層只干一件事。ODS 保留原樣方便回溯DWD 做清洗、去重、統(tǒng)一字段DWS 按主題做輕度匯總ADS 面向看板和接口。層級(jí)越往下越穩(wěn)定越往上越貼近業(yè)務(wù)變更頻率也越高。3.1 分層邊界每層只做該做的事ODS 層不要做復(fù)雜清洗尤其不要在這里過(guò)濾測(cè)試訂單或修改金額否則回溯時(shí)找不到原始值。DWD 層負(fù)責(zé)把 ODS 的臟數(shù)據(jù)變成可信明細(xì)去重、過(guò)濾測(cè)試賬號(hào)、統(tǒng)一金額單位、補(bǔ)全維度、解析 JSON 屬性。DWS 層按主題匯總比如交易主題按日、品類(lèi)、渠道匯總支付金額和退款金額用戶(hù)主題按日匯總活躍、下單、復(fù)購(gòu)。ADS 層直接服務(wù)看板和接口可以寬表化但要在指標(biāo)口徑固定之后再做。選型上離線鏈路優(yōu)先保證可重跑和可對(duì)賬實(shí)時(shí)鏈路只承接真正需要秒級(jí)響應(yīng)的場(chǎng)景。資料里把采集、存儲(chǔ)、打通、應(yīng)用串起來(lái)但并沒(méi)有鼓勵(lì)所有表都實(shí)時(shí)化。交易看板 T1 足夠?qū)崟r(shí)大屏才需要流式計(jì)算標(biāo)簽平臺(tái)大部分標(biāo)簽 T1 更新只有“最近 1 小時(shí)活躍”這類(lèi)標(biāo)簽走實(shí)時(shí)。分層表建議統(tǒng)一按天分區(qū)小文件多的任務(wù)加合并步驟冷數(shù)據(jù)按保留周期歸檔避免存儲(chǔ)成本失控。層級(jí)職責(zé)典型表更新頻率禁止事項(xiàng)ODS原樣落地、可回溯ods_order、ods_user_event每日/實(shí)時(shí)不做業(yè)務(wù)過(guò)濾DWD清洗、去重、統(tǒng)一口徑dwd_trade_order每日不直接出看板DWS主題匯總、輕度聚合dws_trade_day每日不堆過(guò)多維度ADS看板、接口、人群包ads_trade_board每日/按需不反寫(xiě)明細(xì)注意指標(biāo)口徑變更必須留版本否則歷史看板會(huì)集體失真。DWD 的過(guò)濾條件一改DWS 和 ADS 都要重跑不能只改最上層。3.2 指標(biāo)管理體系一個(gè)指標(biāo)一條 YAML指標(biāo)口徑對(duì)不上根因通常不是 SQL 寫(xiě)錯(cuò)而是同一個(gè)詞在不同團(tuán)隊(duì)嘴里含義不同。資料里強(qiáng)調(diào)指標(biāo)要有業(yè)務(wù)口徑、技術(shù)口徑、計(jì)算周期、計(jì)算方式、責(zé)任人。我的做法是一個(gè)指標(biāo)一條 YAML進(jìn) Git 管理變更走評(píng)審。指標(biāo)碼用“域_主題_度量_周期”比如trade_order_pay_amt_1d業(yè)務(wù)口徑寫(xiě)清楚“支付成功訂單金額扣除退款不含測(cè)試訂單”技術(shù)口徑寫(xiě)清楚源表、過(guò)濾條件、去重主鍵、金額單位維度列出常用切分比如日期、品類(lèi)、渠道、產(chǎn)品線。metric_code: trade_order_pay_amt_1d name: 日支付訂單金額 biz_caliber: 支付成功訂單金額扣除當(dāng)日退款不含測(cè)試訂單和風(fēng)控?cái)r截訂單 tech_caliber: dwd_trade_order 按 order_id 去重 pay_status PAID order_type TEST pay_amount 單位為元保留兩位小數(shù) owner: 某交易數(shù)據(jù)負(fù)責(zé)人 period: 1d dimensions: - dt - category_id - channel_id - product_line source_table: dwd_trade_order filter: pay_status PAID AND order_type TEST version: v1.2這份 YAML 的關(guān)鍵是“可執(zhí)行”。biz_caliber不是寫(xiě)給領(lǐng)導(dǎo)看的而是給開(kāi)發(fā)和分析師對(duì)照的tech_caliber要能直接翻譯成 SQLdimensions決定 DWS 建哪些匯總維度version讓歷史看板能追到口徑版本。指標(biāo)變更時(shí)不要直接改原文件新增版本并保留舊版本ADS 表按版本號(hào)切換。否則一個(gè)“支付金額”改了口徑歷史報(bào)表全部對(duì)不上業(yè)務(wù)方會(huì)直接不信數(shù)據(jù)。指標(biāo)管理還有一條血淚經(jīng)驗(yàn)不要讓每個(gè)看板自己寫(xiě) SQL 算指標(biāo)?？窗逯幌M(fèi) ADS 或指標(biāo)服務(wù)指標(biāo)服務(wù)背后統(tǒng)一走 YAML 定義。這樣新看板接入時(shí)不用重新討論口徑直接選指標(biāo)碼和維度即可。自助分析平臺(tái)也一樣業(yè)務(wù)人員可以拖拽維度但指標(biāo)必須是注冊(cè)過(guò)的不能讓他們?cè)陧?yè)面上隨便寫(xiě)sum(pay_amount)。3.3 從 ODS 到 ADS 的 SQL 骨架先看 DWD 清洗。下面這段 SQL 從 ODS 訂單表取數(shù)按order_id去重過(guò)濾測(cè)試訂單統(tǒng)一金額單位補(bǔ)上品類(lèi)和渠道維度。參數(shù)上dt是業(yè)務(wù)日期row_number()按update_time倒序取最新一條處理業(yè)務(wù)庫(kù)同一訂單多次變更pay_amount如果源表是分要除以 100這里假設(shè)源表已經(jīng)是元。測(cè)試訂單的識(shí)別條件用order_type TEST不要用訂單號(hào)前綴猜規(guī)則要寫(xiě)在指標(biāo) YAML 里。-- DWD 訂單明細(xì)去重、過(guò)濾測(cè)試單、統(tǒng)一金額單位 INSERT OVERWRITE TABLE dwd_trade_order PARTITION (dt ${biz_date}) SELECT order_id, user_id, product_line, category_id, channel_id, pay_status, pay_amount, refund_amount, create_time, update_time FROM ( SELECT order_id, user_id, product_line, category_id, channel_id, pay_status, CAST(pay_amount AS DECIMAL(16,2)) AS pay_amount, CAST(refund_amount AS DECIMAL(16,2)) AS refund_amount, create_time, update_time, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) AS rn FROM ods_order WHERE dt ${biz_date} AND order_type TEST ) t WHERE rn 1;DWS 匯總按日、品類(lèi)、渠道、產(chǎn)品線聚合支付金額和退款金額分開(kāi)算最后在 ADS 里做凈額。這樣退款政策變化時(shí)不用重跑 DWD只改匯總邏輯。下面這段 SQL 的pay_amt只統(tǒng)計(jì)支付成功訂單refund_amt統(tǒng)計(jì)當(dāng)日退款net_amt是凈額。參數(shù)上dt分區(qū)對(duì)齊category_id和channel_id允許為空空值單獨(dú)歸到“未知”維度不要直接丟掉否則大盤(pán)對(duì)不上。-- DWS 交易日匯總按品類(lèi)、渠道、產(chǎn)品線 INSERT OVERWRITE TABLE dws_trade_day PARTITION (dt ${biz_date}) SELECT ${biz_date} AS dt, product_line, COALESCE(category_id, UNKNOWN) AS category_id, COALESCE(channel_id, UNKNOWN) AS channel_id, COUNT(DISTINCT CASE WHEN pay_status PAID THEN order_id END) AS pay_order_cnt, SUM(CASE WHEN pay_status PAID THEN pay_amount ELSE 0 END) AS pay_amt, SUM(CASE WHEN refund_amount 0 THEN refund_amount ELSE 0 END) AS refund_amt, SUM(CASE WHEN pay_status PAID THEN pay_amount ELSE 0 END) - SUM(CASE WHEN refund_amount 0 THEN refund_amount ELSE 0 END) AS net_amt FROM dwd_trade_order WHERE dt ${biz_date} GROUP BY product_line, COALESCE(category_id, UNKNOWN), COALESCE(channel_id, UNKNOWN);ADS 看板表可以直接從 DWS 取數(shù)按角色拆指標(biāo)。領(lǐng)導(dǎo)層看大盤(pán)凈額、同比環(huán)比運(yùn)營(yíng)看品類(lèi)、渠道、退款率產(chǎn)品看轉(zhuǎn)化漏斗。下面這段只做一張交易看板樣例net_amt和refund_rate是核心指標(biāo)。參數(shù)上dt用業(yè)務(wù)日期refund_rate分母為 0 時(shí)要返回 0避免除零同比環(huán)比可以在調(diào)度里另算不要在看板查詢(xún)時(shí)實(shí)時(shí)算否則數(shù)據(jù)量大時(shí)頁(yè)面會(huì)卡。-- ADS 交易看板大盤(pán)核心指標(biāo) INSERT OVERWRITE TABLE ads_trade_board PARTITION (dt ${biz_date}) SELECT dt, SUM(pay_amt) AS pay_amt, SUM(refund_amt) AS refund_amt, SUM(net_amt) AS net_amt, SUM(pay_order_cnt) AS pay_order_cnt, CASE WHEN SUM(pay_amt) 0 THEN 0 ELSE ROUND(SUM(refund_amt) / SUM(pay_amt), 4) END AS refund_rate FROM dws_trade_day WHERE dt ${biz_date} GROUP BY dt;跑完還要做對(duì)賬。至少查三項(xiàng)DWD 主鍵是否重復(fù)、DWS 匯總金額是否等于 DWD 明細(xì)金額、ADS 展示金額是否等于 DWS 匯總金額。下面這段對(duì)賬 SQL 輸出差異行差異不為 0 就阻斷下游看板發(fā)布。參數(shù)上${biz_date}同一天金額比較允許 0.01 的浮點(diǎn)誤差超過(guò)就查清洗規(guī)則或匯總維度是否丟數(shù)。-- 交易鏈路對(duì)賬DWD 明細(xì) vs DWS 匯總 SELECT d.dt, d.pay_amt AS dwd_pay_amt, s.pay_amt AS dws_pay_amt, d.pay_amt - s.pay_amt AS diff_amt FROM ( SELECT dt, SUM(pay_amount) AS pay_amt FROM dwd_trade_order WHERE dt ${biz_date} AND pay_status PAID GROUP BY dt ) d LEFT JOIN ( SELECT dt, SUM(pay_amt) AS pay_amt FROM dws_trade_day WHERE dt ${biz_date} GROUP BY dt ) s ON d.dt s.dt WHERE ABS(COALESCE(d.pay_amt,0) - COALESCE(s.pay_amt,0)) 0.01;4. 數(shù)據(jù)打通標(biāo)簽平臺(tái)從寬表到人群圈選的最小閉環(huán)標(biāo)簽平臺(tái)不是把用戶(hù)屬性拼成一張大寬表就完事真正難的是身份映射和標(biāo)簽更新節(jié)奏。資料里把標(biāo)簽平臺(tái)放在“打通”環(huán)節(jié)是因?yàn)橛脩?hù)行為數(shù)據(jù)和業(yè)務(wù)數(shù)據(jù)往往存在不同產(chǎn)品線、不同系統(tǒng)里行為數(shù)據(jù)知道用戶(hù)點(diǎn)了什么業(yè)務(wù)數(shù)據(jù)知道用戶(hù)買(mǎi)了什么兩邊對(duì)不上用戶(hù)畫(huà)像就是殘缺的。打通的目標(biāo)不是把所有字段都塞進(jìn)一張表而是讓“用戶(hù)是誰(shuí)、干了什么、買(mǎi)了什么”能在同一個(gè)用戶(hù) ID 下被查詢(xún)和圈選。4.1 標(biāo)簽體系先分三層基礎(chǔ)、行為、業(yè)務(wù)標(biāo)簽體系建議先分三層基礎(chǔ)標(biāo)簽、行為標(biāo)簽、業(yè)務(wù)標(biāo)簽?；A(chǔ)標(biāo)簽是注冊(cè)天數(shù)、會(huì)員等級(jí)、所在城市等級(jí)這類(lèi)變化慢的屬性行為標(biāo)簽是近 30 天點(diǎn)擊品類(lèi)數(shù)、近 7 天訪問(wèn)頻次、最近一次活躍時(shí)間業(yè)務(wù)標(biāo)簽是首單金額、近 90 天訂單數(shù)、退款次數(shù)。每類(lèi)標(biāo)簽都要有元數(shù)據(jù)標(biāo)簽碼、名稱(chēng)、類(lèi)型、更新方式、源表、責(zé)任人、有效期。不要一上來(lái)做幾百個(gè)標(biāo)簽先做 20 個(gè)業(yè)務(wù)真用的跑通“寬表生成—標(biāo)簽計(jì)算—人群圈選—營(yíng)銷(xiāo)調(diào)用”閉環(huán)。寬表按天分區(qū)只放常用標(biāo)簽長(zhǎng)尾標(biāo)簽按需計(jì)算。下面建表語(yǔ)句把用戶(hù) ID、基礎(chǔ)標(biāo)簽、行為標(biāo)簽、業(yè)務(wù)標(biāo)簽放在一張日分區(qū)表里dt是標(biāo)簽日期user_id是主鍵。參數(shù)上標(biāo)簽列允許為空空值代表“未知”而不是“否”更新方式用全量覆蓋避免增量 merge 寫(xiě)錯(cuò)導(dǎo)致標(biāo)簽漂移。寬表字段不要無(wú)限擴(kuò)超過(guò) 100 列就拆成基礎(chǔ)寬表和行為寬表否則查詢(xún)和跑批都會(huì)變慢。-- 用戶(hù)標(biāo)簽寬表按天分區(qū)常用標(biāo)簽放一張表 CREATE TABLE IF NOT EXISTS dws_user_tag_wide ( user_id STRING COMMENT 統(tǒng)一用戶(hù)ID, register_days INT COMMENT 注冊(cè)天數(shù), member_level STRING COMMENT 會(huì)員等級(jí), last_active_time STRING COMMENT 最近活躍時(shí)間, click_category_cnt_30d INT COMMENT 近30天點(diǎn)擊品類(lèi)數(shù), visit_cnt_7d INT COMMENT 近7天訪問(wèn)頻次, first_order_amt DECIMAL(16,2) COMMENT 首單金額, order_cnt_90d INT COMMENT 近90天訂單數(shù), refund_cnt_90d INT COMMENT 近90天退款次數(shù), dt STRING COMMENT 標(biāo)簽日期 yyyy-MM-dd ) PARTITIONED BY (dt) STORED AS PARQUET;標(biāo)簽計(jì)算不要每個(gè)標(biāo)簽一條獨(dú)立任務(wù)按源表分組批量算。行為標(biāo)簽從 DWD 事件表算業(yè)務(wù)標(biāo)簽從 DWD 訂單表算基礎(chǔ)標(biāo)簽從用戶(hù)維度表算最后按user_id全外連接。連接時(shí)注意空值行為表里沒(méi)有行為的用戶(hù)也要保留否則寬表只剩活躍用戶(hù)圈選“沉默用戶(hù)”就圈不出來(lái)。標(biāo)簽有效期也要寫(xiě)進(jìn)元數(shù)據(jù)比如“近 30 天點(diǎn)擊品類(lèi)數(shù)”每天重算“會(huì)員等級(jí)”每天同步過(guò)期標(biāo)簽要標(biāo)記不可用。4.2 身份映射打通產(chǎn)品線數(shù)據(jù)的硬骨頭跨產(chǎn)品線打通最難的是身份映射。同一個(gè)用戶(hù)在產(chǎn)品線 A 用手機(jī)號(hào)注冊(cè)在產(chǎn)品線 B 用設(shè)備號(hào)瀏覽在 product_line C 用會(huì)員 ID 下單如果沒(méi)有映射表三個(gè) ID 就是三個(gè)人。常見(jiàn)做法是建一張身份映射表字段包括統(tǒng)一用戶(hù) ID、手機(jī)號(hào)哈希、設(shè)備 ID、會(huì)員 ID、來(lái)源產(chǎn)品線、最近活躍時(shí)間、分區(qū)日期。優(yōu)先級(jí)一般是會(huì)員 ID 高于手機(jī)號(hào)哈希手機(jī)號(hào)哈希高于設(shè)備 ID沖突時(shí)按最近活躍時(shí)間保留人工可修正。手機(jī)號(hào)只存哈希不落明文設(shè)備 ID 也要做脫敏。-- 身份映射表多產(chǎn)品線 ID 歸一到 union_id CREATE TABLE IF NOT EXISTS dim_user_identity ( union_id STRING COMMENT 統(tǒng)一用戶(hù)ID, user_id STRING COMMENT 產(chǎn)品線內(nèi)用戶(hù)ID, phone_hash STRING COMMENT 手機(jī)號(hào)哈希不存明文, device_id STRING COMMENT 設(shè)備ID脫敏后存儲(chǔ), member_id STRING COMMENT 會(huì)員ID, product_line STRING COMMENT 來(lái)源產(chǎn)品線, last_active STRING COMMENT 最近活躍時(shí)間, dt STRING COMMENT 分區(qū)日期 ) PARTITIONED BY (dt) STORED AS PARQUET;映射邏輯分兩步先按強(qiáng) ID 配對(duì)手機(jī)號(hào)哈希相同或會(huì)員 ID 相同就歸到同一個(gè)union_id再用設(shè)備 ID 補(bǔ)弱關(guān)聯(lián)但設(shè)備 ID 沖突多不能單獨(dú)作為唯一依據(jù)。同一設(shè)備多賬號(hào)、同一手機(jī)號(hào)多產(chǎn)品線都要保留映射明細(xì)不能直接覆蓋。下面這段 SQL 是歸一的骨架ROW_NUMBER()按last_active倒序取最近一條COALESCE按優(yōu)先級(jí)選 ID。參數(shù)上dt用映射日期phone_hash要統(tǒng)一哈希算法和鹽值否則跨產(chǎn)品線對(duì)不上。-- 身份歸一強(qiáng)ID優(yōu)先設(shè)備ID兜底 INSERT OVERWRITE TABLE dim_user_identity PARTITION (dt ${biz_date}) SELECT CONCAT(U, MD5(CONCAT(COALESCE(phone_hash, member_id, device_id), ${salt}))) AS union_id, user_id, phone_hash, device_id, member_id, product_line, last_active, ${biz_date} AS dt FROM ( SELECT user_id, phone_hash, device_id, member_id, product_line, last_active, ROW_NUMBER() OVER ( PARTITION BY COALESCE(phone_hash, member_id, device_id) ORDER BY last_active DESC ) AS rn FROM ods_user_identity WHERE dt ${biz_date} ) t WHERE rn 1;提示身份映射表一定要保留來(lái)源產(chǎn)品線和最近活躍時(shí)間后續(xù)本文還有配套的精品資源點(diǎn)擊獲取