查询加速引擎：数据仓库中的聚合表与设计实践

查询加速引擎：数据仓库中的聚合表与设计实践

• • 1. 聚合表概述
  • • 1.1 什么是聚合表？
    • 1.2 聚合表 vs 事实表
  • 2. 聚合表的作用
  • • 2.1 核心作用概览
    • 2.2 详细作用说明
    • • 2.2.1 查询性能加速
      • 2.2.2 计算资源节省
      • 2.2.3 支持高并发分析
  • 3. 聚合表设计流程图
  • • 3.1 聚合表设计决策流程
    • 3.2 查询路由流程图
  • 4. 聚合表设计原则
  • • 4.1 维度选择原则
    • 4.2 指标设计原则
    • 4.3 粒度选择策略
  • 5. 聚合表类型
  • • 5.1 按粒度分类
    • 5.2 按业务主题分类
  • 6. 聚合表刷新策略
  • • 6.1 刷新策略对比
    • 6.2 增量刷新实现示例
    • 6.3 增量刷新流程图
  • 7. 聚合表管理最佳实践
  • • 7.1 聚合表设计检查清单
    • 7.2 常见问题与解决方案
    • 7.3 聚合表优化技巧
  • 8. 实战案例：电商平台聚合表设计
  • • 8.1 业务场景
    • 8.2 聚合表设计方案
    • 8.3 聚合策略与刷新频率
    • 8.4 查询路由实现
  • 9. 结语

在数据仓库的实际应用中，随着数据量的爆炸式增长和业务分析复杂度的提升，查询性能往往成为制约用户体验的关键瓶颈。聚合表作为一种经典的性能优化技术，通过预计算和预存储汇总数据，能够将复杂的聚合查询转化为简单的表扫描，从而实现查询性能的数量级提升。本文将深入剖析聚合表的概念、作用、设计方法以及最佳实践，帮助读者构建高性能的数据分析平台。

1. 聚合表概述

1.1 什么是聚合表？

聚合表（Aggregation Table）是指基于事实表，按照业务分析常用的维度组合，预先进行汇总计算并存储的结果表。它本质上是"用空间换时间"的性能优化策略，通过冗余存储预计算数据来加速查询响应。

核心思想：预计算常用聚合，避免实时计算开销。

1.2 聚合表 vs 事实表

2. 聚合表的作用

2.1 核心作用概览

