跳到主要内容

数据建模实战

问题​

常见业务场景如何进行数据建模？

答案​

场景一：电商订单建模​

分层设计：

层次表粒度
DWDdwd.order_detail一行 = 一个订单明细
DWSdws.user_order_1d一行 = 一个用户一天的汇总
ADSads.daily_sales_report一行 = 一天的销售指标

场景二：SaaS 订阅建模​

SaaS 产品的核心是订阅生命周期：

-- 订阅事实表
CREATE TABLE dwd.subscription (
    subscription_id  BIGINT,
    tenant_id        BIGINT,     -- 租户（企业客户）
    plan_id          INT,        -- 套餐
    start_date       DATE,
    end_date         DATE,
    mrr              DECIMAL(18,2), -- 月经常性收入
    status           STRING      -- active/churned/upgraded
);

-- SaaS 关键指标宽表
CREATE TABLE dws.saas_metrics_monthly AS
SELECT
    month,
    COUNT(DISTINCT tenant_id) AS active_tenants,
    SUM(mrr) AS total_mrr,
    -- 新增 MRR
    SUM(CASE WHEN is_new = 1 THEN mrr ELSE 0 END) AS new_mrr,
    -- 流失 MRR
    SUM(CASE WHEN status = 'churned' THEN mrr ELSE 0 END) AS churned_mrr
FROM dwd.subscription
GROUP BY month;

场景三：用户行为建模​

-- 行为日志事实表（细粒度）
CREATE TABLE dwd.user_action (
    event_id     STRING,          -- 唯一事件 ID
    user_id      BIGINT,
    event_name   STRING,          -- page_view / click / purchase
    event_time   TIMESTAMP,
    page_url     STRING,
    properties   MAP<STRING, STRING>,  -- 事件属性（灵活扩展）
    dt           STRING           -- 日期分区
);

-- 用户行为汇总宽表
CREATE TABLE dws.user_behavior_1d AS
SELECT
    user_id,
    dt,
    COUNT(*) AS total_events,
    COUNT(CASE WHEN event_name = 'page_view' THEN 1 END) AS pv,
    COUNT(CASE WHEN event_name = 'click' THEN 1 END) AS clicks,
    COUNT(CASE WHEN event_name = 'purchase' THEN 1 END) AS purchases,
    MIN(event_time) AS first_event_time,
    MAX(event_time) AS last_event_time
FROM dwd.user_action
GROUP BY user_id, dt;
建模口诀
  1. 先定粒度：每行代表什么？
  2. 再选维度：从哪些角度分析？
  3. 最后定指标：需要哪些度量？

常见面试问题​

Q1: 如何确定事实表的粒度？​

答案：

  • 粒度 = 每行数据代表的业务含义
  • 原则：选最细的粒度（可以聚合，但不能拆分）
  • 例如订单表粒度是「一笔订单」，订单明细表是「一个商品项」

Q2: 维度变化了怎么办？​

答案：

  • 使用 缓慢变化维（SCD）
  • Type 1：直接覆盖（不保留历史）
  • Type 2：新增一行（保留历史，最常用）
  • Type 3：增加列（保留上一个版本）

Q3: 事实表有哪几种类型？​

答案：

  • 事务事实表：一行一笔交易（订单、支付）
  • 周期快照事实表：定期快照（每日库存、每月 MRR）
  • 累积快照事实表：跟踪整个生命周期（订单从创建到完成的各阶段时间）

相关链接​