-- ============================================================================= -- 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）