#mermaid-svg-0O2Y2bxxd6zZge3O{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-0O2Y2bxxd6zZge3O .error-icon{fill:#552222;}#mermaid-svg-0O2Y2bxxd6zZge3O .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-0O2Y2bxxd6zZge3O .marker{fill:#333333;stroke:#333333;}#mermaid-svg-0O2Y2bxxd6zZge3O .marker.cross{stroke:#333333;}#mermaid-svg-0O2Y2bxxd6zZge3O svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-0O2Y2bxxd6zZge3O p{margin:0;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge{stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .section–1 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section–1 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section–1 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section–1 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section–1 path{fill:hsl(240, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section–1 text{fill:#ffffff;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon–1{font-size:40px;color:#ffffff;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge–1{stroke:hsl(240, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth–1{stroke-width:17;}#mermaid-svg-0O2Y2bxxd6zZge3O .section–1 line{stroke:hsl(60, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-0 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-0 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-0 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-0 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-0 path{fill:hsl(60, 100%, 73.5294117647%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-0 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-0{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-0{stroke:hsl(60, 100%, 73.5294117647%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-0{stroke-width:14;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-0 line{stroke:hsl(240, 100%, 83.5294117647%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-1 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-1 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-1 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-1 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-1 path{fill:hsl(80, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-1 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-1{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-1{stroke:hsl(80, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-1{stroke-width:11;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-1 line{stroke:hsl(260, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-2 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-2 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-2 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-2 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-2 path{fill:hsl(270, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-2 text{fill:#ffffff;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-2{font-size:40px;color:#ffffff;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-2{stroke:hsl(270, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-2{stroke-width:8;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-2 line{stroke:hsl(90, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-3 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-3 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-3 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-3 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-3 path{fill:hsl(300, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-3 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-3{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-3{stroke:hsl(300, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-3{stroke-width:5;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-3 line{stroke:hsl(120, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-4 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-4 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-4 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-4 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-4 path{fill:hsl(330, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-4 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-4{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-4{stroke:hsl(330, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-4{stroke-width:2;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-4 line{stroke:hsl(150, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-5 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-5 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-5 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-5 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-5 path{fill:hsl(0, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-5 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-5{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-5{stroke:hsl(0, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-5{stroke-width:-1;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-5 line{stroke:hsl(180, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-6 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-6 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-6 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-6 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-6 path{fill:hsl(30, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-6 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-6{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-6{stroke:hsl(30, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-6{stroke-width:-4;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-6 line{stroke:hsl(210, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-7 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-7 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-7 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-7 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-7 path{fill:hsl(90, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-7 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-7{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-7{stroke:hsl(90, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-7{stroke-width:-7;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-7 line{stroke:hsl(270, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-8 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-8 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-8 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-8 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-8 path{fill:hsl(150, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-8 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-8{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-8{stroke:hsl(150, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-8{stroke-width:-10;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-8 line{stroke:hsl(330, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-9 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-9 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-9 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-9 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-9 path{fill:hsl(180, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-9 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-9{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-9{stroke:hsl(180, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-9{stroke-width:-13;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-9 line{stroke:hsl(0, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-10 rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-10 path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-10 circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-10 polygon,#mermaid-svg-0O2Y2bxxd6zZge3O .section-10 path{fill:hsl(210, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-10 text{fill:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .node-icon-10{font-size:40px;color:black;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-edge-10{stroke:hsl(210, 100%, 76.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .edge-depth-10{stroke-width:-16;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-10 line{stroke:hsl(30, 100%, 86.2745098039%);stroke-width:3;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled circle,#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:lightgray;}#mermaid-svg-0O2Y2bxxd6zZge3O .disabled text{fill:#efefef;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-root rect,#mermaid-svg-0O2Y2bxxd6zZge3O .section-root path,#mermaid-svg-0O2Y2bxxd6zZge3O .section-root circle,#mermaid-svg-0O2Y2bxxd6zZge3O .section-root polygon{fill:hsl(240, 100%, 46.2745098039%);}#mermaid-svg-0O2Y2bxxd6zZge3O .section-root text{fill:#ffffff;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-root span{color:#ffffff;}#mermaid-svg-0O2Y2bxxd6zZge3O .section-2 span{color:#ffffff;}#mermaid-svg-0O2Y2bxxd6zZge3O .icon-container{height:100%;display:flex;justify-content:center;align-items:center;}#mermaid-svg-0O2Y2bxxd6zZge3O .edge{fill:none;}#mermaid-svg-0O2Y2bxxd6zZge3O .mindmap-node-label{dy:1em;alignment-baseline:middle;text-anchor:middle;dominant-baseline:middle;text-align:center;}#mermaid-svg-0O2Y2bxxd6zZge3O :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

root（聚合表的作用）

性能提升

查询响应时间从分钟级降至秒级

减少CPU/内存计算消耗

支持高并发查询

成本优化

降低计算资源消耗

减少重复计算开销

提升资源利用率

简化开发

封装复杂聚合逻辑

提供标准化数据接口

降低查询复杂度

数据治理

统一指标口径

规范维度组合

保障数据一致性

2.2 详细作用说明

2.2.1 查询性能加速

场景对比：

2.2.2 计算资源节省

# 无聚合表：每次查询都需要全量计算
SELECT
DATE(order_date) as sale_date,
product_category,
SUM(amount) as total_amount,
COUNT(DISTINCT customer_id) as unique_customers
FROM fact_orders –– 10亿行
WHERE order_date >= '2024-01-01'
GROUP BY DATE(order_date), product_category;

# 有聚合表：直接读取预计算结果
SELECT
sale_date,
product_category,
total_amount,
unique_customers
FROM agg_daily_sales –– 365行 × 品类数
WHERE sale_date >= '2024-01-01';

2.2.3 支持高并发分析

聚合表通过大幅减少查询所需扫描的数据量，使得单张聚合表可以支持数百甚至数千的并发查询，满足BI看板、数据产品等高频访问场景。

3. 聚合表设计流程图

3.1 聚合表设计决策流程

