Template

Files

Neo a3f4d04335 Updata2

2026-02-04 21:39:01 +08:00

65 KiB

Raw Permalink Blame History

DWS 数据层实施任务计划 v2.0

版本: 2.0
创建日期: 2026-02-01
更新日期: 2026-02-01
项目路径: C:\dev\LLTQ\ETL\feiqiu-ETL
目标: 在已完成的 DWD 层数据基础上，完成 DWS（数据汇总层）的数据库设计与 ETL 实现

一、项目背景与现状分析

1.1 已有基础设施

层级	状态	Schema	说明
ODS（操作数据层）	✅ 完成	`billiards_ods`	原始数据落地，20+ 张表
DWD（明细数据层）	✅ 完成	`billiards_dwd`	维度表 9 张 + 事实表 12 张
DWS（汇总数据层）	🔄 部分完成	`billiards_dws`	仅有 `dws_order_summary` 1 张

1.2 DWD 核心表与关键字段

1.2.1 助教服务相关

dwd_assistant_service_log - 助教服务流水表

字段	说明	枚举值
`skill_id`	技能ID（用于判断课程类型）	2790683529513797=基础课, 2790683529513798=附加课/激励课, 3039912271463941=包厢课
`skill_name`	技能名称	"基础课", "附加课", "包厢课"
`assistant_level`	助教等级	8=助教管理, 10=初级, 20=中级, 30=高级, 40=星级
`order_assistant_type`	服务类型	1=基础课或包厢课, 2=附加课/激励课
`ledger_unit_price`	单价（元/小时）	98/108/118/138/190

1.2.2 会员卡相关

dim_member_card_account - 会员卡账户表

card_type_id	卡类型	说明	分类
2793249295533893	储值卡	充值获得，可抵扣任意费用	现金卡（充值）
2791990152417157	台费卡	充值赠送，仅可抵扣台费	赠送卡
2793266846533445	活动抵用券	充值赠送，不可抵扣助教费	赠送卡
2794699703437125	酒水卡	充值赠送，仅可抵扣酒水饮料食品	赠送卡
2793306611533637	月卡	充值获得，时长卡，仅可抵扣台费	时长卡
2791987095408517	年卡	充值获得，时长卡，仅可抵扣台费	时长卡

1.2.3 台区分布

dim_table - 台桌维度表

台区名称	台桌数	大类/索引
A区	18	台球/打球/中八/追分
B区	15	台球/打球/中八/追分
C区	6	台球/打球/中八/追分
VIP包厢	4	台球/打球/中八(V5为斯诺克)
斯诺克区	4	台球/打球/斯诺克
TV台	1	台球/打球/中八/追分
麻将房	5	麻将/麻将棋牌
M7/M8/666/发财	6	麻将/麻将棋牌
K包/k包活动区/幸会158	8	K包/K歌/KTV
补时长	7	补时长

二、数据口径定义（数据来源矩阵）

2.1 发生额/正价口径

项目	DWD来源	计算公式	说明
台费正价	dwd_settlement_head	`table_charge_money`	结账单中的台费原价
商品正价	dwd_settlement_head	`goods_money`	结账单中的商品原价
助教基础课正价	dwd_settlement_head	`assistant_pd_money`	陪打费用（PD=陪打）
助教激励课正价	dwd_settlement_head	`assistant_cx_money`	促销课费用（CX=促销/超休）
团购台费正价	dwd_groupbuy_redemption	`ledger_amount`	团购券对应的台费正价
发生额合计	dwd_settlement_head	`table_charge_money + goods_money + assistant_pd_money + assistant_cx_money`	各项正价之和

2.2 优惠分类口径

优惠类型	DWD来源	计算公式	说明
团购优惠	dwd_groupbuy_redemption	`ledger_amount - coupon_money`	正价与券面值差额
赠送卡抵扣	dwd_member_balance_change	`SUM(change_amount) WHERE card_type_id IN (台费卡, 酒水卡, 活动抵用券) AND from_type=1`	三类赠送卡消费合计
大客户优惠	dwd_table_fee_log / dwd_store_goods_sale	`adjust_amount`（需抽样分析）	手动调账产生的优惠
会员折扣	dwd_settlement_head	`member_discount_amount`	会员身份折扣
抹零	dwd_settlement_head	`rounding_amount`	抹零金额
其他优惠	待确认	抽样100单分析	订单折扣、台桌折扣、商品折扣、手动优惠的关系
优惠合计	dwd_settlement_head	`consume_money - pay_amount`	消费总额与实付差额

2.3 支付方式口径

支付类型	DWD来源	计算公式	说明
储值卡支付	dwd_settlement_head	`balance_amount + recharge_card_amount`	余额+储值卡
赠送卡支付	dwd_settlement_head	`gift_card_amount`	礼品卡（赠送卡）支付
团购核销	dwd_groupbuy_redemption	`coupon_money`	团购券面值（核销价）
现金/线上支付	dwd_settlement_head	`pay_amount - balance_amount - recharge_card_amount - gift_card_amount - coupon_amount`	扣除卡支付后的实付

2.4 充值与赠送口径

类型	DWD来源	筛选条件	说明
储值卡充值	dwd_recharge_order	`member_card_type_name='储值卡' AND settle_type=5`	现金充值到储值卡
首充金额	dwd_recharge_order	`is_first=1`	首次充值
续费金额	dwd_recharge_order	`is_first=2`	非首次充值
赠送卡新增	dwd_member_balance_change	`card_type_id IN (台费卡,酒水卡,活动抵用券) AND from_type=4`	活动赠送
赠送卡消费	dwd_member_balance_change	`card_type_id IN (...) AND from_type=1 AND change_amount<0`	消费扣款
各类卡余额	dim_member_card_account	`SUM(balance) WHERE scd2_is_current=1 GROUP BY card_type_id`	当前余额快照

2.5 助教业绩口径

指标	DWD来源	筛选条件	说明
基础课时长	dwd_assistant_service_log	`skill_id=2790683529513797 AND is_delete=0`	基础课秒数
附加课时长	dwd_assistant_service_log	`skill_id=2790683529513798 AND is_delete=0`	附加课/激励课秒数
包厢课时长	dwd_assistant_service_log	`skill_id=3039912271463941 AND is_delete=0`	包厢课秒数
有效服务	dwd_assistant_service_log	排除 dwd_assistant_trash_event 中已废除的记录	需LEFT JOIN排除
废除记录	dwd_assistant_trash_event	全量	废除的服务不计入业绩

2.6 SCD2 维度取数说明

重要：历史月份统计需要按业务时间点做 as-of join，而非直接使用 scd2_is_current=1

-- 正确做法：按业务时间获取当时的维度版本
SELECT asl.*, da.level, da.level_name
FROM dwd_assistant_service_log asl
LEFT JOIN dim_assistant da 
  ON asl.site_assistant_id = da.assistant_id
  AND asl.start_use_time >= da.scd2_start_time
  AND asl.start_use_time < COALESCE(da.scd2_end_time, '9999-12-31')
WHERE ...

-- 错误做法：历史月份会套用当前等级
SELECT asl.*, da.level
FROM dwd_assistant_service_log asl
LEFT JOIN dim_assistant da ON ... AND da.scd2_is_current = 1  -- 错误！

三、DWS 表设计

3.1 表清单总览

3.1.1 配置表（4张）

表名	说明	数据来源
`cfg_performance_tier`	绩效档位配置（6档）	初始化+手动维护
`cfg_assistant_level_price`	助教等级定价	初始化+手动维护
`cfg_bonus_rules`	奖金规则配置	初始化+手动维护
`cfg_area_category`	台区分类映射	初始化+手动维护

3.1.2 助教维度表（5张）

表名	说明	更新频率
`dws_assistant_monthly_summary`	月度业绩汇总	每日增量
`dws_assistant_daily_detail`	日度业绩明细	每小时增量
`dws_assistant_customer_stats`	服务客户统计	每日全量
`dws_assistant_salary_calc`	月度工资计算	月初全量
`dws_assistant_recharge_commission`	充值提成记录	手动导入

3.1.3 客户维度表（2张）

表名	说明	更新频率
`dws_member_consumption_summary`	消费情况统计	每日全量
`dws_member_visit_detail`	来店消费明细	每日增量

3.1.4 财务维度表（7张）

表名	说明	更新频率
`dws_finance_daily_summary`	日度财务汇总	每小时增量
`dws_finance_income_structure`	收入结构	每日增量
`dws_finance_discount_detail`	优惠明细	每日增量
`dws_finance_recharge_summary`	充值与预收	每日增量
`dws_finance_expense_summary`	支出结构	手动导入
`dws_assistant_finance_analysis`	助教收支分析	每日增量
`dws_platform_settlement`	平台回款/服务费	手动导入

