freedakgmail
193 lines
6.5 KiB
Markdown
193 lines
6.5 KiB
Markdown
# 06 · 步骤五：AI协同本体层构建
|
||
|
||
> 目标：AI参与数据层代码编写——物化视图SQL、导入脚本、数据质量校验，并逐项验证。 `[Delta主导，AI为主]`
|
||
>
|
||
> FDE角色：这一步是Delta层的核心工作。AI承担80%的代码生成（SQL、脚本），人聚焦审查和验证。本体层的沉淀质量直接决定下一个同类客户的交付速度。
|
||
|
||
## 1. AI协同模式
|
||
|
||
### 1.1 工作流程
|
||
|
||
```
|
||
1. 人工提供：数据现状（表结构、样本数据、数据量）
|
||
2. AI生成：物化视图SQL草案
|
||
3. 人工审查：业务逻辑是否正确
|
||
4. AI执行：在数据库上创建视图 + 验证数据
|
||
5. 人工确认：抽查数据准确性
|
||
6. 迭代：发现问题→AI修复→再验证
|
||
```
|
||
|
||
### 1.2 AI Prompt模式
|
||
|
||
```
|
||
"请基于以下原始表设计物化视图：
|
||
- 原始表：bill_records（字段：c003门店, c009消费, c068优惠, c114实收, c175下单时间, c176结账时间, c191收银员）
|
||
- 需求：按月×门店×小时聚合账单数、消费、优惠、实收
|
||
- 注意：c114可能为空字符串，需COALESCE处理
|
||
输出：CREATE MATERIALIZED VIEW SQL + 索引SQL"
|
||
```
|
||
|
||
## 2. 物化视图构建
|
||
|
||
### 2.1 构建顺序
|
||
|
||
```
|
||
1. 基础视图（不依赖其他视图）
|
||
├── mv_bill_hourly（账单小时聚合）
|
||
├── mv_overview_daily（日度概览）
|
||
└── mv_overview_monthly（月度概览）
|
||
|
||
2. 门店评级视图（依赖基础视图）
|
||
├── mv_store_risk_rating_monthly（门店风险评级）
|
||
└── mv_store_benchmark_composite_monthly（门店对标）
|
||
|
||
3. 费用视图（依赖外部费用数据）
|
||
└── mv_store_operating_expense_monthly（门店费用）
|
||
|
||
4. 风险明细视图（依赖原始表）
|
||
├── mv_risk_anomaly（异常账单）
|
||
├── mv_risk_zero（零实收）
|
||
└── mv_risk_cashier（收银员风险）
|
||
|
||
5. 平台视图
|
||
└── mv_store_platform_economics_monthly（平台经济性）
|
||
```
|
||
|
||
### 2.2 AI协同编写物化视图
|
||
|
||
**典型交互**：
|
||
|
||
```
|
||
人：bill_records中c003是门店名，c114是实收金额，但有些是空字符串
|
||
AI：生成SQL：
|
||
CREATE MATERIALIZED VIEW mv_bill_hourly AS
|
||
SELECT
|
||
c003 AS store_name,
|
||
EXTRACT(HOUR FROM c176::timestamp) AS hour,
|
||
to_char(c176::timestamp, 'YYYY-MM') AS month,
|
||
count(*) AS bills,
|
||
sum(COALESCE(NULLIF(c009,'')::numeric, 0)) AS consumption,
|
||
sum(COALESCE(NULLIF(c114,'')::numeric, 0)) AS received
|
||
FROM bill_records
|
||
WHERE c176 IS NOT NULL
|
||
GROUP BY 1, 2, 3;
|
||
|
||
人：门店名需要和费用表对齐，有些名字不一样
|
||
AI：补充映射逻辑，使用store_name_mapping
|
||
```
|
||
|
||
### 2.3 异常账单视图设计
|
||
|
||
```sql
|
||
-- AI协同设计的异常判断逻辑
|
||
CASE
|
||
WHEN consumption > 0 AND received = 0 THEN '有消费无实收'
|
||
WHEN discount > consumption THEN '优惠大于消费'
|
||
WHEN abs(consumption - discount - received) > 1 THEN '消费-优惠与实收不平'
|
||
ELSE NULL
|
||
END
|
||
```
|
||
|
||
**关键教训**：阈值0.05元太严格，会将浮点舍入差异标为异常。AI建议初始阈值设为1元，后续根据数据分布调整。
|
||
|
||
## 3. 数据导入脚本
|
||
|
||
### 3.1 AI协同编写导入脚本
|
||
|
||
```
|
||
人：这是4月的薪资Excel，列名是中文，需要导入salary_detail_records
|
||
AI：生成Python/SQL导入脚本，包含：
|
||
- 列名映射（中文→英文字段名）
|
||
- 数据类型转换
|
||
- 空值处理
|
||
- 去重逻辑
|
||
- 进度输出
|
||
```
|
||
|
||
### 3.2 导入关键点
|
||
|
||
| 数据源 | 关键问题 | 解决方案 |
|
||
|--------|---------|---------|
|
||
| bill_records | c001~c200列名无含义 | 建立字段映射文档 |
|
||
| salary_detail_records | salary_period格式"2026年4月" | 查询时用to_char中文格式 |
|
||
| attendance_records | department是路径字符串 | extractStore从后往前找"店" |
|
||
| 费用数据 | 门店名与账单系统不一致 | store_name_mapping映射表 |
|
||
|
||
## 4. 数据质量校验
|
||
|
||
### 4.1 AI协同校验
|
||
|
||
```
|
||
AI Prompt：
|
||
"请对以下物化视图进行数据质量校验：
|
||
1. 记录数是否合理（与原始表对比）
|
||
2. 金额加总是否一致（物化视图 vs 原始表）
|
||
3. 是否有NULL或异常值
|
||
4. 门店数是否完整
|
||
输出：校验SQL + 预期结果 + 实际结果"
|
||
```
|
||
|
||
### 4.2 校验清单
|
||
|
||
| 校验项 | SQL | 预期 |
|
||
|--------|-----|------|
|
||
| 门店数 | `SELECT count(DISTINCT store_name) FROM mv_bill_hourly WHERE month='2026-04'` | ~94家 |
|
||
| 实收总额 | `SELECT sum(received) FROM mv_bill_hourly WHERE month='2026-04'` | ~6369万 |
|
||
| 账单总数 | `SELECT sum(bills) FROM mv_bill_hourly WHERE month='2026-04'` | ~168万 |
|
||
| 异常账单占比 | `SELECT count(*) FROM mv_risk_anomaly / SELECT count(*) FROM bill_records` | <5% |
|
||
| 物化视图新鲜度 | 对比物化视图和原始表的count | 一致 |
|
||
|
||
## 5. 门店名映射
|
||
|
||
### 5.1 映射表构建
|
||
|
||
```sql
|
||
CREATE TABLE public.store_name_mapping (
|
||
salary_name TEXT, -- 薪资/考勤系统中的名称
|
||
bill_name TEXT -- 账单系统中的名称
|
||
);
|
||
```
|
||
|
||
### 5.2 AI协同发现映射
|
||
|
||
```
|
||
AI Prompt：
|
||
"请对比以下两个数据源的门店名，找出不一致的：
|
||
- 账单系统：SELECT DISTINCT store_name FROM mv_bill_hourly
|
||
- 考勤系统：SELECT DISTINCT extractStore(department) FROM attendance_records
|
||
输出：需要映射的对照表"
|
||
```
|
||
|
||
### 5.3 代码中的映射查找模式
|
||
|
||
```typescript
|
||
// 构建查找表：同时用原名和映射名
|
||
const staffLookup: Record<string, any> = {}
|
||
for (const [store, data] of Object.entries(staffSummary)) {
|
||
staffLookup[store] = data // 原名
|
||
const mapped = nameMap[store]
|
||
if (mapped && mapped !== store) {
|
||
staffLookup[mapped] = data // 映射名
|
||
}
|
||
}
|
||
```
|
||
|
||
## 6. 输出物
|
||
|
||
| 输出物 | 说明 | 验证方式 |
|
||
|--------|------|---------|
|
||
| 物化视图SQL | 所有视图的CREATE语句 | psql执行成功 |
|
||
| 索引SQL | 每个视图的索引 | 查询性能达标 |
|
||
| 导入脚本 | 各数据源的导入脚本 | 导入后数据量正确 |
|
||
| 映射表 | store_name_mapping | 所有门店能匹配 |
|
||
| 数据质量报告 | 校验结果汇总 | 人工抽查确认 |
|
||
| 刷新脚本 | 物化视图刷新流程 | 执行后数据更新 |
|
||
|
||
## 7. 关键注意事项
|
||
|
||
1. **先查数据再写SQL**：AI生成SQL前，先让它查实际表结构和样本数据
|
||
2. **物化视图必须建索引**：无索引的物化视图查询比原始表还慢
|
||
3. **刷新脚本要完整**：遗漏任何一个视图的刷新都会导致数据不一致
|
||
4. **映射表要持续维护**：新开门店时需要补充映射
|
||
5. **AI生成的SQL必须人工审查**：AI可能忽略业务约束（如空值处理、日期格式）
|