# dws_member_project_tag 客户项目标签表

> 生成时间：2026-03-07

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_member_project_tag |
| 主键 | id |
| 唯一键 | (site_id, member_id, time_window, category_code) |
| 数据来源 | dwd_table_fee_log + dim_table + cfg_area_category |
| 更新频率 | 每日全量重建（按 site_id 删除后重新插入） |
| 说明 | 按每位客户最近 30 次消费计算四大项目时长占比，≥25% 分配标签。散客不参与。 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | id | BIGSERIAL | NO | 自增主键 |
| 2 | site_id | BIGINT | NO | 门店ID |
| 3 | tenant_id | BIGINT | NO | 租户ID |
| 4 | member_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 | 该项目总计费时长（秒，来源 ledger_count） |
| 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 | 更新时间 |

## 时间窗口

| 枚举值 | 说明 |
|--------|------|
| LAST_30_VISITS | 每位客户最近 30 次消费（开台记录），按 ledger_end_time 倒序 |

> 2026-04-11 改为按消费次数取数，不再按固定日期范围，避免长期未到店或来店频率不稳定的客户标签丢失。

## 索引

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


## 数据链路

```
dwd.dwd_table_fee_log (ledger_count, site_table_id, ledger_end_time)
    → ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY ledger_end_time DESC)
    → 取最近 30 条记录（LAST_N_VISITS=30）
    → 按 (member_id, site_table_id) 聚合 ledger_count
    → 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
    → 按 (member_id, category_code) 汇总
    → 计算占比 percentage = duration_seconds / total_seconds
    → ≥0.25 标记 is_tagged=TRUE
    → 写入 dws.dws_member_project_tag（time_window='LAST_30_VISITS'）
```

### 关键规则

1. 按每位客户最近 30 次消费取数，不按固定日期范围，避免来店频率不稳定的客户标签丢失
2. 数据链路走 `dim_table`（通过 `site_table_id` JOIN），不直接用事实表的 `site_table_area_name`
3. 客户时长使用 `ledger_count`（计费时长），不使用 `income_seconds`（那是助教工作时长）
4. 散客（member_id=0 或 NULL）不参与标签计算
5. 只计算四大项目（BILLIARD/SNOOKER/MAHJONG/KTV）
6. 标签阈值 25%（`TAG_THRESHOLD = 0.25`）
7. 全量删除重建策略：按 `site_id` 删除后重新插入
8. `COALESCE(is_delete, 0) = 0` 过滤已删除的台费记录

## ETL 任务

| 属性 | 值 |
|------|-----|
| 任务代码 | DWS_MEMBER_PROJECT_TAG |
| Python 类 | MemberProjectTagTask |
| 文件 | tasks/dws/member_project_tag_task.py |
| 依赖 | DWD_LOAD_FROM_ODS |

## 变更记录

| 日期 | 变更 | 说明 |
|------|------|------|
| 2026-03-07 | 新建表 | 支持客户看板按项目类型筛选 |
| 2026-04-11 | 时间窗口改为消费次数 | LAST_30/60_DAYS → LAST_30_VISITS，按最近30次消费取数 |

## 验证 SQL

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

-- 2. 确认无散客数据
SELECT COUNT(*) FROM dws.dws_member_project_tag WHERE member_id = 0 OR member_id IS NULL;
-- 期望：0

-- 3. 确认占比计算正确
SELECT site_id, member_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_member_project_tag
WHERE percentage >= 0.25 AND is_tagged = FALSE
LIMIT 10;
-- 期望：0 行

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

## 回滚策略

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