3.2 配置表 DDL

3.2.1 `cfg_performance_tier` - 绩效档位配置

-- ============================================================
-- 表名: cfg_performance_tier
-- 用途: 助教绩效档位配置表，定义6个档位的阈值与分成规则
-- 业务规则:
--   - 过档后，所有时长按新档位进行计算
--   - 总业绩小时数 = 基础课 + 附加课 + 包厢课
-- 数据来源: 初始化脚本 + 手动维护
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_performance_tier (
    tier_id             SERIAL PRIMARY KEY,                    -- 档位主键
    tier_code           INTEGER NOT NULL UNIQUE,               -- 档位代码：0-5
    tier_name           VARCHAR(50) NOT NULL,                  -- 档位名称
    tier_reason         VARCHAR(100),                          -- 档位设计原因
    min_hours           NUMERIC(10,2) NOT NULL,                -- 最小小时数阈值（含）
    max_hours           NUMERIC(10,2),                         -- 最大小时数阈值（不含），NULL=无上限
    base_deduction      NUMERIC(10,2) NOT NULL,                -- 基础课球房抽成（元/小时）
    bonus_ratio         NUMERIC(5,4) NOT NULL,                 -- 附加课球房抽成比例（如0.50表示50%）
    vacation_days       INTEGER,                               -- 次月休假天数，NULL=休假自由
    effective_from      DATE NOT NULL DEFAULT '2026-01-01',    -- 生效开始日期
    effective_to        DATE DEFAULT '9999-12-31',             -- 生效结束日期
    is_active           BOOLEAN DEFAULT TRUE,                  -- 是否启用
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now()
);

COMMENT ON TABLE billiards_dws.cfg_performance_tier IS '助教绩效档位配置表：定义各档位的业绩阈值与分成规则';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.tier_code IS '档位代码：0=淘汰压力，1=及格档，2=良好档，3=优秀档，4=卓越加速档，5=冠军加速档';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.base_deduction IS '基础课球房抽成金额（元/小时），助教实得=客户单价-此值';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.bonus_ratio IS '附加课球房抽成比例，助教实得=客户支付*(1-此值)';

3.2.2 `cfg_assistant_level_price` - 助教等级定价

-- ============================================================
-- 表名: cfg_assistant_level_price
-- 用途: 助教等级对应的客户收费标准
-- 数据来源: 初始化脚本 + 手动维护
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_assistant_level_price (
    price_id            SERIAL PRIMARY KEY,
    level_code          INTEGER NOT NULL,                      -- 等级代码（对应dim_assistant.assistant_level）
    level_name          VARCHAR(20) NOT NULL,                  -- 等级名称：初级/中级/高级/星级
    base_price          NUMERIC(10,2) NOT NULL,                -- 基础课客户收费（元/小时）
    bonus_price         NUMERIC(10,2) NOT NULL DEFAULT 190,    -- 附加课客户收费（元/小时），统一190
    effective_from      DATE NOT NULL DEFAULT '2026-01-01',
    effective_to        DATE DEFAULT '9999-12-31',
    is_active           BOOLEAN DEFAULT TRUE,
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now(),
    UNIQUE(level_code, effective_from)
);

COMMENT ON TABLE billiards_dws.cfg_assistant_level_price IS '助教等级定价表：定义各等级对客户的收费标准';
COMMENT ON COLUMN billiards_dws.cfg_assistant_level_price.level_code IS '等级代码：8=助教管理，10=初级，20=中级，30=高级，40=星级';

3.2.3 `cfg_bonus_rules` - 奖金规则配置

-- ============================================================
-- 表名: cfg_bonus_rules
-- 用途: 冲刺奖、Top3奖等额外奖金规则配置
-- 业务规则:
--   - 冲刺奖：H>=190得300元，H>=220得800元（取高不叠加）
--   - Top3奖：第1名1000元，第2名600元，第3名400元
-- 数据来源: 初始化脚本 + 手动维护
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_bonus_rules (
    rule_id             SERIAL PRIMARY KEY,
    rule_type           VARCHAR(20) NOT NULL,                  -- 规则类型：SPRINT(冲刺奖)/TOP_RANK(排名奖)
    rule_name           VARCHAR(50) NOT NULL,                  -- 规则名称
    condition_type      VARCHAR(20) NOT NULL,                  -- 条件类型：HOURS_GTE(时长>=)/RANK_EQ(排名=)
    condition_value     NUMERIC(10,2) NOT NULL,                -- 条件值
    bonus_amount        NUMERIC(10,2) NOT NULL,                -- 奖金金额（元）
    priority            INTEGER DEFAULT 0,                     -- 优先级（同类型取高优先级）
    is_stackable        BOOLEAN DEFAULT FALSE,                 -- 是否可叠加
    effective_from      DATE NOT NULL DEFAULT '2026-01-01',
    effective_to        DATE DEFAULT '9999-12-31',
    is_active           BOOLEAN DEFAULT TRUE,
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now()
);

COMMENT ON TABLE billiards_dws.cfg_bonus_rules IS '奖金规则配置表：定义冲刺奖和Top3排名奖';
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.is_stackable IS '是否可叠加：FALSE表示同类型奖金取高不叠加';

3.2.4 `cfg_area_category` - 台区分类映射

-- ============================================================
-- 表名: cfg_area_category
-- 用途: 台区名称到业务大类的映射，用于财务报表分类筛选
-- 数据来源: 基于 dim_table.site_table_area_name 初始化 + 手动维护
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_area_category (
    category_id         SERIAL PRIMARY KEY,
    area_name           VARCHAR(50) NOT NULL UNIQUE,           -- 原始台区名称（来自dim_table）
    category_l1         VARCHAR(30) NOT NULL,                  -- 一级分类：台球/麻将/K包/其他
    category_l2         VARCHAR(30),                           -- 二级分类：大厅/包厢/斯诺克等
    display_name        VARCHAR(50),                           -- 显示名称（用于报表）
    sort_order          INTEGER DEFAULT 0,                     -- 排序序号
    is_active           BOOLEAN DEFAULT TRUE,
    created_at          TIMESTAMPTZ DEFAULT now(),
    updated_at          TIMESTAMPTZ DEFAULT now()
);

COMMENT ON TABLE billiards_dws.cfg_area_category IS '台区分类映射表：将自由文本的台区名称映射到规范分类';

3.3 助教维度表 DDL

3.3.1 `dws_assistant_monthly_summary` - 月度业绩汇总

