# dws_assistant_customer_stats 助教服务客户统计表

> 生成时间：2026-02-03 | 更新时间：2026-02-13

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_assistant_customer_stats |
| 主键 | id |
| 唯一键 | (site_id, assistant_id, member_id, stat_date) |
| 数据来源 | dwd_assistant_service_log |
| 更新频率 | 每日更新 |
| 说明 | 以"助教+客户"为粒度，统计服务关系和滚动窗口指标 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | id | BIGSERIAL | NO | 自增主键 |
| 2 | site_id | BIGINT | NO | 门店ID |
| 3 | tenant_id | BIGINT | NO | 租户ID |
| 4 | assistant_id | BIGINT | NO | 助教ID |
| 5 | assistant_nickname | VARCHAR(50) | YES | 助教花名 |
| 6 | member_id | BIGINT | NO | 客户ID（member_id=0散客不入此表） |
| 7 | member_nickname | VARCHAR(100) | YES | 客户昵称 |
| 8 | member_mobile | VARCHAR(20) | YES | 客户手机号（脱敏） |
| 9 | stat_date | DATE | NO | 统计基准日期 |
| 10 | first_service_date | DATE | YES | 首次服务日期 |
| 11 | last_service_date | DATE | YES | 最近服务日期 |
| 12 | total_service_count | INTEGER | NO | 累计服务次数 |
| 13 | total_service_hours | NUMERIC(10,2) | NO | 累计服务小时数 |
| 14 | total_service_amount | NUMERIC(12,2) | NO | 累计服务金额 |
| 15-20 | service_count_7d/10d/15d/30d/60d/90d | INTEGER | NO | 近N天服务次数 |
| 21-26 | service_hours_7d/10d/15d/30d/60d/90d | NUMERIC(10,2) | NO | 近N天服务小时数 |
| 27-32 | service_amount_7d/10d/15d/30d/60d/90d | NUMERIC(12,2) | NO | 近N天服务金额 |
| 33 | days_since_last | INTEGER | YES | 距离最近服务的天数 |
| 34 | is_active_7d | BOOLEAN | NO | 近7天是否活跃 |
| 35 | is_active_30d | BOOLEAN | NO | 近30天是否活跃 |
| 36 | created_at | TIMESTAMPTZ | NO | 创建时间 |
| 37 | updated_at | TIMESTAMPTZ | NO | 更新时间 |

## 数据来源

### 滚动窗口计算
```sql
-- 统计每个助教-客户组合的滚动窗口指标
WITH service_data AS (
    SELECT 
        site_id,
        site_assistant_id AS assistant_id,
        tenant_member_id AS member_id,
        DATE(start_use_time) AS service_date,
        COUNT(*) AS service_count,
        SUM(income_seconds) / 3600.0 AS service_hours,
        SUM(ledger_amount) AS service_amount
    FROM dwd.dwd_assistant_service_log
    WHERE is_delete = 0
      AND tenant_member_id != 0  -- 排除散客
    GROUP BY site_id, site_assistant_id, tenant_member_id, DATE(create_time)
)
SELECT 
    assistant_id,
    member_id,
    :stat_date AS stat_date,
    MIN(service_date) AS first_service_date,
    MAX(service_date) AS last_service_date,
    SUM(service_count) AS total_service_count,
    SUM(CASE WHEN service_date >= :stat_date - 6 THEN service_count ELSE 0 END) AS service_count_7d,
    SUM(CASE WHEN service_date >= :stat_date - 29 THEN service_count ELSE 0 END) AS service_count_30d,
    -- ... 其他窗口
FROM service_data
GROUP BY assistant_id, member_id;
```

## 使用说明

**散客处理**
- member_id=0 的散客不进入此表统计
- 仅统计有会员身份的客户

**活跃度判断**
```sql
-- 近7天活跃 = 近7天有服务记录
is_active_7d = (service_count_7d > 0)
-- 近30天活跃 = 近30天有服务记录
is_active_30d = (service_count_30d > 0)
```

## 可回溯性

| 项目 | 说明 |
|------|------|
| 可回溯 | ✅ 完全可回溯 |
| 数据范围 | 2025-07-21 ~ 至今 |
| 依赖表 | dwd_assistant_service_log, dim_member |
| 注意事项 | 滚动窗口需要足够的历史数据支撑 |
