从 ODS 到 Materialized View：一文讲透 OLAP 与数据仓库中的核心概念

CREATE TABLE orders (
    order_id      BIGINT PRIMARY KEY,
    user_id       BIGINT NOT NULL,
    region_id     INT NOT NULL,
    order_status  VARCHAR(32) NOT NULL,
    order_time    DATETIME NOT NULL,
    update_time   DATETIME NOT NULL,
    KEY idx_user_time (user_id, order_time)
);

CREATE TABLE order_items (
    order_id      BIGINT NOT NULL,
    product_id    BIGINT NOT NULL,
    quantity      INT NOT NULL,
    amount        DECIMAL(18, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
    product_id    BIGINT PRIMARY KEY,
    category_id   INT NOT NULL,
    product_name  VARCHAR(255) NOT NULL
);

SELECT
    DATE_FORMAT(o.order_time, '%Y-%m') AS month,
    o.region_id,
    p.category_id,
    SUM(i.amount) AS revenue,
    COUNT(DISTINCT o.user_id) AS buyers
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
JOIN products p ON i.product_id = p.product_id
WHERE o.order_status = 'PAID'
  AND o.order_time >= '2025-01-01'
  AND o.order_time <  '2026-01-01'
GROUP BY
    DATE_FORMAT(o.order_time, '%Y-%m'),
    o.region_id,
    p.category_id;

                       MySQL
                         |
       +-----------------+-----------------+
       |                                   |
  在线交易请求                         分析查询
  点查 / 小范围更新                    大范围扫描
  毫秒级事务                           多表 Join
  少量数据页                           GROUP BY
  延迟敏感                             COUNT DISTINCT
       |                                   |
       +----------- 争抢 CPU / IO / Buffer Pool

 MySQL / PostgreSQL       App Log        Kafka         SaaS API
        |                    |             |              |
        +--------------------+------+------+--------------+
                                  |
                       CDC / Batch ETL / Streaming
                                  |
                                  v
                         Data Warehouse / Lake
                                  |
                   +--------------+---------------+
                   |                              |
             Hive / Spark SQL             Doris / StarRocks
             离线加工与回溯                 实时 OLAP 查询
                   |                              |
                   +--------------+---------------+
                                  |
                         BI / Dashboard / API

              Object Storage / HDFS
         Parquet / ORC / Iceberg / Hudi
                       |
          +------------+------------+
          |                         |
   Hive / Spark SQL             Presto / Trino
   离线 ETL、回溯重算            联邦查询、即席分析
          |
          v
  Doris / StarRocks / ClickHouse
  Dashboard、API、实时分析服务

CREATE TABLE ods_orders (
    order_id       BIGINT,
    user_id        BIGINT,
    region_id      INT,
    order_status   STRING,
    order_time     TIMESTAMP,
    update_time    TIMESTAMP,
    op_type        STRING,
    ingest_time    TIMESTAMP
);

CREATE TABLE dwd_order_items (
    order_id       BIGINT,
    product_id     BIGINT,
    user_id        BIGINT,
    region_id      INT,
    order_time     TIMESTAMP,
    quantity       INT,
    amount         DECIMAL(18, 2)
);

CREATE TABLE dim_product (
    product_id      BIGINT,
    product_name    STRING,
    category_id     INT,
    category_name   STRING,
    brand_id        INT,
    brand_name      STRING
);

CREATE TABLE dws_sales_day_region_category AS
SELECT
    DATE(order_time) AS dt,
    region_id,
    category_id,
    SUM(amount) AS revenue,
    SUM(quantity) AS sold_quantity,
    COUNT(*) AS item_count
FROM dwd_order_items i
JOIN dim_product p ON i.product_id = p.product_id
GROUP BY DATE(order_time), region_id, category_id;

CREATE TABLE ads_region_monthly_sales AS
SELECT
    DATE_FORMAT(dt, 'yyyy-MM') AS month,
    region_id,
    SUM(revenue) AS revenue
FROM dws_sales_day_region_category
GROUP BY DATE_FORMAT(dt, 'yyyy-MM'), region_id;

fact_order_items
+----------+------------+---------+-----------+----------+--------+
| order_id | product_id | user_id | region_id | quantity | amount |
+----------+------------+---------+-----------+----------+--------+
| 10001    | 2001       | 501     | 10        | 2        | 199.00 |
+----------+------------+---------+-----------+----------+--------+

dim_product                           dim_region
+------------+-------------+         +-----------+-------------+
| product_id | category_id |         | region_id | province    |
+------------+-------------+         +-----------+-------------+
| 2001       | 301         |         | 10        | Zhejiang    |
+------------+-------------+         +-----------+-------------+

SELECT region_id, category_id, SUM(revenue)
FROM dws_sales_day_region_category
WHERE dt = '2026-05-01'
GROUP BY region_id, category_id;

SELECT dt, region_id, category_id, SUM(revenue)
FROM dws_sales_day_region_category
WHERE dt >= '2026-05-01'
  AND dt <  '2026-06-01'
  AND region_id IN (10, 11)
  AND category_id IN (301, 302)
GROUP BY dt, region_id, category_id;

年销售额 -> 月销售额 -> 日销售额
省销售额 -> 市销售额 -> 门店销售额
品类销售额 -> SKU 销售额

-- 从日粒度上卷到月粒度
SELECT
    DATE_FORMAT(dt, 'yyyy-MM') AS month,
    region_id,
    SUM(revenue) AS revenue
FROM dws_sales_day_region_category
GROUP BY DATE_FORMAT(dt, 'yyyy-MM'), region_id;

SELECT
    region_id,
    category_id,
    SUM(revenue)
FROM dws_sales_day_region_category
GROUP BY ROLLUP(region_id, category_id);

SELECT dt, region_id, SUM(amount)
FROM dwd_order_items
WHERE dt >= '2026-05-01'
GROUP BY dt, region_id;

Scan 明细数据
      |
      v
Filter 分区与谓词
      |
      v
Local Hash Aggregate
      |
      v
Network Shuffle by (dt, region_id)
      |
      v
Global Hash Aggregate
      |
      v
Result

CREATE TABLE sales_day_region AS
SELECT dt, region_id, SUM(amount) AS revenue
FROM dwd_order_items
GROUP BY dt, region_id;

CREATE TABLE dws_sales_day_region (
    dt             DATE,
    region_id      INT,
    revenue        DECIMAL(18, 2),
    sold_quantity  BIGINT
);

                        直接查询明细          查询预聚合结果
扫描行数                3,000,000,000         3,000
读取列数据              大量数据页             少量数据页
Hash Table 更新次数      十亿级                千级
Shuffle 数据量           大                    小
CPU Cache 局部性         较差                  较好

城市 -> 省份 -> 国家
日期 -> 月份 -> 年份
SKU  -> 品类 -> 全部商品

明细基表：order_items
(dt, region_id, category_id, product_id, user_id, amount)
             |
             | Rollup
             v
汇总索引：sales_day_region
(dt, region_id, sum(amount))

INSERT OVERWRITE dws_sales_day_region
SELECT dt, region_id, SUM(amount)
FROM dwd_order_items
GROUP BY dt, region_id;

SELECT dt, region_id, SUM(amount)
FROM order_items
GROUP BY dt, region_id;

CREATE VIEW v_sales_day_region AS
SELECT dt, region_id, SUM(amount) AS revenue
FROM dwd_order_items
GROUP BY dt, region_id;

普通 View = 保存查询文本
          != 保存查询结果

Materialized View
    |
    +-- 定义：SELECT dt, region_id, SUM(amount) ...
    |
    +-- 数据：已经计算好的结果

CREATE MATERIALIZED VIEW mv_sales_day_region_category AS
SELECT
    dt,
    region_id,
    category_id,
    SUM(amount) AS revenue
FROM dwd_order_items
GROUP BY dt, region_id, category_id;

SELECT
    DATE_TRUNC('month', dt) AS month,
    region_id,
    SUM(amount) AS revenue
FROM dwd_order_items
GROUP BY DATE_TRUNC('month', dt), region_id;

SELECT
    DATE_TRUNC('month', dt) AS month,
    region_id,
    SUM(revenue) AS revenue
FROM mv_sales_day_region_category
GROUP BY DATE_TRUNC('month', dt), region_id;

REFRESH MATERIALIZED VIEW mv_sales_day_region_category;

基表新增 2026-05-31 分区
             |
             v
只刷新 MV 的 2026-05-31 分区
             |
             v
历史分区无需重新计算

INSERT 基表
    |
    +--> 写入 Base Index
    |
    +--> 同一写入链路维护 Sync MV / Rollup

基表发生变化
    |
    v
等待刷新策略触发
    |
    v
重新计算受影响分区或完整结果
    |
    v
替换 MV 数据

CREATE TABLE sales_day_region (
    dt             DATE,
    region_id      INT,
    revenue        DECIMAL(18, 2) SUM,
    sold_quantity  BIGINT SUM
)
AGGREGATE KEY(dt, region_id)
DISTRIBUTED BY HASH(region_id) BUCKETS 16;

ALTER TABLE order_items
ADD ROLLUP rollup_day_region (
    dt,
    region_id,
    amount
);

CREATE MATERIALIZED VIEW mv_sales_day_region AS
SELECT
    dt,
    region_id,
    SUM(amount)
FROM order_items
GROUP BY dt, region_id;

CREATE MATERIALIZED VIEW mv_sales_day_category
BUILD IMMEDIATE
REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
AS
SELECT
    i.dt,
    p.category_id,
    SUM(i.amount) AS revenue
FROM dwd_order_items i
JOIN dim_product p ON i.product_id = p.product_id
GROUP BY i.dt, p.category_id;

CREATE MATERIALIZED VIEW mv_sales_day_region AS
SELECT
    dt,
    region_id,
    SUM(amount)
FROM order_items
GROUP BY dt, region_id;

EXPLAIN
SELECT dt, region_id, SUM(amount)
FROM order_items
GROUP BY dt, region_id;

0:OlapScanNode
   TABLE: order_items
   PREAGGREGATION: ON
   rollup: mv_sales_day_region

CREATE MATERIALIZED VIEW mv_sales_day_category
PARTITION BY dt
DISTRIBUTED BY HASH(category_id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS
SELECT
    i.dt,
    p.category_id,
    SUM(i.amount) AS revenue
FROM dwd_order_items i
JOIN dim_product p ON i.product_id = p.product_id
GROUP BY i.dt, p.category_id;

CREATE TABLE sales_day_region (
    dt Date,
    region_id UInt32,
    revenue SimpleAggregateFunction(sum, Decimal(18, 2))
)
ENGINE = AggregatingMergeTree
ORDER BY (dt, region_id);

CREATE MATERIALIZED VIEW mv_sales_day_region
TO sales_day_region
AS
SELECT
    toDate(order_time) AS dt,
    region_id,
    sum(amount) AS revenue
FROM order_items
GROUP BY dt, region_id;

ALTER TABLE order_items
ADD PROJECTION p_sales_day_region
(
    SELECT
        toDate(order_time) AS dt,
        region_id,
        sum(amount)
    GROUP BY dt, region_id
);

需要插入时转换、分流、写入独立结果表
    -> Incremental Materialized View

需要周期性执行复杂查询并保存快照
    -> Refreshable Materialized View

需要同一张表的备用排序或聚合布局，并由优化器透明选择
    -> Projection

Trino Coordinator
       |
       v
   生成分布式 Plan
       |
       +----------+----------+
       |          |          |
       v          v          v
    Worker      Worker      Worker
       |          |          |
       +----------+----------+
                  |
                  v
       S3 / HDFS 上的大量文件

CREATE MATERIALIZED VIEW iceberg.analytics.mv_sales_day_region
WITH (
    format = 'ORC',
    partitioning = ARRAY['dt']
)
AS
SELECT dt, region_id, SUM(amount) AS revenue
FROM iceberg.dwd.order_items
GROUP BY dt, region_id;

REFRESH MATERIALIZED VIEW iceberg.analytics.mv_sales_day_region;

候选 1：扫描明细基表
候选 2：扫描日粒度 MV，再聚合到月
候选 3：扫描月粒度 MV
候选 4：扫描包含额外维度的 MV，再做 Roll Up

SELECT
    DATE_TRUNC('minute', event_time) AS minute,
    service_name,
    status_code,
    COUNT(*) AS request_count,
    SUM(latency_ms) AS latency_sum
FROM request_logs
GROUP BY
    DATE_TRUNC('minute', event_time),
    service_name,
    status_code;

业务系统记录状态
        |
        v
ODS 忠实接住原始数据
        |
        v
DWD 整理原子事实，DIM 提供分析视角
        |
        v
DWS 沉淀公共汇总，ADS 服务具体应用
        |
        v
OLAP 在多个维度上 Slice、Dice、Drill Down、Roll Up
        |
        v
Aggregate Table / Rollup / MV 将重复计算提前完成
        |
        v
Query Rewrite 与 CBO 自动选择更低成本的执行计划