-- ============================================================
-- 表名: dws_assistant_monthly_summary
-- 用途: 助教月度业绩汇总表，核心统计表
-- 更新策略: 每日增量更新当月数据，月初全量刷新上月
-- 数据来源矩阵:
--   base_service_* → dwd_assistant_service_log WHERE skill_id=2790683529513797
--   bonus_service_* → dwd_assistant_service_log WHERE skill_id=2790683529513798
--   room_service_* → dwd_assistant_service_log WHERE skill_id=3039912271463941
--   trashed_* → dwd_assistant_trash_event
--   tier_* → cfg_performance_tier
--   assistant_level → dim_assistant（按业务时间as-of join）
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_monthly_summary (
    summary_id              BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,               -- 门店ID
    assistant_id            BIGINT NOT NULL,               -- 助教ID（dim_assistant.assistant_id）
    stat_month              DATE NOT NULL,                 -- 统计月份（每月1日）
    
    -- ========== 基础课统计 ==========
    base_service_count      INTEGER DEFAULT 0,             -- 基础课服务次数
    base_service_seconds    INTEGER DEFAULT 0,             -- 基础课服务秒数
    base_service_hours      NUMERIC(10,4) DEFAULT 0,       -- 基础课服务小时数（秒数/3600，精确到分钟）
    base_income_original    NUMERIC(12,2) DEFAULT 0,       -- 基础课原始收入（客户支付）
    base_income_deduction   NUMERIC(12,2) DEFAULT 0,       -- 基础课球房抽成
    base_income_actual      NUMERIC(12,2) DEFAULT 0,       -- 基础课助教实得
    
    -- ========== 附加课/激励课统计 ==========
    bonus_service_count     INTEGER DEFAULT 0,             -- 附加课服务次数
    bonus_service_seconds   INTEGER DEFAULT 0,             -- 附加课服务秒数
    bonus_service_hours     NUMERIC(10,4) DEFAULT 0,       -- 附加课服务小时数
    bonus_income_original   NUMERIC(12,2) DEFAULT 0,       -- 附加课原始收入
    bonus_income_ratio_ded  NUMERIC(12,2) DEFAULT 0,       -- 附加课球房按比例抽成
    bonus_income_actual     NUMERIC(12,2) DEFAULT 0,       -- 附加课助教实得
    
    -- ========== 包厢课统计 ==========
    room_service_count      INTEGER DEFAULT 0,             -- 包厢课服务次数
    room_service_seconds    INTEGER DEFAULT 0,             -- 包厢课服务秒数
    room_service_hours      NUMERIC(10,4) DEFAULT 0,       -- 包厢课服务小时数
    room_income_original    NUMERIC(12,2) DEFAULT 0,       -- 包厢课原始收入
    room_income_actual      NUMERIC(12,2) DEFAULT 0,       -- 包厢课助教实得
    
    -- ========== 汇总统计 ==========
    total_service_count     INTEGER DEFAULT 0,             -- 总服务次数
    total_service_hours     NUMERIC(10,4) DEFAULT 0,       -- 总服务小时数（用于档位计算）
    total_income_original   NUMERIC(12,2) DEFAULT 0,       -- 总原始收入
    total_income_actual     NUMERIC(12,2) DEFAULT 0,       -- 总助教实得
    
    -- ========== 废除影响 ==========
    trashed_service_count   INTEGER DEFAULT 0,             -- 被废除服务次数
    trashed_service_seconds INTEGER DEFAULT 0,             -- 被废除服务秒数
    trashed_amount          NUMERIC(12,2) DEFAULT 0,       -- 被废除金额
    
    -- ========== 档位信息（根据total_service_hours计算）==========
    tier_code               INTEGER,                       -- 当前档位代码（0-5）
    tier_name               VARCHAR(50),                   -- 档位名称
    tier_base_deduction     NUMERIC(10,2),                 -- 档位基础课抽成
    tier_bonus_ratio        NUMERIC(5,4),                  -- 档位附加课抽成比例
    
    -- ========== 奖金 ==========
    sprint_bonus            NUMERIC(10,2) DEFAULT 0,       -- 冲刺奖金额
    rank_bonus              NUMERIC(10,2) DEFAULT 0,       -- 排名奖金额
    recharge_commission     NUMERIC(10,2) DEFAULT 0,       -- 充值提成（关联dws_assistant_recharge_commission）
    other_bonus             NUMERIC(10,2) DEFAULT 0,       -- 其他奖金
    total_bonus             NUMERIC(10,2) DEFAULT 0,       -- 奖金合计
    
    -- ========== 最终工资 ==========
    final_salary            NUMERIC(12,2) DEFAULT 0,       -- 最终应发工资
    
    -- ========== 助教快照信息（按stat_month首日as-of join）==========
    assistant_level         INTEGER,                       -- 助教等级（8/10/20/30/40）
    assistant_level_name    VARCHAR(20),                   -- 等级名称
    assistant_level_price   NUMERIC(10,2),                 -- 等级对应客户单价
    entry_date              DATE,                          -- 入职日期
    is_new_employee         BOOLEAN DEFAULT FALSE,         -- 是否本月新入职
    work_days_in_month      INTEGER,                       -- 本月在职天数
    
    -- ========== 排名信息 ==========
    monthly_rank            INTEGER,                       -- 当月排名（按total_service_hours）
    
    -- ========== 元数据 ==========
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, assistant_id, stat_month)
);

-- 索引优化
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_site_month 
    ON billiards_dws.dws_assistant_monthly_summary(site_id, stat_month);
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_assistant 
    ON billiards_dws.dws_assistant_monthly_summary(assistant_id, stat_month);
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_month 
    ON billiards_dws.dws_assistant_monthly_summary(stat_month);

COMMENT ON TABLE billiards_dws.dws_assistant_monthly_summary IS '助教月度业绩汇总表：统计每位助教每月的业绩、收入、档位、工资';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.total_service_hours IS '总服务小时数=基础课+附加课+包厢课，用于档位计算';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.assistant_level IS '按stat_month首日通过as-of join获取的历史等级，非当前等级';

3.3.2 `dws_assistant_daily_detail` - 日度业绩明细

-- ============================================================
-- 表名: dws_assistant_daily_detail
-- 用途: 助教日度业绩明细，用于月中进度追踪
-- 更新策略: 每小时增量更新
-- 数据来源: dwd_assistant_service_log + dwd_assistant_trash_event
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_daily_detail (
    detail_id               BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    assistant_id            BIGINT NOT NULL,
    stat_date               DATE NOT NULL,                 -- 统计日期
    
    -- 基础课日统计
    base_service_count      INTEGER DEFAULT 0,
    base_service_seconds    INTEGER DEFAULT 0,
    base_service_hours      NUMERIC(10,4) DEFAULT 0,
    base_income_original    NUMERIC(12,2) DEFAULT 0,
    
    -- 附加课日统计
    bonus_service_count     INTEGER DEFAULT 0,
    bonus_service_seconds   INTEGER DEFAULT 0,
    bonus_service_hours     NUMERIC(10,4) DEFAULT 0,
    bonus_income_original   NUMERIC(12,2) DEFAULT 0,
    
    -- 包厢课日统计
    room_service_count      INTEGER DEFAULT 0,
    room_service_seconds    INTEGER DEFAULT 0,
    room_service_hours      NUMERIC(10,4) DEFAULT 0,
    room_income_original    NUMERIC(12,2) DEFAULT 0,
    
    -- 汇总
    total_service_count     INTEGER DEFAULT 0,
    total_service_hours     NUMERIC(10,4) DEFAULT 0,
    total_income_original   NUMERIC(12,2) DEFAULT 0,
    
    -- 废除
    trashed_count           INTEGER DEFAULT 0,
    trashed_seconds         INTEGER DEFAULT 0,
    trashed_amount          NUMERIC(12,2) DEFAULT 0,
    
    -- 累计（当月至今）
    mtd_total_hours         NUMERIC(10,4) DEFAULT 0,       -- Month-To-Date 累计小时数
    mtd_tier_code           INTEGER,                       -- 累计后档位
    
    -- 服务客户统计
    unique_customers        INTEGER DEFAULT 0,             -- 当日服务不同客户数
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, assistant_id, stat_date)
);

CREATE INDEX IF NOT EXISTS idx_dws_asst_daily_site_date 
    ON billiards_dws.dws_assistant_daily_detail(site_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_asst_daily_assistant 
    ON billiards_dws.dws_assistant_daily_detail(assistant_id, stat_date);

COMMENT ON TABLE billiards_dws.dws_assistant_daily_detail IS '助教日度业绩明细表：便于实时追踪月中业绩进度';

3.3.3 `dws_assistant_customer_stats` - 服务客户统计

-- ============================================================
-- 表名: dws_assistant_customer_stats
-- 用途: 助教服务客户统计（多时间窗口）
-- 更新策略: 每日凌晨全量刷新
-- 数据来源: dwd_assistant_service_log
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_customer_stats (
    stats_id                BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    assistant_id            BIGINT NOT NULL,
    stat_date               DATE NOT NULL,                 -- 统计基准日期
    window_days             INTEGER NOT NULL,              -- 时间窗口天数：7/10/15/30/60/90
    
    -- 客户统计
    unique_customers        INTEGER DEFAULT 0,             -- 服务不同客户数
    total_service_count     INTEGER DEFAULT 0,             -- 总服务次数
    total_service_hours     NUMERIC(10,2) DEFAULT 0,       -- 总服务小时数
    
    -- 按课程类型
    base_customers          INTEGER DEFAULT 0,             -- 基础课客户数
    base_service_count      INTEGER DEFAULT 0,
    bonus_customers         INTEGER DEFAULT 0,             -- 附加课客户数
    bonus_service_count     INTEGER DEFAULT 0,
    
    -- 收入
    total_income            NUMERIC(12,2) DEFAULT 0,
    
    -- 客户复购
    repeat_customers        INTEGER DEFAULT 0,             -- 复购客户数（服务>=2次）
    repeat_rate             NUMERIC(5,4) DEFAULT 0,        -- 复购率
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, assistant_id, stat_date, window_days)
);

CREATE INDEX IF NOT EXISTS idx_dws_asst_cust_window 
    ON billiards_dws.dws_assistant_customer_stats(assistant_id, stat_date, window_days);

COMMENT ON TABLE billiards_dws.dws_assistant_customer_stats IS '助教服务客户统计表：支持7/10/15/30/60/90天多时间窗口';

3.3.4 `dws_assistant_salary_calc` - 月度工资计算

