DWD/DWS/ADS 物化视图层详细设计

运维

DWD/DWS/ADS 物化视图层详细设计

> 最后更新: 2026-06-07

> 版本: v0.2（根据迁移指南补充21个物化视图设计）

> 参考: 瑞美云数据分析体系迁移指南（21个视图设计）

---

设计原则

  • 分层清晰：DWD（明细清洗）→ DWS（岗位汇总）→ KPI（管理指标）→ APP（查询接口）
  • 自然日聚合：同一客户同一天到店 = 1次（DWD层处理）
  • 岗位分离：咨询师/医生/护士/手艺人各自独立物化视图
  • 业绩三指标分离：收款/开单/划扣 分别统计
  • 唯一索引必须建：每个物化视图必须建 UNIQUE INDEX，否则 CONCURRENTLY 刷新会失败

---

完整物化视图清单（12个）

| 层 | 物化视图 | 用途 | 大? | 覆盖分析场景 |

|----|---------|------|-----|------------|

| DWD | m_dwd_customer_visit | 客户到院归一 | 11MB | 到院分析、到院频次 |

| DWD | m_dwd_customer_consultant | 客户归属信息 | 8.5MB | 客户归属、有效咨询师 |

| DWS | m_dws_daily_kpi | 每日门店KPI | 472KB | 日报、门店每日业绩 |

| DWS | m_dws_consultant_monthly | 咨询师月绩效 | 80KB | 咨询师排名、绩效 |

| DWS | m_dws_doctor_monthly | 医生月度指标 | 304KB | 医生治疗统计 |

| DWS | m_dws_recall_performance | 回访考核 | 72KB | 回访完成率、覆盖率、超期 |

| DWS | m_dws_nurse_monthly 🆕 | 护士月绩效 | 72KB | 医美护士操作统计 |

| DWS | m_dws_craftsman_monthly 🆕 | 手艺人月绩效 | 72KB | 生美手艺人操作统计 |

| DWS | m_dws_project_analysis 🆕 | 项目分析 | 2.6MB | 产品销量排行、业绩排名 |

| ADS | m_ads_customer_rfm | RFM客户分层 | 1.7MB | 客户分层、高价值客户 |

| ADS | m_ads_churn_warning | 流失预警 | 3MB | 客户流失风险 |

| ADS | m_ads_executive_dashboard | 管理层看板 | 48KB | 月度营收汇总 |

与原始21视图方案对比：去掉了项目联动组合(分析复杂度高、使用频率低)、预约分析(数据量小可直接查原始表)、以及6个APP查询接口层视图(按需动态查询,无需预计算)。

关键口径：

  • 有效归属咨询师 = COALESCE(当前归属咨询师, 开单时咨询师)
  • 客户同一天多次到院只计1次（防刷数据）

Layer 2: DWS 岗位事实层（4个）

| 物化视图 | 岗位 | 口径 | 分组维度 |

|---------|------|------|---------|

| dws_consultant_daily | 咨询师 | 客户归属制（收款/开单/划扣分离） | 咨询师+日期 |

| dws_doctor_daily | 医生 | 直接业绩（按doctor_name） | 医生+日期 |

| dws_nurse_daily | 护士（医美） | deduct_operator + 医美科室 | 护士+日期 |

| dws_craftsman_daily | 手艺人（生美） | deduct_operator + 生美科室 | 手艺人+日期 |

关键口径：

  • 医生/护士维度不按门店拆分（避免同一医生跨店重复统计）
  • 护士只统计医美科室（微创/皮肤/外科）
  • 手艺人只统计生美科室（洗脸/修丽可/白鲸）

Layer 3: KPI 管理指标层（9个）

#### 门店级（1个）

| 物化视图 | 关键指标 | 粒度 |

|---------|---------|------|

| kpi_store_monthly | 收款、执行、开单、耗占比、人均单价 | 门店+月份 |

#### 岗位级（5个）

