DWS 与 ADS 实现

本章目标：自己在实验室里建出 6 张 DWS 主题表和 8 张 ADS 结果表，并用校验表确认每个数字。 前置知识：GROUP BY、COUNT(DISTINCT)、窗口函数 RANK、LAG、NTILE；已完成 02 ODS 与 DWD 预计用时：90 分钟。

0. 本章你要产出什么

在实验室库里建出 14 张表，并得到一组能写进报告的数字：

完成标志：下面 8 个指标全对。

1. 先看懂：DWS 和 ADS 分别解决什么

DWD 是明细，查一次要扫 11 万行；如果报表每天被点几百次，每次都从明细算，既慢又容易口径不一致。

• DWS（主题宽表）：按“交易、品类、卖家、客户、履约”等主题预聚合，一行代表一个分析粒度。
• ADS（应用数据层）：直接给报表、看板、面试展示用的结果表，通常一表一个场景。

分层的判断标准不是“表多不多”，而是：下游是否重复算同一个指标。重复三次以上，就值得沉淀到 DWS/ADS。

本章的构建顺序是：先 DWS（1d → 1m → 品类/卖家 → RFM → 履约），再 ADS（总览 → 趋势 → 排行 → 分层 → 复购 → 履约 → 评价）。每建一张就查一次行数。

2. 动手一：交易主题 dws_trade_1d / dws_trade_1m

2.1 日汇总

CREATE OR REPLACE TABLE dws_trade_1d AS
SELECT
    order_date                              AS dt,
    COUNT(DISTINCT order_id)                AS order_cnt,
    COUNT(*)                                AS item_cnt,
    COUNT(DISTINCT customer_unique_id)      AS buyer_cnt,
    SUM(item_amount)                        AS goods_gmv,
    SUM(freight)                            AS freight_amount,
    SUM(item_amount_with_freight)           AS gmv
FROM dwd_order_detail
GROUP BY order_date;

python project/run_sql.py -c "SELECT COUNT(*) AS days, MIN(dt) AS min_dt, MAX(dt) AS max_dt FROM dws_trade_1d"

应该得到 614 天，范围 2016-09-04 ~ 2018-10-17。其中分析窗口内有 602 天，窗口日均买家 161.1，峰值 1,151（2017-11-24 黑五）。

2.2 月汇总

月表只保留完整窗口 2017-01 ~ 2018-08。窗口参数放在 project/sql/00_params.sql，run_sql.py 会自动加载：

CREATE OR REPLACE TABLE dws_trade_1m AS
SELECT
    CAST(date_trunc('month', order_date) AS DATE) AS month,
    COUNT(DISTINCT order_id)                      AS order_cnt,
    COUNT(*)                                      AS item_cnt,
    COUNT(DISTINCT customer_unique_id)            AS buyer_cnt,
    SUM(item_amount)                              AS goods_gmv,
    SUM(freight)                                  AS freight_amount,
    SUM(item_amount_with_freight)                 AS gmv
FROM dwd_order_detail
WHERE order_date >= analysis_start()
  AND order_date <  analysis_end()
GROUP BY 1;

自查：

SELECT COUNT(*) AS months, MIN(month) AS min_month,
       MAX(month) AS max_month, ROUND(SUM(gmv), 2) AS window_gmv
FROM dws_trade_1m;

结果：20 个月，2017-01-01 ~ 2018-08-01，窗口 GMV 15,688,396.64。

两个边界要记住：

• 2016-09 和 2018-09/10 不进入趋势；
• COUNT(DISTINCT customer_unique_id) 在月粒度会重复计数跨月买家，20 个月的买家数不能直接相加当总买家数。

3. 动手二：品类和卖家主题

3.1 品类月汇总

CREATE OR REPLACE TABLE dws_category_1m AS
SELECT
    CAST(date_trunc('month', order_date) AS DATE) AS month,
    category,
    COUNT(DISTINCT order_id)                      AS order_cnt,
    COUNT(DISTINCT seller_id)                     AS seller_cnt,
    COUNT(*)                                      AS item_cnt,
    SUM(item_amount)                              AS goods_gmv,
    SUM(item_amount_with_freight)                 AS gmv
FROM dwd_order_detail
WHERE order_date >= analysis_start()
  AND order_date <  analysis_end()
GROUP BY 1, 2;

自查：dws_category_1m 应 1,237 行、72 个品类。

3.2 卖家月汇总

卖家表同理，把 category 换成 seller_id, seller_state：