-- ============================================================
-- 表名: dws_assistant_salary_calc
-- 用途: 助教月度工资计算详情，记录计算过程便于核对
-- 更新策略: 月初全量计算上月工资
-- 数据来源: dws_assistant_monthly_summary + cfg_* 配置表
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_salary_calc (
    calc_id                 BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    assistant_id            BIGINT NOT NULL,
    stat_month              DATE NOT NULL,                 -- 统计月份
    
    -- ========== 输入参数 ==========
    assistant_level         INTEGER,                       -- 助教等级
    level_base_price        NUMERIC(10,2),                 -- 等级基础课单价
    tier_code               INTEGER,                       -- 最终档位
    tier_base_deduction     NUMERIC(10,2),                 -- 档位抽成
    tier_bonus_ratio        NUMERIC(5,4),                  -- 附加课抽成比例
    
    -- ========== 时长输入 ==========
    base_hours              NUMERIC(10,4),                 -- 基础课小时数
    bonus_hours             NUMERIC(10,4),                 -- 附加课小时数
    room_hours              NUMERIC(10,4),                 -- 包厢课小时数
    total_hours             NUMERIC(10,4),                 -- 总小时数
    
    -- ========== 计算过程 ==========
    -- 基础课收入 = base_hours * (level_base_price - tier_base_deduction)
    base_calc_unit_income   NUMERIC(10,2),                 -- 基础课单小时助教收入
    base_calc_income        NUMERIC(12,2),                 -- 基础课收入
    
    -- 附加课收入 = bonus_原始收入 * (1 - tier_bonus_ratio)
    bonus_original_income   NUMERIC(12,2),                 -- 附加课原始收入
    bonus_calc_income       NUMERIC(12,2),                 -- 附加课助教收入
    
    -- 包厢课收入
    room_calc_income        NUMERIC(12,2),                 -- 包厢课收入
    
    -- ========== 奖金计算 ==========
    sprint_rule_matched     VARCHAR(50),                   -- 匹配的冲刺奖规则
    sprint_bonus            NUMERIC(10,2) DEFAULT 0,       -- 冲刺奖
    rank_value              INTEGER,                       -- 排名
    rank_rule_matched       VARCHAR(50),                   -- 匹配的排名奖规则
    rank_bonus              NUMERIC(10,2) DEFAULT 0,       -- 排名奖
    recharge_commission     NUMERIC(10,2) DEFAULT 0,       -- 充值提成
    other_bonus             NUMERIC(10,2) DEFAULT 0,       -- 其他奖金
    
    -- ========== 新入职折算 ==========
    is_new_employee         BOOLEAN DEFAULT FALSE,         -- 是否新入职
    entry_date              DATE,                          -- 入职日期
    work_days               INTEGER,                       -- 在职天数
    projected_hours         NUMERIC(10,4),                 -- 折算后小时数（用于定档）
    max_tier_limit          INTEGER,                       -- 最高档位限制（25日后入职限3档）
    
    -- ========== 最终结果 ==========
    total_service_income    NUMERIC(12,2),                 -- 服务收入合计
    total_bonus             NUMERIC(10,2),                 -- 奖金合计
    final_salary            NUMERIC(12,2),                 -- 最终应发工资
    
    -- ========== 元数据 ==========
    calc_time               TIMESTAMPTZ DEFAULT now(),     -- 计算时间
    calc_version            INTEGER DEFAULT 1,             -- 计算版本
    remark                  TEXT,                          -- 备注
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, assistant_id, stat_month, calc_version)
);

COMMENT ON TABLE billiards_dws.dws_assistant_salary_calc IS '助教月度工资计算详情表：记录计算过程便于核对和审计';

3.3.5 `dws_assistant_recharge_commission` - 充值提成记录

-- ============================================================
-- 表名: dws_assistant_recharge_commission
-- 用途: 助教充值提成记录（手动导入）
-- 更新策略: 通过Excel等方式手动导入
-- 数据来源: 外部Excel文件
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_recharge_commission (
    commission_id           BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    assistant_id            BIGINT NOT NULL,               -- 助教ID
    stat_month              DATE NOT NULL,                 -- 统计月份
    
    -- 充值关联
    recharge_order_id       BIGINT,                        -- 充值订单ID（如有）
    member_card_id          BIGINT,                        -- 储值卡ID
    member_name             VARCHAR(100),                  -- 会员名称
    member_phone            VARCHAR(50),                   -- 会员电话
    
    -- 充值信息
    recharge_time           TIMESTAMPTZ,                   -- 充值时间
    recharge_amount         NUMERIC(12,2) NOT NULL,        -- 充值金额
    commission_rate         NUMERIC(5,4),                  -- 提成比例
    commission_amount       NUMERIC(10,2) NOT NULL,        -- 提成金额
    
    -- 导入信息
    import_batch            VARCHAR(50),                   -- 导入批次号
    import_time             TIMESTAMPTZ DEFAULT now(),     -- 导入时间
    import_source           VARCHAR(100),                  -- 来源文件名
    remark                  TEXT,
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_dws_asst_recharge_month 
    ON billiards_dws.dws_assistant_recharge_commission(assistant_id, stat_month);

COMMENT ON TABLE billiards_dws.dws_assistant_recharge_commission IS '助教充值提成记录表：通过Excel等方式手动导入';

3.4 客户维度表 DDL

3.4.1 `dws_member_consumption_summary` - 消费情况统计

-- ============================================================
-- 表名: dws_member_consumption_summary
-- 用途: 会员消费情况统计（多时间窗口）
-- 更新策略: 每日凌晨全量刷新
-- 数据来源矩阵:
--   visit_count → dwd_settlement_head（按order_settle_id去重）
--   table_fee_* → dwd_table_fee_log
--   assistant_* → dwd_assistant_service_log
--   goods_* → dwd_store_goods_sale
--   金额字段 → dwd_settlement_head
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_member_consumption_summary (
    summary_id              BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    member_id               BIGINT NOT NULL,               -- dim_member.member_id
    stat_date               DATE NOT NULL,                 -- 统计基准日期
    window_days             INTEGER NOT NULL,              -- 7/10/15/30/60/90
    
    -- 到店统计
    visit_count             INTEGER DEFAULT 0,             -- 到店次数（结账单数）
    visit_days              INTEGER DEFAULT 0,             -- 到店天数
    
    -- 台费消费
    table_fee_count         INTEGER DEFAULT 0,             -- 开台次数
    table_fee_seconds       INTEGER DEFAULT 0,             -- 台费时长（秒）
    table_fee_hours         NUMERIC(10,2) DEFAULT 0,       -- 台费小时数
    table_fee_amount        NUMERIC(12,2) DEFAULT 0,       -- 台费金额（正价）
    table_fee_actual        NUMERIC(12,2) DEFAULT 0,       -- 台费实付
    
    -- 助教服务
    assistant_service_count INTEGER DEFAULT 0,             -- 助教服务次数
    assistant_service_hours NUMERIC(10,2) DEFAULT 0,       -- 助教服务小时数
    assistant_service_amt   NUMERIC(12,2) DEFAULT 0,       -- 助教服务金额
    unique_assistants       INTEGER DEFAULT 0,             -- 服务过的不同助教数
    
    -- 商品消费
    goods_order_count       INTEGER DEFAULT 0,             -- 商品订单数
    goods_item_count        INTEGER DEFAULT 0,             -- 商品项数
    goods_amount            NUMERIC(12,2) DEFAULT 0,       -- 商品金额
    
    -- 汇总金额（来源：dwd_settlement_head）
    total_consume_money     NUMERIC(12,2) DEFAULT 0,       -- 消费总额（consume_money）
    total_discount_amount   NUMERIC(12,2) DEFAULT 0,       -- 优惠总额
    total_pay_amount        NUMERIC(12,2) DEFAULT 0,       -- 实付总额（pay_amount）
    
    -- 支付方式
    cash_payment            NUMERIC(12,2) DEFAULT 0,       -- 现金/线上支付
    stored_card_payment     NUMERIC(12,2) DEFAULT 0,       -- 储值卡支付
    gift_card_payment       NUMERIC(12,2) DEFAULT 0,       -- 赠送卡支付
    coupon_payment          NUMERIC(12,2) DEFAULT 0,       -- 团购券支付
    
    -- 客户价值指标
    avg_visit_amount        NUMERIC(10,2) DEFAULT 0,       -- 单次到店平均消费
    avg_visit_duration_min  INTEGER DEFAULT 0,             -- 单次平均停留时长（分钟）
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, member_id, stat_date, window_days)
);

