# dws_assistant_finance_analysis 助教收支分析表

> 生成时间：2026-02-03

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_assistant_finance_analysis |
| 主键 | id |
| 唯一键 | (site_id, stat_date, assistant_id) |
| 数据来源 | dwd_assistant_service_log + dws_assistant_salary_calc |
| 更新频率 | 每日更新 |
| 说明 | 以"日期+助教"为粒度，分析助教产出的收入和成本 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | id | BIGSERIAL | NO | 自增主键 |
| 2 | site_id | BIGINT | NO | 门店ID |
| 3 | tenant_id | BIGINT | NO | 租户ID |
| 4 | stat_date | DATE | NO | 统计日期 |
| 5 | assistant_id | BIGINT | NO | 助教ID |
| 6 | assistant_nickname | VARCHAR(50) | YES | 助教花名 |
| 7 | revenue_total | NUMERIC(14,2) | NO | 助教产出收入（ledger_amount汇总） |
| 8 | revenue_base | NUMERIC(14,2) | NO | 基础课收入 |
| 9 | revenue_bonus | NUMERIC(14,2) | NO | 附加课收入 |
| 10 | revenue_room | NUMERIC(14,2) | NO | 包厢课收入 |
| 11 | cost_daily | NUMERIC(14,2) | NO | 日均工资成本（月工资/工作天数） |
| 12 | gross_profit | NUMERIC(14,2) | NO | 毛利 = 收入 - 成本 |
| 13 | gross_margin | NUMERIC(5,4) | NO | 毛利率 |
| 14 | service_count | INTEGER | NO | 服务次数 |
| 15 | service_hours | NUMERIC(10,2) | NO | 服务小时数 |
| 16 | room_service_count | INTEGER | NO | 包厢课服务次数 |
| 17 | room_service_hours | NUMERIC(10,2) | NO | 包厢课服务小时数 |
| 18 | unique_customers | INTEGER | NO | 服务客户数 |
| 19 | created_at | TIMESTAMPTZ | NO | 创建时间 |
| 20 | updated_at | TIMESTAMPTZ | NO | 更新时间 |

## 数据来源

### 收入来源：dwd_assistant_service_log
```sql
SELECT 
    DATE(start_use_time) AS stat_date,
    site_assistant_id AS assistant_id,
    SUM(ledger_amount) AS revenue_total,
    SUM(CASE WHEN COALESCE(st.course_type_code, 'BASE') = 'BASE' THEN ledger_amount ELSE 0 END) AS revenue_base,
    SUM(CASE WHEN COALESCE(st.course_type_code, 'BASE') = 'BONUS' THEN ledger_amount ELSE 0 END) AS revenue_bonus,
    SUM(CASE WHEN COALESCE(st.course_type_code, 'BASE') = 'ROOM' THEN ledger_amount ELSE 0 END) AS revenue_room,
    COUNT(*) AS service_count,
    SUM(income_seconds) / 3600.0 AS service_hours,
    COUNT(CASE WHEN COALESCE(st.course_type_code, 'BASE') = 'ROOM' THEN 1 END) AS room_service_count,
    SUM(CASE WHEN COALESCE(st.course_type_code, 'BASE') = 'ROOM' THEN income_seconds ELSE 0 END) / 3600.0 AS room_service_hours,
    COUNT(DISTINCT tenant_member_id) AS unique_customers
FROM dwd.dwd_assistant_service_log s
LEFT JOIN dws.cfg_skill_type st
    ON st.skill_id = s.skill_id AND st.is_active = TRUE
WHERE s.is_delete = 0
GROUP BY DATE(start_use_time), site_assistant_id;
```

### 成本来源：dws_assistant_salary_calc
```sql
-- 日均成本 = 月度应发工资 / 当月工作天数
SELECT 
    assistant_id,
    salary_month,
    gross_salary / NULLIF(work_days, 0) AS cost_daily
FROM dws.dws_assistant_salary_calc sc
JOIN dws.dws_assistant_monthly_summary ms 
    ON sc.assistant_id = ms.assistant_id AND sc.salary_month = ms.stat_month;
```

## 使用说明

**毛利计算**
```
gross_profit = revenue_total - cost_daily
gross_margin = gross_profit / NULLIF(revenue_total, 0)
```

**注意事项**
- cost_daily 基于月度工资分摊，非实际日薪
- 当月数据在月末工资计算前 cost_daily 可能不准确

## 可回溯性

| 项目 | 说明 |
|------|------|
| 可回溯 | ⚠️ 部分可回溯 |
| 数据范围 | 2025-07-21 ~ 至今 |
| 依赖表 | dwd_assistant_service_log, dws_assistant_salary_calc |
| 限制 | cost_daily 依赖 salary_calc，需先完成薪资计算 |