CREATE OR REPLACE TABLE dws_seller_1m AS
SELECT
    CAST(date_trunc('month', order_date) AS DATE) AS month,
    seller_id,
    seller_state,
    COUNT(DISTINCT order_id)                      AS order_cnt,
    COUNT(*)                                      AS item_cnt,
    SUM(item_amount)                              AS goods_gmv,
    SUM(item_amount_with_freight)                 AS gmv
FROM dwd_order_detail
WHERE order_date >= analysis_start()
  AND order_date <  analysis_end()
GROUP BY 1, 2, 3;

自查：dws_seller_1m 应 16,171 行，窗口内有 3,029 个卖家。

粒度提醒：品类表和卖家表都是“月 × 实体”。一个订单跨月、或者一个订单买多个品类，会在不同行重复出现。所以后面 ADS 汇总时，order_cnt 只能叫“订单月次数”，不能直接当去重订单数。

4. 动手三：客户 RFM

RFM 的三项定义：

• R（Recency）：最近一次下单距基准日的天数，越小越好；
• F（Frequency）：有效订单数；
• M（Monetary）：累计消费金额。

基准日取 全部有效订单的最大下单日 2018-09-03，不是系统当天。否则每次跑数 R 都会变，结果不可复现。RFM 覆盖全部 94,990 个买家；没有明细的 8 个有效订单金额按 0 计，涉及 7 个买家。

4.1 先聚合到客户粒度

CREATE OR REPLACE TABLE dws_customer_rfm AS
WITH per AS (
    SELECT customer_unique_id,
           COUNT(DISTINCT order_id)        AS order_cnt,
           COALESCE(SUM(order_amount), 0)  AS total_amount,
           MIN(order_date)                 AS first_order_date,
           MAX(order_date)                 AS last_order_date
    FROM dwd_order
    WHERE customer_unique_id IS NOT NULL
    GROUP BY customer_unique_id
),
anchor AS (
    SELECT MAX(order_date) AS anchor_date FROM dwd_order
)
SELECT p.*,
       date_diff('day', p.last_order_date, a.anchor_date) AS recency_days
FROM per p CROSS JOIN anchor a;

自查：94,990 行。如果少于这个数，检查是不是漏了金额为 NULL 的订单，或者用了 customer_id 而不是 customer_unique_id。

4.2 再补 R / F / M 得分和分层

F 分不能直接 NTILE(5)：Olist 有 97% 的买家只下过一单，大量并列值会让 NTILE 把“1 单”随机分到不同分数。项目改成显式阈值：

CASE
    WHEN order_cnt >= 10 THEN 5
    WHEN order_cnt >= 5  THEN 4
    WHEN order_cnt >= 3  THEN 3
    WHEN order_cnt = 2   THEN 2
    ELSE 1
END AS f_score

R 和 M 用 NTILE(5) 分桶，并在排序里加 customer_unique_id 作为并列时的稳定 tie-breaker，保证每次构建分桶结果一致。分层规则：

python project/run_sql.py project/sql/dws_customer_rfm.sql

4.3 校验分层规模

SELECT rfm_segment, COUNT(*) AS customer_cnt
FROM dws_customer_rfm
GROUP BY 1
ORDER BY 2 DESC;

没有“重要价值客户”，原因是 97% 的买家只买一次，严格按 F≥3 筛不出规模人群。这不是代码错，而是数据特征。

5. 动手四：履约与评价主题

CREATE OR REPLACE TABLE dws_service_1m AS
WITH base AS (
    SELECT *
    FROM dwd_order
    WHERE order_date >= analysis_start()
      AND order_date <  analysis_end()
)
SELECT
    CAST(date_trunc('month', order_date) AS DATE) AS month,
    COUNT(*)                                      AS valid_order_cnt,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL) AS delivered_order_cnt,
    COUNT(*) FILTER (
        WHERE delivered_at IS NOT NULL AND estimated_at IS NOT NULL
    )                                             AS late_base,
    COUNT(*) FILTER (WHERE is_late)               AS late_order_cnt,
    ROUND(
        COUNT(*) FILTER (WHERE is_late) * 100.0
        / NULLIF(COUNT(*) FILTER (
            WHERE delivered_at IS NOT NULL AND estimated_at IS NOT NULL
        ), 0), 2)                                 AS late_rate_pct,
    ROUND(AVG(delivery_days), 1)                  AS avg_delivery_days,
    MEDIAN(delivery_days)                         AS median_delivery_days,
    ROUND(AVG(review_score), 2)                   AS avg_review_score
