后续系统构建方案

管理

后续系统构建方案

> 最后更新: 2026-06-07

> 版本: v1.0

> 基于：瑞美云数据分析体系迁移指南 + 光合盐现有架构审计 + 三层数据架构设计

---

总体路线图

`

Phase 1: 基础加固（已完成 ✅）

├── ✅ 每日 SQLite 自动备份（3:00）

├── ✅ 全 Profile Holographic 记忆

├── ✅ Web UI 升级 v0.6.11

├── ✅ 光合盐BI管理手册（9个核心文档）

└── ✅ Dify Plugin Daemon 运行 + 知识库导入（8个文档）

Phase 2: 三层数据仓库建设（当前阶段 ⏳）

├── 2A: PostgreSQL 建库 + ETL + DWD层

├── 2B: DWS + ADS 层（21个物化视图）

├── 2C: 展示层配置（Dify + BI）

└── 2D: 知识库持续补充

Phase 3: 企业级增强（规划中）

├── SQLite → PostgreSQL 迁移评估

├── AI 客服训练管线

├── 操作审计日志

└── 高可用/灾备

`

---

Phase 2A: 基础设施 + DWD 明细层（预计 1 天）

任务 2A-1: PostgreSQL 建数据仓库

`sql

-- 创建数据仓库数据库

CREATE DATABASE ruimeiyun_dw;

-- 创建 analytics schema（物化视图统一放在此 schema）

\c ruimeiyun_dw

CREATE SCHEMA analytics;

`

任务 2A-2: MySQL → PostgreSQL ETL 脚本

编写 Python 脚本，将 MySQL ruimeiyun 的 8 张基表同步到 PostgreSQL：

| 目标表 | 来源表 | 同步方式 |

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

| stg_出纳结算单 | MySQL.出纳结算单 | 全量/增量 |

| stg_到院明细 | MySQL.到院明细 | 全量/增量 |

| stg_客户盘点 | MySQL.客户盘点 | 全量/增量 |

| stg_回访记录 | MySQL.回访记录 | 全量/增量 |

| stg_712执行业绩 | MySQL.712执行业绩 | 全量/增量 |

| stg_预约记录 | MySQL.预约记录 | 全量 |

| stg_入库记录 | MySQL.入库记录 | 全量 |

| stg_白鲸门店日报 | MySQL.白鲸门店日报 | 全量 |

ETL 逻辑（已踩坑经验）：

  • 字段类型映射（MySQL VARCHAR → PostgreSQL TEXT）
  • 日期格式标准化（统一为 TIMESTAMP）
  • 空值处理（空字符串 → NULL）
  • 增量同步依据：修改时间 字段
  • 初次部署全量，后续增量

任务 2A-3: 创建 DWD 层物化视图（2个）

`sql

-- DWD 1: 客户到院归一

CREATE MATERIALIZED VIEW analytics.dwd_customer_visit AS

SELECT

vip_num,

DATE(biz_time) AS visit_date,

store_name,

visit_type,

-- 同一客户同一天 = 1次

ROW_NUMBER() OVER (PARTITION BY vip_num, DATE(biz_time) ORDER BY biz_time) AS visit_seq

FROM stg_到院明细;

-- DWD 2: 客户咨询师归属

CREATE MATERIALIZED VIEW analytics.dwd_customer_consultant AS

SELECT

c.vip_num,

c.customer_name,

COALESCE(c.current_consultant, c.consultant_at_order) AS effective_consultant,

-- ...其他字段

FROM stg_客户盘点 c;

`

任务 2A-4: 每日刷新 crontab

`

crontab:

0 4 * /home/ubuntu/scripts/etl_mysql_to_pg.sh # ETL同步

30 4 * /home/ubuntu/scripts/refresh_dwd.sh # 刷新DWD

`

---

Phase 2B: DWS + KPI + APP 层（预计 1 天）

任务 2B-1: DWS 岗位事实层（4个物化视图）

