root/ZQYY.FQ-ETL
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
ZQYY.FQ-ETL/database/schema_dws.sql

1711 lines
107 KiB
PL/PgSQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- =============================================================================
-- DWS 数据层完整 DDL
-- 版本: v3.0
-- 创建日期: 2026-02-01
-- 描述: 包含配置表(5张)、助教维度(5张)、客户维度(2张)、财务维度(7张)、订单汇总(1张)
-- =============================================================================
-- 创建 DWS Schema
CREATE SCHEMA IF NOT EXISTS billiards_dws;
-- =============================================================================
-- 第一部分：配置表（5张）
-- =============================================================================
-- -----------------------------------------------------------------------------
-- 1. cfg_performance_tier - 绩效档位配置表
-- 说明:
-- - 助教绩效档位配置，包含阈值、抽成比例、假期天数
-- - 数据来源：DWS 数据库处理需求.md 第35-41行
-- - 基础课收入 = 基础课小时数 × (客户支付价格 - 专业课抽成)
-- - 附加课收入 = 附加课小时数 × 190 × (1 - 打赏课抽成比例)
-- - 支持按时间生效，通过 effective_from/effective_to 控制历史口径
-- - 新入职定档规则: 月1日0点之后入职的，计算为新入职
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_performance_tier CASCADE;
CREATE TABLE billiards_dws.cfg_performance_tier (
tier_id SERIAL PRIMARY KEY, -- 档位ID（自增）
tier_code VARCHAR(20) NOT NULL, -- 档位代码（如 T0-T4）
tier_name VARCHAR(50) NOT NULL, -- 档位名称
tier_level INTEGER NOT NULL, -- 档位等级（数字越大档位越高）
min_hours NUMERIC(10,2) NOT NULL, -- 最低业绩小时数阈值（>=）
max_hours NUMERIC(10,2), -- 最高业绩小时数阈值（<，NULL表示无上限）
base_deduction NUMERIC(10,2) NOT NULL DEFAULT 0, -- 专业课抽成（元/小时），球房从基础课扣除
bonus_deduction_ratio NUMERIC(5,4) NOT NULL DEFAULT 0, -- 打赏课抽成比例（0-1），球房从附加课扣除
vacation_days INTEGER NOT NULL DEFAULT 0, -- 次月可休假天数
vacation_unlimited BOOLEAN NOT NULL DEFAULT FALSE, -- 是否休假自由（最高档特殊）
is_new_hire_tier BOOLEAN NOT NULL DEFAULT FALSE, -- 是否为新入职专用档位（预留）
effective_from DATE NOT NULL DEFAULT '2000-01-01', -- 生效起始日期（含）
effective_to DATE NOT NULL DEFAULT '9999-12-31', -- 生效截止日期（含）
description TEXT, -- 档位说明
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_cfg_performance_tier UNIQUE (tier_code, effective_from)
);
COMMENT ON TABLE billiards_dws.cfg_performance_tier IS '绩效档位配置表：定义绩效阈值、抽成比例、假期，数据来源DWS数据库处理需求.md';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.tier_code IS '档位代码：按规则表配置（如 T0-T4）';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.min_hours IS '业绩小时数下限（含），基础课+附加课总和';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.max_hours IS '业绩小时数上限（不含），NULL表示无上限';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.base_deduction IS '专业课抽成（元/小时）：球房从基础课每小时扣除的金额';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.bonus_deduction_ratio IS '打赏课抽成比例：球房从附加课收入中扣除的比例，如0.35表示35%';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.vacation_days IS '次月可休假天数';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.vacation_unlimited IS '休假自由标记：最高档为TRUE';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.is_new_hire_tier IS '新入职专用档位标记（预留，当前规则不使用）';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.effective_from IS '规则生效起始日期，用于历史月份正确取档';
-- 创建查询索引
CREATE INDEX idx_cfg_performance_tier_effective
ON billiards_dws.cfg_performance_tier (effective_from, effective_to);
-- -----------------------------------------------------------------------------
-- 2. cfg_assistant_level_price - 助教等级定价表
-- 说明:
-- - 助教等级（初级/中级/高级/星级）对应的基础课和附加课单价
-- - 支持按时间生效，便于历史月份薪资计算使用历史单价
-- - SCD2口径: 助教等级来自dim_assistant，取数时需按有效期as-of join
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_assistant_level_price CASCADE;
CREATE TABLE billiards_dws.cfg_assistant_level_price (
price_id SERIAL PRIMARY KEY, -- 定价ID（自增）
level_code INTEGER NOT NULL, -- 等级代码（来自dim_assistant.assistant_level）
level_name VARCHAR(20) NOT NULL, -- 等级名称（初级/中级/高级/星级）
base_course_price NUMERIC(10,2) NOT NULL, -- 基础课单价（元/小时）
bonus_course_price NUMERIC(10,2) NOT NULL, -- 附加课单价（元/小时），固定190元
effective_from DATE NOT NULL DEFAULT '2000-01-01', -- 生效起始日期（含）
effective_to DATE NOT NULL DEFAULT '9999-12-31', -- 生效截止日期（含）
description TEXT, -- 说明
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_cfg_assistant_level_price 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=金牌';
COMMENT ON COLUMN billiards_dws.cfg_assistant_level_price.base_course_price IS '基础课（陪打/PD）单价，按等级不同';
COMMENT ON COLUMN billiards_dws.cfg_assistant_level_price.bonus_course_price IS '附加课（超休/CX）单价，固定190元/小时';
CREATE INDEX idx_cfg_assistant_level_price_effective
ON billiards_dws.cfg_assistant_level_price (effective_from, effective_to);
-- -----------------------------------------------------------------------------
-- 3. cfg_bonus_rules - 奖金规则配置表
-- 说明:
-- - 包含冲刺奖金（按小时阈值，历史/可选）和Top3奖金（按排名）
-- - Top3排名口径: 按绩效总小时数，如遇并列则都算（如2个第一，则记为2个第一，一个第三）
-- - 冲刺奖金: 按规则表配置，不累计取最高档
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_bonus_rules CASCADE;
CREATE TABLE billiards_dws.cfg_bonus_rules (
rule_id SERIAL PRIMARY KEY, -- 规则ID（自增）
rule_type VARCHAR(20) NOT NULL, -- 规则类型: SPRINT（冲刺奖金）, TOP_RANK（Top排名奖金）
rule_code VARCHAR(30) NOT NULL, -- 规则代码: SPRINT_190, SPRINT_220, TOP_1, TOP_2, TOP_3
rule_name VARCHAR(50) NOT NULL, -- 规则名称
threshold_hours NUMERIC(10,2), -- 小时数阈值（冲刺奖金用）
rank_position INTEGER, -- 排名位置（Top奖金用）
bonus_amount NUMERIC(12,2) NOT NULL, -- 奖金金额（元）
is_cumulative BOOLEAN NOT NULL DEFAULT FALSE, -- 是否可累计（冲刺奖金为FALSE，取最高档）
priority INTEGER NOT NULL DEFAULT 0, -- 优先级（数字越大优先级越高，用于非累计时取最高）
effective_from DATE NOT NULL DEFAULT '2000-01-01', -- 生效起始日期（含）
effective_to DATE NOT NULL DEFAULT '9999-12-31', -- 生效截止日期（含）
description TEXT, -- 说明
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_cfg_bonus_rules UNIQUE (rule_type, rule_code, effective_from)
);
COMMENT ON TABLE billiards_dws.cfg_bonus_rules IS '奖金规则配置表：冲刺奖金(按小时阈值)和Top3奖金(按排名)，支持按时间生效';
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.rule_type IS '规则类型：SPRINT=冲刺奖金, TOP_RANK=Top排名奖金';
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.is_cumulative IS '是否累计：冲刺奖金不累计取最高档，Top奖金独立发放';
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.priority IS '优先级：非累计时用于取最高档奖金';
CREATE INDEX idx_cfg_bonus_rules_effective
ON billiards_dws.cfg_bonus_rules (effective_from, effective_to);
CREATE INDEX idx_cfg_bonus_rules_type
ON billiards_dws.cfg_bonus_rules (rule_type);
-- -----------------------------------------------------------------------------
-- 4. cfg_area_category - 台区分类映射表
-- 说明:
-- - 将 dim_table.site_table_area_name 映射到财务报表区域分类
-- - 数据来源: BD_manual_dim_table.md 中的实际台区分布
-- - 分类设计:
-- * BILLIARD: 台球散台（A区/B区/C区/TV台）
-- * BILLIARD_VIP: 台球VIP包厢
-- * SNOOKER: 斯诺克区
-- * MAHJONG: 麻将棋牌（麻将房/M7/M8/666/发财）
-- * KTV: K歌娱乐（K包/k包活动区/幸会158）
-- * SPECIAL: 特殊（补时长）
-- * OTHER: 其他
-- - 映射规则: 精确匹配 > 模糊匹配 > 默认兜底
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_area_category CASCADE;
CREATE TABLE billiards_dws.cfg_area_category (
category_id SERIAL PRIMARY KEY, -- 分类ID（自增）
source_area_name VARCHAR(100) NOT NULL, -- 源区域名称（来自dim_table.site_table_area_name）
category_code VARCHAR(20) NOT NULL, -- 分类代码: BILLIARD, BILLIARD_VIP, SNOOKER, MAHJONG, KTV, SPECIAL, OTHER
category_name VARCHAR(50) NOT NULL, -- 分类名称: 台球散台、台球VIP、斯诺克、麻将棋牌、K歌娱乐、补时长、其他
match_type VARCHAR(10) NOT NULL DEFAULT 'EXACT', -- 匹配类型: EXACT(精确), LIKE(模糊), DEFAULT(兜底)
match_priority INTEGER NOT NULL DEFAULT 100, -- 匹配优先级（数字越小优先级越高）
is_active BOOLEAN NOT NULL DEFAULT TRUE, -- 是否启用
description TEXT, -- 说明
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_cfg_area_category UNIQUE (source_area_name)
);
COMMENT ON TABLE billiards_dws.cfg_area_category IS '台区分类映射表：将dim_table区域名称映射到财务报表分类，基于BD_manual_dim_table.md实际数据';
COMMENT ON COLUMN billiards_dws.cfg_area_category.category_code IS '分类代码：BILLIARD台球散台, BILLIARD_VIP台球VIP, SNOOKER斯诺克, MAHJONG麻将, KTV K歌, SPECIAL特殊, OTHER其他';
COMMENT ON COLUMN billiards_dws.cfg_area_category.match_type IS '匹配类型：EXACT精确匹配, LIKE模糊匹配(用于包含关系), DEFAULT兜底';
COMMENT ON COLUMN billiards_dws.cfg_area_category.match_priority IS '匹配优先级：多条匹配时取优先级最高的';
CREATE INDEX idx_cfg_area_category_code ON billiards_dws.cfg_area_category (category_code);
-- -----------------------------------------------------------------------------
-- 5. cfg_skill_type - 技能→课程类型映射表
-- 说明:
-- - 将 skill_id 映射到课程类型（基础课/附加课）
-- - 基础课（陪打/PD）: skill_id = 2791903611396869
-- - 附加课（超休/CX）: skill_id = 2807440316432197
-- - 避免依赖 skill_name 文本匹配，使用配置表管理
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_skill_type CASCADE;
CREATE TABLE billiards_dws.cfg_skill_type (
skill_type_id SERIAL PRIMARY KEY, -- 映射ID（自增）
skill_id BIGINT NOT NULL, -- 技能ID（来自dwd_assistant_service_log.skill_id）
skill_name VARCHAR(50), -- 技能名称（仅用于展示和校验）
course_type_code VARCHAR(10) NOT NULL, -- 课程类型代码: BASE（基础课）, BONUS（附加课）
course_type_name VARCHAR(20) NOT NULL, -- 课程类型名称: 基础课/陪打, 附加课/超休
is_active BOOLEAN NOT NULL DEFAULT TRUE, -- 是否启用
description TEXT, -- 说明
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_cfg_skill_type UNIQUE (skill_id)
);
COMMENT ON TABLE billiards_dws.cfg_skill_type IS '技能→课程类型映射表：将skill_id映射到基础课/附加课，避免依赖skill_name文本';
COMMENT ON COLUMN billiards_dws.cfg_skill_type.course_type_code IS '课程类型：BASE=基础课(陪打), BONUS=附加课(超休)';
CREATE INDEX idx_cfg_skill_type_course ON billiards_dws.cfg_skill_type (course_type_code);
-- =============================================================================
-- 第二部分：助教维度（5张）
-- =============================================================================
-- -----------------------------------------------------------------------------
-- 6. dws_assistant_daily_detail - 助教日度业绩明细表
-- 说明:
-- - 以"助教+日期"为粒度，汇总每日业绩明细
-- - 数据来源: dwd_assistant_service_log + dwd_assistant_trash_event（排除废除记录）
-- - 更新频率: 每小时增量更新
-- - 时间分层: 通过 stat_date 筛选实现近2天/近1月/近3月/全量
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_daily_detail CASCADE;
CREATE TABLE billiards_dws.dws_assistant_daily_detail (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
assistant_id BIGINT NOT NULL, -- 助教ID（dim_assistant.site_assistant_id）
assistant_nickname VARCHAR(50), -- 助教花名（冗余，便于查询展示）
stat_date DATE NOT NULL, -- 统计日期
-- 等级信息（as-of取值，使用统计日期时点的等级）
assistant_level_code INTEGER, -- 助教等级代码（统计日当日生效的等级）
assistant_level_name VARCHAR(20), -- 助教等级名称
-- 业绩统计
total_service_count INTEGER NOT NULL DEFAULT 0, -- 总服务次数
base_service_count INTEGER NOT NULL DEFAULT 0, -- 基础课服务次数
bonus_service_count INTEGER NOT NULL DEFAULT 0, -- 附加课服务次数
room_service_count INTEGER NOT NULL DEFAULT 0, -- 包厢/房间服务次数
total_seconds INTEGER NOT NULL DEFAULT 0, -- 总计费时长（秒）
base_seconds INTEGER NOT NULL DEFAULT 0, -- 基础课计费时长（秒）
bonus_seconds INTEGER NOT NULL DEFAULT 0, -- 附加课计费时长（秒）
room_seconds INTEGER NOT NULL DEFAULT 0, -- 包厢/房间计费时长（秒）
total_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 总计费小时数（total_seconds/3600）
base_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 基础课小时数
bonus_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 附加课小时数
room_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 包厢/房间小时数
-- 金额统计
total_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 总计费金额（元）
base_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 基础课计费金额
bonus_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 附加课计费金额
room_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 包厢/房间计费金额
-- 客户与台桌统计
unique_customers INTEGER NOT NULL DEFAULT 0, -- 服务客户数（去重）
unique_tables INTEGER NOT NULL DEFAULT 0, -- 服务台桌数（去重）
-- 废除记录统计
trashed_seconds INTEGER NOT NULL DEFAULT 0, -- 被废除的服务时长（秒）
trashed_count INTEGER NOT NULL DEFAULT 0, -- 被废除的服务次数
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_assistant_daily UNIQUE (site_id, assistant_id, stat_date)
);
COMMENT ON TABLE billiards_dws.dws_assistant_daily_detail IS '助教日度业绩明细：按助教+日期汇总服务次数、时长、金额，支持时间分层查询';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.assistant_level_code IS 'SCD2口径：取stat_date当日生效的助教等级';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.trashed_seconds IS '被废除时长：来自dwd_assistant_trash_event，影响有效业绩';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.room_service_count IS '包厢/房间服务次数';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.room_seconds IS '包厢/房间计费时长（秒）';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.room_hours IS '包厢/房间计费小时数';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.room_ledger_amount IS '包厢/房间计费金额';
-- 时间分层查询索引（核心）
CREATE INDEX idx_dws_assistant_daily_date ON billiards_dws.dws_assistant_daily_detail (stat_date);
CREATE INDEX idx_dws_assistant_daily_asst_date ON billiards_dws.dws_assistant_daily_detail (assistant_id, stat_date);
CREATE INDEX idx_dws_assistant_daily_site_date ON billiards_dws.dws_assistant_daily_detail (site_id, stat_date);
-- -----------------------------------------------------------------------------
-- 7. dws_assistant_monthly_summary - 助教月度业绩汇总表
-- 说明:
-- - 以"助教+月份"为粒度，汇总月度业绩及档位计算
-- - 数据来源: dws_assistant_daily_detail 聚合 + cfg_performance_tier 档位匹配
-- - 更新频率: 每日更新当月数据
-- - 新入职判断: 入职日期在月1日0点之后则为新入职
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_monthly_summary CASCADE;
CREATE TABLE billiards_dws.dws_assistant_monthly_summary (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
assistant_id BIGINT NOT NULL, -- 助教ID
assistant_nickname VARCHAR(50), -- 助教花名
stat_month DATE NOT NULL, -- 统计月份（月第一天，如2026-01-01）
-- 等级信息（as-of取值，使用月末时点的等级）
assistant_level_code INTEGER, -- 助教等级代码
assistant_level_name VARCHAR(20), -- 助教等级名称
-- 入职信息
hire_date DATE, -- 入职日期（来自dim_assistant）
is_new_hire BOOLEAN NOT NULL DEFAULT FALSE, -- 是否新入职（入职日期 >= 统计月1日0点）
-- 月度业绩汇总
work_days INTEGER NOT NULL DEFAULT 0, -- 有服务天数
total_service_count INTEGER NOT NULL DEFAULT 0, -- 总服务次数
base_service_count INTEGER NOT NULL DEFAULT 0, -- 基础课服务次数
bonus_service_count INTEGER NOT NULL DEFAULT 0, -- 附加课服务次数
room_service_count INTEGER NOT NULL DEFAULT 0, -- 包厢/房间服务次数
total_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 总计费小时数
base_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 基础课小时数
bonus_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 附加课小时数
room_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 包厢/房间小时数
-- 有效业绩（扣除废除记录后）
effective_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 有效业绩小时数（影响档位）
trashed_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 被废除小时数
-- 金额统计
total_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 总计费金额
base_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 基础课计费金额
bonus_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 附加课计费金额
room_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 包厢/房间计费金额
-- 客户统计
unique_customers INTEGER NOT NULL DEFAULT 0, -- 月度服务客户数（去重）
unique_tables INTEGER NOT NULL DEFAULT 0, -- 月度服务台桌数（去重）
avg_service_seconds NUMERIC(10,2) NOT NULL DEFAULT 0, -- 平均单次服务时长（秒）
-- 档位信息（根据有效业绩匹配）
tier_id INTEGER, -- 匹配的档位ID
tier_code VARCHAR(20), -- 档位代码
tier_name VARCHAR(50), -- 档位名称
-- 排名信息（用于Top3奖金，按有效业绩小时数排名）
rank_by_hours INTEGER, -- 月度排名（按effective_hours降序）
rank_with_ties INTEGER, -- 考虑并列的排名（如2个第一则都是1）
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_assistant_monthly UNIQUE (site_id, assistant_id, stat_month)
);
COMMENT ON TABLE billiards_dws.dws_assistant_monthly_summary IS '助教月度业绩汇总：按助教+月份汇总业绩、档位匹配、排名计算';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.stat_month IS '统计月份：存储月第一天日期，如2026-01-01';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.is_new_hire IS '新入职标记：入职日期>=月1日0点则为TRUE';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.effective_hours IS '有效业绩：total_hours - trashed_hours，用于档位匹配';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.rank_with_ties IS 'Top3排名口径：如遇并列都算，如2个第一则都是1，下一个是3';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.room_service_count IS '包厢/房间服务次数（月度汇总）';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.room_hours IS '包厢/房间服务小时数（月度汇总）';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.room_ledger_amount IS '包厢/房间计费金额（月度汇总）';
CREATE INDEX idx_dws_assistant_monthly_month ON billiards_dws.dws_assistant_monthly_summary (stat_month);
CREATE INDEX idx_dws_assistant_monthly_asst ON billiards_dws.dws_assistant_monthly_summary (assistant_id, stat_month);
CREATE INDEX idx_dws_assistant_monthly_tier ON billiards_dws.dws_assistant_monthly_summary (tier_code);
-- -----------------------------------------------------------------------------
-- 8. dws_assistant_customer_stats - 助教服务客户统计表
-- 说明:
-- - 以"助教+客户"为粒度，统计服务关系和滚动窗口指标
-- - 滚动窗口: 7/10/15/30/60/90天，从统计日期往前计算
-- - 更新频率: 每日更新
-- - 散客处理: member_id=0 不进入此表统计
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_customer_stats CASCADE;
CREATE TABLE billiards_dws.dws_assistant_customer_stats (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
assistant_id BIGINT NOT NULL, -- 助教ID
assistant_nickname VARCHAR(50), -- 助教花名
member_id BIGINT NOT NULL, -- 客户ID（member_id=0散客不入此表）
member_nickname VARCHAR(100), -- 客户昵称
member_mobile VARCHAR(20), -- 客户手机号（脱敏）
stat_date DATE NOT NULL, -- 统计基准日期
-- 全量累计统计
first_service_date DATE, -- 首次服务日期
last_service_date DATE, -- 最近服务日期
total_service_count INTEGER NOT NULL DEFAULT 0, -- 累计服务次数
total_service_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 累计服务小时数
total_service_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 累计服务金额
-- 滚动窗口统计（近N天）
service_count_7d INTEGER NOT NULL DEFAULT 0, -- 近7天服务次数
service_count_10d INTEGER NOT NULL DEFAULT 0, -- 近10天服务次数
service_count_15d INTEGER NOT NULL DEFAULT 0, -- 近15天服务次数
service_count_30d INTEGER NOT NULL DEFAULT 0, -- 近30天服务次数
service_count_60d INTEGER NOT NULL DEFAULT 0, -- 近60天服务次数
service_count_90d INTEGER NOT NULL DEFAULT 0, -- 近90天服务次数
service_hours_7d NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近7天服务小时数
service_hours_10d NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近10天服务小时数
service_hours_15d NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近15天服务小时数
service_hours_30d NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近30天服务小时数
service_hours_60d NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近60天服务小时数
service_hours_90d NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近90天服务小时数
service_amount_7d NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近7天服务金额
service_amount_10d NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近10天服务金额
service_amount_15d NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近15天服务金额
service_amount_30d NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近30天服务金额
service_amount_60d NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近60天服务金额
service_amount_90d NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近90天服务金额
-- 活跃度指标
days_since_last INTEGER, -- 距离最近服务的天数
is_active_7d BOOLEAN NOT NULL DEFAULT FALSE, -- 近7天是否活跃
is_active_30d BOOLEAN NOT NULL DEFAULT FALSE, -- 近30天是否活跃
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_assistant_customer UNIQUE (site_id, assistant_id, member_id, stat_date)
);
COMMENT ON TABLE billiards_dws.dws_assistant_customer_stats IS '助教服务客户统计：按助教+客户统计服务关系和滚动窗口指标';
COMMENT ON COLUMN billiards_dws.dws_assistant_customer_stats.member_id IS '客户ID：member_id=0散客不入此表';
COMMENT ON COLUMN billiards_dws.dws_assistant_customer_stats.service_count_7d IS '滚动窗口：从stat_date往前7天的服务次数';
CREATE INDEX idx_dws_assistant_customer_date ON billiards_dws.dws_assistant_customer_stats (stat_date);
CREATE INDEX idx_dws_assistant_customer_asst ON billiards_dws.dws_assistant_customer_stats (assistant_id, stat_date);
CREATE INDEX idx_dws_assistant_customer_member ON billiards_dws.dws_assistant_customer_stats (member_id, stat_date);
-- -----------------------------------------------------------------------------
-- 9. dws_assistant_salary_calc - 助教工资计算详情表
-- 说明:
-- - 以"助教+月份"为粒度，计算月度工资明细
-- - 数据来源: dws_assistant_monthly_summary + cfg_* 配置表
-- - 计算公式（来自DWS数据库处理需求.md）:
-- * 基础课收入 = 基础课小时数 × (客户支付价格 - 专业课抽成)
-- * 附加课收入 = 附加课小时数 × 190 × (1 - 打赏课抽成比例)
-- * 包厢课收入 = 包厢课小时数 × (138 - 专业课抽成)
-- * 应发工资 = 课时收入 + 奖金
-- - 更新频率: 月初计算上月工资
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_salary_calc CASCADE;
CREATE TABLE billiards_dws.dws_assistant_salary_calc (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
assistant_id BIGINT NOT NULL, -- 助教ID
assistant_nickname VARCHAR(50), -- 助教花名
salary_month DATE NOT NULL, -- 工资月份（月第一天）
-- 助教信息快照
assistant_level_code INTEGER, -- 助教等级代码（8/10/20/30/40）
assistant_level_name VARCHAR(20), -- 助教等级名称（初级/中级/高级/星级）
hire_date DATE, -- 入职日期
is_new_hire BOOLEAN NOT NULL DEFAULT FALSE, -- 是否新入职
-- 业绩数据（来自monthly_summary）
effective_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 有效业绩小时数（基础课+附加课-废除）
base_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 基础课/专业课小时数
bonus_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 附加课/打赏课小时数
room_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 包厢/房间服务小时数
-- 档位信息（来自cfg_performance_tier）
tier_id INTEGER, -- 档位ID
tier_code VARCHAR(20), -- 档位代码（如 T0-T4）
tier_name VARCHAR(50), -- 档位名称
-- 排名信息
rank_with_ties INTEGER, -- 月度排名（考虑并列，用于Top3奖金）
-- 定价信息（SCD2口径，取salary_month对应的值）
base_course_price NUMERIC(10,2) NOT NULL DEFAULT 0, -- 基础课客户支付价格（98/108/118/138）
bonus_course_price NUMERIC(10,2) NOT NULL DEFAULT 0, -- 附加课客户支付价格（固定190）
base_deduction NUMERIC(10,2) NOT NULL DEFAULT 0, -- 专业课抽成（元/小时）
bonus_deduction_ratio NUMERIC(5,4) NOT NULL DEFAULT 0, -- 打赏课抽成比例（0-1）
-- 工资计算明细
base_income NUMERIC(12,2) NOT NULL DEFAULT 0, -- 基础课收入 = base_hours × (base_course_price - base_deduction)
bonus_income NUMERIC(12,2) NOT NULL DEFAULT 0, -- 附加课收入 = bonus_hours × 190 × (1 - bonus_deduction_ratio)
room_income NUMERIC(12,2) NOT NULL DEFAULT 0, -- 包厢/房间收入
total_course_income NUMERIC(12,2) NOT NULL DEFAULT 0, -- 课时收入合计
-- 奖金
sprint_bonus NUMERIC(12,2) NOT NULL DEFAULT 0, -- 冲刺奖金（按规则表配置，不累计取最高）
top_rank_bonus NUMERIC(12,2) NOT NULL DEFAULT 0, -- Top3排名奖金（1st:1000, 2nd:600, 3rd:400）
recharge_commission NUMERIC(12,2) NOT NULL DEFAULT 0, -- 充值提成（来自dws_assistant_recharge_commission）
other_bonus NUMERIC(12,2) NOT NULL DEFAULT 0, -- 其他奖金（手动调整）
total_bonus NUMERIC(12,2) NOT NULL DEFAULT 0, -- 奖金合计
-- 工资汇总
gross_salary NUMERIC(12,2) NOT NULL DEFAULT 0, -- 应发工资 = total_course_income + total_bonus
-- 假期信息
vacation_days INTEGER NOT NULL DEFAULT 0, -- 次月可休假天数
vacation_unlimited BOOLEAN NOT NULL DEFAULT FALSE, -- 休假自由标记（最高档为TRUE）
-- 备注
calc_notes TEXT, -- 计算备注（异常说明等）
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_assistant_salary UNIQUE (site_id, assistant_id, salary_month)
);
COMMENT ON TABLE billiards_dws.dws_assistant_salary_calc IS '助教工资计算详情：按DWS数据库处理需求.md公式计算，包含课时收入和各类奖金';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.base_deduction IS '专业课抽成（元/小时）：档位决定，球房从基础课每小时扣除';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.bonus_deduction_ratio IS '打赏课抽成比例：档位决定，球房从附加课收入扣除的比例';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.base_income IS '基础课收入 = 小时数 × (客户价格 - 专业课抽成)，如170×(108-13)=16150';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.bonus_income IS '附加课收入 = 小时数 × 190 × (1 - 抽成比例)，如15×190×0.65=1852.5';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.room_hours IS '包厢/房间服务小时数（来自monthly_summary）';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.room_income IS '包厢/房间收入（包厢课统一138元/小时）';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.sprint_bonus IS '冲刺奖金：按规则表配置，不累计取最高档';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.top_rank_bonus IS 'Top3奖金：按effective_hours排名，并列都算（如2个第1则无第2）';
CREATE INDEX idx_dws_assistant_salary_month ON billiards_dws.dws_assistant_salary_calc (salary_month);
CREATE INDEX idx_dws_assistant_salary_asst ON billiards_dws.dws_assistant_salary_calc (assistant_id, salary_month);
-- -----------------------------------------------------------------------------
-- 10. dws_assistant_recharge_commission - 助教充值提成表
-- 说明:
-- - 以"助教+月份+充值订单"为粒度，记录充值提成
-- - 数据来源: Excel手动导入
-- - 导入字段: 月份、充值订单金额、助教获得的提成金额
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_recharge_commission CASCADE;
CREATE TABLE billiards_dws.dws_assistant_recharge_commission (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
assistant_id BIGINT NOT NULL, -- 助教ID
assistant_nickname VARCHAR(50), -- 助教花名
commission_month DATE NOT NULL, -- 提成月份（月第一天）
-- 充值订单关联
recharge_order_id BIGINT, -- 充值订单ID（可选，关联dwd_recharge_order）
recharge_order_no VARCHAR(50), -- 充值订单号
-- 提成信息
recharge_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 充值订单金额
commission_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 提成金额
commission_ratio NUMERIC(5,4), -- 提成比例（可选，反算或导入）
-- 导入信息
import_batch_no VARCHAR(50), -- 导入批次号
import_file_name VARCHAR(200), -- 导入文件名
import_time TIMESTAMPTZ, -- 导入时间
import_user VARCHAR(50), -- 导入操作人
-- 备注
remark TEXT, -- 备注
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE billiards_dws.dws_assistant_recharge_commission IS '助教充值提成：Excel导入，记录月份、充值金额、提成金额';
COMMENT ON COLUMN billiards_dws.dws_assistant_recharge_commission.commission_month IS '提成月份：导入表格中明确的月份';
COMMENT ON COLUMN billiards_dws.dws_assistant_recharge_commission.import_batch_no IS '导入批次号：用于追溯和去重';
CREATE INDEX idx_dws_assistant_commission_month ON billiards_dws.dws_assistant_recharge_commission (commission_month);
CREATE INDEX idx_dws_assistant_commission_asst ON billiards_dws.dws_assistant_recharge_commission (assistant_id, commission_month);
CREATE INDEX idx_dws_assistant_commission_batch ON billiards_dws.dws_assistant_recharge_commission (import_batch_no);
-- =============================================================================
-- 第三部分：客户维度（2张）
-- =============================================================================
-- -----------------------------------------------------------------------------
-- 11. dws_member_consumption_summary - 会员消费汇总表
-- 说明:
-- - 以"会员"为粒度，统计消费行为和滚动窗口指标
-- - 散客处理: member_id=0 不进入此表
-- - 滚动窗口: 7/10/15/30/60/90天
-- - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_consumption_summary CASCADE;
CREATE TABLE billiards_dws.dws_member_consumption_summary (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
member_id BIGINT NOT NULL, -- 会员ID（member_id=0散客不入此表）
stat_date DATE NOT NULL, -- 统计基准日期
-- 会员基本信息快照
member_nickname VARCHAR(100), -- 会员昵称
member_mobile VARCHAR(20), -- 手机号（脱敏）
card_grade_name VARCHAR(50), -- 卡等级名称
register_date DATE, -- 注册日期
-- 全量累计统计
first_consume_date DATE, -- 首次消费日期
last_consume_date DATE, -- 最近消费日期
total_visit_count INTEGER NOT NULL DEFAULT 0, -- 累计到店次数
total_consume_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计消费金额
total_recharge_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计充值金额
total_table_fee NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计台费
total_goods_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计商品消费
total_assistant_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计助教服务消费
-- 滚动窗口统计（近N天）
visit_count_7d INTEGER NOT NULL DEFAULT 0, -- 近7天到店次数
visit_count_10d INTEGER NOT NULL DEFAULT 0, -- 近10天到店次数
visit_count_15d INTEGER NOT NULL DEFAULT 0, -- 近15天到店次数
visit_count_30d INTEGER NOT NULL DEFAULT 0, -- 近30天到店次数
visit_count_60d INTEGER NOT NULL DEFAULT 0, -- 近60天到店次数
visit_count_90d INTEGER NOT NULL DEFAULT 0, -- 近90天到店次数
consume_amount_7d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近7天消费金额
consume_amount_10d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近10天消费金额
consume_amount_15d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近15天消费金额
consume_amount_30d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近30天消费金额
consume_amount_60d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近60天消费金额
consume_amount_90d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近90天消费金额
-- 会员卡余额快照
cash_card_balance NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值卡余额（现金卡）
gift_card_balance NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送卡余额（台费卡+酒水卡+活动券）
total_card_balance NUMERIC(14,2) NOT NULL DEFAULT 0, -- 总卡余额
-- 活跃度指标
days_since_last INTEGER, -- 距离最近消费的天数
is_active_7d BOOLEAN NOT NULL DEFAULT FALSE, -- 近7天是否活跃
is_active_30d BOOLEAN NOT NULL DEFAULT FALSE, -- 近30天是否活跃
is_active_90d BOOLEAN NOT NULL DEFAULT FALSE, -- 近90天是否活跃
-- 客户分层标签
customer_tier VARCHAR(20), -- 客户分层（高价值/中等/低活跃/流失）
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_member_consumption UNIQUE (site_id, member_id, stat_date)
);
COMMENT ON TABLE billiards_dws.dws_member_consumption_summary IS '会员消费汇总：按会员统计消费行为和滚动窗口指标，散客不入此表';
COMMENT ON COLUMN billiards_dws.dws_member_consumption_summary.member_id IS '会员ID：member_id=0散客不统计';
COMMENT ON COLUMN billiards_dws.dws_member_consumption_summary.cash_card_balance IS '储值卡余额：card_type_id=2793249295533893';
COMMENT ON COLUMN billiards_dws.dws_member_consumption_summary.gift_card_balance IS '赠送卡余额：台费卡+酒水卡+活动抵用券';
CREATE INDEX idx_dws_member_consumption_date ON billiards_dws.dws_member_consumption_summary (stat_date);
CREATE INDEX idx_dws_member_consumption_member ON billiards_dws.dws_member_consumption_summary (member_id, stat_date);
CREATE INDEX idx_dws_member_consumption_tier ON billiards_dws.dws_member_consumption_summary (customer_tier);
-- -----------------------------------------------------------------------------
-- 12. dws_member_visit_detail - 会员来店明细表
-- 说明:
-- - 以"会员+订单"为粒度，记录每次来店消费明细
-- - 散客处理: member_id=0 不进入此表
-- - 数据来源: dwd_settlement_head + 关联明细表
-- - 更新频率: 每日增量更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_visit_detail CASCADE;
CREATE TABLE billiards_dws.dws_member_visit_detail (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
member_id BIGINT NOT NULL, -- 会员ID（散客不入此表）
order_settle_id BIGINT NOT NULL, -- 结账单ID
visit_date DATE NOT NULL, -- 来店日期
visit_time TIMESTAMPTZ, -- 来店时间
-- 会员信息快照
member_nickname VARCHAR(100), -- 会员昵称
member_mobile VARCHAR(20), -- 手机号
member_birthday DATE, -- 会员生日（关联dim_member）
-- 台桌信息
table_id BIGINT, -- 台桌ID
table_name VARCHAR(50), -- 台桌名称
area_name VARCHAR(50), -- 区域名称（原始）
area_category VARCHAR(20), -- 区域分类（散台区/包厢区/VIP区）
-- 消费金额明细
table_fee NUMERIC(12,2) NOT NULL DEFAULT 0, -- 台费
goods_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 商品金额
assistant_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 助教服务金额
total_consume NUMERIC(12,2) NOT NULL DEFAULT 0, -- 消费总额（正价）
total_discount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 优惠总额
actual_pay NUMERIC(12,2) NOT NULL DEFAULT 0, -- 实付金额
-- 支付方式明细
cash_pay NUMERIC(12,2) NOT NULL DEFAULT 0, -- 现金/刷卡支付
cash_card_pay NUMERIC(12,2) NOT NULL DEFAULT 0, -- 储值卡支付
gift_card_pay NUMERIC(12,2) NOT NULL DEFAULT 0, -- 赠送卡支付
groupbuy_pay NUMERIC(12,2) NOT NULL DEFAULT 0, -- 团购券支付
-- 时长信息
table_duration_min INTEGER NOT NULL DEFAULT 0, -- 台桌使用时长（分钟）
assistant_duration_min INTEGER NOT NULL DEFAULT 0, -- 助教服务时长（分钟）
-- 助教服务明细（JSON格式，便于存储多个助教）
assistant_services JSONB, -- 助教服务列表 [{assistant_id, nickname, duration_min, amount}]
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_member_visit UNIQUE (site_id, member_id, order_settle_id)
);
COMMENT ON TABLE billiards_dws.dws_member_visit_detail IS '会员来店明细：按会员+订单记录每次来店消费，包含台桌、助教、支付明细';
COMMENT ON COLUMN billiards_dws.dws_member_visit_detail.member_id IS '会员ID：member_id=0散客不入此表';
COMMENT ON COLUMN billiards_dws.dws_member_visit_detail.area_category IS '区域分类：来自cfg_area_category映射';
COMMENT ON COLUMN billiards_dws.dws_member_visit_detail.assistant_services IS 'JSON格式：[{assistant_id, nickname, duration_min, amount}]';
CREATE INDEX idx_dws_member_visit_date ON billiards_dws.dws_member_visit_detail (visit_date);
CREATE INDEX idx_dws_member_visit_member ON billiards_dws.dws_member_visit_detail (member_id, visit_date);
CREATE INDEX idx_dws_member_visit_order ON billiards_dws.dws_member_visit_detail (order_settle_id);
-- =============================================================================
-- 第四部分：财务维度（7张）
-- =============================================================================
-- -----------------------------------------------------------------------------
-- 13. dws_finance_daily_summary - 财务日度汇总表
-- 说明:
-- - 以"日期"为粒度，汇总当日财务数据
-- - 时间分层: 通过 stat_date 筛选实现近2天/近1月/近3月/全量
-- - 时间口径: 本周起始为周一，本月/季度起始为第一天0点
-- - 更新频率: 每小时更新当日数据
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_daily_summary CASCADE;
CREATE TABLE billiards_dws.dws_finance_daily_summary (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
stat_date DATE NOT NULL, -- 统计日期
-- 发生额（正价）
gross_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 发生额合计
table_fee_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 台费正价
goods_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 商品正价
assistant_pd_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教基础课正价（陪打）
assistant_cx_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教激励课正价（超休）
-- 优惠拆分
discount_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 优惠合计
discount_groupbuy NUMERIC(14,2) NOT NULL DEFAULT 0, -- 团购优惠 = coupon_amount - 团购支付金额
discount_vip NUMERIC(14,2) NOT NULL DEFAULT 0, -- 会员折扣（member_discount_amount）
discount_gift_card NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送卡抵扣
discount_manual NUMERIC(14,2) NOT NULL DEFAULT 0, -- 手动调整（adjust_amount）
discount_rounding NUMERIC(14,2) NOT NULL DEFAULT 0, -- 抹零（rounding_amount）
discount_other NUMERIC(14,2) NOT NULL DEFAULT 0, -- 其他优惠
-- 确认收入
confirmed_income NUMERIC(14,2) NOT NULL DEFAULT 0, -- 确认收入 = 发生额 - 优惠
-- 现金流入
cash_inflow_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 现金流入合计
cash_pay_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 收银实付（pay_amount）
groupbuy_pay_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 团购支付金额
platform_settlement_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 平台回款金额（导入）
platform_fee_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 平台佣金+服务费（导入）
recharge_cash_inflow NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值现金流入（不含赠送）
-- 储值卡消费（非现金流入）
card_consume_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 卡消费合计
cash_card_consume NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值卡消费
gift_card_consume NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送卡消费
-- 现金流出
cash_outflow_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 现金流出合计（支出汇总）
-- 现金余额变动
cash_balance_change NUMERIC(14,2) NOT NULL DEFAULT 0, -- 现金余额变动 = 流入 - 流出
-- 充值统计
recharge_count INTEGER NOT NULL DEFAULT 0, -- 充值笔数
recharge_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值总额（含赠送）
recharge_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值现金部分
recharge_gift NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值赠送部分
first_recharge_count INTEGER NOT NULL DEFAULT 0, -- 首充笔数
first_recharge_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 首充金额
renewal_count INTEGER NOT NULL DEFAULT 0, -- 续充笔数
renewal_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 续充金额
-- 订单统计
order_count INTEGER NOT NULL DEFAULT 0, -- 结账单数
member_order_count INTEGER NOT NULL DEFAULT 0, -- 会员订单数
guest_order_count INTEGER NOT NULL DEFAULT 0, -- 散客订单数（member_id=0）
avg_order_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 平均客单价
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_finance_daily UNIQUE (site_id, stat_date)
);
COMMENT ON TABLE billiards_dws.dws_finance_daily_summary IS '财务日度汇总：按日期汇总发生额、优惠、收入、现金流、充值等财务指标';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.gross_amount IS '发生额：table_charge_money + goods_money + assistant_pd_money + assistant_cx_money';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.discount_groupbuy IS '团购优惠：coupon_amount - 团购支付金额（pl_coupon_sale_amount或groupbuy_redemption.ledger_unit_price）';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.platform_settlement_amount IS '平台回款金额：来自dws_platform_settlement.settlement_amount';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.platform_fee_amount IS '平台费用：commission_amount + service_fee';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.first_recharge_count IS '首充：dwd_recharge_order.is_first=1';
CREATE INDEX idx_dws_finance_daily_date ON billiards_dws.dws_finance_daily_summary (stat_date);
CREATE INDEX idx_dws_finance_daily_site ON billiards_dws.dws_finance_daily_summary (site_id, stat_date);
-- -----------------------------------------------------------------------------
-- 14. dws_finance_income_structure - 收入结构分析表
-- 说明:
-- - 以"日期+区域/类型"为粒度，分析收入结构
-- - 区域分类: 使用cfg_area_category映射
-- - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_income_structure CASCADE;
CREATE TABLE billiards_dws.dws_finance_income_structure (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
stat_date DATE NOT NULL, -- 统计日期
-- 分类维度
structure_type VARCHAR(20) NOT NULL, -- 结构类型: AREA（区域）, INCOME_TYPE（收入类型）
category_code VARCHAR(30) NOT NULL, -- 分类代码
category_name VARCHAR(50) NOT NULL, -- 分类名称
-- 收入金额
income_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 收入金额
income_ratio NUMERIC(5,4) NOT NULL DEFAULT 0, -- 收入占比
-- 订单统计
order_count INTEGER NOT NULL DEFAULT 0, -- 订单数
-- 时长统计（仅台费/助教相关）
duration_minutes INTEGER NOT NULL DEFAULT 0, -- 时长（分钟）
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_finance_income_structure UNIQUE (site_id, stat_date, structure_type, category_code)
);
COMMENT ON TABLE billiards_dws.dws_finance_income_structure IS '收入结构分析：按区域/收入类型分析收入构成';
COMMENT ON COLUMN billiards_dws.dws_finance_income_structure.structure_type IS '结构类型：AREA=按区域, INCOME_TYPE=按收入类型（台费/商品/助教）';
COMMENT ON COLUMN billiards_dws.dws_finance_income_structure.category_code IS '分类代码：区域用SCATTER/ROOM/VIP, 收入类型用TABLE_FEE/GOODS/ASSISTANT';
CREATE INDEX idx_dws_finance_income_date ON billiards_dws.dws_finance_income_structure (stat_date);
CREATE INDEX idx_dws_finance_income_type ON billiards_dws.dws_finance_income_structure (structure_type, category_code);
-- -----------------------------------------------------------------------------
-- 15. dws_finance_discount_detail - 优惠明细表
-- 说明:
-- - 以"日期+优惠类型"为粒度，分析优惠构成
-- - 优惠类型: 团购优惠、会员折扣、赠送卡、手动调整、抹零、大客户优惠、其他
-- - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_discount_detail CASCADE;
CREATE TABLE billiards_dws.dws_finance_discount_detail (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
stat_date DATE NOT NULL, -- 统计日期
-- 优惠类型
discount_type_code VARCHAR(30) NOT NULL, -- 优惠类型代码
discount_type_name VARCHAR(50) NOT NULL, -- 优惠类型名称
-- 优惠金额
discount_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 优惠金额
discount_ratio NUMERIC(5,4) NOT NULL DEFAULT 0, -- 优惠占比（占总优惠）
-- 使用统计
usage_count INTEGER NOT NULL DEFAULT 0, -- 使用次数
affected_orders INTEGER NOT NULL DEFAULT 0, -- 影响订单数
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_finance_discount_detail UNIQUE (site_id, stat_date, discount_type_code)
);
COMMENT ON TABLE billiards_dws.dws_finance_discount_detail IS '优惠明细：按优惠类型分析优惠构成';
COMMENT ON COLUMN billiards_dws.dws_finance_discount_detail.discount_type_code IS '优惠类型：GROUPBUY/VIP/GIFT_CARD/MANUAL/ROUNDING/BIG_CUSTOMER/OTHER';
CREATE INDEX idx_dws_finance_discount_date ON billiards_dws.dws_finance_discount_detail (stat_date);
CREATE INDEX idx_dws_finance_discount_type ON billiards_dws.dws_finance_discount_detail (discount_type_code);
-- -----------------------------------------------------------------------------
-- 16. dws_finance_recharge_summary - 充值统计表
-- 说明:
-- - 以"日期"为粒度，统计充值数据
-- - 区分首充/续充（is_first字段）
-- - 区分现金充值/赠送金额
-- - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_recharge_summary CASCADE;
CREATE TABLE billiards_dws.dws_finance_recharge_summary (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
stat_date DATE NOT NULL, -- 统计日期
-- 充值汇总
recharge_count INTEGER NOT NULL DEFAULT 0, -- 充值笔数
recharge_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值总额（含赠送）
recharge_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 现金充值金额
recharge_gift NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送金额
-- 首充统计
first_recharge_count INTEGER NOT NULL DEFAULT 0, -- 首充笔数
first_recharge_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 首充现金
first_recharge_gift NUMERIC(14,2) NOT NULL DEFAULT 0, -- 首充赠送
first_recharge_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 首充总额
-- 续充统计
renewal_count INTEGER NOT NULL DEFAULT 0, -- 续充笔数
renewal_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 续充现金
renewal_gift NUMERIC(14,2) NOT NULL DEFAULT 0, -- 续充赠送
renewal_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 续充总额
-- 充值会员统计
recharge_member_count INTEGER NOT NULL DEFAULT 0, -- 充值会员数（去重）
new_member_count INTEGER NOT NULL DEFAULT 0, -- 新增会员数
-- 卡余额快照（当日末）
total_card_balance NUMERIC(14,2) NOT NULL DEFAULT 0, -- 全部会员卡余额
cash_card_balance NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值卡余额
gift_card_balance NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送卡余额
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_finance_recharge UNIQUE (site_id, stat_date)
);
COMMENT ON TABLE billiards_dws.dws_finance_recharge_summary IS '充值统计：按日期统计充值数据，区分首充/续充、现金/赠送';
COMMENT ON COLUMN billiards_dws.dws_finance_recharge_summary.first_recharge_count IS '首充：dwd_recharge_order.is_first=1';
COMMENT ON COLUMN billiards_dws.dws_finance_recharge_summary.cash_card_balance IS '储值卡余额：card_type_id=2793249295533893';
CREATE INDEX idx_dws_finance_recharge_date ON billiards_dws.dws_finance_recharge_summary (stat_date);
-- -----------------------------------------------------------------------------
-- 17. dws_finance_expense_summary - 支出结构表
-- 说明:
-- - 以"月份+支出类型"为粒度，记录支出数据
-- - 数据来源: Excel手动导入
-- - 支出类型: 房租、水电、物业、工资、报销、平台费等
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_expense_summary CASCADE;
CREATE TABLE billiards_dws.dws_finance_expense_summary (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
expense_month DATE NOT NULL, -- 支出月份（月第一天）
-- 支出分类
expense_type_code VARCHAR(30) NOT NULL, -- 支出类型代码
expense_type_name VARCHAR(50) NOT NULL, -- 支出类型名称
expense_category VARCHAR(20), -- 支出大类（固定成本/变动成本/其他）
-- 支出金额
expense_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 支出金额
-- 明细（可选）
expense_detail TEXT, -- 支出明细说明
-- 导入信息
import_batch_no VARCHAR(50), -- 导入批次号
import_file_name VARCHAR(200), -- 导入文件名
import_time TIMESTAMPTZ, -- 导入时间
import_user VARCHAR(50), -- 导入操作人
-- 备注
remark TEXT, -- 备注
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_finance_expense UNIQUE (site_id, expense_month, expense_type_code, import_batch_no)
);
COMMENT ON TABLE billiards_dws.dws_finance_expense_summary IS '支出结构：Excel导入，按月份+类型记录支出数据';
COMMENT ON COLUMN billiards_dws.dws_finance_expense_summary.expense_type_code IS '支出类型：RENT/UTILITY/PROPERTY/SALARY/REIMBURSE/PLATFORM_FEE/OTHER';
COMMENT ON COLUMN billiards_dws.dws_finance_expense_summary.expense_category IS '支出大类：FIXED_COST/VARIABLE_COST/OTHER';
CREATE INDEX idx_dws_finance_expense_month ON billiards_dws.dws_finance_expense_summary (expense_month);
CREATE INDEX idx_dws_finance_expense_type ON billiards_dws.dws_finance_expense_summary (expense_type_code);
CREATE INDEX idx_dws_finance_expense_batch ON billiards_dws.dws_finance_expense_summary (import_batch_no);
-- -----------------------------------------------------------------------------
-- 18. dws_assistant_finance_analysis - 助教收支分析表
-- 说明:
-- - 以"日期+助教"为粒度，分析助教产出的收入和成本
-- - 数据来源: dwd_assistant_service_log + dws_assistant_salary_calc
-- - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_finance_analysis CASCADE;
CREATE TABLE billiards_dws.dws_assistant_finance_analysis (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
stat_date DATE NOT NULL, -- 统计日期
assistant_id BIGINT NOT NULL, -- 助教ID
assistant_nickname VARCHAR(50), -- 助教花名
-- 收入（助教产出）
revenue_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教产出收入（ledger_amount汇总）
revenue_base NUMERIC(14,2) NOT NULL DEFAULT 0, -- 基础课收入
revenue_bonus NUMERIC(14,2) NOT NULL DEFAULT 0, -- 附加课收入
revenue_room NUMERIC(14,2) NOT NULL DEFAULT 0, -- 包厢/房间收入
-- 成本（助教工资分摊）
cost_daily NUMERIC(14,2) NOT NULL DEFAULT 0, -- 日均工资成本（月工资/工作天数）
-- 毛利
gross_profit NUMERIC(14,2) NOT NULL DEFAULT 0, -- 毛利 = 收入 - 成本
gross_margin NUMERIC(5,4) NOT NULL DEFAULT 0, -- 毛利率
-- 服务统计
service_count INTEGER NOT NULL DEFAULT 0, -- 服务次数
service_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 服务小时数
room_service_count INTEGER NOT NULL DEFAULT 0, -- 包厢/房间服务次数
room_service_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 包厢/房间服务小时数
unique_customers INTEGER NOT NULL DEFAULT 0, -- 服务客户数
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_assistant_finance UNIQUE (site_id, stat_date, assistant_id)
);
COMMENT ON TABLE billiards_dws.dws_assistant_finance_analysis IS '助教收支分析：按日期+助教分析产出收入和工资成本';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.revenue_total IS '助教产出收入：dwd_assistant_service_log.ledger_amount汇总';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.cost_daily IS '日均工资成本：月工资/当月工作天数';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.revenue_room IS '包厢/房间收入';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.room_service_count IS '包厢/房间服务次数';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.room_service_hours IS '包厢/房间服务小时数';
CREATE INDEX idx_dws_assistant_finance_date ON billiards_dws.dws_assistant_finance_analysis (stat_date);
CREATE INDEX idx_dws_assistant_finance_asst ON billiards_dws.dws_assistant_finance_analysis (assistant_id, stat_date);
-- -----------------------------------------------------------------------------
-- 19. dws_platform_settlement - 平台回款/服务费表
-- 说明:
-- - 以"回款日期+平台+订单"为粒度，记录平台结算数据
-- - 数据来源: Excel手动导入
-- - 字段: 回款金额、佣金、服务费、回款日期、平台类型、订单关联键
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_platform_settlement CASCADE;
CREATE TABLE billiards_dws.dws_platform_settlement (
id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
settlement_date DATE NOT NULL, -- 回款日期
-- 平台信息
platform_type VARCHAR(30) NOT NULL, -- 平台类型（美团/抖音/大众点评/其他）
platform_name VARCHAR(50), -- 平台名称
-- 订单关联
platform_order_no VARCHAR(100), -- 平台订单号
order_settle_id BIGINT, -- 关联的结账单ID（可选）
-- 金额明细
settlement_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 回款金额（实际入账）
commission_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 佣金（平台抽成）
service_fee NUMERIC(14,2) NOT NULL DEFAULT 0, -- 服务费
gross_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 订单原始金额
-- 导入信息
import_batch_no VARCHAR(50), -- 导入批次号
import_file_name VARCHAR(200), -- 导入文件名
import_time TIMESTAMPTZ, -- 导入时间
import_user VARCHAR(50), -- 导入操作人
-- 备注
remark TEXT, -- 备注
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE billiards_dws.dws_platform_settlement IS '平台回款/服务费：Excel导入，记录各平台结算明细';
COMMENT ON COLUMN billiards_dws.dws_platform_settlement.platform_type IS '平台类型：MEITUAN/DOUYIN/DIANPING/OTHER';
COMMENT ON COLUMN billiards_dws.dws_platform_settlement.settlement_amount IS '回款金额：实际入账金额 = gross_amount - commission_amount - service_fee';
CREATE INDEX idx_dws_platform_settlement_date ON billiards_dws.dws_platform_settlement (settlement_date);
CREATE INDEX idx_dws_platform_settlement_platform ON billiards_dws.dws_platform_settlement (platform_type);
CREATE INDEX idx_dws_platform_settlement_order ON billiards_dws.dws_platform_settlement (order_settle_id);
CREATE INDEX idx_dws_platform_settlement_batch ON billiards_dws.dws_platform_settlement (import_batch_no);
-- =============================================================================
-- 第五部分：订单汇总（保留原有表，增强字段）
-- =============================================================================
-- -----------------------------------------------------------------------------
-- 20. dws_order_summary - 订单汇总表
-- 说明:
-- - 以"订单"为粒度，汇总订单级别的数据
-- - 作为订单级别的聚合层，用于财务与经营分析
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_order_summary CASCADE;
CREATE TABLE billiards_dws.dws_order_summary (
site_id BIGINT NOT NULL, -- 门店ID
order_settle_id BIGINT NOT NULL, -- 结账单ID
order_trade_no VARCHAR(64), -- 交易单号
order_date DATE NOT NULL, -- 订单日期
tenant_id BIGINT NOT NULL, -- 租户ID
member_id BIGINT, -- 会员ID
member_flag BOOLEAN NOT NULL DEFAULT FALSE, -- 是否会员订单
recharge_order_flag BOOLEAN NOT NULL DEFAULT FALSE, -- 是否充值订单
item_count INTEGER NOT NULL DEFAULT 0, -- 项目条目数
total_item_quantity INTEGER NOT NULL DEFAULT 0, -- 项目总数量
table_fee_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 台费金额
assistant_service_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教服务金额
goods_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 商品金额
group_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 团购金额
total_coupon_deduction NUMERIC(14,2) NOT NULL DEFAULT 0, -- 优惠券抵扣
member_discount_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 会员折扣
manual_discount_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 手动优惠
order_original_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 订单原价
order_final_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 订单最终金额
stored_card_deduct NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值卡抵扣
external_paid_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 外部支付金额
total_paid_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 实收金额
book_table_flow NUMERIC(14,2) NOT NULL DEFAULT 0, -- 台费流水
book_assistant_flow NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教流水
book_goods_flow NUMERIC(14,2) NOT NULL DEFAULT 0, -- 商品流水
book_group_flow NUMERIC(14,2) NOT NULL DEFAULT 0, -- 团购流水
book_order_flow NUMERIC(14,2) NOT NULL DEFAULT 0, -- 订单总流水
order_effective_consume_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 有效消费现金
order_effective_recharge_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 有效充值现金
order_effective_flow NUMERIC(14,2) NOT NULL DEFAULT 0, -- 有效流水
refund_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 退款金额
net_income NUMERIC(14,2) NOT NULL DEFAULT 0, -- 净收入
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT pk_dws_order_summary PRIMARY KEY (site_id, order_settle_id)
);
COMMENT ON TABLE billiards_dws.dws_order_summary IS '订单汇总：按订单汇总各项金额、优惠、支付信息';
COMMENT ON COLUMN billiards_dws.dws_order_summary.order_date IS '订单日期：优先 pay_time，缺失时取 create_time';
COMMENT ON COLUMN billiards_dws.dws_order_summary.recharge_order_flag IS '充值订单标记：消费金额=0 且 实收>0';
COMMENT ON COLUMN billiards_dws.dws_order_summary.order_original_amount IS '原价金额 = 实收 + 优惠/折扣';
COMMENT ON COLUMN billiards_dws.dws_order_summary.external_paid_amount IS '外部支付金额（实收-储值抵扣）';
COMMENT ON COLUMN billiards_dws.dws_order_summary.book_order_flow IS '订单总流水 = 台费 + 助教 + 商品 + 团购';
COMMENT ON COLUMN billiards_dws.dws_order_summary.net_income IS '净收入 = 实收 - 退款';
CREATE INDEX idx_dws_order_summary_member ON billiards_dws.dws_order_summary (member_id, order_date);
CREATE INDEX idx_dws_order_summary_site_date ON billiards_dws.dws_order_summary (site_id, order_date);
CREATE INDEX idx_dws_order_summary_trade_no ON billiards_dws.dws_order_summary (order_trade_no);
-- =============================================================================
-- 第六部分：时间分层辅助视图
-- 说明:
-- - 时间分层通过查询条件实现，不单独创建分层表
-- - 提供常用时间窗口的参考视图
-- - 时间口径: 周起始为周一，月/季度起始为第一天0点
-- =============================================================================
-- -----------------------------------------------------------------------------
-- 时间窗口计算函数
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION billiards_dws.get_time_window(
p_window_type VARCHAR(30), -- 窗口类型: THIS_WEEK, LAST_WEEK, THIS_MONTH, LAST_MONTH, LAST_3_MONTHS_EXCL_CURRENT, LAST_3_MONTHS_INCL_CURRENT, THIS_QUARTER, LAST_QUARTER, LAST_6_MONTHS, LAST_2_DAYS, LAST_1_MONTH, LAST_3_MONTHS
p_base_date DATE DEFAULT CURRENT_DATE
)
RETURNS TABLE (
window_start DATE,
window_end DATE
) AS $$
DECLARE
v_year INTEGER;
v_month INTEGER;
v_quarter INTEGER;
BEGIN
v_year := EXTRACT(YEAR FROM p_base_date);
v_month := EXTRACT(MONTH FROM p_base_date);
v_quarter := EXTRACT(QUARTER FROM p_base_date);
CASE p_window_type
-- 本周（周一起始）
WHEN 'THIS_WEEK' THEN
window_start := DATE_TRUNC('week', p_base_date)::DATE;
window_end := p_base_date;
-- 上周
WHEN 'LAST_WEEK' THEN
window_start := (DATE_TRUNC('week', p_base_date) - INTERVAL '7 days')::DATE;
window_end := (DATE_TRUNC('week', p_base_date) - INTERVAL '1 day')::DATE;
-- 本月
WHEN 'THIS_MONTH' THEN
window_start := DATE_TRUNC('month', p_base_date)::DATE;
window_end := p_base_date;
-- 上月
WHEN 'LAST_MONTH' THEN
window_start := (DATE_TRUNC('month', p_base_date) - INTERVAL '1 month')::DATE;
window_end := (DATE_TRUNC('month', p_base_date) - INTERVAL '1 day')::DATE;
-- 前3个月（不含本月）
WHEN 'LAST_3_MONTHS_EXCL_CURRENT' THEN
window_start := (DATE_TRUNC('month', p_base_date) - INTERVAL '3 months')::DATE;
window_end := (DATE_TRUNC('month', p_base_date) - INTERVAL '1 day')::DATE;
-- 前3个月（含本月）
WHEN 'LAST_3_MONTHS_INCL_CURRENT' THEN
window_start := (DATE_TRUNC('month', p_base_date) - INTERVAL '2 months')::DATE;
window_end := p_base_date;
-- 本季度
WHEN 'THIS_QUARTER' THEN
window_start := DATE_TRUNC('quarter', p_base_date)::DATE;
window_end := p_base_date;
-- 上季度
WHEN 'LAST_QUARTER' THEN
window_start := (DATE_TRUNC('quarter', p_base_date) - INTERVAL '3 months')::DATE;
window_end := (DATE_TRUNC('quarter', p_base_date) - INTERVAL '1 day')::DATE;
-- 最近半年（不含本月）
WHEN 'LAST_6_MONTHS' THEN
window_start := (DATE_TRUNC('month', p_base_date) - INTERVAL '6 months')::DATE;
window_end := (DATE_TRUNC('month', p_base_date) - INTERVAL '1 day')::DATE;
-- 近2天
WHEN 'LAST_2_DAYS' THEN
window_start := (p_base_date - INTERVAL '1 day')::DATE;
window_end := p_base_date;
-- 近1月
WHEN 'LAST_1_MONTH' THEN
window_start := (p_base_date - INTERVAL '1 month')::DATE;
window_end := p_base_date;
-- 近3月
WHEN 'LAST_3_MONTHS' THEN
window_start := (p_base_date - INTERVAL '3 months')::DATE;
window_end := p_base_date;
ELSE
-- 默认全量
window_start := '2000-01-01'::DATE;
window_end := p_base_date;
END CASE;
RETURN NEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
COMMENT ON FUNCTION billiards_dws.get_time_window IS '时间窗口计算函数：根据窗口类型返回起止日期，周起始为周一';
-- -----------------------------------------------------------------------------
-- 环比计算辅助函数
-- 说明: 环比规则为"对比上一个等长区间"
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION billiards_dws.get_comparison_window(
p_start_date DATE,
p_end_date DATE
)
RETURNS TABLE (
prev_start DATE,
prev_end DATE
) AS $$
DECLARE
v_interval INTERVAL;
BEGIN
-- 计算区间长度
v_interval := (p_end_date - p_start_date + 1) * INTERVAL '1 day';
-- 上一个等长区间
prev_end := p_start_date - INTERVAL '1 day';
prev_start := (prev_end - v_interval + INTERVAL '1 day')::DATE;
RETURN NEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
COMMENT ON FUNCTION billiards_dws.get_comparison_window IS '环比窗口计算：返回上一个等长区间的起止日期';
-- =============================================================================
-- 第六部分：指数算法（6张）
-- =============================================================================
-- -----------------------------------------------------------------------------
-- 21. cfg_index_parameters - 指数算法参数配置表
-- 说明:
-- - 存储客户召回/新客转化/客户唤回/亲密指数的算法参数
-- - 支持按时间生效，便于参数调优和历史追溯
-- - 参数类型: RECALL（召回指数）, INTIMACY（亲密指数）, NCI（新客转化）, WBI（唤回指数）
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_index_parameters CASCADE;
CREATE TABLE billiards_dws.cfg_index_parameters (
param_id SERIAL PRIMARY KEY, -- 参数ID（自增）
index_type VARCHAR(50) NOT NULL, -- 指数类型: RECALL/INTIMACY
param_name VARCHAR(100) NOT NULL, -- 参数名称
param_value NUMERIC(14,6) NOT NULL, -- 参数值
description TEXT, -- 参数说明
effective_from DATE NOT NULL DEFAULT CURRENT_DATE, -- 生效起始日期
effective_to DATE, -- 生效截止日期（NULL=永久有效）
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_cfg_index_parameters UNIQUE (index_type, param_name, effective_from)
);
COMMENT ON TABLE billiards_dws.cfg_index_parameters IS '指数算法参数配置表：存储 RS/OS/MS/ML/NCI/WBI 等指数参数';
COMMENT ON COLUMN billiards_dws.cfg_index_parameters.index_type IS '指数类型：RS/OS/MS/ML/NCI/WBI（兼容保留 RECALL/INTIMACY）';
COMMENT ON COLUMN billiards_dws.cfg_index_parameters.param_name IS '参数名称：如lookback_days, halflife_new, weight_overdue等';
CREATE INDEX idx_cfg_index_params_type ON billiards_dws.cfg_index_parameters (index_type);
CREATE INDEX idx_cfg_index_params_effective ON billiards_dws.cfg_index_parameters (effective_from, effective_to);
-- -----------------------------------------------------------------------------
-- 22. dws_member_recall_index - 客户召回指数表
-- 说明:
-- - 以"会员"为粒度，计算客户召回的必要性和紧急程度
-- - 算法基于：超期紧急性、新客户加分、刚充值加分、热度断档加分
-- - 尊重客户个人到店周期（μ=中位数, σ=MAD）
-- - 散客(member_id=0)不参与计算
-- - 更新频率: 每2小时
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_recall_index CASCADE;
CREATE TABLE billiards_dws.dws_member_recall_index (
recall_id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
member_id BIGINT NOT NULL, -- 会员ID (散客不计算)
-- 计算输入特征
days_since_last_visit INTEGER, -- 距最近一次到店的天数 (t)
visit_interval_median NUMERIC(10,2), -- 到店周期中位数 (μ)
visit_interval_mad NUMERIC(10,2), -- 到店周期MAD (σ)
days_since_first_visit INTEGER, -- 距首访天数
days_since_last_recharge INTEGER, -- 距最近充值天数
visits_last_14_days INTEGER NOT NULL DEFAULT 0, -- 近14天到店次数
visits_last_60_days INTEGER NOT NULL DEFAULT 0, -- 近60天到店次数
-- 分项得分
score_overdue NUMERIC(10,4), -- 超期紧急性得分: 1-exp(-max(0,(t-μ)/σ))
score_new_bonus NUMERIC(10,4), -- 新客户加分: decay(d_first, h_new)
score_recharge_bonus NUMERIC(10,4), -- 刚充值加分: decay(d_recharge, h_re)
score_hot_drop NUMERIC(10,4), -- 热度断档加分: max(0, ln(1+(r14/r60-1)))
-- 最终分数
raw_score NUMERIC(14,6), -- Raw Score (无上限)
display_score NUMERIC(4,2), -- Display Score (0-10)
-- 元数据
calc_time TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计算时间
calc_version INTEGER NOT NULL DEFAULT 1, -- 计算版本（参数变更时递增）
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_member_recall UNIQUE (site_id, member_id)
);
COMMENT ON TABLE billiards_dws.dws_member_recall_index IS '客户召回指数：衡量客户召回的必要性和紧急程度，尊重个人到店周期';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.visit_interval_median IS '到店周期中位数(μ)：近60天到店间隔的中位数';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.visit_interval_mad IS '到店周期MAD(σ)：中位绝对偏差，下限为sigma_min=2';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.score_overdue IS '超期紧急性：1-exp(-z), z=max(0,(t-μ)/σ)';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.raw_score IS 'Raw Score：无上限，公式=w_over*overdue+w_new*new+w_re*recharge+w_hot*hot_drop';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.display_score IS 'Display Score：0-10，Winsorize(P5,P95)+MinMax映射';
CREATE INDEX idx_dws_recall_display ON billiards_dws.dws_member_recall_index (site_id, display_score DESC);
CREATE INDEX idx_dws_recall_raw ON billiards_dws.dws_member_recall_index (site_id, raw_score DESC);
CREATE INDEX idx_dws_recall_calc_time ON billiards_dws.dws_member_recall_index (calc_time);
-- -----------------------------------------------------------------------------
-- 23. dws_member_newconv_index - 新客转化指数表
-- 说明:
-- - 以"会员"为粒度，衡量新客转化潜力与触达优先级
-- - 更新频率: 每2小时
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_newconv_index CASCADE;
CREATE TABLE billiards_dws.dws_member_newconv_index (
newconv_id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
member_id BIGINT NOT NULL, -- 会员ID
status VARCHAR(30), -- 状态标记
segment VARCHAR(10), -- 分群标签
member_create_time TIMESTAMPTZ, -- 注册/建档时间
first_visit_time TIMESTAMPTZ, -- 首访时间
last_visit_time TIMESTAMPTZ, -- 最近到店时间
last_recharge_time TIMESTAMPTZ, -- 最近充值时间
t_v NUMERIC(6,2), -- 访问间隔特征
t_r NUMERIC(6,2), -- 充值间隔特征
t_a NUMERIC(6,2), -- 活跃度特征
visits_14d INTEGER NOT NULL DEFAULT 0, -- 近14天到店次数
visits_60d INTEGER NOT NULL DEFAULT 0, -- 近60天到店次数
visits_total INTEGER NOT NULL DEFAULT 0, -- 累计到店次数
spend_30d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近30天消费
spend_180d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近180天消费
sv_balance NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值余额
recharge_60d_amt NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近60天充值
interval_count INTEGER NOT NULL DEFAULT 0, -- 访次间隔计数
need_new NUMERIC(10,4), -- 需求强度
salvage_new NUMERIC(10,4), -- 挽回强度
recharge_new NUMERIC(10,4), -- 充值驱动
value_new NUMERIC(10,4), -- 价值权重
welcome_new NUMERIC(10,4), -- 欢迎触达权重
raw_score_welcome NUMERIC(14,6), -- Raw Score（欢迎阶段）
raw_score_convert NUMERIC(14,6), -- Raw Score（转化阶段）
raw_score NUMERIC(14,6), -- Raw Score（综合）
display_score_welcome NUMERIC(4,2), -- Display Score（欢迎阶段）
display_score_convert NUMERIC(4,2), -- Display Score（转化阶段）
display_score NUMERIC(4,2), -- Display Score（综合）
last_wechat_touch_time TIMESTAMPTZ, -- 最近触达时间
calc_time TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计算时间
calc_version INTEGER NOT NULL DEFAULT 1, -- 计算版本
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_member_newconv UNIQUE (site_id, member_id)
);
COMMENT ON TABLE billiards_dws.dws_member_newconv_index IS '新客转化指数：衡量新客转化潜力与触达优先级';
COMMENT ON COLUMN billiards_dws.dws_member_newconv_index.raw_score IS 'Raw Score：无上限，综合各项特征权重后的原始分数';
COMMENT ON COLUMN billiards_dws.dws_member_newconv_index.display_score IS 'Display Score：0-10，标准化展示分';
CREATE INDEX idx_dws_newconv_display ON billiards_dws.dws_member_newconv_index (site_id, display_score DESC);
-- -----------------------------------------------------------------------------
-- 24. dws_member_winback_index - 客户唤回指数表
-- 说明:
-- - 以"会员"为粒度，衡量客户唤回紧急度与优先级
-- - 更新频率: 每2小时
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_winback_index CASCADE;
CREATE TABLE billiards_dws.dws_member_winback_index (
winback_id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
member_id BIGINT NOT NULL, -- 会员ID
status VARCHAR(30), -- 状态标记
segment VARCHAR(10), -- 分群标签
member_create_time TIMESTAMPTZ, -- 注册/建档时间
first_visit_time TIMESTAMPTZ, -- 首访时间
last_visit_time TIMESTAMPTZ, -- 最近到店时间
last_recharge_time TIMESTAMPTZ, -- 最近充值时间
t_v NUMERIC(6,2), -- 访问间隔特征
t_r NUMERIC(6,2), -- 充值间隔特征
t_a NUMERIC(6,2), -- 活跃度特征
visits_14d INTEGER NOT NULL DEFAULT 0, -- 近14天到店次数
visits_60d INTEGER NOT NULL DEFAULT 0, -- 近60天到店次数
visits_total INTEGER NOT NULL DEFAULT 0, -- 累计到店次数
spend_30d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近30天消费
spend_180d NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近180天消费
sv_balance NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值余额
recharge_60d_amt NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近60天充值
interval_count INTEGER NOT NULL DEFAULT 0, -- 访次间隔计数
overdue_old NUMERIC(10,4), -- 超期强度
drop_old NUMERIC(10,4), -- 流失强度
recharge_old NUMERIC(10,4), -- 充值驱动
value_old NUMERIC(10,4), -- 价值权重
raw_score NUMERIC(14,6), -- Raw Score（综合）
display_score NUMERIC(4,2), -- Display Score（综合）
last_wechat_touch_time TIMESTAMPTZ, -- 最近触达时间
calc_time TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计算时间
calc_version INTEGER NOT NULL DEFAULT 1, -- 计算版本
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
overdue_cdf_p NUMERIC(10,4), -- 超期CDF分位
ideal_interval_days NUMERIC(10,2), -- 理想到店间隔（天）
ideal_next_visit_date DATE, -- 理想下次到店日期
CONSTRAINT uk_dws_member_winback UNIQUE (site_id, member_id)
);
COMMENT ON TABLE billiards_dws.dws_member_winback_index IS '客户唤回指数：衡量客户回访紧急度与优先级';
COMMENT ON COLUMN billiards_dws.dws_member_winback_index.raw_score IS 'Raw Score：无上限，综合各项特征权重后的原始分数';
COMMENT ON COLUMN billiards_dws.dws_member_winback_index.display_score IS 'Display Score：0-10，标准化展示分';
CREATE INDEX idx_dws_winback_display ON billiards_dws.dws_member_winback_index (site_id, display_score DESC);
-- -----------------------------------------------------------------------------
-- 25. v_member_recall_priority - 召回优先级视图
-- 说明:
-- - 合并唤回指数(WBI)与新客转化指数(NCI)用于统一排序
-- -----------------------------------------------------------------------------
CREATE OR REPLACE VIEW billiards_dws.v_member_recall_priority AS
SELECT
dws_member_winback_index.site_id,
dws_member_winback_index.tenant_id,
dws_member_winback_index.member_id,
'WBI'::character varying(10) AS index_type,
dws_member_winback_index.status,
dws_member_winback_index.segment,
dws_member_winback_index.member_create_time,
dws_member_winback_index.first_visit_time,
dws_member_winback_index.last_visit_time,
dws_member_winback_index.last_recharge_time,
dws_member_winback_index.t_v,
dws_member_winback_index.t_r,
dws_member_winback_index.t_a,
dws_member_winback_index.visits_14d,
dws_member_winback_index.visits_60d,
dws_member_winback_index.visits_total,
dws_member_winback_index.spend_30d,
dws_member_winback_index.spend_180d,
dws_member_winback_index.sv_balance,
dws_member_winback_index.recharge_60d_amt,
NULL::numeric(10,4) AS need_new,
NULL::numeric(10,4) AS salvage_new,
NULL::numeric(10,4) AS recharge_new,
NULL::numeric(10,4) AS value_new,
NULL::numeric(10,4) AS welcome_new,
NULL::numeric(14,6) AS raw_score_welcome,
NULL::numeric(14,6) AS raw_score_convert,
dws_member_winback_index.raw_score,
NULL::numeric(4,2) AS display_score_welcome,
NULL::numeric(4,2) AS display_score_convert,
dws_member_winback_index.display_score,
dws_member_winback_index.last_wechat_touch_time,
dws_member_winback_index.calc_time
FROM billiards_dws.dws_member_winback_index
UNION ALL
SELECT
dws_member_newconv_index.site_id,
dws_member_newconv_index.tenant_id,
dws_member_newconv_index.member_id,
'NCI'::character varying(10) AS index_type,
dws_member_newconv_index.status,
dws_member_newconv_index.segment,
dws_member_newconv_index.member_create_time,
dws_member_newconv_index.first_visit_time,
dws_member_newconv_index.last_visit_time,
dws_member_newconv_index.last_recharge_time,
dws_member_newconv_index.t_v,
dws_member_newconv_index.t_r,
dws_member_newconv_index.t_a,
dws_member_newconv_index.visits_14d,
dws_member_newconv_index.visits_60d,
dws_member_newconv_index.visits_total,
dws_member_newconv_index.spend_30d,
dws_member_newconv_index.spend_180d,
dws_member_newconv_index.sv_balance,
dws_member_newconv_index.recharge_60d_amt,
dws_member_newconv_index.need_new,
dws_member_newconv_index.salvage_new,
dws_member_newconv_index.recharge_new,
dws_member_newconv_index.value_new,
dws_member_newconv_index.welcome_new,
dws_member_newconv_index.raw_score_welcome,
dws_member_newconv_index.raw_score_convert,
dws_member_newconv_index.raw_score,
dws_member_newconv_index.display_score_welcome,
dws_member_newconv_index.display_score_convert,
dws_member_newconv_index.display_score,
dws_member_newconv_index.last_wechat_touch_time,
dws_member_newconv_index.calc_time
FROM billiards_dws.dws_member_newconv_index;
COMMENT ON VIEW billiards_dws.v_member_recall_priority IS '召回优先级视图：合并WBI与NCI指数用于统一排序';
-- -----------------------------------------------------------------------------
-- 26. dws_member_assistant_relation_index - 客户-助教关系指数表（RS/OS/MS/ML）
-- 说明:
-- - 关系粒度: site_id + member_id + assistant_id
-- - 单任务产出 RS（关系强度）/OS（归属份额）/MS（升温动量）/ML（付费关联）
-- - ML 由人工台账窄表 dws_ml_manual_order_alloc 驱动
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_assistant_relation_index CASCADE;
CREATE TABLE billiards_dws.dws_member_assistant_relation_index (
relation_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
tenant_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
assistant_id BIGINT NOT NULL,
-- 服务行为特征
session_count INTEGER NOT NULL DEFAULT 0,
total_duration_minutes INTEGER NOT NULL DEFAULT 0,
basic_session_count INTEGER NOT NULL DEFAULT 0,
incentive_session_count INTEGER NOT NULL DEFAULT 0,
days_since_last_session INTEGER,
-- RS
rs_f NUMERIC(14,6) NOT NULL DEFAULT 0,
rs_d NUMERIC(14,6) NOT NULL DEFAULT 0,
rs_r NUMERIC(14,6) NOT NULL DEFAULT 0,
rs_raw NUMERIC(14,6) NOT NULL DEFAULT 0,
rs_display NUMERIC(4,2) NOT NULL DEFAULT 0,
-- OS
os_share NUMERIC(10,6) NOT NULL DEFAULT 0,
os_label VARCHAR(20) NOT NULL DEFAULT 'POOL',
os_rank INTEGER,
-- MS
ms_f_short NUMERIC(14,6) NOT NULL DEFAULT 0,
ms_f_long NUMERIC(14,6) NOT NULL DEFAULT 0,
ms_raw NUMERIC(14,6) NOT NULL DEFAULT 0,
ms_display NUMERIC(4,2) NOT NULL DEFAULT 0,
-- ML
ml_order_count INTEGER NOT NULL DEFAULT 0,
ml_allocated_amount NUMERIC(14,2) NOT NULL DEFAULT 0,
ml_raw NUMERIC(14,6) NOT NULL DEFAULT 0,
ml_display NUMERIC(4,2) NOT NULL DEFAULT 0,
-- 元数据
calc_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_member_assistant_relation_index UNIQUE (site_id, member_id, assistant_id)
);
COMMENT ON TABLE billiards_dws.dws_member_assistant_relation_index IS '客户-助教关系指数结果表（RS/OS/MS/ML）';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_relation_index.os_label IS '归属标签：UNASSIGNED/MAIN/COMANAGE/POOL';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_relation_index.ml_allocated_amount IS '人工台账分摊后金额';
CREATE INDEX idx_dws_relation_member ON billiards_dws.dws_member_assistant_relation_index (site_id, member_id, os_share DESC);
CREATE INDEX idx_dws_relation_assistant ON billiards_dws.dws_member_assistant_relation_index (site_id, assistant_id, rs_display DESC);
CREATE INDEX idx_dws_relation_calc_time ON billiards_dws.dws_member_assistant_relation_index (calc_time);
-- -----------------------------------------------------------------------------
-- 27. dws_ml_manual_order_source - ML 人工台账宽表（订单一行）
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_ml_manual_order_source CASCADE;
CREATE TABLE billiards_dws.dws_ml_manual_order_source (
source_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
biz_date DATE NOT NULL,
external_id VARCHAR(128) NOT NULL,
member_id BIGINT NOT NULL DEFAULT 0,
pay_time TIMESTAMPTZ NOT NULL,
order_amount NUMERIC(14,2) NOT NULL DEFAULT 0,
currency VARCHAR(16) NOT NULL DEFAULT 'CNY',
assistant_id_1 BIGINT,
assistant_name_1 VARCHAR(128),
assistant_id_2 BIGINT,
assistant_name_2 VARCHAR(128),
assistant_id_3 BIGINT,
assistant_name_3 VARCHAR(128),
assistant_id_4 BIGINT,
assistant_name_4 VARCHAR(128),
assistant_id_5 BIGINT,
assistant_name_5 VARCHAR(128),
import_batch_no VARCHAR(64) NOT NULL,
import_file_name VARCHAR(255) NOT NULL,
import_scope_key VARCHAR(128) NOT NULL,
import_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
import_user VARCHAR(64),
row_no INTEGER NOT NULL,
remark TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_ml_manual_order_source UNIQUE (site_id, external_id, import_scope_key, row_no)
);
COMMENT ON TABLE billiards_dws.dws_ml_manual_order_source IS 'ML人工台账宽表：订单一行，支持最多5名助教';
COMMENT ON COLUMN billiards_dws.dws_ml_manual_order_source.external_id IS '外部订单ID，必填';
COMMENT ON COLUMN billiards_dws.dws_ml_manual_order_source.import_scope_key IS '覆盖范围键：DAY或P30';
CREATE INDEX idx_dws_ml_source_scope ON billiards_dws.dws_ml_manual_order_source (site_id, biz_date);
CREATE INDEX idx_dws_ml_source_external ON billiards_dws.dws_ml_manual_order_source (site_id, external_id);
-- -----------------------------------------------------------------------------
-- 28. dws_ml_manual_order_alloc - ML 人工台账分摊窄表（用于计算 ML_raw）
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_ml_manual_order_alloc CASCADE;
CREATE TABLE billiards_dws.dws_ml_manual_order_alloc (
alloc_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
biz_date DATE NOT NULL,
external_id VARCHAR(128) NOT NULL,
member_id BIGINT NOT NULL DEFAULT 0,
pay_time TIMESTAMPTZ NOT NULL,
order_amount NUMERIC(14,2) NOT NULL DEFAULT 0,
assistant_id BIGINT NOT NULL,
assistant_name VARCHAR(128),
share_ratio NUMERIC(14,8) NOT NULL DEFAULT 0,
allocated_amount NUMERIC(14,2) NOT NULL DEFAULT 0,
currency VARCHAR(16) NOT NULL DEFAULT 'CNY',
import_scope_key VARCHAR(128) NOT NULL,
import_batch_no VARCHAR(64) NOT NULL,
import_file_name VARCHAR(255) NOT NULL,
import_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
import_user VARCHAR(64),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_ml_manual_order_alloc UNIQUE (site_id, external_id, assistant_id)
);
COMMENT ON TABLE billiards_dws.dws_ml_manual_order_alloc IS 'ML人工台账窄表：按订单-助教分摊后明细，关系指数ML直接读取';
COMMENT ON COLUMN billiards_dws.dws_ml_manual_order_alloc.share_ratio IS '分摊比例：一单多助教默认均分';
COMMENT ON COLUMN billiards_dws.dws_ml_manual_order_alloc.allocated_amount IS '分摊金额 = order_amount × share_ratio';
CREATE INDEX idx_dws_ml_alloc_scope ON billiards_dws.dws_ml_manual_order_alloc (site_id, biz_date);
CREATE INDEX idx_dws_ml_alloc_member_assistant ON billiards_dws.dws_ml_manual_order_alloc (site_id, member_id, assistant_id);
-- -----------------------------------------------------------------------------
-- 29. dws_member_assistant_intimacy - 客户-助教亲密指数表（兼容保留）
-- 说明:
-- - 以"会员+助教"为粒度，衡量客户与助教的关系强度
-- - 用于助教约课精力分配和约课成功率预估
-- - 算法基于：频次强度、最近温度、归因充值、时长贡献、激增放大
-- - 附加课权重=基础课的1.5倍
-- - 会话合并：同一客人对同一助教，间隔<4小时算同次服务
-- - 充值归因：服务结束后1小时内的充值算做该助教贡献
-- - 散客(member_id=0)不参与计算
-- - 更新频率: 每4小时
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_assistant_intimacy CASCADE;
CREATE TABLE billiards_dws.dws_member_assistant_intimacy (
intimacy_id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
tenant_id BIGINT NOT NULL, -- 租户ID
member_id BIGINT NOT NULL, -- 会员ID
assistant_id BIGINT NOT NULL, -- 助教ID（来自dim_assistant.assistant_id，通过user_id关联获取）
-- 计算输入特征
session_count INTEGER NOT NULL DEFAULT 0, -- 会话次数（合并后）
total_duration_minutes INTEGER NOT NULL DEFAULT 0, -- 总服务时长(分钟)
basic_session_count INTEGER NOT NULL DEFAULT 0, -- 基础课次数
incentive_session_count INTEGER NOT NULL DEFAULT 0, -- 附加课次数（激励课/超休）
days_since_last_session INTEGER, -- 距最近一次服务的天数
attributed_recharge_count INTEGER NOT NULL DEFAULT 0, -- 归因充值次数
attributed_recharge_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 归因充值金额
-- 分项得分
score_frequency NUMERIC(10,4), -- 频次强度 F: Σ(τ_i × decay(d_i, h_sess))
score_recency NUMERIC(10,4), -- 最近温度 R: decay(d_last, h_last)
score_recharge NUMERIC(10,4), -- 归因充值强度 M: Σ(ln(1+amt/A0) × decay(d_r, h_pay))
score_duration NUMERIC(10,4), -- 时长贡献 D: Σ(sqrt(dur/60) × τ × decay(d, h_sess))
burst_multiplier NUMERIC(6,4), -- 激增放大因子: 1 + γ × burst
-- 最终分数
raw_score NUMERIC(14,6), -- Raw Score (无上限)
display_score NUMERIC(4,2), -- Display Score (0-10)
-- 元数据
calc_time TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计算时间
calc_version INTEGER NOT NULL DEFAULT 1, -- 计算版本
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_member_assistant_intimacy UNIQUE (site_id, member_id, assistant_id)
);
COMMENT ON TABLE billiards_dws.dws_member_assistant_intimacy IS '客户-助教亲密指数：衡量客户与助教的关系强度，用于约课分配';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.assistant_id IS '助教ID：来自dim_assistant.assistant_id，通过服务日志的user_id关联dim_assistant.user_id获取';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.incentive_session_count IS '附加课次数：skill_id=2790683529513798（附加课/激励课），权重1.5倍';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.attributed_recharge_count IS '归因充值：服务结束后1小时内发生的充值';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.score_frequency IS '频次强度F：课型加权(τ)×时间衰减，τ=1.5(附加课)/1.0(基础课)';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.burst_multiplier IS '激增放大：1+γ×max(0,ln(1+(F_short/F_long-1)))';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.raw_score IS 'Raw Score：(w_F×F+w_R×R+w_M×M+w_D×D)×mult';
CREATE INDEX idx_dws_intimacy_member ON billiards_dws.dws_member_assistant_intimacy (site_id, member_id, display_score DESC);
CREATE INDEX idx_dws_intimacy_assistant ON billiards_dws.dws_member_assistant_intimacy (site_id, assistant_id, display_score DESC);
CREATE INDEX idx_dws_intimacy_calc_time ON billiards_dws.dws_member_assistant_intimacy (calc_time);
-- -----------------------------------------------------------------------------
-- 30. dws_index_percentile_history - 分位点历史表
-- 说明:
-- - 记录每轮指数计算的分位点，用于EWMA平滑
-- - 防止分数分布轻微变化导致全员分数跳动
-- - 更新频率高时（如每小时）建议启用EWMA平滑
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_index_percentile_history CASCADE;
CREATE TABLE billiards_dws.dws_index_percentile_history (
history_id BIGSERIAL PRIMARY KEY, -- 自增主键
site_id BIGINT NOT NULL, -- 门店ID
index_type VARCHAR(50) NOT NULL, -- 指数类型: RS/MS/ML/NCI/WBI（兼容 RECALL/INTIMACY）
calc_time TIMESTAMPTZ NOT NULL, -- 计算时间
-- 原始分位点
percentile_5 NUMERIC(14,6), -- 5分位（下锚）
percentile_95 NUMERIC(14,6), -- 95分位（上锚）
-- EWMA平滑后的分位点
percentile_5_smoothed NUMERIC(14,6), -- 平滑后的5分位
percentile_95_smoothed NUMERIC(14,6), -- 平滑后的95分位
-- 统计信息
record_count INTEGER, -- 记录数
min_raw_score NUMERIC(14,6), -- 最小Raw Score
max_raw_score NUMERIC(14,6), -- 最大Raw Score
avg_raw_score NUMERIC(14,6), -- 平均Raw Score
-- 元数据
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uk_dws_index_percentile_history UNIQUE (site_id, index_type, calc_time)
);
COMMENT ON TABLE billiards_dws.dws_index_percentile_history IS '分位点历史表：记录每轮指数计算的分位点，用于EWMA平滑防抖';
COMMENT ON COLUMN billiards_dws.dws_index_percentile_history.percentile_5_smoothed IS 'EWMA平滑：Q_t=(1-α)×Q_{t-1}+α×Q_now，α=0.2';
CREATE INDEX idx_dws_percentile_history ON billiards_dws.dws_index_percentile_history (site_id, index_type, calc_time DESC);
-- =============================================================================
-- 完成提示
-- =============================================================================
-- DDL执行完成，共创建：
-- - 6张配置表（cfg_*）
-- - 5张助教维度表（dws_assistant_*）
-- - 客户维度表（dws_member_*）：包含召回/新客转化/唤回/亲密指数
-- - 1张关系指数表（dws_member_assistant_relation_index）
-- - 2张ML人工台账表（dws_ml_manual_order_source, dws_ml_manual_order_alloc）
-- - 7张财务维度表（dws_finance_* + dws_assistant_finance_* + dws_platform_*）
-- - 1张订单汇总表（dws_order_summary）
-- - 1张分位点历史表（dws_index_percentile_history）
-- - 1个召回优先级视图（v_member_recall_priority）
-- - 2个辅助函数（get_time_window, get_comparison_window）
Reference in New Issue View Git Blame Copy Permalink