做电商分析平台这类大数据项目真正让人“头秃”的往往不是环境搭建和管道传输而是数据进来之后数仓层怎么分、指标口径怎么统一、SQL怎么写才既能跑得动又不怕上下游改表。前一篇我们把环境、模拟数据和采集通道搞定了这一篇直接进入离线数仓的核心地带讲清楚分层设计、关键指标实现、调度编排和调优避坑。这篇内容适合正在做离线数仓项目、或者想把指标计算方法沉淀下来的同学不是纯理论基本是我把整个链路跑通后最想复盘的部分。1. 离线数仓的整体骨架先把分层定明白1.1 从一张“万能大宽表”说起为什么必须分层很多第一次做数仓项目的人有一个天然冲动把订单、日志、商品、用户全塞进一张大宽表然后用SQL一把梭算出所有指标。听起来简单实际上不到一周就会出问题。我刚做某电商项目时就是这么干的ods层同步完直接把明细表join成一张“超级表”一开始还能跑后来字段越来越多计算任务越跑越慢改一个指标要重跑整张表而且业务方问“你这个GMV怎么跟财务口径对不上”我查了半天才发现是口径混着用。分层的核心目的不是“显得专业”而是把复杂度拆开让每一层只干一件事。原始数据进来后先做保留和清洗再加工成明细中间层然后按主题做汇总最后只把结果交出去。这样做的直接好处有三个一是复用。DWS层做好的商品粒度汇总可以被好几个ADS报表同时用不用每个需求重新跑一次全量明细。二是可控。每层边界清楚了出问题能快速定位到具体环节不会一把梭查到底。三是节省资源。ADS只暴露轻量汇总结果BI查询和临时取数都压在这层底层明细不会反复被扫描。所以无论你是用Hive还是Spark先把ODS、DWD、DWS、ADS这四层想清楚后面所有代码和调度都会顺很多。1.2 ODS到ADS每一层该放什么按我习惯的落地方式每层的命名和职责是这样的层职责典型表名加工方式ODS原样接入保留最全信息ods_log_inc、ods_order_inc采集通道直接落表不做过多的业务处理DWD清洗、脱敏、标准化按业务过程建模dwd_trade_order_di、dwd_traffic_page_di对ODS做ETL补全维度字段拆成明细事实DWS按主题汇总面向分析场景dws_trade_order_nd、dws_traffic_pv_uv_di按用户、商品、渠道等维度预聚合ADS面向报表和即席查询的结果表ads_trade_gmv_sum、ads_user_repurchase_rate通常从DWS再汇总结果轻量ODS层我建议不要做太多过滤哪怕有些字段暂时用不上也别删。原因是源系统可能随时调整你删了以后再想追溯就麻烦了原样保留反而是最安全的做法。DWD层是清洗重点。日志数据要过滤爬虫、去掉无效字段订单数据要统一状态枚举、金额单位还要把user_id缺失的记录单独标记不能简单丢弃否则后续留存和复购计算会少一截。DWS层开始做预聚合。比如流量主题汇总按“日渠道页面”统计PV/UV交易主题按“日商品”统计订单量和交易金额。预聚合粒度别做得太粗建议保留到能支撑绝大多数查询的最小粒度比如商品粒度别直接聚合到全站总金额。ADS层其实很“薄”它只是把DWS结果继续加工成最终指标比如周环比、月累计。别在这一层做复杂join否则报表查询很容易卡住。1.3 公共维度设计别每次写SQL都去join一遍分层之外维度建模也需要提前定好。电商分析里最常用的公共维度包括用户维度、商品维度、时间维度。用户维度表我一般做成拉链表包含用户注册时间、会员等级、首单时间等。拉链表的好处是既能查当前状态也能回溯历史代价是每次刷新要处理闭链和开链不算复杂但要写对。商品维度表相对简单主要保存类目层级、品牌、上架状态每天全量刷新即可。时间维度表我强烈建议直接生成一张十年的日期表包含年、季、月、周、日、是否是工作日、是否节假日。日期表属于一次性建设成本很低但后续做同环比、周维度统计时能省很多事不用自己算日期偏移。公共维度设计的原则是“一对多引用”事实表只保存维度ID查询时再由维度表补充名称和属性不要在事实表里冗余一堆维度描述字段。这样维度变了只改维度表不用动事实表。2. 指标口径先把“元凶”锁定再动手2.1 电商绕不开的7个核心指标不少项目做到后面出问题不是SQL写得错而是大家理解的数不一样。做电商分析平台至少要把这7个指标的定义敲死GMV用户下单的总金额。这里要特别注意GMV是“拍下金额”还是“支付金额”两套口径结果差很远。支付金额用户实际付款金额扣掉取消订单和退款。订单量按订单主表统计的订单个数分有效订单、取消订单。UV独立访客数。按user_id去重还是按cookie去重口径不同结果也会差很多。PV页面浏览量每加载一次页面就加1。客单价GMV除以下单用户数或者支付金额除以支付用户数两种口径都要说清楚。复购率通常指“在统计周期内产生过至少2次购买的用户数”除以“总购买用户数”。这些指标看起来简单但如果你不做指标字典直接开写SQL三个人能写出三种结果。项目开发最怕这个。2.2 三个最容易踩的口径坑我踩过最深的坑是时间口径。订单表里有下单时间、支付时间、完成时间多个字段统计“当日GMV”到底按哪个时间做活动分析时按下单时间最合理做财务对账时按支付时间做发货履约时按完成时间。同一张表三个时间三个部门三种需求如果指标字典不写清楚最后就是扯皮。第二个坑是用户去重逻辑。埋点日志里有时有user_id有时只有设备ID。我的处理办法是登录用户用user_id未登录用户合并到设备维度下的匿名标识并且在汇总表里单独标记“登录/匿名”两个分类报表里可以选口径但底表数据不互相污染。第三个坑是会话切分。判断PV、UV通常在“session”粒度上有不同标准有的按30分钟无动作断开有的按凌晨零点切分。没有固定标准可以商量但代码必须统一。切分逻辑尽量放在DWD层用窗口函数算好别在DWS或者报表里各自实现。2.3 用指标字典把口径固定下来电商分析平台这种多人协作项目上线前一定要做一张指标字典表。不需要多复杂字段就是指标名称、指标英文名、统计口径、适用时间字段、统计维度、负责人、上线时间、备注。比如一条记录可以这样写指标名称英文标识统计口径统计时间字段负责人商品交易总额gmv用户已下单订单的金额汇总包含未支付订单不含取消订单下单时间A同学有了这张表哪怕团队换人后来者也不会把口径改歪。更重要的是所有ADS表字段注释里都要标明指标标识让人看到字段就知道它对应字典里的哪条逻辑。3. 核心计算场景的SQL实现与调优3.1 流量主题UV/PV这样算才不会重算漏算流量计算的第一步是把埋点明细整理成会话记录再基于会话做聚合。下面是一段比较典型的DWD层会话切分SQL-- 基于30分钟无行为切分会话 with ( select user_id, page_url, visit_time, if(unix_timestamp(visit_time) - unix_timestamp(lag(visit_time) over(partition by user_id order by visit_time)) 1800, 1, 0) as is_new_session from ods_log_inc where dt 2025-01-01 ), session_flag as ( select user_id, page_url, visit_time, sum(is_new_session) over(partition by user_id order by visit_time rows between unbounded preceding and current row) as session_id from page_data ) select user_id, session_id, min(visit_time) as session_start, max(visit_time) as session_end, count(*) as pv_in_session from session_flag group by user_id, session_id;这段SQL的核心思路是用lag函数取出上一条访问时间时间差超过30分钟就打标成新会话再用累加生成会话ID。DWS层再按天聚合UV是登录用户的去重数。需要注意count(distinct user_id)在数据量很大时特别吃资源可以在DWD层先按“日期用户”去重成中间表再count(*)效果会好很多。PV则直接sum(会话内pv数)即可。我调优时遇到过一个问题同一用户短时间内在多个设备上访问导致UV偏大。后来加了“多端合并”逻辑用登录态把同一账号的跨端行为捆绑到一个统一用户ID。如果某电商平台自身就有统一账号体系最好直接用账号体系ID别只看设备ID。3.2 订单主题GMV、客单价和累计值订单主题最核心的明细模型是“订单事实表” “订单明细事实表”一张记录订单主表信息用户、时间、状态、总额另一张记录每个订单里的商品明细商品、数量、金额。计算GMV时按订单主表聚合即可select date_format(order_time, yyyy-MM-dd) as dt, count(distinct order_id) as order_cnt, sum(order_amount) as gmv from dwd_trade_order_di where dt between date_add(current_date, -6) and current_date and order_status in (paid, unpaid, pending) group by date_format(order_time, yyyy-MM-dd);这里有个业务判断GMV到底包不包含“未支付”订单我建议GMV包含未支付订单支付金额单独用支付成功状态统计。这样活动大促期间能看到“下单冲动”与“真实交易”的差距财务也能有对照。客单价的计算公式通常写成select dt, sum(gmv) / count(distinct buyer_id) as avg_order_value from dws_trade_order_nd group by dt;如果你按“每个订单的实付金额加起来除以订单数”算客单价其实是“单均金额”跟“客单价”不一样。两者都有人用但千万别把名字搞混否则跟业务方对不上。累计值场景也很常见比如“截至当日累计GMV”。我写DWS时是这样处理的select dt, gmv as day_gmv, sum(gmv) over(order by dt rows between unbounded preceding and current row) as cum_gmv from dws_trade_order_nd;窗口函数在这里非常合适比自己join自己性能高得多。注意如果你需要按多个维度累计分区里就要写清楚partition by字段。3.3 用户主题复购率和留存率复购率计算的关键是先确定“第一次购买时间”再统计首次购买之后是否还有第二次购买。我推荐这样处理with first_buy as ( select user_id, min(date_format(order_time, yyyy-MM-dd)) as first_dt from dwd_trade_order_di where order_status paid group by user_id ), repeat_buy as ( select fb.user_id, fb.first_dt, count(distinct case when o.order_time fb.first_dt then o.order_id end) as repeat_cnt from first_buy fb left join dwd_trade_order_di o on fb.user_id o.user_id and o.order_status paid group by fb.user_id, fb.first_dt ) select first_dt, count(*) as new_buyers, sum(case when repeat_cnt 1 then 1 else 0 end) as repeat_buyers, round(sum(case when repeat_cnt 1 then 1 else 0 end) / count(*), 4) as repurchase_rate from repeat_buy group by first_dt;先把首购时间用min()算出来再对比订单时间是否晚于首购时间。注意“两单”的定义我按的是“再次购买订单数1”如果你要把“当天多次下单但只算一次”剔除需要按日期聚合后再判断。留存率的计算逻辑稍微不同它是看首购后第N天是否还有回访或购买。建议在DWD层把活跃行为浏览、下单、支付统一成“用户行为事实表”然后按“首购日”和“行为发生日”回填最后形成一个“首购日×行为日×用户数”的矩阵表BI就能直接透视。3.4 跨层校验算完必须对得上数很多同学做完汇总层直接出报表结果数据错误查半天找不到原因。我的习惯是每一层都留校验SQL上线前必须跑-- 校验ODS到DWD的行数变化 select ods_orders as src, count(*) as cnt from ods_order_inc where dt 2025-01-01 union all select dwd_orders as src, count(*) as cnt from dwd_trade_order_di where dt 2025-01-01;金额同理把sum(order_amount)对比一遍。如果差异超过0.01元就要查原因不能模棱两可。4. 调度系统选型与任务编排4.1 为什么依赖调度手动跑会第3天就崩溃一开始项目表少手动跑SQL还能接受。表一多前后依赖交错手动执行根本排不开。比如DWD层的订单清洗必须等ODS同步完成DWS层月度汇总必须等日汇总全部结束中间任何一个任务失败后面全跟着错。所以我直接引入开源调度框架来编排任务。你可以用任何主流调度平台关键是把任务依赖、重跑机制、告警三件事做好。4.2 完整工作流长什么样我编排的一个完整离线工作流大致是ODS同步任务每隔整点同步前一小时数据或每天统一同步昨日全量。DWD清洗任务依赖ODS任务完成。清洗完成后发送一条状态记录。DWS汇总任务依赖DWD任务。按主题划分各节点比如流量汇总和交易汇总可以并行跑互不依赖。ADS导出任务依赖DWS。多个ADS任务可能共同依赖同一个DWS节点。数据质量校验依赖ADS跑校验SQL异常则发告警并阻塞后续任务。在调度平台上配置时最需要注意“超时时间”和“失败重试次数”。网络抖动或者集群资源紧张时任务偶发失败很常见我一般设置重试2次、重试间隔5分钟超过重试次数才真正失败并告警。依赖配置上建议用“节点级依赖”而不是“工作流级依赖”。例如“ads_trade_gmv_sum”只依赖“dws_trade_order_nd”不要所有任务都统一依赖一个根节点否则跑完一个任务要等全局干等很久。4.3 补数、重跑与告警数据分析平台免不了补数比如上游修复了某天数据或调整了计算逻辑。调度平台都支持补数但要小心补数时指定好业务日期并且同一天任务覆盖写之前的结果确保下游看到的始终是最终值。重跑某一天任务会连带重跑下游任务吗不一定。建议对“可重入”的ADS任务关闭全链路重跑只重跑当前节点防止一个补数操作把整条链路全部重算一遍把集群资源打爆。告警渠道我用的是“任务失败告警 数据质量告警”双通道。数据质量告警不要只在任务失败时触发有些任务跑完但数据为空任务状态是成功的这时候必须靠校验SQL兜底。校验不通过邮件和站内通知同时报警人才能及时重启流程。5. 性能优化与问题排查实录5.1 数据倾斜症状、定位与三种解法离线任务随着数据量增长最常见的翻车现场就是数据倾斜。症状非常明显同一个SQL之前跑20分钟现在跑两个小时看Spark或Hive的Task列表发现大部分Task都结束了就剩几个Task卡着跑不完。定位方法很简单找到跑得最慢的那个Task看它的key分布。如果是group by维度倾斜大量记录集中在一个维度值上如果是join倾斜关联键空值过多或者热点值过于集中。我常用的三种解法空值处理如果null值导致倾斜可以把null随机打散比如给null分配随机后缀。加盐group by时先对key加随机前缀做两阶段聚合比如“key 随机数”先做部分聚合再做最终聚合。map join小表关联大表时用map join把关联表广播到内存规避reduce端倾斜。两阶段聚合的典型SQL如下select dt, key, sum(partial_cnt) as total_cnt from ( select dt, concat(key, _, cast(rand() * 10 as int)) as key, count(*) as partial_cnt from dwd_trade_order_di group by dt, concat(key, _, cast(rand() * 10 as int)) ) t group by dt, key;先加盐打散热点再合并还原结果。注意如果key本身基数就低比如只有几个类目加盐后第一阶段聚合效果仍然有限这种情况还是考虑改维度设计比如把总量指标拆出去。5.2 小文件合并为什么不能小文件满天飞电商明细日志经常产生大量小文件。HDFS适合大文件存储小文件过多后NameNode内存压力巨大SQL扫描文件列表的耗时也会指数级上升。我的经验是在每次DWD清洗结束后做一次“小文件合并”操作。Spark场景下可以在写入时设置如下参数set spark.sql.shuffle.partitions 200; set spark.sql.adaptive.enabled true; set spark.sql.adaptive.coalescePartitions.enabled true; set spark.sql.adaptive.coalescePartitions.minPartitions 50;如果使用Hive可以用set hive.merge.mapfiles true; set hive.merge.mapredfiles true; set hive.merge.size.per.task 256000000; set hive.merge.smallfiles.avgsize 16000000;文件大小控制在256MB左右比较健康既能减少扫描开销也不至于因为文件过大导致并发度下降。5.3 内存和并行度的参数参考很多人跑Spark任务时不调参默认配置跑小数据集没问题数据量一大就完蛋。我常用的一个比较稳妥的参数组合是这样spark-submit \ --driver-memory 4g \ --executor-memory 8g \ --executor-cores 4 \ --num-executors 20 \ --conf spark.sql.shuffle.partitions200 \ --conf spark.sql.adaptive.enabledtrue \ --conf spark.sql.adaptive.coalescePartitions.enabledtrue \ --conf spark.sql.adaptive.shuffle.targetPostShuffleInputSize128MB这里有几个容易理解错的地方executor内存不要一味开大开太大可能导致YARN调度延迟或GC严重。一般单个executor配4~8GB足够shuffle partitions跟数据规模匹配不是越大越好。50GB明细数据200个分区通常够用如果每条数据特别大再适当增加分区数。实际调优时我通常看执行计划里的Shuffle写量写量除以分区数就是每个reduce大概要处理的数据量目标控制在256MB以内比较合理。5.4 上线前必跑的质量核对脚本最后分享一套我每次发版前都会跑的质量核对SQL模板。不止查表“跑通没有”更要查数据“对不对”-- 空值率检查 select count(*) as total_rows, sum(case when user_id is null or user_id then 1 else 0 end) as null_user_cnt from dwd_trade_order_di where dt 2025-01-01; -- 重复订单检查 select order_id, count(*) as dup_cnt from dwd_trade_order_di where dt 2025-01-01 group by order_id having count(*) 1; -- 金额异常检查 select count(*) as abnormal_cnt from dwd_trade_order_di where dt 2025-01-01 and order_amount 0 and order_status not in (canceled, closed);这三类规则基本能拦下绝大多数低级错误。空值过多先查清洗逻辑重复订单查上游是否重复同步金额异常查状态枚举是否覆盖完整。如果让我重做一次这个电商分析平台我会把指标字典从第一天就建好而不是等表都但上线前才补。字典先行不仅让开发流程顺很多也让我在跟业务方对齐的时候省掉了一大半扯皮。至于那些倾斜、小文件、OOM问题如果你把分层和调优参数从一开始就当成既定规范后续其实很少会翻车。