#mermaid-svg-S6ICpYm5EeGIYYwm{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-S6ICpYm5EeGIYYwm .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-S6ICpYm5EeGIYYwm .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-S6ICpYm5EeGIYYwm .error-icon{fill:#552222;}#mermaid-svg-S6ICpYm5EeGIYYwm .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-S6ICpYm5EeGIYYwm .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-S6ICpYm5EeGIYYwm .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-S6ICpYm5EeGIYYwm .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-S6ICpYm5EeGIYYwm .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-S6ICpYm5EeGIYYwm .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-S6ICpYm5EeGIYYwm .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-S6ICpYm5EeGIYYwm .marker{fill:#333333;stroke:#333333;}#mermaid-svg-S6ICpYm5EeGIYYwm .marker.cross{stroke:#333333;}#mermaid-svg-S6ICpYm5EeGIYYwm svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-S6ICpYm5EeGIYYwm p{margin:0;}#mermaid-svg-S6ICpYm5EeGIYYwm .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-S6ICpYm5EeGIYYwm .cluster-label text{fill:#333;}#mermaid-svg-S6ICpYm5EeGIYYwm .cluster-label span{color:#333;}#mermaid-svg-S6ICpYm5EeGIYYwm .cluster-label span p{background-color:transparent;}#mermaid-svg-S6ICpYm5EeGIYYwm .label text,#mermaid-svg-S6ICpYm5EeGIYYwm span{fill:#333;color:#333;}#mermaid-svg-S6ICpYm5EeGIYYwm .node rect,#mermaid-svg-S6ICpYm5EeGIYYwm .node circle,#mermaid-svg-S6ICpYm5EeGIYYwm .node ellipse,#mermaid-svg-S6ICpYm5EeGIYYwm .node polygon,#mermaid-svg-S6ICpYm5EeGIYYwm .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-S6ICpYm5EeGIYYwm .rough-node .label text,#mermaid-svg-S6ICpYm5EeGIYYwm .node .label text,#mermaid-svg-S6ICpYm5EeGIYYwm .image-shape .label,#mermaid-svg-S6ICpYm5EeGIYYwm .icon-shape .label{text-anchor:middle;}#mermaid-svg-S6ICpYm5EeGIYYwm .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-S6ICpYm5EeGIYYwm .rough-node .label,#mermaid-svg-S6ICpYm5EeGIYYwm .node .label,#mermaid-svg-S6ICpYm5EeGIYYwm .image-shape .label,#mermaid-svg-S6ICpYm5EeGIYYwm .icon-shape .label{text-align:center;}#mermaid-svg-S6ICpYm5EeGIYYwm .node.clickable{cursor:pointer;}#mermaid-svg-S6ICpYm5EeGIYYwm .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-S6ICpYm5EeGIYYwm .arrowheadPath{fill:#333333;}#mermaid-svg-S6ICpYm5EeGIYYwm .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-S6ICpYm5EeGIYYwm .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-S6ICpYm5EeGIYYwm .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-S6ICpYm5EeGIYYwm .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-S6ICpYm5EeGIYYwm .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-S6ICpYm5EeGIYYwm .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-S6ICpYm5EeGIYYwm .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-S6ICpYm5EeGIYYwm .cluster text{fill:#333;}#mermaid-svg-S6ICpYm5EeGIYYwm .cluster span{color:#333;}#mermaid-svg-S6ICpYm5EeGIYYwm div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-S6ICpYm5EeGIYYwm .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-S6ICpYm5EeGIYYwm rect.text{fill:none;stroke-width:0;}#mermaid-svg-S6ICpYm5EeGIYYwm .icon-shape,#mermaid-svg-S6ICpYm5EeGIYYwm .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-S6ICpYm5EeGIYYwm .icon-shape p,#mermaid-svg-S6ICpYm5EeGIYYwm .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-S6ICpYm5EeGIYYwm .icon-shape .label rect,#mermaid-svg-S6ICpYm5EeGIYYwm .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-S6ICpYm5EeGIYYwm .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-S6ICpYm5EeGIYYwm .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-S6ICpYm5EeGIYYwm :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

否

是

事实表<1000万行

事实表>1000万行

查询扫描量减少>90%

收益不明显

开始聚合表设计

分析查询模式

统计高频查询

识别常用维度

确定聚合粒度

查询频次分析

查询频次高?

暂不建聚合表

数据量评估

优化索引即可

聚合收益评估

设计聚合表