| 物化视图 | 关键指标 | 粒度 |

|---------|---------|------|

| kpi_consultant_monthly | 收款、开单、划扣、客单价 | 咨询师+月份 |

| kpi_doctor_monthly | 治疗人数、复购率、联动率、耗占比 | 医生+月份 |

| kpi_nurse_monthly | 服务人数、操作次数、客单价 | 护士+月份 |

| kpi_craftsman_monthly | 服务人数、操作次数、客单价 | 手艺人+月份 |

> ⚠️ 复购率计算注意：必须按月限定范围，不能统计历史所有数据（否则复购率会超100%）。正确做法：子查询按月GROUP BY + HAVING COUNT(*) >= 2。

#### 业务分析级（3个）

| 物化视图 | 用途 | 核心逻辑 |

|---------|------|---------|

| kpi_customer_segment | 客户分层 | RFM + 医美/生美周期差异（30天 vs 60-90天） |

| m_dws_recall_performance | ✅ 已上线 回访考核物化视图 | 回访完成率、回访覆盖率、超60天未回访客户数（507条记录） |

| kpi_project_analysis | 项目分析 | TOP排名、科室排名、耗占比 |

| kpi_project_combo | 项目联动组合 | 同一客户购买的项目组合频率 |

Layer 4: APP 查询接口层（6个）

| 物化视图 | 用途 | 目标用户 |

|---------|------|---------|

| app_customer_summary | 客户360汇总（总消费、RFM、到店状态） | 管理层 |

| app_customer_detail | 客户360明细（每次到店记录） | 咨询师 |

| app_customer_monthly | 客户月度消费趋势 | 管理层 |

| app_staff_summary | 员工360汇总（咨询师/医生/护士/手艺人） | 管理层 |

| app_store_dashboard | 门店仪表盘（本月业绩、环比、超期客户） | 门店店长 |

| app_consultant_customer_status | 咨询师客户跟进状态（优先级排序） | 咨询师 |

---

物化视图 vs 标准视图对比

| 对比项 | 当前 MySQL 标准视图（24个） | 目标 PostgreSQL 物化视图（21个） |

|-------|--------------------------|-------------------------------|

| 查询性能 | 全量重算，10-30秒 | 预计算，<50ms |

| 刷新策略 | — | 每日自动 + 按需刷新 |

| 是否支持CONCURRENTLY | ❌ | ✅ 刷新不阻塞查询 |

| 唯一索引 | — | ✅ 必须建 |

| 依赖管理 | 无 | 需注意 DROP CASCADE 风险 |

---

刷新策略

`mermaid

gantt

title 每日刷新时间线

dateFormat HH:mm

axisFormat %H:%M

section ETL

ODS MySQL同步（已有） :done, 03:00, 1h

section 物化视图刷新

DWD层 :active, 04:00, 10m

DWS层 :04:15, 10m

KPI层 :04:30, 10m

APP层 :04:45, 10m

`

  • 使用 REFRESH MATERIALIZED VIEW CONCURRENTLY 保证查询不中断
  • 各层按依赖顺序刷新（DWD → DWS → KPI → APP）
  • 支持单视图按需触发刷新 API

---

已知坑点

| 坑 | 表现 | 解决方案 |

|----|------|---------|

| 复购率超100% | 子查询未按月限定 | 子查询按月GROUP BY + HAVING COUNT(*) >= 2 |

| 医生跨店重复 | 按门店拆分维度 | 按医生+月份聚合，去掉门店维度 |

| DROP CASCADE 误删 | 删除视图时级联删除了依赖视图 | 先检查依赖，手动重建 |

| CONCURRENTLY 需要唯一索引 | 刷新报错 | 每个物化视图必须先建 UNIQUE INDEX |

| 查了原始表 | 查询很慢 | 确认查询的是 analytics. 不是 stg_ 或原始表 |

最后更新: 2026/6/16 18:51:38