`sql

-- 咨询师日表

CREATE MATERIALIZED VIEW analytics.dws_consultant_daily AS

SELECT

consultant_name,

biz_date,

SUM(actual_money) AS 收款,

SUM(project_performance) AS 开单,

SUM(settlement_amount) AS 划扣,

COUNT(DISTINCT vip_num) AS 服务客户数,

COUNT(*) AS 操作次数

FROM stg_出纳结算单

GROUP BY consultant_name, biz_date;

-- 医生日表

CREATE MATERIALIZED VIEW analytics.dws_doctor_daily AS

SELECT

doctor_name,

biz_date,

SUM(settlement_amount) AS 执行业绩,

COUNT(DISTINCT vip_num) AS 治疗人数,

COUNT(*) AS 治疗次数

FROM stg_712执行业绩

GROUP BY doctor_name, biz_date;

-- 护士日表（仅医美科室）

CREATE MATERIALIZED VIEW analytics.dws_nurse_daily AS

SELECT

deduct_operator AS nurse_name,

biz_date,

SUM(settlement_amount) AS 执行业绩,

COUNT(DISTINCT vip_num) AS 服务人数,

COUNT(*) AS 操作次数

FROM stg_712执行业绩

WHERE dept_l1 IN ('微创中心', '皮肤中心', '外科中心')

GROUP BY deduct_operator, biz_date;

-- 手艺人日表（仅生美科室）

CREATE MATERIALIZED VIEW analytics.dws_craftsman_daily AS

SELECT

deduct_operator AS craftsman_name,

biz_date,

SUM(settlement_amount) AS 执行业绩,

COUNT(DISTINCT vip_num) AS 服务人数,

COUNT(*) AS 操作次数

FROM stg_712执行业绩

WHERE dept_l1 IN ('洗脸', '修丽可', '医萃托兰', '白鲸洗脸')

GROUP BY deduct_operator, biz_date;

`

任务 2B-2: KPI 管理指标层（9个物化视图）

关键视图实现：

客户分层（kpi_customer_segment）：

  • RFM 评分（参考各行业周期差异：生美30天、医美60-90天）
  • 客户分层（高价值VIP/重点发展/潜力保持/需唤醒/已流失）

医生复购率（kpi_doctor_monthly）：

  • ⚠️ 此处有已知坑：必须按月限定范围
  • 正确：子查询按月 GROUP BY → HAVING COUNT(*) >= 2

回访考核（kpi_recall_performance）：

  • 完成率 = 已回访 / 总计划
  • 平均延迟天数 = AVG(实际回访日期 - 计划回访日期)

任务 2B-3: APP 查询接口层（6个物化视图）

各视图面向不同用户角色：

| 视图 | 数据来源 | 刷新依赖 |

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

| app_customer_summary | DWD + KPI层 | DWD→DWS→KPI全刷新后 |

| app_customer_detail | DWD层 | DWD刷新后 |

| app_customer_monthly | DWS层 | DWS刷新后 |

| app_staff_summary | DWS+KPI层 | DWS→KPI刷新后 |

| app_store_dashboard | KPI层 | KPI刷新后 |

| app_consultant_customer_status | DWD+DWS层 | DWD→DWS刷新后 |

任务 2B-4: 刷新脚本

`bash

#!/bin/bash

refresh_all.sh — 按依赖顺序刷新所有物化视图

export PGHOST=localhost

export PGDATABASE=ruimeiyun_dw

export PGUSER=dify

export PGPASSWORD=*

echo "$(date): 刷新 DWD..."

psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.dwd_customer_visit;"

psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.dwd_customer_consultant;"

echo "$(date): 刷新 DWS..."

psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.dws_consultant_daily;"

psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.dws_doctor_daily;"

psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.dws_nurse_daily;"

psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.dws_craftsman_daily;"

echo "$(date): 刷新 KPI..."

... 9个KPI视图

echo "$(date): 刷新 APP..."

... 6个APP视图

echo "$(date): 全部刷新完成"

`

---

Phase 2C: 展示层配置（预计 0.5 天，需用户配合）

任务 2C-1: 安装 Dify 插件

通过 Dify Web UI（http://:3000）登录后：

| 插件 | 用途 | 安装方式 |

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

| database | 连接 PostgreSQL 数据源 | Dify 插件市场 → 安装 |

