DWS汇总层20+张表，有些根本没人用——怎么避免过度建设？

-- DWS渠道ROI日汇总表
CREATE TABLE dws_channel_roi_1d (
    channel         STRING  COMMENT '投放渠道',
    country_code    STRING  COMMENT '国家代码',
    -- UV类指标
    click_uv        BIGINT  COMMENT '点击UV',
    install_uv      BIGINT  COMMENT '安装UV',
    register_uv     BIGINT  COMMENT '注册UV',
    paid_uv         BIGINT  COMMENT '付费UV（首次付费）',
    -- PV类指标
    click_pv        BIGINT  COMMENT '点击PV',
    impression_pv   BIGINT  COMMENT '展示PV',
    -- 金额类指标
    total_cost      DECIMAL(12,2)  COMMENT '投放花费(USD)',
    total_revenue   DECIMAL(12,2)  COMMENT '付费收入(USD)',
    -- 计算指标
    roi             DECIMAL(8,4)   COMMENT 'ROI=收入/花费',
    install_rate    DECIMAL(8,4)   COMMENT '安装率=安装UV/点击UV',
    pay_rate        DECIMAL(8,4)   COMMENT '付费率=付费UV/安装UV'
) PARTITIONED BY (dt STRING)
COMMENT '渠道ROI日汇总 | 粒度：渠道×国家×天 | 下游：ROI日报、渠道分析、管理驾驶舱';

-- 粒度选择示例
 
-- DWS层（公共粒度：渠道×国家×天）
-- 满足3个以上下游的需求
SELECT channel, country_code, dt,
    COUNT(DISTINCT user_id) AS click_uv,
    SUM(pay_amount) AS total_revenue
FROM dwd_ad_attribution_di
GROUP BY channel, country_code, dt;
 
-- ADS层（个别需求：渠道×国家×广告组×天）
-- 只有投放优化团队用，直接从DWD取
SELECT channel, country_code, ad_group_id, dt,
    COUNT(DISTINCT user_id) AS click_uv,
    SUM(pay_amount) AS total_revenue
FROM dwd_ad_attribution_di  -- 直接从DWD取，不经过DWS
GROUP BY channel, country_code, ad_group_id, dt;

宽表设计：什么时候该建宽表

DWS宽表是一种特殊的汇总表——把多个主题的指标拼到一张大表里。比如'用户行为宽表'把登录、观看、付费、分享等多个事件的指标都聚合到同一张表里：

-- DWS用户行为宽表
SELECT
    user_id,
    dt,
    -- 登录行为
    MAX(CASE WHEN event_type='login' THEN 1 ELSE 0 END) AS is_login,
    -- 观看行为
    SUM(CASE WHEN event_type='watch' THEN watch_duration ELSE 0 END)
        AS total_watch_seconds,
    COUNT(CASE WHEN event_type='watch' THEN 1 END) AS watch_cnt,
    -- 付费行为
    SUM(CASE WHEN event_type='purchase' THEN pay_amount ELSE 0 END)
        AS total_pay_amount,
    MAX(CASE WHEN event_type='purchase' THEN 1 ELSE 0 END) AS is_paid,
    -- 分享行为
    COUNT(CASE WHEN event_type='share' THEN 1 END) AS share_cnt
FROM dwd_user_event_di
WHERE dt = '${bizdate}'
GROUP BY user_id, dt;

宽表的优势：下游只需要查一张表就能拿到用户的全方位行为数据，不需要JOIN多张表。用户留存分析、RFM分层、用户画像这些需求都可以直接从宽表取数。

宽表的代价：① 字段多、维护成本高（新增一个事件类型就要加字段）；② 计算量大（要扫描多个事件类型的明细数据）；③ 如果粒度是user_id级别，数据量会很大。所以宽表不要滥建——只有跨主题分析是高频需求时才值得建。

DWS瘦身实战：从35张精简到23张

我接手某短剧出海公司数仓时，DWS层有35张表。我做了一次全面审计：

-- DWS表使用情况审计SQL
-- 查询每张DWS表被多少个下游任务引用
SELECT
    table_name,
    COUNT(DISTINCT downstream_task) AS downstream_cnt,
    MAX(last_query_time) AS last_used,
    -- 最近30天的查询次数
    SUM(CASE WHEN query_time >= DATE_SUB(CURRENT_DATE, 30)
        THEN 1 ELSE 0 END) AS query_cnt_30d
FROM table_dependency_log
WHERE table_name LIKE 'dws_%'
GROUP BY table_name
ORDER BY downstream_cnt ASC;