CREATE INDEX IF NOT EXISTS idx_dws_member_cons_window 
    ON billiards_dws.dws_member_consumption_summary(member_id, stat_date, window_days);
CREATE INDEX IF NOT EXISTS idx_dws_member_cons_site 
    ON billiards_dws.dws_member_consumption_summary(site_id, stat_date, window_days);

COMMENT ON TABLE billiards_dws.dws_member_consumption_summary IS '会员消费汇总表：支持7/10/15/30/60/90天多时间窗口';

3.4.2 `dws_member_visit_detail` - 来店消费明细

-- ============================================================
-- 表名: dws_member_visit_detail
-- 用途: 会员来店消费明细表，记录每次到店的详细消费
-- 更新策略: 每日增量更新
-- 数据来源: dwd_settlement_head + 各明细表JOIN
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_member_visit_detail (
    visit_id                BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    member_id               BIGINT NOT NULL,
    order_settle_id         BIGINT NOT NULL,               -- 结账单ID
    order_trade_no          BIGINT,                        -- 订单号
    
    -- 时间信息
    visit_date              DATE NOT NULL,                 -- 到店日期
    start_time              TIMESTAMPTZ,                   -- 开台时间
    end_time                TIMESTAMPTZ,                   -- 结账时间
    duration_minutes        INTEGER,                       -- 停留时长（分钟）
    
    -- 台桌信息
    table_id                BIGINT,                        -- 台桌ID
    table_name              VARCHAR(50),                   -- 台桌名称
    area_name               VARCHAR(50),                   -- 台区名称
    area_category           VARCHAR(30),                   -- 台区分类（映射后）
    
    -- 台费明细
    table_fee_seconds       INTEGER DEFAULT 0,
    table_fee_amount        NUMERIC(12,2) DEFAULT 0,       -- 台费正价
    table_fee_actual        NUMERIC(12,2) DEFAULT 0,       -- 台费实付
    
    -- 助教服务明细
    assistant_count         INTEGER DEFAULT 0,             -- 助教服务人次
    assistant_names         TEXT,                          -- 助教姓名列表（逗号分隔）
    assistant_hours         NUMERIC(10,2) DEFAULT 0,       -- 助教总时长
    assistant_amount        NUMERIC(12,2) DEFAULT 0,       -- 助教费用
    
    -- 商品明细
    goods_count             INTEGER DEFAULT 0,             -- 商品种类数
    goods_names             TEXT,                          -- 商品名称列表（Top5）
    goods_amount            NUMERIC(12,2) DEFAULT 0,       -- 商品金额
    
    -- 金额汇总
    consume_money           NUMERIC(12,2) DEFAULT 0,       -- 消费总额
    discount_amount         NUMERIC(12,2) DEFAULT 0,       -- 优惠总额
    pay_amount              NUMERIC(12,2) DEFAULT 0,       -- 实付金额
    
    -- 支付方式
    payment_method_desc     VARCHAR(100),                  -- 支付方式描述
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, member_id, order_settle_id)
);

CREATE INDEX IF NOT EXISTS idx_dws_member_visit_member 
    ON billiards_dws.dws_member_visit_detail(member_id, visit_date);
CREATE INDEX IF NOT EXISTS idx_dws_member_visit_date 
    ON billiards_dws.dws_member_visit_detail(site_id, visit_date);

COMMENT ON TABLE billiards_dws.dws_member_visit_detail IS '会员来店消费明细表：记录每次到店的详细消费情况';

3.5 财务维度表 DDL

3.5.1 `dws_finance_daily_summary` - 日度财务汇总

-- ============================================================
-- 表名: dws_finance_daily_summary
-- 用途: 门店日度财务汇总（核心财务报表数据源）
-- 更新策略: 每小时增量更新
-- 数据来源矩阵:
--   gross_* → dwd_settlement_head (table_charge_money, goods_money, assistant_pd_money, assistant_cx_money)
--   discount_* → dwd_settlement_head + dwd_member_balance_change + dwd_groupbuy_redemption
--   payment_* → dwd_settlement_head (balance_amount, recharge_card_amount, gift_card_amount, coupon_amount)
--   recharge_* → dwd_recharge_order
--   refund_* → dwd_refund
--   stored_card_* → dwd_member_balance_change WHERE card_type_id=储值卡
--   gift_card_* → dwd_member_balance_change WHERE card_type_id IN (台费卡,酒水卡,活动抵用券)
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_daily_summary (
    summary_id              BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    stat_date               DATE NOT NULL,
    
    -- ========== 经营链：发生额 → 优惠 → 确认收入 ==========
    -- 发生额（正价）- 来源：dwd_settlement_head
    gross_revenue           NUMERIC(14,2) DEFAULT 0,       -- 发生额/正价总计
    gross_table_fee         NUMERIC(14,2) DEFAULT 0,       -- 台费发生额 ← table_charge_money
    gross_assistant_base    NUMERIC(14,2) DEFAULT 0,       -- 助教基础课发生额 ← assistant_pd_money
    gross_assistant_bonus   NUMERIC(14,2) DEFAULT 0,       -- 助教激励课发生额 ← assistant_cx_money
    gross_goods             NUMERIC(14,2) DEFAULT 0,       -- 食品酒水发生额 ← goods_money
    
    -- 优惠明细
    discount_total          NUMERIC(14,2) DEFAULT 0,       -- 优惠总额 = gross_revenue - confirmed_revenue
    discount_groupbuy       NUMERIC(14,2) DEFAULT 0,       -- 团购优惠 ← dwd_groupbuy_redemption(ledger_amount - coupon_money)
    discount_vip            NUMERIC(14,2) DEFAULT 0,       -- 大客户优惠（预留，需抽样分析）
    discount_gift_card      NUMERIC(14,2) DEFAULT 0,       -- 赠送卡抵扣 ← dwd_member_balance_change(台费卡+酒水卡+活动抵用券消费)
    discount_member         NUMERIC(14,2) DEFAULT 0,       -- 会员折扣 ← member_discount_amount
    discount_manual         NUMERIC(14,2) DEFAULT 0,       -- 手动调整 ← adjust_amount
    discount_free           NUMERIC(14,2) DEFAULT 0,       -- 免单（预留）
    discount_rounding       NUMERIC(14,2) DEFAULT 0,       -- 抹零 ← rounding_amount
    
    -- 成交/确认收入
    confirmed_revenue       NUMERIC(14,2) DEFAULT 0,       -- 成交/确认收入（不含充值）← pay_amount汇总
    
    -- ========== 支付方式构成 ==========
    payment_stored_card     NUMERIC(14,2) DEFAULT 0,       -- 储值卡结算冲销 ← balance_amount + recharge_card_amount
    payment_gift_card       NUMERIC(14,2) DEFAULT 0,       -- 赠送卡支付 ← gift_card_amount
    payment_cash_online     NUMERIC(14,2) DEFAULT 0,       -- 现金/线上支付 = pay_amount - 卡支付
    payment_groupbuy_deal   NUMERIC(14,2) DEFAULT 0,       -- 团购核销确认收入 ← dwd_groupbuy_redemption.coupon_money
    
    -- ========== 现金流 ==========
    -- 现金流入
    cash_inflow_consume     NUMERIC(14,2) DEFAULT 0,       -- 消费现金流入（现金+线上-退款）
    cash_inflow_recharge    NUMERIC(14,2) DEFAULT 0,       -- 充值到账 ← dwd_recharge_order.pay_amount
    cash_inflow_platform    NUMERIC(14,2) DEFAULT 0,       -- 平台回款（预留，手动导入）
    cash_inflow_total       NUMERIC(14,2) DEFAULT 0,       -- 现金流入合计
    
    -- 现金支出（预留，通过 dws_finance_expense_summary 手动导入）
    cash_outflow_total      NUMERIC(14,2) DEFAULT 0,       -- 现金支出合计
    
    -- 现金结余
    cash_balance            NUMERIC(14,2) DEFAULT 0,       -- 现金结余 = inflow - outflow
    cash_balance_rate       NUMERIC(5,4) DEFAULT 0,        -- 结余率 = balance / inflow
    
    -- ========== 充值与预收 ==========
    recharge_first          NUMERIC(14,2) DEFAULT 0,       -- 首充金额 ← is_first=1
    recharge_renew          NUMERIC(14,2) DEFAULT 0,       -- 续费金额 ← is_first=2
    recharge_total          NUMERIC(14,2) DEFAULT 0,       -- 充值合计
    recharge_count          INTEGER DEFAULT 0,             -- 充值笔数
    
    -- 储值卡（card_type_id=2793249295533893）
    stored_card_new         NUMERIC(14,2) DEFAULT 0,       -- 储值卡充值（from_type=3）
    stored_card_consume     NUMERIC(14,2) DEFAULT 0,       -- 储值卡消耗（from_type=1，change_amount<0）
    stored_card_balance_eod NUMERIC(14,2) DEFAULT 0,       -- 储值卡余额（截止当日）
    
    -- 赠送卡（台费卡+酒水卡+活动抵用券）
    gift_card_new           NUMERIC(14,2) DEFAULT 0,       -- 赠送卡新增（from_type=4）
    gift_card_consume       NUMERIC(14,2) DEFAULT 0,       -- 赠送卡消耗
    gift_card_balance_eod   NUMERIC(14,2) DEFAULT 0,       -- 赠送卡余额
    
    -- 细分赠送卡
    gift_card_table_new     NUMERIC(14,2) DEFAULT 0,       -- 台费卡新增
    gift_card_table_consume NUMERIC(14,2) DEFAULT 0,       -- 台费卡消耗
    gift_card_table_balance NUMERIC(14,2) DEFAULT 0,       -- 台费卡余额
    gift_card_drink_new     NUMERIC(14,2) DEFAULT 0,       -- 酒水卡新增
    gift_card_drink_consume NUMERIC(14,2) DEFAULT 0,       -- 酒水卡消耗
    gift_card_drink_balance NUMERIC(14,2) DEFAULT 0,       -- 酒水卡余额
    gift_card_coupon_new    NUMERIC(14,2) DEFAULT 0,       -- 活动抵用券新增
    gift_card_coupon_consume NUMERIC(14,2) DEFAULT 0,      -- 活动抵用券消耗
    gift_card_coupon_balance NUMERIC(14,2) DEFAULT 0,      -- 活动抵用券余额
    
    -- ========== 订单统计 ==========
    order_count             INTEGER DEFAULT 0,             -- 订单数
    order_avg_amount        NUMERIC(10,2) DEFAULT 0,       -- 客单价
    member_order_count      INTEGER DEFAULT 0,             -- 会员订单数
    member_order_rate       NUMERIC(5,4) DEFAULT 0,        -- 会员订单占比
    
    -- ========== 退款统计 ==========
    refund_count            INTEGER DEFAULT 0,             -- 退款笔数
    refund_amount           NUMERIC(14,2) DEFAULT 0,       -- 退款金额
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, stat_date)
);