FROM base
GROUP BY 1;

自查：

SELECT COUNT(*) AS months,
       SUM(late_order_cnt) AS late_orders,
       SUM(late_base)      AS late_base,
       ROUND(SUM(late_order_cnt) * 100.0 / SUM(late_base), 2) AS late_rate_pct
FROM dws_service_1m;

窗口内是 7,822 / 96,203 = 8.13%；总览口径（全部有效订单）是 7,826 / 96,470 = 8.11%。两个数都对，区别只是范围，报告里要写清楚用的是哪个。

6. 动手五：ADS 总览与月度趋势

ADS 的原则是“一表一个场景”，列名直接给报表用。先做最重要的两张。

6.1 核心指标总览

先把几块指标拼起来：明细金额、订单/买家、复购、履约、评价。

CREATE OR REPLACE TABLE ads_gmv_overview AS
SELECT
    (SELECT COUNT(*) FROM dwd_order)                          AS valid_orders,
    (SELECT COUNT(*) FROM dwd_order_detail)                   AS valid_item_rows,
    (SELECT COUNT(DISTINCT order_id) FROM dwd_order_detail)   AS orders_with_items,
    (SELECT SUM(item_amount_with_freight) FROM dwd_order_detail) AS gmv,
    (SELECT SUM(item_amount) FROM dwd_order_detail)           AS goods_gmv,
    (SELECT COUNT(DISTINCT customer_unique_id) FROM dwd_order) AS buyers,
    (SELECT COUNT(*) FILTER (WHERE is_late) FROM dwd_order)    AS late_order_cnt;

这是一个可运行的骨架，先把 7 个核心数对出来，再补 AOV、复购率、迟到率、评价均分。

python project/run_sql.py project/sql/ads_gmv_overview.sql

校验：

SELECT valid_orders, valid_item_rows, orders_with_items, gmv, goods_gmv,
       aov, buyers, repeat_buyer_cnt, repeat_rate_pct,
       late_base, late_order_cnt, late_rate_pct, avg_review_score
FROM ads_gmv_overview;

6.2 月度趋势与环比

CREATE OR REPLACE TABLE ads_month_trend AS
WITH base AS (
    SELECT month, order_cnt, item_cnt, buyer_cnt, gmv,
           ROUND(gmv / NULLIF(order_cnt, 0), 2) AS aov
    FROM dws_trade_1m
)
SELECT month, order_cnt, item_cnt, buyer_cnt, gmv, aov,
       LAG(gmv) OVER (ORDER BY month) AS last_month_gmv,
       ROUND(
           (gmv - LAG(gmv) OVER (ORDER BY month)) * 100.0
           / NULLIF(LAG(gmv) OVER (ORDER BY month), 0), 2
       ) AS mom_pct
FROM base
ORDER BY month;

校验：

SELECT COUNT(*) AS months, MAX(mom_pct) AS max_mom_pct
FROM ads_month_trend;

应该得到 20 行，最大环比 +107.06%（2017-02，因为 2017-01 是窗口起点，基数低）。2017-11 的 GMV 是 1,173,387.01，环比 +53.41%。

7. 动手六：其余 6 张 ADS

7.1 品类排行：窗口函数算累计占比