审计结果让我很吃惊：

类别	数量	处理方式
下游=0（完全没人用）	8张	确认后直接下线
下游=1（只有一个下游用）	4张	合并到ADS层，DWS下线
下游=2（边缘复用）	6张	保留观察，标记低优
下游≥3（真正的公共层）	17张	保留并优化
重复指标（多张表算同一个指标）	3组	合并去重，保留一张

最终从35张精简到23张。精简后不仅节省了计算资源（每天少跑12张DWS表，节省约2小时计算时间），还减少了口径混乱——之前有3对DWS表在算同一个指标但口径微妙不同，合并后统一了口径。

面试高频追问

追问一：DWS的指标和ADS的指标有什么区别？

DWS的指标是'公共指标'——独立于任何特定业务需求，可以被多个下游复用。比如'渠道日UV'就是一个公共指标，ROI报表用它、渠道分析用它、管理大屏也用它。ADS的指标是'业务指标'——针对特定需求定制，可能是DWS指标的组合或二次加工。比如'ROI=收入/花费'就是用DWS的total_revenue除以total_cost。DWS提供原材料，ADS做最后的拼装。

追问二：DWS表的更新策略是什么？全量覆盖还是增量？

日汇总表用INSERT OVERWRITE按天分区覆盖——每天只重算当天的数据，历史分区不动。累计汇总表比较特殊，有两种做法：① 每天从历史到当天全部重算（数据量小时可以）；② 增量更新——读昨天的累计值+今天的增量值合并写入（数据量大时用这种）。我们的渠道ROI日汇总用方式①，用户生命周期累计用方式②。

追问三：DWS和数据集市（Data Mart）是一回事吗？

有交集但不完全一样。DWS是数仓分层里的汇总层，按主题组织公共指标，服务于多个下游。数据集市是面向特定部门或业务线的数据视图，可能包含DWS的数据也可能包含ADS的数据。在实践中，我们的DWS相当于'全局数据集市'，每个部门的特定需求在ADS层实现——可以理解为ADS就是'部门级数据集市'。

实战踩坑

DWS层最痛的一次踩坑是'指标膨胀'。我们的内容分析宽表一开始只有15个指标字段，后来业务方不断提需求——'加一个完播率'、'加一个人均观看时长'、'加一个次日回访率'——半年后膨胀到了50+个字段。每次加字段都要改DWS的SQL、测试、上线，而且表越来越宽，查询性能也越来越差。

后来我们的解决方案是'指标分级'：把50个指标分成三级——核心指标（10个，必须在DWS里预计算）、常用指标（15个，在DWS里预计算但标记为可选）、长尾指标（25个，不放DWS，在ADS层即时计算）。分级的依据是查询频率——通过审计SQL日志，统计每个指标被查询的次数，高频的放DWS预计算，低频的不放。

-- 指标分级管理
-- 审计SQL：统计DWS表中每个指标字段的实际被使用频率
SELECT
    column_name,
    COUNT(*) AS usage_count_30d,
    CASE
        WHEN COUNT(*) >= 100 THEN 'CORE（核心指标，必须在DWS）'
        WHEN COUNT(*) >= 20  THEN 'COMMON（常用指标，推荐在DWS）'
        ELSE 'LONG_TAIL（长尾指标，放ADS即时计算）'
    END AS indicator_level
FROM query_column_access_log
WHERE table_name = 'dws_content_wide_1d'
  AND query_time >= DATE_SUB(CURRENT_DATE, 30)
GROUP BY column_name
ORDER BY usage_count_30d DESC;

总结

DWS层的设计核心是'精准复用'——不多建（浪费资源）、不少建（口径混乱）。建表前看下游复用度（≥3个下游才建DWS），粒度按最小公共需求定，定期审计瘦身清理僵尸表。面试时讲DWS，重点说：① 你的DWS有多少张表、按什么粒度设计的；② 怎么判断一个指标该放DWS还是ADS；③ 有没有做过DWS的优化或瘦身。能讲出'从35张精简到23张'这种故事，面试官就知道你是有治理思维的。

DWS层设计讲清楚了，下一篇聊一个高频实战问题——拉链表跑了半年越来越慢，SCD Type2怎么优化？

你们的DWS层有多少张表？有没有做过DWS瘦身？评论区聊聊～

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

数仓项目实战面试通关手册

第11-15篇：治理体系与实时保障

Wood聊大数据

9年大数据老兵，聊技术、聊职场、聊成长