考虑其他优化

确定聚合粒度

每日聚合

每周聚合

每月聚合

每年聚合

设计聚合指标

SUM/COUNT/AVG/MIN/MAX

COUNT DISTINCT

复杂计算表达式

创建聚合表

建立刷新策略

实时刷新

定时批处理

监控与优化

3.2 查询路由流程图

#mermaid-svg-3G7ztqQsBVKrPBCo{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-3G7ztqQsBVKrPBCo .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-3G7ztqQsBVKrPBCo .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-3G7ztqQsBVKrPBCo .error-icon{fill:#552222;}#mermaid-svg-3G7ztqQsBVKrPBCo .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-3G7ztqQsBVKrPBCo .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-3G7ztqQsBVKrPBCo .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-3G7ztqQsBVKrPBCo .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-3G7ztqQsBVKrPBCo .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-3G7ztqQsBVKrPBCo .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-3G7ztqQsBVKrPBCo .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-3G7ztqQsBVKrPBCo .marker{fill:#333333;stroke:#333333;}#mermaid-svg-3G7ztqQsBVKrPBCo .marker.cross{stroke:#333333;}#mermaid-svg-3G7ztqQsBVKrPBCo svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-3G7ztqQsBVKrPBCo p{margin:0;}#mermaid-svg-3G7ztqQsBVKrPBCo .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-3G7ztqQsBVKrPBCo .cluster-label text{fill:#333;}#mermaid-svg-3G7ztqQsBVKrPBCo .cluster-label span{color:#333;}#mermaid-svg-3G7ztqQsBVKrPBCo .cluster-label span p{background-color:transparent;}#mermaid-svg-3G7ztqQsBVKrPBCo .label text,#mermaid-svg-3G7ztqQsBVKrPBCo span{fill:#333;color:#333;}#mermaid-svg-3G7ztqQsBVKrPBCo .node rect,#mermaid-svg-3G7ztqQsBVKrPBCo .node circle,#mermaid-svg-3G7ztqQsBVKrPBCo .node ellipse,#mermaid-svg-3G7ztqQsBVKrPBCo .node polygon,#mermaid-svg-3G7ztqQsBVKrPBCo .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-3G7ztqQsBVKrPBCo .rough-node .label text,#mermaid-svg-3G7ztqQsBVKrPBCo .node .label text,#mermaid-svg-3G7ztqQsBVKrPBCo .image-shape .label,#mermaid-svg-3G7ztqQsBVKrPBCo .icon-shape .label{text-anchor:middle;}#mermaid-svg-3G7ztqQsBVKrPBCo .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-3G7ztqQsBVKrPBCo .rough-node .label,#mermaid-svg-3G7ztqQsBVKrPBCo .node .label,#mermaid-svg-3G7ztqQsBVKrPBCo .image-shape .label,#mermaid-svg-3G7ztqQsBVKrPBCo .icon-shape .label{text-align:center;}#mermaid-svg-3G7ztqQsBVKrPBCo .node.clickable{cursor:pointer;}#mermaid-svg-3G7ztqQsBVKrPBCo .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-3G7ztqQsBVKrPBCo .arrowheadPath{fill:#333333;}#mermaid-svg-3G7ztqQsBVKrPBCo .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-3G7ztqQsBVKrPBCo .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-3G7ztqQsBVKrPBCo .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-3G7ztqQsBVKrPBCo .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-3G7ztqQsBVKrPBCo .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-3G7ztqQsBVKrPBCo .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-3G7ztqQsBVKrPBCo .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-3G7ztqQsBVKrPBCo .cluster text{fill:#333;}#mermaid-svg-3G7ztqQsBVKrPBCo .cluster span{color:#333;}#mermaid-svg-3G7ztqQsBVKrPBCo div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-3G7ztqQsBVKrPBCo .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-3G7ztqQsBVKrPBCo rect.text{fill:none;stroke-width:0;}#mermaid-svg-3G7ztqQsBVKrPBCo .icon-shape,#mermaid-svg-3G7ztqQsBVKrPBCo .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-3G7ztqQsBVKrPBCo .icon-shape p,#mermaid-svg-3G7ztqQsBVKrPBCo .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-3G7ztqQsBVKrPBCo .icon-shape .label rect,#mermaid-svg-3G7ztqQsBVKrPBCo .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-3G7ztqQsBVKrPBCo .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-3G7ztqQsBVKrPBCo .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-3G7ztqQsBVKrPBCo :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

