freedak/SBrainCO
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
v0.3
SBrainCO/docs/20260812-1/API数据检查报告.md
T
selfrelease 7ea6a2515f docs: 归档历史文档 + 新增战略外脑实施方法论 
- 归档根目录散落文档到 docs/20260812/ 和 docs/20260812-1/
- 新增 16-战略外脑实施方法论.md：七项能力、十二步工作法、AI与决策治理、成熟度和验收
- 新增 17-SBrainCO战略外脑建设对照表.md：方法论映射为领域模型、产品模块、API和分阶段待办
- 新增 调查表/战略外脑能力验收Checklist.csv：35项能力验收检查项
- 更新 01-总体框架与八步工作法.md：新增战略外脑扩展章节
- 更新 README.md：新增战略外脑体系导航

Generated with [Devin](https://devin.ai)

Co-Authored-By: Devin <158243242+devin-ai-integration[bot]@users.noreply.github.com>
2026-08-12 22:28:51 +08:00

203 lines
9.2 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# 后端 API 数据正确性检查报告
## 检查时间: 2026-08-11
---
## 一、问题汇总
| 严重度 | 问题 | 影响API | 状态 |
|--------|------|---------|------|
| **高** | `v_store_operating_expense_monthly` 视图硬编码 `_april` 视图 | 所有费用相关API | 未修复 |
| **高** | `mv_store_operating_expense_monthly` 物化表未随数据导入刷新 | 所有 `/store-expense/*` API | 需手动刷新 |
| **中** | 部分API直接查 `bill_records` 而非 `bill_fact` | 6个API | 设计如此 |
| **中** | `e.operating_expense IS NOT NULL` 过滤排除无费用门店 | 3个API | 部分已修复 |
| **低** | 固定月份物化视图 `*_april` 不支持其他月份 | 间接影响 | 待重构 |
---
## 二、问题详细分析
### 问题1: 视图硬编码 `_april`（严重）
**位置**: `v_store_operating_expense_monthly` 视图定义
**问题**: 该视图的 `sales` CTE 引用 `v_store_site_profile_april`，`food` CTE 引用 `v_store_theoretical_actual_cost_april`，这些是4月专用视图，硬编码了 `'2026-04-01'::date`。
**影响**:
- 当查询 `report_month = '2026-05-01'` 时，sales 和 food 数据仍来自4月
- 所有通过 `v_store_operating_expense_monthly` 的数据都只有4月数据正确
- `mv_store_operating_expense_monthly` 物化表从此视图写入，也受影响
**受影响API**:
- `/api/store-expense/overview`
- `/api/store-expense/expense-structure`（直接查 `v_operating_expense_account_monthly`，不受影响）
- `/api/store-expense/store-ranking`
- `/api/store-expense/store-contribution`
- `/api/store-expense/rent-risk`
- `/api/store-expense/delivery-commission`
- `/api/store-expense/efficiency`
- `/api/store-expense/fixed-variable`
- `/api/store-expense/break-even`
- `/api/store-expense/loss-diagnosis`
- `/api/store-expense/store-evaluation`
- `/api/overview/profit-waterfall`
- `/api/overview/store-profit-ranking`
- `/api/overview/profit-opportunity`
- `/api/overview/profit-trend`
- `/api/bank/report`
**修复方案**: 将 `v_store_operating_expense_monthly` 视图中的 `_april` 引用改为通用月份查询，或使用 `mv_store_site_profile_monthly` 和 `mv_store_theoretical_actual_cost_monthly`。
### 问题2: 物化表未刷新（严重）
**位置**: `mv_store_operating_expense_monthly`（物化表，非物化视图）
**问题**: 费用数据导入后，物化表未同步更新，需要手动 DELETE + INSERT。
**当前状态**: 已手动刷新2026年4月数据。
**受影响API**: 同问题1中的所有 `/store-expense/*` API
**修复方案**: 每次费用数据导入后执行：
```sql
DELETE FROM analytics.mv_store_operating_expense_monthly WHERE report_month = '目标月份';
INSERT INTO analytics.mv_store_operating_expense_monthly
SELECT * FROM analytics.v_store_operating_expense_monthly WHERE report_month = '目标月份';
```
### 问题3: 部分API直接查 bill_records（中）
**设计说明**: 以下API直接查 `bill_records` 原始表而非 `bill_fact` 物化视图：
| API | 文件:行 | 说明 |
|-----|---------|------|
| `GET /stores/:code` | data.ts:184 | 门店详情（按c002门店编码查询） |
| `GET /stores/:code/daily` | data.ts:251 | 门店日度（按c003门店名称查询） |
| `GET /smart-scheduling/traffic-heatmap` | smart-scheduling.ts:165 | 流量热力图 |
| `GET /smart-scheduling/meal-period-traffic` | smart-scheduling.ts:293 | 餐段流量 |
| `GET /smart-scheduling/stores` | smart-scheduling.ts:325 | 门店列表 |
| `GET /smart-scheduling/dow-traffic` | smart-scheduling.ts:456 | 星期流量 |
| `GET /tasks/stores/:code/daily-card` | tasks.ts:311 | 门店日度卡片 |
| `GET /situational-awareness/forecast` | situational-awareness.ts:427 | 预测分析 |
**影响**: 这些API能实时反映 `bill_records` 的新数据，不受物化视图刷新影响。但缺点是查询性能较差（199列表无索引优化），且数据未标准化。
**结论**: 当前设计可接受，这些API需要实时数据且查询量不大。
### 问题4: `operating_expense IS NOT NULL` 过滤（中）
**已修复的API**:
- `/api/overview/profit-waterfall` — 已移除过滤，改用 `COALESCE(..., 0)`
- `/api/store-expense/overview` — 已移除过滤
**仍存在过滤的API**:
| API | 文件:行 | 过滤条件 | 影响 |
|-----|---------|----------|------|
| `GET /overview/store-profit-ranking` | data.ts:1104 | `e.operating_expense IS NOT NULL` | 无费用门店不参与排名 |
| `GET /overview/profit-opportunity` | data.ts:1132 | `e.operating_expense IS NOT NULL` | 无费用门店不参与机会计算 |
| `GET /overview/profit-trend` | data.ts:1440 | `e.operating_expense IS NOT NULL` | 无费用门店不参与趋势 |
**修复方案**: 将 `WHERE e.operating_expense IS NOT NULL` 改为 `LEFT JOIN` + `COALESCE`，确保所有有营收的门店都纳入分析。
### 问题5: 固定月份物化视图（低）
**问题**: 以下物化视图硬编码2026年4月：
- `mv_store_theoretical_actual_cost_april`
- `mv_store_site_profile_april`
- `mv_store_action_priority_deep_april`
- `dish_*_april` (6个)
**影响**: 这些视图被 `v_store_operating_expense_monthly` 引用，限制了多月份支持。
**修复方案**: 使用对应的 `_monthly` 通用物化视图替代。
---
## 三、数据源对照表
### 查询 `bill_fact`（物化视图，需REFRESH）
| API | 数据源 |
|-----|--------|
| `/overview` | `mv_overview_monthly` + `bill_fact` |
| `/overview/profit-waterfall` | `mv_store_risk_rating_monthly` + `mv_store_operating_expense_monthly` + `bill_fact` |
| `/overview/profit-opportunity` | `mv_store_risk_rating_monthly` + `mv_store_operating_expense_monthly` + `mv_dish_sku_abc_monthly` |
| `/revenue/daily-summary` | `mv_daily_revenue` |
| `/stores/risk` | `mv_store_risk_rating_monthly` |
| `/stores/priority` | `mv_store_action_priority_deep_monthly` |
| `/cost/comparison` | `mv_store_theoretical_actual_cost_monthly` |
| `/cost-analysis/store-overview` | `mv_store_theoretical_actual_cost_monthly` |
| `/cost-analysis/store-ranking` | `mv_store_theoretical_actual_cost_monthly` |
### 查询 `bill_records`（原始表，实时）
| API | 数据源 |
|-----|--------|
| `/stores/:code` | `bill_records` |
| `/stores/:code/daily` | `bill_records` |
| `/smart-scheduling/*` (4个) | `bill_records` |
| `/tasks/stores/:code/daily-card` | `bill_records` |
| `/situational-awareness/forecast` | `bill_records` |
### 查询 `mv_store_operating_expense_monthly`（物化表，需手动更新）
| API | 数据源 |
|-----|--------|
| `/store-expense/overview` | `mv_store_operating_expense_monthly` |
| `/store-expense/store-ranking` | `mv_store_operating_expense_monthly` |
| `/store-expense/store-contribution` | `mv_store_operating_expense_monthly` |
| `/store-expense/rent-risk` | `mv_store_operating_expense_monthly` |
| `/store-expense/delivery-commission` | `mv_store_operating_expense_monthly` |
| `/store-expense/efficiency` | `mv_store_operating_expense_monthly` |
| `/store-expense/fixed-variable` | `mv_store_operating_expense_monthly` |
| `/store-expense/break-even` | `mv_store_operating_expense_monthly` |
| `/store-expense/loss-diagnosis` | `mv_store_operating_expense_monthly` |
| `/store-expense/store-evaluation` | `mv_store_operating_expense_monthly` |
### 查询普通视图（实时，无需刷新）
| API | 数据源 |
|-----|--------|
| `/store-expense/expense-structure` | `v_operating_expense_account_monthly` |
| `/store-expense/data-quality` | `v_operating_expense_data_quality` |
| `/overview/daily` | `v_overview_daily` |
| `/time/weekday` | `v_weekday_summary` |
| `/time/hourly` | `v_hourly_summary` |
| `/channel` | `v_channel_daily` |
| `/data-quality` | `v_data_quality_check` |
### 查询 `dish_cost_analysis_summary`（原始表，按import_id）
| API | 数据源 |
|-----|--------|
| `/cost-analysis/overview` | `dish_cost_analysis_summary` |
| `/cost-analysis/category-comparison` | `dish_cost_analysis_summary` |
| `/cost-analysis/margin-deviation` | `dish_cost_analysis_summary` |
| `/cost-analysis/variance-top` | `dish_cost_analysis_summary` |
---
## 四、已完成的修复
| 修复项 | 文件 | 说明 |
|--------|------|------|
| 利润瀑布排除无费用门店 | data.ts | 移除 `operating_expense IS NOT NULL` 过滤，改用 `COALESCE` |
| 费用总览排除无费用门店 | store-expense.ts | 移除过滤 |
| 费用视图重复行 | v_store_operating_expense_monthly | 增加 `expense_agg` CTE 按 `sales_store_code` 聚合 |
| 利润机会池负数 | data.ts:1234 | `GREATEST(..., 0)` 确保opportunity不为负 |
| 前端占比显示 | BossPage.tsx:136 | `totalOpportunity` 只累加正数 |
| 物化视图刷新 | 数据库 | 已刷新 `bill_fact`、`mv_store_risk_rating_monthly`、`mv_store_theoretical_actual_cost_monthly` |
| 费用物化表更新 | 数据库 | 已更新 `mv_store_operating_expense_monthly` |
---
## 五、待修复项
1. **`v_store_operating_expense_monthly` 视图硬编码 `_april`** — 需重构为通用月份
2. **`/overview/store-profit-ranking` 过滤** — 移除 `operating_expense IS NOT NULL`
3. **`/overview/profit-opportunity` 过滤** — 移除 `operating_expense IS NOT NULL`
4. **`/overview/profit-trend` 过滤** — 移除 `operating_expense IS NOT NULL`
5. **自动化物化视图刷新** — 建立数据导入后自动刷新机制
Reference in New Issue View Git Blame Copy Permalink