CREATE OR REPLACE TABLE ads_category_rank AS
WITH cat AS (
    SELECT category,
           SUM(order_cnt) AS order_month_cnt,
           SUM(item_cnt)  AS item_cnt,
           SUM(gmv)       AS gmv
    FROM dws_category_1m
    GROUP BY category
),
total AS (
    SELECT SUM(gmv) AS total_gmv FROM cat
),
ranked AS (
    SELECT c.*,
           ROW_NUMBER() OVER (ORDER BY c.gmv DESC, c.category) AS rk,
           ROUND(c.gmv * 100.0 / NULLIF(t.total_gmv, 0), 2) AS share_pct,
           ROUND(
               SUM(c.gmv) OVER (ORDER BY c.gmv DESC, c.category
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               * 100.0 / NULLIF(t.total_gmv, 0), 2
           ) AS cumulative_pct
    FROM cat c CROSS JOIN total t
)
SELECT category, order_month_cnt, item_cnt, gmv, share_pct, cumulative_pct, rk
FROM ranked
ORDER BY rk;

校验：

SELECT category, gmv, share_pct, cumulative_pct
FROM ads_category_rank
ORDER BY rk
LIMIT 10;

Top 10 累计占比 62.37%，不是典型的“二八分布”，长尾很明显。

7.2 其余 5 张：先写，再对答案

建议按下面的顺序自己写，每张都先想清楚“输入表是什么、粒度是什么、输出几行”：

1. ads_seller_rank：从 dws_seller_1m 按卖家汇总，ROW_NUMBER 排名，WHERE rk <= 50；
2. ads_customer_rfm：从 dws_customer_rfm 按分层汇总人数和金额，用 SUM(...) OVER () 算占比；
3. ads_repurchase：从 dwd_order 按 customer_unique_id 聚合订单数，算复购人数、复购率和复购 GMV 占比；
4. ads_delivery_sla：从 dws_service_1m 直接取月度履约字段；
5. ads_review_lateness：从 dwd_order 取有评分、有签收和预计时间的订单，按 is_late 分组算均分和低分率。

python project/run_sql.py project/sql/ads_seller_rank.sql
python project/run_sql.py project/sql/ads_customer_rfm.sql
python project/run_sql.py project/sql/ads_repurchase.sql
python project/run_sql.py project/sql/ads_delivery_sla.sql
python project/run_sql.py project/sql/ads_review_lateness.sql

7.3 一次校验 14 张表

SELECT 'dws_trade_1d' AS table_name, COUNT(*) AS row_cnt FROM dws_trade_1d
UNION ALL SELECT 'dws_trade_1m', COUNT(*) FROM dws_trade_1m
UNION ALL SELECT 'dws_category_1m', COUNT(*) FROM dws_category_1m
UNION ALL SELECT 'dws_seller_1m', COUNT(*) FROM dws_seller_1m
UNION ALL SELECT 'dws_customer_rfm', COUNT(*) FROM dws_customer_rfm
UNION ALL SELECT 'dws_service_1m', COUNT(*) FROM dws_service_1m
UNION ALL SELECT 'ads_gmv_overview', COUNT(*) FROM ads_gmv_overview
UNION ALL SELECT 'ads_month_trend', COUNT(*) FROM ads_month_trend
UNION ALL SELECT 'ads_category_rank', COUNT(*) FROM ads_category_rank
UNION ALL SELECT 'ads_seller_rank', COUNT(*) FROM ads_seller_rank
UNION ALL SELECT 'ads_customer_rfm', COUNT(*) FROM ads_customer_rfm
UNION ALL SELECT 'ads_repurchase', COUNT(*) FROM ads_repurchase
UNION ALL SELECT 'ads_delivery_sla', COUNT(*) FROM ads_delivery_sla
UNION ALL SELECT 'ads_review_lateness', COUNT(*) FROM ads_review_lateness
ORDER BY table_name;

对照第 0 节的行数表，14 个数字全对才算完成。

8. 核心结果

8.1 总览

8.2 月度趋势

• GMV 最高月份：2017-11，1,173,387.01，订单 7,421，AOV 158.12；
• 2017-11 环比增长 53.41%，与黑五促销吻合；
• 2018 年 3~5 月 GMV 稳定在 114~116 万；
• 2018-08 是窗口最后一个月，GMV 997,040.03，不要拿它和完整月份直接比。

8.3 品类

Top 5 品类（窗口内 GMV）：

Top 10 品类累计占比 62.37%。这不是典型的“二八分布”，长尾很明显。面试时不要硬说“20% 品类贡献 80%”，要按实际数据说。

8.4 卖家

• Top 10 卖家 GMV 合计占全平台 12.88%；
• 说明平台卖家集中度低，不依赖少数大卖家；
• ads_seller_rank 只保留 Top 50，避免结果表过大。

8.5 客户分层

没有“重要价值客户”，原因是 97% 的买家只买一次，严格按 F≥3 筛不出规模人群。这不是代码错，而是数据特征。RFM 在这种低复购业务里只能做粗分层，不能照搬零售会员体系。

8.6 履约与评价

• 迟到订单 7,826 / 96,470，迟到率 8.11%；
• 平均履约 12.5 天，中位 10.0 天；
• 月度迟到率最低 1.36%（2018-06），最高 21.36%（2018-03）；
• 迟到订单平均评分 2.57，准时订单 4.29；
• 迟到订单低分率（≤2 分）53.99%，准时订单 9.19%。

这组对比是项目里最有业务价值的发现：迟到会显著拉低评分。但它只是相关，不是因果；可能还有商品质量、卖家服务等混杂因素。

8.7 日活买家

• 日均买家 161.1（分析窗口内 602 个有下单日期的均值，不含无下单日期）；
• 峰值 1,151，出现在 2017-11-24（黑五）；
• 峰值当天 GMV 178,519.22。

日活买家按“当天下单的去重 customer_unique_id”计算，不是登录用户。Olist 没有行为日志，不能叫 DAU。

9. 指标 SQL 示例

9.1 GMV 与 AOV

SELECT
    SUM(item_amount_with_freight) AS gmv,
    COUNT(DISTINCT order_id)      AS order_cnt,
    ROUND(SUM(item_amount_with_freight) / COUNT(DISTINCT order_id), 2) AS aov
FROM dwd_order_detail;

9.2 月度环比

SELECT
    month,
    gmv,
    LAG(gmv) OVER (ORDER BY month) AS last_month_gmv,
    ROUND((gmv - LAG(gmv) OVER (ORDER BY month)) * 100.0
          / LAG(gmv) OVER (ORDER BY month), 2) AS mom_pct
FROM dws_trade_1m
ORDER BY month;

9.3 品类累计占比

WITH cat AS (
    SELECT category, SUM(gmv) AS gmv
    FROM dws_category_1m
    GROUP BY category
)
SELECT
    category,
    gmv,
    ROUND(gmv * 100.0 / SUM(gmv) OVER (), 2) AS share_pct,
    ROUND(SUM(gmv) OVER (ORDER BY gmv DESC
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
          * 100.0 / SUM(gmv) OVER (), 2) AS cumulative_pct
FROM cat
ORDER BY gmv DESC;

9.4 复购率

WITH per AS (
    SELECT customer_unique_id, COUNT(DISTINCT order_id) AS order_cnt
    FROM dwd_order
    GROUP BY customer_unique_id
)
SELECT
    COUNT(*) AS buyer_cnt,
    COUNT(*) FILTER (WHERE order_cnt >= 2) AS repeat_buyer_cnt,
    ROUND(COUNT(*) FILTER (WHERE order_cnt >= 2) * 100.0 / COUNT(*), 2) AS repeat_rate_pct
FROM per;

10. 本章验收

• 能说出 6 张 DWS 表各自什么粒度，为什么这样切。
• 14 张表的行数与第 0 节校验表一致。
• 能写出月度 GMV、环比、品类累计占比、复购率四条 SQL。
• 能解释为什么 F 分不用 NTILE。
• 能解释 Top 10 品类累计 62.37% 说明什么。
• 能说出迟到与评分的相关性，并且不把它说成因果。
• 能解释 RFM 在低复购业务里的局限。
• 能区分窗口口径 8.13% 与全量口径 8.11%，并说明原因。

11. 面试延伸

面试题：DWS 和 ADS 的区别是什么？

是什么：DWS 是按主题预聚合的中间层，ADS 是面向具体场景的结果层。

解决什么问题：DWS 让多个报表复用同一套聚合，减少重复计算；ADS 让前端和报表拿到即用结果，不需要再写复杂 SQL。DWS 追求“主题稳定”，ADS 追求“场景直接”。

我的实操边界：项目里 DWS 有 6 张主题表，ADS 有 8 张结果表；数据量小，DWS 的性能收益有限，主要价值是口径统一和减少重复 SQL。

面试题：为什么 Olist 的复购率只有 3%？

是什么：复购率按 customer_unique_id 统计，有效订单 ≥2 单的买家占 3.04%。

解决什么问题：Olist 是巴西平台，数据覆盖时间有限，很多用户只买一次；同时 customer_unique_id 能把同一人的多个收货地址合并，口径已经比较准确。低复购是业务事实，不是 SQL 写错。

我的实操边界：我对比过用 customer_id 和 customer_unique_id 的差异；用错 ID 会把同一个人拆成多个用户，复购率更低。项目里所有复购口径统一用 customer_unique_id。

面试题：迟到率和评分有什么关系？

是什么：迟到订单平均评分 2.57，准时订单 4.29；迟到订单低分率 53.99%，准时 9.19%。

解决什么问题：这组数据说明履约体验与评价强相关，可以作为运营抓手。但不能直接说“迟到导致低分”，因为可能存在商品质量、卖家服务等混杂因素，需要实验或回归控制变量。

我的实操边界：项目只做了描述性对比，没有做因果推断；面试时我会明确说是相关性。