一个指标该放DWS还是ADS？——派生指标和原子指标的边界怎么划？

-- 接手时的灾难现场（部分字段）
CREATE TABLE dws_user_action_1d (
  user_id BIGINT,
  -- 原子指标（合理）
  pv_cnt BIGINT, order_cnt BIGINT, pay_amount DECIMAL,
  -- 派生指标（错位）
  pv_cnt_7d BIGINT,        -- 不该在每天的事实表里
  pv_cnt_30d BIGINT,       -- 不该在每天的事实表里
  order_cnt_active BIGINT, -- 含义重复
  order_cnt_new BIGINT,    -- 含义重复
  -- ...还有180个字段
) PARTITIONED BY (dt);
 
-- 后果：每天ETL跑3小时，存储50TB/月，查询响应20秒+

-- 重构后的DWS：只有原子指标，干净清爽
CREATE TABLE dws_user_action_1d (
  dt              STRING,
  user_id         BIGINT,
  -- 行为类原子指标
  pv_cnt          BIGINT,
  click_cnt       BIGINT,
  duration_sec    BIGINT,
  -- 交易类原子指标
  order_cnt       BIGINT,
  pay_amount      DECIMAL,
  refund_amount   DECIMAL,
  -- 用户属性维度（用作切片）
  country         STRING,
  channel         STRING,
  user_level      STRING
) PARTITIONED BY (dt);
-- 列数从200+砍到20，存储降50%，查询提速3倍

规则：DWS的列必须是"事件本身就有的属性"或"当天就能聚合出来的原子度量"。任何跨天聚合（如7日累计）都不应在DWS主表里。

方法二：派生指标按使用频次决定物化

派生指标=原子指标+维度+时间窗口+修饰词。要不要物化看两个维度：

         高复用度（>10个报表用）  低复用度（<3个报表用）
高计算成本   ✅ 必须物化到ADS         ⚠️ 评估再说
（>10秒）    （如近30日GMV）         （如年累计但只查一次）
 
低计算成本   📦 物化到DWS派生表       ❌ BI层算就行
（<2秒）    （如近7日GMV）           （如客单价=GMV/订单数）

我在出海硬件品牌按这个矩阵清理了一遍，从200+派生指标砍到80个核心物化派生，存储降40%，查询响应平均从20秒降到5秒。

方法三：DWS派生表的命名和组织

高频派生指标单独建表，命名规则要清晰：

-- 命名规则：dws_{业务域}_{维度}_{时间窗口}
 
-- 例1：用户×7日聚合
dws_trade_user_7d
  user_id, pay_amount_7d, order_cnt_7d, refund_amount_7d, ...
 
-- 例2：商品×30日聚合
dws_trade_sku_30d
  sku_id, sale_amount_30d, sale_qty_30d, ...
 
-- 例3：渠道×当日聚合
dws_growth_channel_1d
  channel, new_user_cnt_1d, cac_1d, ...
 
-- 上层ADS只需要简单select+join，逻辑薄

方法四：衍生指标尽量在BI层算

衍生指标=派生指标的运算（加减乘除、比率）。比如客单价=GMV/订单数，留存率=次日活/前日活。这类指标99%的情况都应该在BI层算，理由：

①避免存储冗余：一个客单价可以有按天、按周、按月、按维度切片，落表会指数膨胀。

②保持灵活性：业务方可能突然问"按渠道看客单价"，BI层算几秒钟搞定，落表的话要重跑数仓。

③避免维护困难：衍生指标的口径经常变（如"客单价是否含税"），改一次成本高。

-- 推荐：BI层做衍生计算
SELECT
  country,
  pay_amount_7d AS gmv_7d,                    -- 派生指标
  order_cnt_7d AS order_cnt_7d,               -- 派生指标
  pay_amount_7d / NULLIF(order_cnt_7d, 0)     -- 衍生指标，BI算
    AS avg_order_value_7d
FROM dws_trade_user_7d a
JOIN dim_user b ON a.user_id = b.user_id
GROUP BY country;

面试高频追问

追问一：什么情况下衍生指标也要物化？

两种情况：①BI层算不出来——比如要算「同比客单价变化率」，需要用到一年前的数据，BI层关联开销大；②高频固定查询——比如核心报表每天查百万次，每次BI算太浪费。这时候我会做一张"指标快照表"，按天预计算，BI直接查。但这是例外不是常态。

追问二：DWS派生表和ADS表的区别？

DWS派生表是"通用聚合"——按用户×时间维度聚合，是给ADS层和指标平台复用的中间层。ADS表是"应用导向"——直接服务于某个具体报表/Dashboard，结构和需求强绑定。判断标准：能被≥3个ADS复用的就放DWS派生层，强专用的就放ADS。

追问三：实时指标怎么分层？

实时分层和离线略有不同：原子指标在DWD层（明细流），派生指标在DWS层（Flink聚合状态），不太做ADS物化（直接走OLAP查询）。原因是实时数据时效性优先，多一层物化就多一份延迟。但口径定义跟离线必须一致——这点在第6篇双链路一致性会展开讲。

实战踩坑：90日累计指标放DWS拖垮整个集群

有次为了赶进度，我把"近90日GMV"放进了DWS主题宽表（每天滑动90日窗口）。结果上线第二周，集群跑批从4小时变成12小时，因为这个字段的窗口计算成本巨大，跟其他字段绑在一起算严重拖后腿。

教训：长周期、高成本的派生指标必须独立成表，不能塞进高频更新的主表。后来拆出来一张dws_trade_user_90d单独跑，用增量更新替代每天全量，跑批时间降到40分钟。这是分层不严格付出的代价。

总结

回答这道题的高分结构：先讲分层原则（原子/派生/衍生）→ 再讲物理落位（DWS/ADS/BI层）→ 最后讲判断标准（复用度+计算成本矩阵）。能给出具体case（如"我把200列DWS拆成20列+派生表，存储降40%"）就是Staff级别答案。

下一篇聊一个流程类话题：业务说"加个新指标，明天上线"——指标需求评审到底该卡哪几关？

你们公司DWS表多少列？派生指标怎么管理？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长