| chartgen | 自动生成图表 | Dify 插件市场 → 安装 |

| wecom-bot / dingtalk | 日报推送 | 根据使用场景选装 |

任务 2C-2: 创建 Dify AI 应用

  • 创建一个"文本生成"类型应用
  • 关联知识库（光合盐BI数据系统管理手册）
  • 添加 database 工具（连接 PostgreSQL ruimeiyun_dw）
  • 配置 Prompt：让 AI 能根据自然语言查询物化视图
  • 配置 ChartGen：自动生成图表

任务 2C-3: BI 仪表盘（可选）

如果固定看板需求明确，评估是否安装 Metabase（最轻量 BI 工具）。

---

Phase 2D: 知识库持续补充

当前知识库结构（9个文件已完成）：

`

光合盐BI数据系统管理手册/

├── 索引.md

├── 01_业务逻辑/

│ ├── 01_业务架构概述.md ✅ 核心6原则

│ ├── 03_管理口径定义.md ✅ 全口径定义

│ └── 05_门店与组织架构.md ✅ 6门店+岗位

├── 02_架构设计/

│ ├── 01_三层数据架构总览.md ✅

│ └── 03_DWD:DWS物化视图层.md ✅ 21视图详细设计

├── 03_实施过程/

│ └── 01_数据库搭建记录.md ✅ 8基表+24视图

├── 04_问题记录/

│ └── 01_数据质量问题.md ✅ 8条已知问题

├── 05_优化策略/

│ └── 01_性能优化.md ✅

├── 06_更新日志/

│ └── CHANGELOG.md ✅

└── 07_附录/

├── 01_数据库连接信息.md ✅

└── 02_常用查询模板.md ✅

`

待补充文档（在实施过程中逐步完善）：

| 文档 | 触发条件 | 责任人 |

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

| 02_业务逻辑/04_数据分析维度.md | 明确分析维度和场景后 | Hermes |

| 03_实施过程/02_视图创建记录.md | 开始创建物化视图时 | Hermes |

| 03_实施过程/03_ETL脚本记录.md | ETL脚本编写时 | Hermes |

| 03_实施过程/04_展示层配置记录.md | 配置Dify时 | Hermes |

| 03_实施过程/05_备份恢复策略.md | 数据量增长后 | Hermes |

---

实施时间线

| 阶段 | 任务 | 预计时间 | 我可以做的 | 需要你做的 |

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

| 2A-1 | PostgreSQL 建库 | 10分钟 | ✅ 全部 | — |

| 2A-2 | ETL Python脚本 | 2小时 | ✅ 全部 | — |

| 2A-3 | 2个DWD物化视图 | 30分钟 | ✅ 全部 | — |

| 2A-4 | crontab刷新脚本 | 10分钟 | ✅ 全部 | — |

| 2B-1 | 4个DWS物化视图 | 1小时 | ✅ 全部 | — |

| 2B-2 | 9个KPI物化视图 | 2小时 | ✅ 全部 | 确认RFM阈值 |

| 2B-3 | 6个APP物化视图 | 1小时 | ✅ 全部 | — |

| 2B-4 | 完整刷新脚本 | 30分钟 | ✅ 全部 | — |

| 2C-1 | Dify登录+安装插件 | 30分钟 | ❌ 需要你 | Web UI操作 |

| 2C-2 | Dify应用配置 | 1小时 | ⚠️ 需要你配置 | 确认Prompt模板 |

| 2C-3 | BI仪表盘 | 按需 | ⚠️ 需评估 | 决定是否上 |

---

总结

| 维度 | 现状 | Phase 2完成后 |

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

| 查询性能 | 10-30秒（标准视图） | < 50ms（物化视图） |

| 数据架构 | 单层 MySQL | 三层（ODS→DWD/DWS/ADS→展示层） |

| 查询方式 | 手动 SQL | Dify AI自然语言 + BI看板 |

| 知识管理 | 散落在对话中 | 结构化知识库 |

| 文档传承 | 无 | 完整的系统管理手册+更新日志 |

| 问题追溯 | 无 | [[问题记录]]系统（8条已记录） |

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