数据仓库实战（三）数据仓库DWS层建设指南（实战步骤+案例解析）

DWS（Data Warehouse Service）层是数据仓库中的服务数据层，它基于DWD层的明细数据，按照业务主题对数据进行轻度汇总，形成主题宽表，主要包含了1d（最近1日）、nd（最近n日）、td（历史至今）等时间维度的指标，是数据仓库中承上启下的关键环节，既保证了数据的可复用性，又为上层应用提供了标准化的数据服务。

一、DWS层定位与价值

1、DWS角色定位

DWS（Data Warehouse Service）层是数据仓库的服务数据层，位于DWD层之上，ADS层之下。它主要承担了将明细数据进行主题聚合的职责，是数据仓库中承上启下的关键环节。

2、业务价值

dws层的业务价值主要是：

降低计算成本：预计算常用指标，避免重复计算
统一指标口径：确保全公司指标计算规则统一
提升分析效率：提供主题化的宽表，方便业务分析
支持多维分析：保留完整维度，支持灵活查询

二、DWS层设计思路

1、主题划分

主题是DWS层最重要的设计要素，需要从以下几个方面考虑：

1）业务维度划分

交易域：订单、支付、退款等
用户域：注册、登录、画像等
流量域：访问、浏览、跳转等
商品域：商品、类目、品牌等

2）分析维度考虑

时间维度：日、周、月、年
地理维度：省份、城市、区域
用户维度：设备、渠道、等级
业务维度：品类、品牌、店铺

2、粒度设计

1）基础粒度

用户粒度：用户行为分析
商品粒度：商品销售分析
店铺粒度：店铺运营分析
订单粒度：交易过程分析

2）时间粒度

1d：最近1日汇总，日常监控
nd：最近n日汇总，趋势分析
td：历史至今汇总，累计分析

3、指标体系

1）指标类型

统计指标：数量、金额等
比率指标：占比、转化率等
环比指标：增长率、变化率等
复合指标：加权分数、综合评分等

2）计算方式

累计值：SUM、COUNT等
去重值：COUNT DISTINCT等
最新值：MAX、LAST_VALUE等
平均值：AVG、MEDIAN等

三、实战案例：交易行为分析

1、业务场景

分析用户购物行为，包括：

商品购买情况
用户消费习惯
品类偏好分析
支付方式分析

2、表设计方案

-- 用户商品交易行为汇总表（最近N日）
CREATE TABLE dws.dws_trade_user_sku_order_nd (
    user_id         STRING COMMENT '用户ID',
    sku_id          STRING COMMENT '商品ID',
    k1              DATE COMMENT '数据日期',

    -- 商品维度冗余
    sku_name        STRING COMMENT '商品名称',
    category1_id    STRING COMMENT '一级品类ID',
    category1_name  STRING COMMENT '一级品类名称',
    tm_id           STRING COMMENT '品牌ID',
    tm_name         STRING COMMENT '品牌名称',

    -- 7日汇总指标
    order_count_7d  BIGINT COMMENT '7日下单次数',
    order_num_7d    BIGINT COMMENT '7日购买件数',
    order_amount_7d DECIMAL(16,2) COMMENT '7日下单金额',

    -- 30日汇总指标
    order_count_30d BIGINT COMMENT '30日下单次数',
    order_num_30d   BIGINT COMMENT '30日购买件数',
    order_amount_30d DECIMAL(16,2) COMMENT '30日下单金额'
) COMMENT '交易域用户商品粒度订单最近N日汇总表'
PARTITION BY k1;

3、实现方案

1）维度关联

-- 1. 关联商品维度信息
SELECT 
    od.user_id, od.sku_id, od.k1,

    -- 2. 冗余维度属性
    COALESCE(sku.sku_name, '未知商品') as sku_name,
    COALESCE(sku.category1_id, '-1') as category1_id,
    COALESCE(sku.category1_name, '未知品类') as category1_name,
    COALESCE(sku.tm_id, '-1') as tm_id,
    COALESCE(sku.tm_name, '未知品牌') as tm_name,

    -- 3. 统计指标
    od.order_count_1d,
    od.order_num_1d,
    od.order_amount_1d
FROM order_detail od
LEFT JOIN dim.dim_sku_full sku
    ON od.sku_id = sku.id
    AND sku.k1 = (
        SELECT MAX(k1) 
        FROM dim.dim_sku_full 
        WHERE k1 <= DATE('${pdate}')
    );

2）指标计算

-- 1. 计算汇总指标
SELECT
    user_id, sku_id, k1,
    sku_name, category1_id, category1_name,
    tm_id, tm_name,

    -- 2. 计算7日累计
    SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
           order_count_1d, 0)) AS order_count_7d,
    SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
           order_num_1d, 0)) AS order_num_7d,
    SUM(IF(k1 >= DATE_ADD(DATE('${pdate}'), -6),
           order_amount_1d, 0)) AS order_amount_7d,

    -- 3. 计算30日累计
    SUM(order_count_1d) AS order_count_30d,
    SUM(order_num_1d) AS order_num_30d,
    SUM(order_amount_1d) AS order_amount_30d
FROM dws.dws_trade_user_sku_order_1d
WHERE k1 >= DATE_ADD(DATE('${pdate}'), -29)
GROUP BY 
    user_id, sku_id, k1,
    sku_name, category1_id, category1_name,
    tm_id, tm_name;

通过以上案例分析，我们可以看到DWS层在实际业务中的应用方式和实现细节。这些设计方案和实现方法可以作为参考，在实际项目中根据具体需求进行调整和优化。