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

08_数据分析2026/6/17

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_per[[f]]ormance`	回访考核	72KB	回访完成率、覆盖率、超期
DWS	`m_dws_nurse_monthly` 🆕	护士月绩效	72KB	医美护士操作统计
DWS	`m_dws_cra[[f]]tsman_monthly` 🆕	手艺人月绩效	72KB	生美手艺人操作统计
DWS	`m_dws_project_analysis` 🆕	项目分析	2.6MB	产品销量排行、业绩排名
ADS	`m_ads_customer_r[[f]]m`	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_cra[[f]]tsman_daily`	手艺人（生美）	deduct_operator + 生美科室	手艺人+日期

关键口径：

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

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

门店级（1个）

物化视图	关键指标	粒度
`kpi_store_monthly`	收款、执行、开单、耗占比、人均单价	门店+月份

岗位级（5个）

物化视图	关键指标	粒度
`kpi_consultant_monthly`	收款、开单、划扣、客单价	咨询师+月份
`kpi_doctor_monthly`	治疗人数、复购率、联动率、耗占比	医生+月份
`kpi_nurse_monthly`	服务人数、操作次数、客单价	护士+月份
`kpi_cra[[f]]tsman_monthly`	服务人数、操作次数、客单价	手艺人+月份

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

业务分析级（3个）

物化视图	用途	核心逻辑
`kpi_customer_segment`	客户分层	RFM + 医美/生美周期差异（30天 vs 60-90天）
`m_dws_recall_per[[f]]ormance`	✅ 已上线回访考核物化视图	回访完成率、回访覆盖率、超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_sta[[f]]f_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/17 23:00:03