CREATE INDEX IF NOT EXISTS idx_dws_finance_daily_site_date 
    ON billiards_dws.dws_finance_daily_summary(site_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_finance_daily_date 
    ON billiards_dws.dws_finance_daily_summary(stat_date);

COMMENT ON TABLE billiards_dws.dws_finance_daily_summary IS '门店日度财务汇总表：核心财务报表数据源';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.gross_revenue IS '发生额/正价：table_charge_money+goods_money+assistant_pd_money+assistant_cx_money';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.confirmed_revenue IS '成交/确认收入：扣除优惠后的营业收入，不含充值';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.discount_gift_card IS '赠送卡抵扣：台费卡+酒水卡+活动抵用券的消费金额';

3.5.2 `dws_finance_income_structure` - 收入结构

-- ============================================================
-- 表名: dws_finance_income_structure
-- 用途: 收入结构按区域/类型细分
-- 更新策略: 每日增量更新
-- 数据来源: dwd_table_fee_log + dwd_assistant_service_log + dwd_store_goods_sale
--           JOIN cfg_area_category 获取标准分类
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_income_structure (
    structure_id            BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    stat_date               DATE NOT NULL,
    
    -- 分类维度
    income_category         VARCHAR(20) NOT NULL,          -- 收入大类：TABLE/ASSISTANT_BASE/ASSISTANT_BONUS/GOODS
    area_name               VARCHAR(50),                   -- 原始台区名称（dim_table.site_table_area_name）
    area_category_l1        VARCHAR(30),                   -- 一级分类（cfg_area_category.category_l1）
    area_category_l2        VARCHAR(30),                   -- 二级分类
    
    -- 金额（三列式）
    gross_amount            NUMERIC(14,2) DEFAULT 0,       -- 发生额
    discount_amount         NUMERIC(14,2) DEFAULT 0,       -- 优惠金额
    confirmed_amount        NUMERIC(14,2) DEFAULT 0,       -- 确认收入
    
    -- 数量/时长
    item_count              INTEGER DEFAULT 0,             -- 项目数/次数
    duration_seconds        INTEGER DEFAULT 0,             -- 时长（秒）
    duration_hours          NUMERIC(10,2) DEFAULT 0,       -- 时长（小时）
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, stat_date, income_category, COALESCE(area_name, ''))
);

CREATE INDEX IF NOT EXISTS idx_dws_finance_structure_date 
    ON billiards_dws.dws_finance_income_structure(site_id, stat_date);

COMMENT ON TABLE billiards_dws.dws_finance_income_structure IS '收入结构表：按大类和区域细分的收入明细';

3.5.3 `dws_finance_discount_detail` - 优惠明细

-- ============================================================
-- 表名: dws_finance_discount_detail
-- 用途: 优惠类型明细统计
-- 更新策略: 每日增量更新
-- 数据来源矩阵:
--   GROUPBUY → dwd_groupbuy_redemption
--   GIFT_CARD_* → dwd_member_balance_change
--   MEMBER_DISCOUNT → dwd_settlement_head.member_discount_amount
--   MANUAL_ADJUST → dwd_settlement_head.adjust_amount
--   ROUNDING → dwd_settlement_head.rounding_amount
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_discount_detail (
    detail_id               BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    stat_date               DATE NOT NULL,
    
    -- 优惠类型
    discount_type           VARCHAR(30) NOT NULL,          -- 优惠类型代码
    discount_type_name      VARCHAR(50) NOT NULL,          -- 优惠类型名称
    
    -- 金额
    discount_amount         NUMERIC(14,2) DEFAULT 0,       -- 优惠金额
    order_count             INTEGER DEFAULT 0,             -- 涉及订单数
    
    -- 占比
    discount_rate           NUMERIC(5,4) DEFAULT 0,        -- 占总优惠比例
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, stat_date, discount_type)
);

-- 优惠类型枚举：
-- GROUPBUY: 团购优惠
-- GIFT_CARD_TABLE: 台费卡抵扣
-- GIFT_CARD_DRINK: 酒水卡抵扣
-- GIFT_CARD_COUPON: 活动抵用券抵扣
-- MEMBER_DISCOUNT: 会员折扣
-- VIP_DISCOUNT: 大客户优惠
-- MANUAL_ADJUST: 手动调整
-- FREE_ORDER: 免单
-- ROUNDING: 抹零

COMMENT ON TABLE billiards_dws.dws_finance_discount_detail IS '优惠明细统计表：按优惠类型细分的优惠金额统计';

3.5.4 `dws_finance_recharge_summary` - 充值与预收汇总

-- ============================================================
-- 表名: dws_finance_recharge_summary
-- 用途: 充值与会员卡余额汇总
-- 更新策略: 每日增量更新
-- 数据来源:
--   充值 → dwd_recharge_order
--   余额变动 → dwd_member_balance_change
--   期末余额 → dim_member_card_account
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_recharge_summary (
    summary_id              BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    stat_date               DATE NOT NULL,
    card_type               VARCHAR(20) NOT NULL,          -- 卡类型：STORED/TABLE_FEE/DRINK/COUPON/MONTH/YEAR
    card_type_name          VARCHAR(30) NOT NULL,          -- 卡类型名称
    
    -- 充值/新增
    recharge_count          INTEGER DEFAULT 0,             -- 充值/新增笔数
    recharge_amount         NUMERIC(14,2) DEFAULT 0,       -- 充值/新增金额
    first_recharge_count    INTEGER DEFAULT 0,             -- 首充笔数
    first_recharge_amount   NUMERIC(14,2) DEFAULT 0,       -- 首充金额
    
    -- 消耗
    consume_count           INTEGER DEFAULT 0,             -- 消费笔数
    consume_amount          NUMERIC(14,2) DEFAULT 0,       -- 消费金额
    
    -- 退款/调整
    refund_amount           NUMERIC(14,2) DEFAULT 0,       -- 退款金额
    adjust_amount           NUMERIC(14,2) DEFAULT 0,       -- 调整金额
    
    -- 期末余额
    balance_eod             NUMERIC(14,2) DEFAULT 0,       -- 期末余额
    card_count              INTEGER DEFAULT 0,             -- 有效卡数量
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, stat_date, card_type)
);

