# dws_member_visit_detail 会员来店明细表

> 生成时间：2026-02-03

## 表信息

| 属性 | 值 |
|------|-----|
| Schema | dws |
| 表名 | dws_member_visit_detail |
| 主键 | id |
| 唯一键 | (site_id, member_id, order_settle_id) |
| 数据来源 | dwd_settlement_head + 关联明细表 |
| 更新频率 | 每日增量更新 |
| 说明 | 以"会员+订单"为粒度，记录每次来店消费明细 |

## 字段说明

| 序号 | 字段名 | 类型 | 可空 | 说明 |
|------|--------|------|------|------|
| 1 | id | BIGSERIAL | NO | 自增主键 |
| 2 | site_id | BIGINT | NO | 门店ID |
| 3 | tenant_id | BIGINT | NO | 租户ID |
| 4 | member_id | BIGINT | NO | 会员ID（散客不入此表） |
| 5 | order_settle_id | BIGINT | NO | 结账单ID |
| 6 | visit_date | DATE | NO | 来店日期 |
| 7 | visit_time | TIMESTAMPTZ | YES | 来店时间 |
| 8 | member_nickname | VARCHAR(100) | YES | 会员昵称 |
| 9 | member_mobile | VARCHAR(20) | YES | 手机号 |
| 10 | member_birthday | DATE | YES | 会员生日 |
| 11 | table_id | BIGINT | YES | 台桌ID |
| 12 | table_name | VARCHAR(50) | YES | 台桌名称 |
| 13 | area_name | VARCHAR(50) | YES | 区域名称（原始） |
| 14 | area_category | VARCHAR(20) | YES | 区域分类 |
| 15 | table_fee | NUMERIC(12,2) | NO | 台费 |
| 16 | goods_amount | NUMERIC(12,2) | NO | 商品金额 |
| 17 | assistant_amount | NUMERIC(12,2) | NO | 助教服务金额 |
| 18 | total_consume | NUMERIC(12,2) | NO | 消费总额（基于 `items_sum` 口径，= tc + goods + pd + cx + electricity） |
| 19 | total_discount | NUMERIC(12,2) | NO | 优惠总额 |
| 20 | actual_pay | NUMERIC(12,2) | NO | 实付金额 |
| 21 | cash_pay | NUMERIC(12,2) | NO | 收银实付（= `pay_amount`，与 actual_pay 同值） |
| 22 | balance_pay | NUMERIC(12,2) | NO | 储值卡总支付（= recharge_card_pay + gift_card_pay） |
| 22a | recharge_card_pay | NUMERIC(12,2) | NO | 现金充值卡支付（balance_pay 的子项） |
| 23 | gift_card_pay | NUMERIC(12,2) | NO | 赠送卡支付 |
| 24 | groupbuy_pay | NUMERIC(12,2) | NO | 团购抵消台费金额（= `coupon_amount`） |
| 25 | table_duration_min | INTEGER | NO | 台桌使用时长（分钟，来自台费流水真实秒数） |
| 26 | assistant_duration_min | INTEGER | NO | 助教服务时长（分钟） |
| 27 | assistant_services | JSONB | YES | 助教服务列表 |
| 28 | created_at | TIMESTAMPTZ | NO | 创建时间 |
| 29 | updated_at | TIMESTAMPTZ | NO | 更新时间 |

## 数据来源

### 主表来源：dwd_settlement_head

> ⚠️ `total_consume` 使用 `items_sum` 口径（全时期一致），不使用 `consume_money`（存在三种历史口径混合）。

```sql
SELECT 
    sh.site_id,
    sh.tenant_id,
    sh.member_id,
    sh.order_settle_id,
    DATE(sh.pay_time) AS visit_date,
    sh.create_time AS visit_time,
    -- ⚠️ member_nickname/member_mobile 实际从 dim_member 关联获取（nickname/mobile），非结算头表字段
    dm.nickname AS member_nickname,
    dm.mobile AS member_mobile,
    sh.table_id,
    sh.table_charge_money AS table_fee,
    sh.goods_money AS goods_amount,
    sh.assistant_pd_money + sh.assistant_cx_money AS assistant_amount,
    -- ✅ 使用 items_sum 口径，不使用 consume_money
    sh.table_charge_money + sh.goods_money + sh.assistant_pd_money
        + sh.assistant_cx_money + sh.electricity_money AS total_consume,
    sh.member_discount_amount + sh.adjust_amount + sh.rounding_amount AS total_discount,
    sh.pay_amount AS actual_pay,
    sh.balance_amount AS balance_pay,
    sh.recharge_card_amount AS recharge_card_pay,
    sh.gift_card_amount AS gift_card_pay
FROM dwd.dwd_settlement_head sh
JOIN dwd.dim_member dm ON sh.tenant_member_id = dm.tenant_member_id AND dm.scd2_is_current = 1
WHERE sh.member_id IS NOT NULL AND sh.member_id != 0
  AND sh.settle_type IN (1, 3);  -- 仅台桌结账+商城订单，排除退货(6)/退款(7)
```

### 助教服务明细：dwd_assistant_service_log
```sql
-- 聚合为JSONB格式
SELECT 
    order_settle_id,
    jsonb_agg(jsonb_build_object(
        'assistant_id', site_assistant_id,
        'nickname', nickname,
        'duration_min', income_seconds / 60,
        'amount', ledger_amount
    )) AS assistant_services
FROM dwd.dwd_assistant_service_log
WHERE is_delete = 0
GROUP BY order_settle_id;
```

### 台费时长：dwd_table_fee_log
```sql
SELECT
    order_settle_id,
    SUM(COALESCE(real_table_use_seconds, 0)) AS table_use_seconds
FROM dwd.dwd_table_fee_log
WHERE COALESCE(is_delete, 0) = 0
GROUP BY order_settle_id;
```

## 使用说明

**assistant_services JSON格式**
```json
[
    {"assistant_id": 123, "nickname": "小燕", "duration_min": 60, "amount": 108.00},
    {"assistant_id": 456, "nickname": "小明", "duration_min": 30, "amount": 54.00}
]
```

**区域分类映射**
```sql
-- 通过cfg_area_category映射
area_category = COALESCE(
    (SELECT category_name FROM dws.cfg_area_category 
     WHERE source_area_name = dim_table.site_table_area_name),
    '其他'
)
```

## 可回溯性

| 项目 | 说明 |
|------|------|
| 可回溯 | ✅ 完全可回溯 |
| 数据范围 | 2025-07-16 ~ 至今 |
| 依赖表 | dwd_settlement_head, dwd_assistant_service_log, dwd_table_fee_log, dim_table, dim_member, cfg_area_category |