明细存储

聚合表存储

年粒度

月粒度

日粒度

小时粒度

明细粒度

用户查询请求

查询路由层

分析查询粒度

年聚合表

月聚合表

日聚合表

小时聚合表

事实表

返回结果

4. 聚合表设计原则

4.1 维度选择原则

4.2 指标设计原则

— 聚合表指标设计示例
CREATE TABLE agg_daily_sales (
— 维度字段
sale_date DATE NOT NULL,
product_category VARCHAR(50),
region_id INT,
channel_type VARCHAR(20),

— 可加性指标（可直接SUM）
total_amount DECIMAL(15,2),
total_quantity INT,
total_discount DECIMAL(15,2),

— 半可加性指标（部分维度可SUM）
max_daily_amount DECIMAL(15,2), — 取最大值
min_daily_amount DECIMAL(15,2), — 取最小值

— 不可加性指标（需特殊处理）
avg_order_value DECIMAL(10,2), — 平均值
unique_customers INT, — COUNT DISTINCT

— 派生指标
total_amount_usd DECIMAL(15,2), — 汇率转换

PRIMARY KEY (sale_date, product_category, region_id, channel_type)
);

4.3 粒度选择策略

#mermaid-svg-6PmiQ9oTtpa73CKd{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-6PmiQ9oTtpa73CKd .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-6PmiQ9oTtpa73CKd .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-6PmiQ9oTtpa73CKd .error-icon{fill:#552222;}#mermaid-svg-6PmiQ9oTtpa73CKd .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-6PmiQ9oTtpa73CKd .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-6PmiQ9oTtpa73CKd .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-6PmiQ9oTtpa73CKd .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-6PmiQ9oTtpa73CKd .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-6PmiQ9oTtpa73CKd .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-6PmiQ9oTtpa73CKd .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-6PmiQ9oTtpa73CKd .marker{fill:#333333;stroke:#333333;}#mermaid-svg-6PmiQ9oTtpa73CKd .marker.cross{stroke:#333333;}#mermaid-svg-6PmiQ9oTtpa73CKd svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-6PmiQ9oTtpa73CKd p{margin:0;}#mermaid-svg-6PmiQ9oTtpa73CKd .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-6PmiQ9oTtpa73CKd .cluster-label text{fill:#333;}#mermaid-svg-6PmiQ9oTtpa73CKd .cluster-label span{color:#333;}#mermaid-svg-6PmiQ9oTtpa73CKd .cluster-label span p{background-color:transparent;}#mermaid-svg-6PmiQ9oTtpa73CKd .label text,#mermaid-svg-6PmiQ9oTtpa73CKd span{fill:#333;color:#333;}#mermaid-svg-6PmiQ9oTtpa73CKd .node rect,#mermaid-svg-6PmiQ9oTtpa73CKd .node circle,#mermaid-svg-6PmiQ9oTtpa73CKd .node ellipse,#mermaid-svg-6PmiQ9oTtpa73CKd .node polygon,#mermaid-svg-6PmiQ9oTtpa73CKd .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-6PmiQ9oTtpa73CKd .rough-node .label text,#mermaid-svg-6PmiQ9oTtpa73CKd .node .label text,#mermaid-svg-6PmiQ9oTtpa73CKd .image-shape .label,#mermaid-svg-6PmiQ9oTtpa73CKd .icon-shape .label{text-anchor:middle;}#mermaid-svg-6PmiQ9oTtpa73CKd .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-6PmiQ9oTtpa73CKd .rough-node .label,#mermaid-svg-6PmiQ9oTtpa73CKd .node .label,#mermaid-svg-6PmiQ9oTtpa73CKd .image-shape .label,#mermaid-svg-6PmiQ9oTtpa73CKd .icon-shape .label{text-align:center;}#mermaid-svg-6PmiQ9oTtpa73CKd .node.clickable{cursor:pointer;}#mermaid-svg-6PmiQ9oTtpa73CKd .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-6PmiQ9oTtpa73CKd .arrowheadPath{fill:#333333;}#mermaid-svg-6PmiQ9oTtpa73CKd .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-6PmiQ9oTtpa73CKd .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-6PmiQ9oTtpa73CKd .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-6PmiQ9oTtpa73CKd .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-6PmiQ9oTtpa73CKd .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-6PmiQ9oTtpa73CKd .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-6PmiQ9oTtpa73CKd .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-6PmiQ9oTtpa73CKd .cluster text{fill:#333;}#mermaid-svg-6PmiQ9oTtpa73CKd .cluster span{color:#333;}#mermaid-svg-6PmiQ9oTtpa73CKd div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-6PmiQ9oTtpa73CKd .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-6PmiQ9oTtpa73CKd rect.text{fill:none;stroke-width:0;}#mermaid-svg-6PmiQ9oTtpa73CKd .icon-shape,#mermaid-svg-6PmiQ9oTtpa73CKd .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-6PmiQ9oTtpa73CKd .icon-shape p,#mermaid-svg-6PmiQ9oTtpa73CKd .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-6PmiQ9oTtpa73CKd .icon-shape .label rect,#mermaid-svg-6PmiQ9oTtpa73CKd .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-6PmiQ9oTtpa73CKd .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-6PmiQ9oTtpa73CKd .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-6PmiQ9oTtpa73CKd :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