COMMENT ON TABLE billiards_dws.dws_finance_recharge_summary IS '充值与会员卡汇总表：按卡类型统计充值、消耗、余额';

3.5.5 `dws_finance_expense_summary` - 支出结构（手动导入）

-- ============================================================
-- 表名: dws_finance_expense_summary
-- 用途: 支出结构汇总（手动导入）
-- 更新策略: 通过Excel等方式手动导入
-- 数据来源: 外部Excel文件
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_expense_summary (
    expense_id              BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    stat_month              DATE NOT NULL,                 -- 统计月份（每月1日）
    
    -- 支出类型
    expense_type            VARCHAR(30) NOT NULL,          -- 支出类型代码
    expense_type_name       VARCHAR(50) NOT NULL,          -- 支出类型名称
    
    -- 金额
    expense_amount          NUMERIC(14,2) NOT NULL,        -- 支出金额
    
    -- 导入信息
    import_batch            VARCHAR(50),                   -- 导入批次号
    import_time             TIMESTAMPTZ DEFAULT now(),     -- 导入时间
    import_source           VARCHAR(100),                  -- 来源文件名
    remark                  TEXT,
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, stat_month, expense_type)
);

-- 支出类型枚举：
-- RENT: 房租
-- UTILITY: 水电
-- PROPERTY: 物业费
-- PURCHASE: 进货成本
-- CONSUMABLE: 耗材
-- REIMBURSE: 报销
-- ASSISTANT_COMMISSION: 助教分成
-- ASSISTANT_BONUS: 助教奖惩
-- SALARY: 固定人员工资
-- PLATFORM_MEITUAN: 美团服务费
-- PLATFORM_DOUYIN: 抖音服务费
-- PLATFORM_HUILAIMI: 汇来米平台服务费
-- OTHER: 其他费用

COMMENT ON TABLE billiards_dws.dws_finance_expense_summary IS '支出结构汇总表：通过Excel等方式手动导入';

3.5.6 `dws_assistant_finance_analysis` - 助教收支分析

-- ============================================================
-- 表名: dws_assistant_finance_analysis
-- 用途: 助教收支分析（财务视角）
-- 更新策略: 每日增量更新
-- 数据来源: dwd_assistant_service_log + cfg_performance_tier + cfg_assistant_level_price
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_finance_analysis (
    analysis_id             BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    stat_date               DATE NOT NULL,
    assistant_level         INTEGER,                       -- 助教等级（NULL表示全部）
    assistant_level_name    VARCHAR(20),
    
    -- 基础课
    base_customer_payment   NUMERIC(14,2) DEFAULT 0,       -- 客户支付
    base_shop_commission    NUMERIC(14,2) DEFAULT 0,       -- 球房抽成
    base_hours              NUMERIC(10,2) DEFAULT 0,       -- 小时数
    base_avg_commission     NUMERIC(10,2) DEFAULT 0,       -- 均小时抽成
    base_service_count      INTEGER DEFAULT 0,             -- 服务次数
    
    -- 激励课
    bonus_customer_payment  NUMERIC(14,2) DEFAULT 0,
    bonus_shop_commission   NUMERIC(14,2) DEFAULT 0,
    bonus_hours             NUMERIC(10,2) DEFAULT 0,
    bonus_avg_commission    NUMERIC(10,2) DEFAULT 0,
    bonus_service_count     INTEGER DEFAULT 0,
    
    -- 包厢课
    room_customer_payment   NUMERIC(14,2) DEFAULT 0,
    room_shop_commission    NUMERIC(14,2) DEFAULT 0,
    room_hours              NUMERIC(10,2) DEFAULT 0,
    room_service_count      INTEGER DEFAULT 0,
    
    -- 汇总
    total_customer_payment  NUMERIC(14,2) DEFAULT 0,
    total_shop_commission   NUMERIC(14,2) DEFAULT 0,
    total_hours             NUMERIC(10,2) DEFAULT 0,
    
    -- 助教数量
    assistant_count         INTEGER DEFAULT 0,
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, stat_date, COALESCE(assistant_level, 0))
);

COMMENT ON TABLE billiards_dws.dws_assistant_finance_analysis IS '助教收支分析表：财务视角的助教业绩与抽成统计';

3.5.7 `dws_platform_settlement` - 平台回款/服务费（手动导入）

-- ============================================================
-- 表名: dws_platform_settlement
-- 用途: 平台回款与服务费记录（手动导入）
-- 更新策略: 通过Excel等方式手动导入
-- 数据来源: 外部Excel文件（美团/抖音/汇来米等平台结算单）
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_platform_settlement (
    settlement_id           BIGSERIAL PRIMARY KEY,
    site_id                 BIGINT NOT NULL,
    stat_month              DATE NOT NULL,                 -- 结算月份
    
    -- 平台信息
    platform_code           VARCHAR(20) NOT NULL,          -- 平台代码：MEITUAN/DOUYIN/HUILAIMI/OTHER
    platform_name           VARCHAR(50) NOT NULL,          -- 平台名称
    
    -- 核销统计
    redemption_count        INTEGER DEFAULT 0,             -- 核销笔数
    redemption_amount       NUMERIC(14,2) DEFAULT 0,       -- 核销券面值
    
    -- 回款
    settlement_amount       NUMERIC(14,2) DEFAULT 0,       -- 平台回款金额
    settlement_date         DATE,                          -- 回款日期
    
    -- 服务费
    service_fee             NUMERIC(14,2) DEFAULT 0,       -- 平台服务费
    service_fee_rate        NUMERIC(5,4),                  -- 服务费率
    
    -- 差价
    price_diff              NUMERIC(14,2) DEFAULT 0,       -- 团购差价（正价-核销价）
    
    -- 导入信息
    import_batch            VARCHAR(50),
    import_time             TIMESTAMPTZ DEFAULT now(),
    import_source           VARCHAR(100),
    remark                  TEXT,
    
    created_at              TIMESTAMPTZ DEFAULT now(),
    updated_at              TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(site_id, stat_month, platform_code)
);

COMMENT ON TABLE billiards_dws.dws_platform_settlement IS '平台回款与服务费表：通过Excel等方式手动导入';

四、数据分层策略

4.1 时间分层设计

分层	时间范围	用途	实现方式	财务支持
L1 热数据	最近2天	实时监控/当日报表	物化视图 + 每小时刷新	✓
L2 近期	最近1月	周报/月报	分区表	✓
L3 中期	最近3月	季度分析	分区表	✓
L4 全量	最近6月	半年分析	分区表	✓ 财务特殊需求
L5 历史	全部	年度汇总/深度分析	归档表	✓

4.2 财务时间筛选支持

UI筛选项	实现方式	环比支持
本月	当前月	vs 上月
上个月	上月全月	vs 上上月
前3个月不加本月	过去3个完整月	vs 更早3个月
前3个月+本月	过去3月+本月	vs 更早4个月
最近半年不含本月	过去6个完整月	vs 更早6个月
本季度含本月	当前季度	vs 上季度
上个季度	上季度全季	vs 上上季度
本周	当前周	vs 上周
上周	上周全周	vs 上上周

五、实施任务清单

5.1 阶段一：基础设施（预计 1-2 天）

序号	任务	产出物
1.1	更新 `schema_dws.sql` 完整DDL	DDL文件
1.2	创建 `seed_dws_config.sql` 配置初始数据	种子数据
1.3	创建 `cfg_area_category` 台区映射数据	映射数据
1.4	更新 `InitDwsSchemaTask`	任务代码
1.5	创建 `BaseDwsTask` 基类	基类代码

5.2 阶段二：助教维度（预计 2-3 天）

序号	任务	产出物
2.1	实现 `AssistantDailyTask`	日度明细
2.2	实现 `AssistantMonthlyTask`	月度汇总
2.3	实现档位计算模块（含新入职折算）	算法模块
2.4	实现工资计算模块	算法模块
2.5	实现 `AssistantCustomerTask`	客户统计
2.6	实现充值提成导入功能	导入脚本

5.3 阶段三：客户维度（预计 1-2 天）

序号	任务	产出物
3.1	实现 `MemberConsumptionTask`	消费统计
3.2	实现 `MemberVisitDetailTask`	来店明细

5.4 阶段四：财务维度（预计 2-3 天）

序号	任务	产出物
4.1	实现 `FinanceDailySummaryTask`	日度汇总
4.2	实现 `FinanceIncomeStructureTask`	收入结构
4.3	实现 `FinanceDiscountDetailTask`	优惠明细
4.4	实现 `FinanceRechargeSummaryTask`	充值统计
4.5	实现 `AssistantFinanceTask`	助教收支
4.6	实现支出/平台结算导入功能	导入脚本

