# dws_assistant_project_tag 助教项目标签表

> 生成时间：2026-03-07

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_assistant_project_tag |
| 主键 | id |
| 唯一键 | (site_id, assistant_id, time_window, category_code) |
| 数据来源 | dwd_assistant_service_log + dim_table + cfg_area_category |
| 更新频率 | 每日全量重建（按 site_id 删除后重新插入） |
| 说明 | 按时间窗口计算助教在四大项目的工作时长占比，≥25% 分配标签 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | id | BIGSERIAL | NO | 自增主键 |
| 2 | site_id | BIGINT | NO | 门店ID |
| 3 | tenant_id | BIGINT | NO | 租户ID |
| 4 | assistant_id | BIGINT | NO | 助教ID |
| 5 | time_window | VARCHAR(40) | NO | 时间窗口枚举值 |
| 6 | category_code | VARCHAR(30) | NO | 项目分类代码（BILLIARD/SNOOKER/MAHJONG/KTV） |
| 7 | category_name | VARCHAR(50) | NO | 项目显示名称（如 🎱 中式/追分） |
| 8 | short_name | VARCHAR(10) | NO | 项目简写（如 🎱） |
| 9 | duration_seconds | BIGINT | NO | 该项目总工作时长（秒） |
| 10 | total_seconds | BIGINT | NO | 所有四大项目总时长（秒） |
| 11 | percentage | NUMERIC(5,4) | NO | 占比（0~1，四位小数） |
| 12 | is_tagged | BOOLEAN | NO | 占比≥0.25 时为 TRUE |
| 13 | computed_at | TIMESTAMPTZ | NO | 计算时间 |
| 14 | created_at | TIMESTAMPTZ | NO | 创建时间 |
| 15 | updated_at | TIMESTAMPTZ | NO | 更新时间 |

## 时间窗口

助教看板使用 6 个时间窗口：

| 枚举值 | 说明 |
|--------|------|
| THIS_MONTH | 本月（月初 ~ 今天） |
| THIS_QUARTER | 本季度（季度首月1日 ~ 今天） |
| LAST_MONTH | 上月（上月初 ~ 上月末） |
| LAST_3_MONTHS_EXCL_CURRENT | 前3个月不含本月 |
| LAST_QUARTER | 上季度 |
| LAST_6_MONTHS | 最近半年（不含本月） |

## 索引

| 索引名 | 字段 | 类型 | 说明 |
|--------|------|------|------|
| pk_dws_assistant_project_tag | id | 主键 | 自增主键 |
| uk_dws_assistant_project_tag | (site_id, assistant_id, time_window, category_code) | 唯一 | 业务唯一键 |
| idx_apt_site_window_tagged | (site_id, time_window) WHERE is_tagged=TRUE | 部分索引 | 加速看板查询 |


## 数据链路

```
dwd.dwd_assistant_service_log (income_seconds, site_table_id)
    → JOIN dwd.dim_table (site_table_id → table_id, scd2_is_current=1)
    → get_area_category(area_name, table_name)  -- 通过 cfg_area_category 映射
    → 只保留 BILLIARD/SNOOKER/MAHJONG/KTV
    → 按 (assistant_id, category_code) 汇总 income_seconds
    → 计算占比 percentage = duration_seconds / total_seconds
    → ≥0.25 标记 is_tagged=TRUE
    → 写入 dws.dws_assistant_project_tag
```

### 关键规则

1. 数据链路走 `dim_table`（通过 `site_table_id` JOIN），不直接用事实表的 `site_table_area_name`
2. 只计算四大项目（BILLIARD/SNOOKER/MAHJONG/KTV），SPECIAL/OTHER 不参与
3. 标签阈值 25%（`TAG_THRESHOLD = 0.25`）
4. 全量删除重建策略：按 `site_id` 删除后重新插入所有时间窗口
5. `is_delete = 0` 过滤已删除的服务记录

## ETL 任务

| 属性 | 值 |
|------|-----|
| 任务代码 | DWS_ASSISTANT_PROJECT_TAG |
| Python 类 | AssistantProjectTagTask |
| 文件 | tasks/dws/assistant_project_tag_task.py |
| 依赖 | DWD_LOAD_FROM_ODS |

## 变更记录

| 日期 | 变更 | 说明 |
|------|------|------|
| 2026-03-07 | 新建表 | 支持助教看板按项目类型筛选 |

## 验证 SQL

```sql
-- 1. 确认表存在且有数据
SELECT COUNT(*) AS row_count,
       COUNT(DISTINCT assistant_id) AS assistant_count,
       COUNT(DISTINCT time_window) AS window_count
FROM dws.dws_assistant_project_tag;

-- 2. 确认 category_code 只有四大项目
SELECT DISTINCT category_code
FROM dws.dws_assistant_project_tag
ORDER BY category_code;
-- 期望：BILLIARD, KTV, MAHJONG, SNOOKER

-- 3. 确认占比计算正确（duration_seconds / total_seconds ≈ percentage）
SELECT site_id, assistant_id, time_window, category_code,
       duration_seconds, total_seconds, percentage,
       ROUND(duration_seconds::numeric / NULLIF(total_seconds, 0), 4) AS calc_pct,
       is_tagged,
       (percentage >= 0.25) AS should_be_tagged
FROM dws.dws_assistant_project_tag
WHERE percentage >= 0.25 AND is_tagged = FALSE
LIMIT 10;
-- 期望：0 行（所有 ≥25% 的都应标记为 TRUE）

-- 4. 确认唯一键无重复
SELECT site_id, assistant_id, time_window, category_code, COUNT(*)
FROM dws.dws_assistant_project_tag
GROUP BY site_id, assistant_id, time_window, category_code
HAVING COUNT(*) > 1;
-- 期望：0 行
```

## 回滚策略

```sql
-- 删除表（不影响其他表）
DROP TABLE IF EXISTS dws.dws_assistant_project_tag CASCADE;
-- 从 task_registry.py 移除 DWS_ASSISTANT_PROJECT_TAG 注册
-- 从 maintenance_task.py DEFAULT_RETENTION_TABLES 移除对应条目
```