使用场景

粒度金字塔

年粒度数据量: 百级

季粒度数据量: 千级

月粒度数据量: 万级

周粒度数据量: 十万级

日粒度数据量: 百万级

小时粒度数据量: 亿级

战略报表

季度复盘

月度KPI

周报

日常运营

实时监控

5. 聚合表类型

5.1 按粒度分类

5.2 按业务主题分类

— 销售主题聚合表
CREATE TABLE agg_daily_sales_by_region (
sale_date DATE,
region_id INT,
total_amount DECIMAL(15,2),
order_count INT,
customer_count INT
);

— 用户主题聚合表
CREATE TABLE agg_daily_user_activity (
activity_date DATE,
user_type VARCHAR(20),
new_users INT,
active_users INT,
retention_rate DECIMAL(5,2)
);

— 产品主题聚合表
CREATE TABLE agg_monthly_product_performance (
month_date DATE,
product_id INT,
sales_amount DECIMAL(15,2),
sales_quantity INT,
refund_amount DECIMAL(15,2),
refund_rate DECIMAL(5,2)
);

6. 聚合表刷新策略

6.1 刷新策略对比

6.2 增量刷新实现示例

— 每日增量刷新日聚合表
— Step 1: 计算当日聚合数据
INSERT INTO agg_daily_sales (
sale_date, product_category, region_id,
total_amount, order_count, customer_count
)
SELECT
DATE(order_date) as sale_date,
product_category,
region_id,
SUM(amount) as total_amount,
COUNT(DISTINCT order_id) as order_count,
COUNT(DISTINCT customer_id) as customer_count
FROM fact_orders
WHERE DATE(order_date) = CURRENT_DATE – INTERVAL 1 DAY
GROUP BY DATE(order_date), product_category, region_id;

— Step 2: 更新月聚合表（从日聚合表汇总）
INSERT INTO agg_monthly_sales (
sale_month, product_category, region_id,
total_amount, order_count, customer_count
)
SELECT
DATE_FORMAT(sale_date, '%Y-%m-01') as sale_month,
product_category,
region_id,
SUM(total_amount) as total_amount,
SUM(order_count) as order_count,
COUNT(DISTINCT customer_count) — 注意：COUNT DISTINCT需要特殊处理
FROM agg_daily_sales
WHERE sale_date >= DATE_FORMAT(CURRENT_DATE – INTERVAL 1 MONTH, '%Y-%m-01')
AND sale_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
GROUP BY DATE_FORMAT(sale_date, '%Y-%m-01'), product_category, region_id;

6.3 增量刷新流程图