5.5 阶段五：文档与测试（预计 1-2 天）

序号	任务	产出物
5.1	编写 `dws_tables_dictionary.md`	数据字典
5.2	更新 `README.md`	README
5.3	抽样100单分析优惠口径	分析报告
5.4	编写单元测试	测试代码

六、配置表初始数据

-- ============================================================
-- 文件: seed_dws_config.sql
-- 用途: DWS 配置表初始数据
-- ============================================================

-- 绩效档位配置
INSERT INTO billiards_dws.cfg_performance_tier 
(tier_code, tier_name, tier_reason, min_hours, max_hours, base_deduction, bonus_ratio, vacation_days)
VALUES
(0, '0档 淘汰压力', '淘汰压力', 0, 100, 28, 0.50, 3),
(1, '1档 及格档', '重点激励', 100, 130, 18, 0.40, 4),
(2, '2档 良好档', '重点激励', 130, 160, 15, 0.38, 4),
(3, '3档 优秀档', '优秀标准', 160, 190, 13, 0.35, 5),
(4, '4档 卓越加速档', '高端人才倾斜', 190, 220, 10, 0.33, 6),
(5, '5档 冠军加速档', '高端人才倾斜', 220, NULL, 8, 0.30, NULL);

-- 助教等级定价（注意：level_code对应dim_assistant.assistant_level）
INSERT INTO billiards_dws.cfg_assistant_level_price 
(level_code, level_name, base_price, bonus_price)
VALUES
(10, '初级', 98, 190),
(20, '中级', 108, 190),
(30, '高级', 118, 190),
(40, '星级', 138, 190),
(8, '助教管理', 0, 0);

-- 奖金规则
INSERT INTO billiards_dws.cfg_bonus_rules 
(rule_type, rule_name, condition_type, condition_value, bonus_amount, priority, is_stackable)
VALUES
('SPRINT', '冲刺奖-190小时', 'HOURS_GTE', 190, 300, 1, FALSE),
('SPRINT', '冲刺奖-220小时', 'HOURS_GTE', 220, 800, 2, FALSE),
('TOP_RANK', 'Top1奖金', 'RANK_EQ', 1, 1000, 1, TRUE),
('TOP_RANK', 'Top2奖金', 'RANK_EQ', 2, 600, 2, TRUE),
('TOP_RANK', 'Top3奖金', 'RANK_EQ', 3, 400, 3, TRUE);

-- 台区分类映射
INSERT INTO billiards_dws.cfg_area_category 
(area_name, category_l1, category_l2, display_name, sort_order)
VALUES
('A区', '台球', '大厅', 'A区', 1),
('B区', '台球', '大厅', 'B区', 2),
('C区', '台球', '大厅', 'C区', 3),
('斯诺克区', '台球', '斯诺克', '斯诺克区', 4),
('VIP包厢', '台球', '包厢', 'VIP包厢', 5),
('TV台', '台球', '大厅', 'TV台', 6),
('麻将房', '麻将', '麻将房', '麻将房', 10),
('M7', '麻将', '麻将房', 'M7', 11),
('M8', '麻将', '麻将房', 'M8', 12),
('666', '麻将', '麻将房', '666', 13),
('发财', '麻将', '麻将房', '发财', 14),
('K包', 'K包', 'KTV', 'K包', 20),
('k包活动区', 'K包', 'KTV', 'K包活动区', 21),
('幸会158', 'K包', 'KTV', '幸会158', 22),
('补时长', '其他', '补时长', '补时长', 99);

七、待确认事项

7.1 需抽样分析

优惠口径分析：抽取100个订单样本，分析 adjust_amount / member_discount_amount / coupon_amount 等字段的关系，明确"大客户优惠"和"其他优惠"的区分规则。

7.2 已确认事项

事项	结论
课程类型判断	使用 `skill_id` 而非 `skill_name`：基础课=2790683529513797，附加课=2790683529513798，包厢课=3039912271463941
助教等级枚举	8=助教管理，10=初级，20=中级，30=高级，40=星级
赠送卡定义	台费卡+酒水卡+活动抵用券
储值卡定义	card_type_id=2793249295533893 的"储值卡"
支出数据	数据库预留结构，后期通过Excel手动导入
平台回款	数据库预留结构，后期通过Excel手动导入
充值提成	数据库预留结构，后期通过Excel手动导入
历史等级取数	使用 as-of join 按业务时间获取当时的助教等级

文档版本历史

版本	日期	变更说明
1.0	2026-02-01	初始版本
2.0	2026-02-01	根据反馈更新：补全DDL、明确数据口径、增加数据来源矩阵、更新skill_id枚举、增加手动导入表结构

65 KiB Raw Permalink Blame History Unescape Escape

DWS 数据层实施任务计划 v2.0

一、项目背景与现状分析

1.1 已有基础设施

1.2 DWD 核心表与关键字段

1.2.1 助教服务相关

1.2.2 会员卡相关

1.2.3 台区分布

二、数据口径定义（数据来源矩阵）

2.1 发生额/正价口径

2.2 优惠分类口径

2.3 支付方式口径

2.4 充值与赠送口径

2.5 助教业绩口径

2.6 SCD2 维度取数说明

三、DWS 表设计

3.1 表清单总览

3.1.1 配置表（4张）

3.1.2 助教维度表（5张）

3.1.3 客户维度表（2张）

3.1.4 财务维度表（7张）

3.2 配置表 DDL

3.2.1 cfg_performance_tier - 绩效档位配置

3.2.2 cfg_assistant_level_price - 助教等级定价

3.2.3 cfg_bonus_rules - 奖金规则配置

3.2.4 cfg_area_category - 台区分类映射

3.3 助教维度表 DDL

3.3.1 dws_assistant_monthly_summary - 月度业绩汇总

3.3.2 dws_assistant_daily_detail - 日度业绩明细

3.3.3 dws_assistant_customer_stats - 服务客户统计

3.3.4 dws_assistant_salary_calc - 月度工资计算

3.3.5 dws_assistant_recharge_commission - 充值提成记录

3.4 客户维度表 DDL

3.4.1 dws_member_consumption_summary - 消费情况统计

3.4.2 dws_member_visit_detail - 来店消费明细

3.5 财务维度表 DDL

3.5.1 dws_finance_daily_summary - 日度财务汇总

3.5.2 dws_finance_income_structure - 收入结构

3.5.3 dws_finance_discount_detail - 优惠明细

3.5.4 dws_finance_recharge_summary - 充值与预收汇总

3.5.5 dws_finance_expense_summary - 支出结构（手动导入）

3.5.6 dws_assistant_finance_analysis - 助教收支分析

3.5.7 dws_platform_settlement - 平台回款/服务费（手动导入）

四、数据分层策略

4.1 时间分层设计

4.2 财务时间筛选支持

五、实施任务清单

5.1 阶段一：基础设施（预计 1-2 天）

5.2 阶段二：助教维度（预计 2-3 天）

5.3 阶段三：客户维度（预计 1-2 天）

5.4 阶段四：财务维度（预计 2-3 天）

5.5 阶段五：文档与测试（预计 1-2 天）

六、配置表初始数据

七、待确认事项

7.1 需抽样分析

7.2 已确认事项

65 KiB

Raw Permalink Blame History

3.2.1 `cfg_performance_tier` - 绩效档位配置

3.2.2 `cfg_assistant_level_price` - 助教等级定价

3.2.3 `cfg_bonus_rules` - 奖金规则配置

3.2.4 `cfg_area_category` - 台区分类映射

3.3.1 `dws_assistant_monthly_summary` - 月度业绩汇总

3.3.2 `dws_assistant_daily_detail` - 日度业绩明细

3.3.3 `dws_assistant_customer_stats` - 服务客户统计

3.3.4 `dws_assistant_salary_calc` - 月度工资计算

3.3.5 `dws_assistant_recharge_commission` - 充值提成记录

3.4.1 `dws_member_consumption_summary` - 消费情况统计

3.4.2 `dws_member_visit_detail` - 来店消费明细

3.5.1 `dws_finance_daily_summary` - 日度财务汇总

3.5.2 `dws_finance_income_structure` - 收入结构

3.5.3 `dws_finance_discount_detail` - 优惠明细

3.5.4 `dws_finance_recharge_summary` - 充值与预收汇总

3.5.5 `dws_finance_expense_summary` - 支出结构（手动导入）

3.5.6 `dws_assistant_finance_analysis` - 助教收支分析

3.5.7 `dws_platform_settlement` - 平台回款/服务费（手动导入）