#mermaid-svg-MlUYyGqIsUr89V9m{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-MlUYyGqIsUr89V9m .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-MlUYyGqIsUr89V9m .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-MlUYyGqIsUr89V9m .error-icon{fill:#552222;}#mermaid-svg-MlUYyGqIsUr89V9m .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-MlUYyGqIsUr89V9m .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-MlUYyGqIsUr89V9m .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-MlUYyGqIsUr89V9m .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-MlUYyGqIsUr89V9m .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-MlUYyGqIsUr89V9m .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-MlUYyGqIsUr89V9m .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-MlUYyGqIsUr89V9m .marker{fill:#333333;stroke:#333333;}#mermaid-svg-MlUYyGqIsUr89V9m .marker.cross{stroke:#333333;}#mermaid-svg-MlUYyGqIsUr89V9m svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-MlUYyGqIsUr89V9m p{margin:0;}#mermaid-svg-MlUYyGqIsUr89V9m .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-MlUYyGqIsUr89V9m .cluster-label text{fill:#333;}#mermaid-svg-MlUYyGqIsUr89V9m .cluster-label span{color:#333;}#mermaid-svg-MlUYyGqIsUr89V9m .cluster-label span p{background-color:transparent;}#mermaid-svg-MlUYyGqIsUr89V9m .label text,#mermaid-svg-MlUYyGqIsUr89V9m span{fill:#333;color:#333;}#mermaid-svg-MlUYyGqIsUr89V9m .node rect,#mermaid-svg-MlUYyGqIsUr89V9m .node circle,#mermaid-svg-MlUYyGqIsUr89V9m .node ellipse,#mermaid-svg-MlUYyGqIsUr89V9m .node polygon,#mermaid-svg-MlUYyGqIsUr89V9m .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-MlUYyGqIsUr89V9m .rough-node .label text,#mermaid-svg-MlUYyGqIsUr89V9m .node .label text,#mermaid-svg-MlUYyGqIsUr89V9m .image-shape .label,#mermaid-svg-MlUYyGqIsUr89V9m .icon-shape .label{text-anchor:middle;}#mermaid-svg-MlUYyGqIsUr89V9m .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-MlUYyGqIsUr89V9m .rough-node .label,#mermaid-svg-MlUYyGqIsUr89V9m .node .label,#mermaid-svg-MlUYyGqIsUr89V9m .image-shape .label,#mermaid-svg-MlUYyGqIsUr89V9m .icon-shape .label{text-align:center;}#mermaid-svg-MlUYyGqIsUr89V9m .node.clickable{cursor:pointer;}#mermaid-svg-MlUYyGqIsUr89V9m .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-MlUYyGqIsUr89V9m .arrowheadPath{fill:#333333;}#mermaid-svg-MlUYyGqIsUr89V9m .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-MlUYyGqIsUr89V9m .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-MlUYyGqIsUr89V9m .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-MlUYyGqIsUr89V9m .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-MlUYyGqIsUr89V9m .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-MlUYyGqIsUr89V9m .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-MlUYyGqIsUr89V9m .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-MlUYyGqIsUr89V9m .cluster text{fill:#333;}#mermaid-svg-MlUYyGqIsUr89V9m .cluster span{color:#333;}#mermaid-svg-MlUYyGqIsUr89V9m div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-MlUYyGqIsUr89V9m .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-MlUYyGqIsUr89V9m rect.text{fill:none;stroke-width:0;}#mermaid-svg-MlUYyGqIsUr89V9m .icon-shape,#mermaid-svg-MlUYyGqIsUr89V9m .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-MlUYyGqIsUr89V9m .icon-shape p,#mermaid-svg-MlUYyGqIsUr89V9m .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-MlUYyGqIsUr89V9m .icon-shape .label rect,#mermaid-svg-MlUYyGqIsUr89V9m .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-MlUYyGqIsUr89V9m .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-MlUYyGqIsUr89V9m .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-MlUYyGqIsUr89V9m :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

是

否

是

否

是

否

新数据到达

数据分区

当日数据

计算日聚合

写入日聚合表

是否需要更新周聚合?

从日聚合表计算周聚合

结束

写入周聚合表

是否需要更新月聚合?

从日聚合表计算月聚合

写入月聚合表

是否需要更新年聚合?

从月聚合表计算年聚合

写入年聚合表

7. 聚合表管理最佳实践

7.1 聚合表设计检查清单

## 需求分析
□ 识别了TOP 10高频查询？
□ 分析了查询的维度组合？
□ 确定了聚合的粒度层级？
□ 评估了聚合表的预期数据量？

## 技术设计
□ 选择了合适的聚合字段（可加/半可加/不可加）？
□ 设计了合理的分区策略？
□ 确定了索引方案？
□ 规划了刷新策略（全量/增量）？

## 性能评估
□ 预估了查询性能提升倍数？
□ 评估了存储成本增量？
□ 测试了刷新任务耗时？
□ 验证了数据一致性？

## 运维保障
□ 建立了数据质量监控？
□ 配置了刷新任务告警？
□ 制定了数据生命周期管理？
□ 文档化了聚合表口径？

7.2 常见问题与解决方案

7.3 聚合表优化技巧

— 技巧1：使用分区表提升管理效率
CREATE TABLE agg_daily_sales (
sale_date DATE,
product_category VARCHAR(50),
total_amount DECIMAL(15,2)
)
PARTITION BY RANGE (sale_date) (
PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
PARTITION p202402 VALUES LESS THAN ('2024-03-01')
);

— 技巧2：使用列式存储压缩数据（ClickHouse/Hive）
CREATE TABLE agg_daily_sales (
sale_date Date,
product_category String,
total_amount Decimal(15,2)
)
ENGINE = MergeTree()
ORDER BY (sale_date, product_category)
SETTINGS min_rows_for_wide_part = 1000000;

— 技巧3：建立物化视图（PostgreSQL）
CREATE MATERIALIZED VIEW agg_daily_sales_mv AS
SELECT
DATE(order_date) as sale_date,
product_category,
SUM(amount) as total_amount
FROM fact_orders
GROUP BY DATE(order_date), product_category
WITH DATA;

REFRESH MATERIALIZED VIEW CONCURRENTLY agg_daily_sales_mv;

8. 实战案例：电商平台聚合表设计

8.1 业务场景

• 事实表：订单事实表（fact_orders），日均1000万行，年增长36亿行
• 核心查询：
  • 每日销售看板（按日期、品类、渠道）
  • 月度经营报表（按月份、区域、品类）
  • 用户行为分析（按日期、用户等级）
  • 实时大屏（按小时、品类）

8.2 聚合表设计方案

— 1. 小时级聚合表（实时大屏）
CREATE TABLE agg_hourly_sales (
sale_hour DATETIME,
product_category VARCHAR(50),
channel_type VARCHAR(20),
order_count INT,
total_amount DECIMAL(15,2),
unique_users INT
) PARTITION BY RANGE (sale_hour);

— 2. 日级聚合表（日常运营）
CREATE TABLE agg_daily_sales (
sale_date DATE,
product_category VARCHAR(50),
region_id INT,
channel_type VARCHAR(20),
user_level VARCHAR(20),
order_count INT,
total_amount DECIMAL(15,2),
discount_amount DECIMAL(15,2),
refund_amount DECIMAL(15,2),
unique_users INT,
avg_order_value DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

— 3. 月级聚合表（月度报表）
CREATE TABLE agg_monthly_sales (
sale_month DATE,
product_category VARCHAR(50),
region_id INT,
channel_type VARCHAR(20),
order_count INT,
total_amount DECIMAL(15,2),
refund_amount DECIMAL(15,2),
unique_users INT
);

8.3 聚合策略与刷新频率

8.4 查询路由实现

# 查询路由伪代码
def route_query(date_range, dimensions):
days = (date_range.end – date_range.start).days

if days == 1:
# 单日查询：使用日聚合表
return query_daily_agg(date_range, dimensions)
elif days <= 31:
# 近30天查询：使用日聚合表聚合
return query_daily_agg_with_rollup(date_range, dimensions)
elif days <= 365:
# 近1年查询：使用月聚合表
return query_monthly_agg(date_range, dimensions)
else:
# 历史查询：使用年聚合表或事实表
return query_yearly_agg_or_fact(date_range, dimensions)

9. 结语

聚合表是数据仓库性能优化的核心武器，它通过预计算和预存储，将复杂的聚合查询转化为简单的表扫描，实现了查询性能的质变。在实际应用中，聚合表的设计需要平衡以下几个维度：

• 性能 vs 存储：更多的聚合表带来更好的性能，但也会增加存储成本
• 实时性 vs 准确性：增量刷新提升时效，但需要处理复杂的数据一致性
• 灵活性 vs 维护性：细粒度聚合支持更多分析场景，但维护成本更高

核心要点回顾：

掌握聚合表的设计与优化，将帮助数据仓库团队从容应对日益增长的数据量和分析需求，为业务提供稳定、高效的数据服务。