47 KiB
47 KiB
DWS 数据层实施任务计划 v1.0
版本: 1.0
创建日期: 2026-02-01
项目路径:C:\dev\LLTQ\ETL\feiqiu-ETL
目标: 在已完成的 DWD 层数据基础上，完成 DWS（数据汇总层）的数据库设计与 ETL 实现
一、项目背景与现状分析
1.1 已有基础设施
| 层级 | 状态 | Schema | 说明 |
|---|---|---|---|
| ODS（操作数据层） | ✅ 完成 | billiards_ods |
原始数据落地，20+ 张表 |
| DWD（明细数据层） | ✅ 完成 | billiards_dwd |
维度表 9 张 + 事实表 12 张 |
| DWS（汇总数据层） | 🔄 部分完成 | billiards_dws |
仅有 dws_order_summary 1 张 |
1.2 现有代码架构
etl_billiards/
├── tasks/
│ ├── base_task.py # BaseTask 基类（E/T/L 模板方法）
│ ├── init_dws_schema_task.py # DWS Schema 初始化任务
│ └── dws_build_order_summary_task.py # 订单汇总表构建任务
├── database/
│ └── schema_dws.sql # DWS DDL（当前仅 dws_order_summary）
├── scripts/
│ └── build_dws_order_summary.py # 订单汇总构建脚本
└── docs/
├── dwd_main_tables_dictionary.md # DWD 数据字典
└── 财务页面需求.md # 财务需求原型
1.3 核心 DWD 表依赖关系
维度表:
dim_assistant: 助教维度（level 字段标识等级：初级/中级/高级/星级）dim_member: 会员维度dim_table: 台桌维度dim_site: 门店维度
事实表:
dwd_assistant_service_log: 助教服务记录（核心：基础课+附加课统计）dwd_assistant_trash_event: 助教废除记录（影响业绩有效性）dwd_settlement_head: 结算单头表dwd_table_fee_log: 台费流水dwd_store_goods_sale: 商品销售dwd_recharge_order: 充值订单dwd_member_balance_change: 会员余额变动dwd_payment: 支付记录dwd_refund: 退款记录
二、需求分析与表设计
2.1 数据分层策略（时间分区）
根据需求，将数据按更新时间分为 4 层以优化查询效率：
| 分层 | 时间范围 | 用途 | 实现方式 |
|---|---|---|---|
| L1（热数据） | 最近 2 天 | 实时监控/当日报表 | 物化视图 + 定时刷新 |
| L2（近期数据） | 最近 1 个月 | 周报/月报 | 分区表 |
| L3（中期数据） | 最近 3 个月 | 季度分析 | 分区表 |
| L4（全量数据） | 历史全部 | 年度汇总/深度分析 | 原始表 |
实现机制:
- 使用 PostgreSQL 表分区（Range Partition by 月份）
- 通过视图自动路由到对应时间层
- 配套清理任务定期归档/删除过期数据
2.2 DWS 表清单（完整设计）
2.2.1 配置表（系统设置）
| 表名 | 类型 | 说明 |
|---|---|---|
cfg_performance_tier |
配置 | 助教绩效档位配置（6档：0-5档） |
cfg_assistant_level_price |
配置 | 助教等级定价（初级/中级/高级/星级） |
cfg_bonus_rules |
配置 | 奖金规则配置（冲刺奖/Top3奖） |
cfg_tier_effective_period |
配置 | 档位配置生效时间范围 |
2.2.2 助教维度汇总表
| 表名 | 类型 | 主要维度 | 说明 |
|---|---|---|---|
dws_assistant_monthly_summary |
汇总 | 助教×月份 | 月度业绩汇总（课时/收入/档位） |
dws_assistant_daily_detail |
明细 | 助教×日期 | 日度业绩明细（便于月中进度追踪） |
dws_assistant_customer_stats |
汇总 | 助教×时间窗口 | 服务客户统计（7/10/15/30/60/90天） |
dws_assistant_salary_calc |
计算 | 助教×月份 | 月度工资计算结果 |
2.2.3 客户维度汇总表
| 表名 | 类型 | 主要维度 | 说明 |
|---|---|---|---|
dws_member_consumption_summary |
汇总 | 会员×时间窗口 | 消费情况统计（7/10/15/30/60/90天） |
dws_member_visit_detail |
明细 | 会员×订单 | 来店消费明细（含服务详情） |
2.2.4 财务维度汇总表
| 表名 | 类型 | 主要维度 | 说明 |
|---|---|---|---|
dws_finance_daily_summary |
汇总 | 门店×日期 | 日度财务汇总 |
dws_finance_income_structure |
汇总 | 门店×日期×区域 | 收入结构（按区域/类型） |
dws_finance_discount_detail |
明细 | 门店×日期 | 优惠明细（团购/大客户/赠送卡等） |
dws_finance_recharge_summary |
汇总 | 门店×日期 | 充值与预收汇总 |
dws_finance_expense_summary |
汇总 | 门店×日期 | 支出结构汇总 |
dws_assistant_finance_analysis |
汇总 | 门店×日期×等级 | 助教收支分析 |
2.2.5 现有表保留
| 表名 | 状态 | 说明 |
|---|---|---|
dws_order_summary |
✅ 保留 | 订单级汇总（已实现） |
三、表结构详细设计
3.1 配置表 DDL
3.1.1 cfg_performance_tier - 绩效档位配置
-- ============================================================
-- 表名: cfg_performance_tier
-- 用途: 助教绩效档位配置表，定义6个档位的阈值与分成规则
-- 业务规则:
-- - 过档后，所有时长按新档位进行计算
-- - 总业绩小时数 = 基础课 + 附加课
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_performance_tier (
tier_id SERIAL PRIMARY KEY, -- 档位主键
tier_code INTEGER NOT NULL UNIQUE, -- 档位代码：0-5
tier_name VARCHAR(50) NOT NULL, -- 档位名称
tier_reason VARCHAR(100), -- 档位设计原因
min_hours NUMERIC(10,2) NOT NULL, -- 最小小时数阈值（含）
max_hours NUMERIC(10,2), -- 最大小时数阈值（不含），NULL表示无上限
base_deduction NUMERIC(10,2) NOT NULL, -- 基础课抽成（元/小时）
bonus_ratio NUMERIC(5,4) NOT NULL, -- 附加课/打赏课抽成比例（如0.50表示50%）
vacation_days INTEGER, -- 次月休假天数，NULL表示休假自由
effective_from DATE NOT NULL DEFAULT '2026-01-01', -- 生效开始日期
effective_to DATE DEFAULT '9999-12-31', -- 生效结束日期
is_active BOOLEAN DEFAULT TRUE, -- 是否启用
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 初始化默认档位配置（2026年标准）
COMMENT ON TABLE billiards_dws.cfg_performance_tier IS '助教绩效档位配置表：定义各档位的业绩阈值与分成规则';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.tier_code IS '档位代码：0=淘汰压力，1=及格档，2=良好档，3=优秀档，4=卓越加速档，5=冠军加速档';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.base_deduction IS '基础课球房抽成金额（元/小时），助教实得=客户单价-此值';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.bonus_ratio IS '附加课/打赏课球房抽成比例，助教实得=收费*(1-此值)';
3.1.2 cfg_assistant_level_price - 助教等级定价
-- ============================================================
-- 表名: cfg_assistant_level_price
-- 用途: 助教等级对应的客户收费标准
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_assistant_level_price (
price_id SERIAL PRIMARY KEY,
level_code INTEGER NOT NULL, -- 等级代码（对应dim_assistant.level）
level_name VARCHAR(20) NOT NULL, -- 等级名称：初级/中级/高级/星级
base_price NUMERIC(10,2) NOT NULL, -- 基础课客户收费（元/小时）
bonus_price NUMERIC(10,2) NOT NULL DEFAULT 190, -- 附加课客户收费（元/小时），统一190
effective_from DATE NOT NULL DEFAULT '2026-01-01',
effective_to DATE DEFAULT '9999-12-31',
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(level_code, effective_from)
);
COMMENT ON TABLE billiards_dws.cfg_assistant_level_price IS '助教等级定价表：定义各等级对客户的收费标准';
COMMENT ON COLUMN billiards_dws.cfg_assistant_level_price.level_code IS '等级代码：1=初级，2=中级，3=高级，4=星级，8=助教管理';
3.1.3 cfg_bonus_rules - 奖金规则配置
-- ============================================================
-- 表名: cfg_bonus_rules
-- 用途: 冲刺奖、Top3奖等额外奖金规则配置
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_bonus_rules (
rule_id SERIAL PRIMARY KEY,
rule_type VARCHAR(20) NOT NULL, -- 规则类型：SPRINT(冲刺奖)/TOP_RANK(排名奖)
rule_name VARCHAR(50) NOT NULL, -- 规则名称
condition_type VARCHAR(20) NOT NULL, -- 条件类型：HOURS_GTE(时长>=)/RANK_EQ(排名=)
condition_value NUMERIC(10,2) NOT NULL, -- 条件值
bonus_amount NUMERIC(10,2) NOT NULL, -- 奖金金额（元）
priority INTEGER DEFAULT 0, -- 优先级（同类型取高优先级）
is_stackable BOOLEAN DEFAULT FALSE, -- 是否可叠加
effective_from DATE NOT NULL DEFAULT '2026-01-01',
effective_to DATE DEFAULT '9999-12-31',
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
COMMENT ON TABLE billiards_dws.cfg_bonus_rules IS '奖金规则配置表：定义冲刺奖和Top3排名奖';
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.is_stackable IS '是否可叠加：FALSE表示同类型奖金取高不叠加';
3.2 助教维度汇总表 DDL
3.2.1 dws_assistant_monthly_summary - 月度业绩汇总
-- ============================================================
-- 表名: dws_assistant_monthly_summary
-- 用途: 助教月度业绩汇总表，核心统计表
-- 更新策略: 每日增量更新当月数据，月初全量刷新上月
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_monthly_summary (
summary_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL, -- 门店ID
assistant_id BIGINT NOT NULL, -- 助教ID
stat_month DATE NOT NULL, -- 统计月份（每月1日）
-- 基础课统计
base_service_count INTEGER DEFAULT 0, -- 基础课服务次数
base_service_seconds INTEGER DEFAULT 0, -- 基础课服务秒数
base_service_hours NUMERIC(10,2) DEFAULT 0, -- 基础课服务小时数（精确到分钟）
base_income_original NUMERIC(12,2) DEFAULT 0, -- 基础课原始收入（客户支付）
base_income_deduction NUMERIC(12,2) DEFAULT 0, -- 基础课球房抽成
base_income_actual NUMERIC(12,2) DEFAULT 0, -- 基础课助教实得
-- 附加课统计
bonus_service_count INTEGER DEFAULT 0, -- 附加课服务次数
bonus_service_seconds INTEGER DEFAULT 0, -- 附加课服务秒数
bonus_service_hours NUMERIC(10,2) DEFAULT 0, -- 附加课服务小时数
bonus_income_original NUMERIC(12,2) DEFAULT 0, -- 附加课原始收入
bonus_income_ratio_ded NUMERIC(12,2) DEFAULT 0, -- 附加课球房按比例抽成
bonus_income_actual NUMERIC(12,2) DEFAULT 0, -- 附加课助教实得
-- 汇总统计
total_service_count INTEGER DEFAULT 0, -- 总服务次数
total_service_hours NUMERIC(10,2) DEFAULT 0, -- 总服务小时数
total_income_original NUMERIC(12,2) DEFAULT 0, -- 总原始收入
total_income_actual NUMERIC(12,2) DEFAULT 0, -- 总助教实得
-- 废除影响
trashed_service_count INTEGER DEFAULT 0, -- 被废除服务次数
trashed_service_seconds INTEGER DEFAULT 0, -- 被废除服务秒数
trashed_amount NUMERIC(12,2) DEFAULT 0, -- 被废除金额
-- 档位信息
tier_code INTEGER, -- 当前档位代码（0-5）
tier_name VARCHAR(50), -- 档位名称
tier_base_deduction NUMERIC(10,2), -- 档位基础课抽成
tier_bonus_ratio NUMERIC(5,4), -- 档位附加课抽成比例
-- 奖金
sprint_bonus NUMERIC(10,2) DEFAULT 0, -- 冲刺奖金额
rank_bonus NUMERIC(10,2) DEFAULT 0, -- 排名奖金额
other_bonus NUMERIC(10,2) DEFAULT 0, -- 其他奖金
total_bonus NUMERIC(10,2) DEFAULT 0, -- 奖金合计
-- 最终工资
final_salary NUMERIC(12,2) DEFAULT 0, -- 最终应发工资
-- 助教快照信息
assistant_level INTEGER, -- 助教等级
assistant_level_name VARCHAR(20), -- 等级名称
entry_date DATE, -- 入职日期
is_new_employee BOOLEAN DEFAULT FALSE, -- 是否本月新入职
work_days_in_month INTEGER, -- 本月在职天数
-- 排名信息
monthly_rank INTEGER, -- 当月排名
-- 元数据
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(site_id, assistant_id, stat_month)
);
-- 索引优化
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_site_month
ON billiards_dws.dws_assistant_monthly_summary(site_id, stat_month);
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_assistant
ON billiards_dws.dws_assistant_monthly_summary(assistant_id, stat_month);
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_month
ON billiards_dws.dws_assistant_monthly_summary(stat_month);
COMMENT ON TABLE billiards_dws.dws_assistant_monthly_summary IS '助教月度业绩汇总表：统计每位助教每月的业绩、收入、档位、工资';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.base_service_hours IS '基础课服务小时数，精确到分钟（秒数/3600）';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.tier_code IS '根据total_service_hours计算的档位，参照cfg_performance_tier';
3.2.2 dws_assistant_daily_detail - 日度业绩明细
-- ============================================================
-- 表名: dws_assistant_daily_detail
-- 用途: 助教日度业绩明细，用于月中进度追踪
-- 更新策略: 每小时增量更新
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_daily_detail (
detail_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
assistant_id BIGINT NOT NULL,
stat_date DATE NOT NULL, -- 统计日期
-- 基础课日统计
base_service_count INTEGER DEFAULT 0,
base_service_seconds INTEGER DEFAULT 0,
base_service_hours NUMERIC(10,4) DEFAULT 0,
base_income_original NUMERIC(12,2) DEFAULT 0,
-- 附加课日统计
bonus_service_count INTEGER DEFAULT 0,
bonus_service_seconds INTEGER DEFAULT 0,
bonus_service_hours NUMERIC(10,4) DEFAULT 0,
bonus_income_original NUMERIC(12,2) DEFAULT 0,
-- 汇总
total_service_count INTEGER DEFAULT 0,
total_service_hours NUMERIC(10,4) DEFAULT 0,
total_income_original NUMERIC(12,2) DEFAULT 0,
-- 废除
trashed_count INTEGER DEFAULT 0,
trashed_seconds INTEGER DEFAULT 0,
trashed_amount NUMERIC(12,2) DEFAULT 0,
-- 累计（当月至今）
mtd_total_hours NUMERIC(10,2) DEFAULT 0, -- Month-To-Date 累计小时数
mtd_tier_code INTEGER, -- 累计后档位
-- 服务客户统计
unique_customers INTEGER DEFAULT 0, -- 当日服务不同客户数
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(site_id, assistant_id, stat_date)
);
CREATE INDEX IF NOT EXISTS idx_dws_asst_daily_site_date
ON billiards_dws.dws_assistant_daily_detail(site_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_asst_daily_assistant
ON billiards_dws.dws_assistant_daily_detail(assistant_id, stat_date);
COMMENT ON TABLE billiards_dws.dws_assistant_daily_detail IS '助教日度业绩明细表：便于实时追踪月中业绩进度';
3.2.3 dws_assistant_customer_stats - 服务客户统计
-- ============================================================
-- 表名: dws_assistant_customer_stats
-- 用途: 助教服务客户统计（多时间窗口）
-- 更新策略: 每日凌晨全量刷新
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_customer_stats (
stats_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
assistant_id BIGINT NOT NULL,
stat_date DATE NOT NULL, -- 统计基准日期
window_days INTEGER NOT NULL, -- 时间窗口天数：7/10/15/30/60/90
-- 客户统计
unique_customers INTEGER DEFAULT 0, -- 服务不同客户数
total_service_count INTEGER DEFAULT 0, -- 总服务次数
total_service_hours NUMERIC(10,2) DEFAULT 0, -- 总服务小时数
-- 按课程类型
base_customers INTEGER DEFAULT 0, -- 基础课客户数
base_service_count INTEGER DEFAULT 0,
bonus_customers INTEGER DEFAULT 0, -- 附加课客户数
bonus_service_count INTEGER DEFAULT 0,
-- 收入
total_income NUMERIC(12,2) DEFAULT 0,
-- 客户复购
repeat_customers INTEGER DEFAULT 0, -- 复购客户数（服务>=2次）
repeat_rate NUMERIC(5,4) DEFAULT 0, -- 复购率
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(site_id, assistant_id, stat_date, window_days)
);
CREATE INDEX IF NOT EXISTS idx_dws_asst_cust_window
ON billiards_dws.dws_assistant_customer_stats(assistant_id, stat_date, window_days);
COMMENT ON TABLE billiards_dws.dws_assistant_customer_stats IS '助教服务客户统计表：支持7/10/15/30/60/90天多时间窗口';
3.3 客户维度汇总表 DDL
3.3.1 dws_member_consumption_summary - 消费情况统计
-- ============================================================
-- 表名: dws_member_consumption_summary
-- 用途: 会员消费情况统计（多时间窗口）
-- 更新策略: 每日凌晨全量刷新
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_member_consumption_summary (
summary_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
stat_date DATE NOT NULL,
window_days INTEGER NOT NULL, -- 7/10/15/30/60/90
-- 到店统计
visit_count INTEGER DEFAULT 0, -- 到店次数
visit_days INTEGER DEFAULT 0, -- 到店天数
-- 台费消费
table_fee_count INTEGER DEFAULT 0, -- 开台次数
table_fee_seconds INTEGER DEFAULT 0, -- 台费时长（秒）
table_fee_hours NUMERIC(10,2) DEFAULT 0, -- 台费小时数
table_fee_amount NUMERIC(12,2) DEFAULT 0, -- 台费金额
-- 助教服务
assistant_service_count INTEGER DEFAULT 0, -- 助教服务次数
assistant_service_hours NUMERIC(10,2) DEFAULT 0, -- 助教服务小时数
assistant_service_amt NUMERIC(12,2) DEFAULT 0, -- 助教服务金额
unique_assistants INTEGER DEFAULT 0, -- 服务过的不同助教数
-- 商品消费
goods_order_count INTEGER DEFAULT 0, -- 商品订单数
goods_item_count INTEGER DEFAULT 0, -- 商品项数
goods_amount NUMERIC(12,2) DEFAULT 0, -- 商品金额
-- 汇总金额
total_original_amount NUMERIC(12,2) DEFAULT 0, -- 原始消费总额
total_discount_amount NUMERIC(12,2) DEFAULT 0, -- 优惠总额
total_actual_amount NUMERIC(12,2) DEFAULT 0, -- 实际支付总额
-- 支付方式
cash_payment NUMERIC(12,2) DEFAULT 0, -- 现金支付
online_payment NUMERIC(12,2) DEFAULT 0, -- 线上支付
card_payment NUMERIC(12,2) DEFAULT 0, -- 储值卡支付
coupon_payment NUMERIC(12,2) DEFAULT 0, -- 团购券支付
-- 客户价值指标
avg_visit_amount NUMERIC(10,2) DEFAULT 0, -- 单次到店平均消费
avg_visit_duration_min INTEGER DEFAULT 0, -- 单次平均停留时长（分钟）
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(site_id, member_id, stat_date, window_days)
);
CREATE INDEX IF NOT EXISTS idx_dws_member_cons_window
ON billiards_dws.dws_member_consumption_summary(member_id, stat_date, window_days);
CREATE INDEX IF NOT EXISTS idx_dws_member_cons_site
ON billiards_dws.dws_member_consumption_summary(site_id, stat_date, window_days);
COMMENT ON TABLE billiards_dws.dws_member_consumption_summary IS '会员消费汇总表：支持7/10/15/30/60/90天多时间窗口';
3.4 财务维度汇总表 DDL
3.4.1 dws_finance_daily_summary - 日度财务汇总
-- ============================================================
-- 表名: dws_finance_daily_summary
-- 用途: 门店日度财务汇总（核心财务报表数据源）
-- 更新策略: 每小时增量更新
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_daily_summary (
summary_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
stat_date DATE NOT NULL,
-- ========== 经营链：发生额 → 优惠 → 确认收入 ==========
-- 发生额（正价）
gross_revenue NUMERIC(14,2) DEFAULT 0, -- 发生额/正价总计
gross_table_fee NUMERIC(14,2) DEFAULT 0, -- 台费发生额
gross_assistant_base NUMERIC(14,2) DEFAULT 0, -- 助教基础课发生额
gross_assistant_bonus NUMERIC(14,2) DEFAULT 0, -- 助教激励课发生额
gross_goods NUMERIC(14,2) DEFAULT 0, -- 食品酒水发生额
-- 优惠明细
discount_total NUMERIC(14,2) DEFAULT 0, -- 优惠总额
discount_groupbuy NUMERIC(14,2) DEFAULT 0, -- 团购优惠
discount_vip NUMERIC(14,2) DEFAULT 0, -- 大客户优惠
discount_gift_card NUMERIC(14,2) DEFAULT 0, -- 赠送卡抵扣（台桌卡+酒水卡+抵用券）
discount_manual NUMERIC(14,2) DEFAULT 0, -- 手动调整
discount_free NUMERIC(14,2) DEFAULT 0, -- 免单
discount_rounding NUMERIC(14,2) DEFAULT 0, -- 抹零
-- 成交/确认收入
confirmed_revenue NUMERIC(14,2) DEFAULT 0, -- 成交/确认收入（不含充值）
-- ========== 支付方式构成 ==========
payment_stored_card NUMERIC(14,2) DEFAULT 0, -- 储值卡结算冲销
payment_cash_online NUMERIC(14,2) DEFAULT 0, -- 现金/线上支付
payment_groupbuy_deal NUMERIC(14,2) DEFAULT 0, -- 团购核销确认收入（成交价）
-- ========== 现金流 ==========
-- 现金流入
cash_inflow_consume NUMERIC(14,2) DEFAULT 0, -- 消费现金流入（现金+线上+平台回款-退款）
cash_inflow_recharge NUMERIC(14,2) DEFAULT 0, -- 充值到账（首充+续费）
cash_inflow_total NUMERIC(14,2) DEFAULT 0, -- 现金流入合计
-- 现金支出（需外部输入或其他系统对接）
cash_outflow_total NUMERIC(14,2) DEFAULT 0, -- 现金支出合计
cash_outflow_rent NUMERIC(14,2) DEFAULT 0, -- 房租
cash_outflow_utility NUMERIC(14,2) DEFAULT 0, -- 水电
cash_outflow_purchase NUMERIC(14,2) DEFAULT 0, -- 进货成本
cash_outflow_assistant NUMERIC(14,2) DEFAULT 0, -- 助教分成
cash_outflow_salary NUMERIC(14,2) DEFAULT 0, -- 固定人员工资
cash_outflow_platform NUMERIC(14,2) DEFAULT 0, -- 平台服务费
cash_outflow_other NUMERIC(14,2) DEFAULT 0, -- 其他费用
-- 现金结余
cash_balance NUMERIC(14,2) DEFAULT 0, -- 现金结余
cash_balance_rate NUMERIC(5,4) DEFAULT 0, -- 结余率
-- ========== 充值与预收 ==========
recharge_first NUMERIC(14,2) DEFAULT 0, -- 首充金额
recharge_renew NUMERIC(14,2) DEFAULT 0, -- 续费金额
recharge_total NUMERIC(14,2) DEFAULT 0, -- 充值合计
recharge_gift NUMERIC(14,2) DEFAULT 0, -- 充值赠送金额
-- 储值卡
stored_card_consume NUMERIC(14,2) DEFAULT 0, -- 储值卡消耗
stored_card_balance NUMERIC(14,2) DEFAULT 0, -- 储值卡余额（截止当日）
-- 赠送卡
gift_card_new NUMERIC(14,2) DEFAULT 0, -- 赠送卡新增
gift_card_consume NUMERIC(14,2) DEFAULT 0, -- 赠送卡消耗
gift_card_balance NUMERIC(14,2) DEFAULT 0, -- 赠送卡余额
-- ========== 订单统计 ==========
order_count INTEGER DEFAULT 0, -- 订单数
order_avg_amount NUMERIC(10,2) DEFAULT 0, -- 客单价
member_order_count INTEGER DEFAULT 0, -- 会员订单数
member_order_rate NUMERIC(5,4) DEFAULT 0, -- 会员订单占比
-- ========== 退款统计 ==========
refund_count INTEGER DEFAULT 0, -- 退款笔数
refund_amount NUMERIC(14,2) DEFAULT 0, -- 退款金额
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(site_id, stat_date)
);
CREATE INDEX IF NOT EXISTS idx_dws_finance_daily_site_date
ON billiards_dws.dws_finance_daily_summary(site_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_finance_daily_date
ON billiards_dws.dws_finance_daily_summary(stat_date);
COMMENT ON TABLE billiards_dws.dws_finance_daily_summary IS '门店日度财务汇总表：核心财务报表数据源';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.gross_revenue IS '发生额/正价：按各项目正价计算的理论销售额';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.confirmed_revenue IS '成交/确认收入：扣除优惠后的营业收入，不含充值';
3.4.2 dws_finance_income_structure - 收入结构（按区域）
-- ============================================================
-- 表名: dws_finance_income_structure
-- 用途: 收入结构按区域/类型细分
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_income_structure (
structure_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
stat_date DATE NOT NULL,
-- 分类维度
income_category VARCHAR(20) NOT NULL, -- 收入大类：TABLE/ASSISTANT_BASE/ASSISTANT_BONUS/GOODS
area_name VARCHAR(50), -- 区域名称：A区/B区/C区/团建区/麻将区/NULL(不分区)
-- 金额
gross_amount NUMERIC(14,2) DEFAULT 0, -- 发生额
discount_amount NUMERIC(14,2) DEFAULT 0, -- 优惠金额
confirmed_amount NUMERIC(14,2) DEFAULT 0, -- 确认收入
-- 数量/时长
item_count INTEGER DEFAULT 0, -- 项目数/次数
duration_seconds INTEGER DEFAULT 0, -- 时长（秒）
duration_hours NUMERIC(10,2) DEFAULT 0, -- 时长（小时）
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(site_id, stat_date, income_category, area_name)
);
COMMENT ON TABLE billiards_dws.dws_finance_income_structure IS '收入结构表：按大类和区域细分的收入明细';
3.4.3 dws_assistant_finance_analysis - 助教收支分析
-- ============================================================
-- 表名: dws_assistant_finance_analysis
-- 用途: 助教收支分析（财务视角）
-- ============================================================
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_finance_analysis (
analysis_id BIGSERIAL PRIMARY KEY,
site_id BIGINT NOT NULL,
stat_date DATE NOT NULL,
assistant_level INTEGER, -- 助教等级（NULL表示全部）
assistant_level_name VARCHAR(20),
-- 基础课
base_customer_payment NUMERIC(14,2) DEFAULT 0, -- 客户支付
base_shop_commission NUMERIC(14,2) DEFAULT 0, -- 球房抽成
base_hours NUMERIC(10,2) DEFAULT 0, -- 小时数
base_avg_commission NUMERIC(10,2) DEFAULT 0, -- 均小时抽成
-- 激励课
bonus_customer_payment NUMERIC(14,2) DEFAULT 0,
bonus_shop_commission NUMERIC(14,2) DEFAULT 0,
bonus_hours NUMERIC(10,2) DEFAULT 0,
bonus_avg_commission NUMERIC(10,2) DEFAULT 0,
-- 汇总
total_customer_payment NUMERIC(14,2) DEFAULT 0,
total_shop_commission NUMERIC(14,2) DEFAULT 0,
total_hours NUMERIC(10,2) DEFAULT 0,
-- 助教数量
assistant_count INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(site_id, stat_date, assistant_level)
);
COMMENT ON TABLE billiards_dws.dws_assistant_finance_analysis IS '助教收支分析表：财务视角的助教业绩与抽成统计';
四、技术实现方案
4.1 代码架构设计
etl_billiards/
├── tasks/
│ ├── dws/ # DWS 任务模块（新增）
│ │ ├── __init__.py
│ │ ├── base_dws_task.py # DWS任务基类
│ │ ├── cfg_init_task.py # 配置表初始化任务
│ │ ├── assistant_monthly_task.py # 助教月度汇总任务
│ │ ├── assistant_daily_task.py # 助教日度明细任务
│ │ ├── assistant_customer_task.py # 助教客户统计任务
│ │ ├── member_consumption_task.py # 会员消费统计任务
│ │ ├── finance_daily_task.py # 财务日度汇总任务
│ │ ├── finance_structure_task.py # 收入结构任务
│ │ └── assistant_finance_task.py # 助教收支分析任务
│ └── init_dws_schema_task.py # 更新：执行完整DWS DDL
├── database/
│ ├── schema_dws.sql # 更新：完整DWS DDL
│ └── seed_dws_config.sql # 新增：配置表初始数据
├── scripts/
│ └── build_dws_*.py # DWS 构建脚本
└── docs/
└── dws_tables_dictionary.md # 新增：DWS 数据字典
4.2 类设计（OOP）
# ============================================================
# 文件: tasks/dws/base_dws_task.py
# 用途: DWS 任务基类，封装通用逻辑
# ============================================================
class BaseDwsTask(BaseTask):
"""
DWS 层任务基类
特性：
1. 支持增量/全量两种刷新模式
2. 支持 delete-before-insert 策略（幂等性保障）
3. 内置时间窗口处理逻辑
"""
def __init__(self, config, db_connection, api_client, logger):
super().__init__(config, db_connection, api_client, logger)
self.target_schema = "billiards_dws"
self.source_schema = "billiards_dwd"
@property
def target_table(self) -> str:
"""子类需实现：返回目标表名"""
raise NotImplementedError
@property
def delete_before_insert(self) -> bool:
"""是否在插入前删除同范围数据，默认True"""
return True
def get_delete_sql(self, context: TaskContext) -> tuple[str, list]:
"""生成删除SQL，子类可覆盖"""
raise NotImplementedError
def get_insert_sql(self, context: TaskContext) -> tuple[str, dict]:
"""生成插入SQL，子类需实现"""
raise NotImplementedError
4.3 核心算法实现
4.3.1 档位计算算法
def calculate_tier(total_hours: float, is_new_employee: bool,
entry_date: date, stat_month: date,
work_days: int) -> dict:
"""
计算助教档位
规则：
1. 过档后，所有时长按新档位计算
2. 新入职助教按 日均*30 折算定档
3. 25日后入职最高定3档
Args:
total_hours: 总业绩小时数
is_new_employee: 是否本月新入职
entry_date: 入职日期
stat_month: 统计月份
work_days: 本月在职天数
Returns:
{tier_code, tier_name, base_deduction, bonus_ratio}
"""
effective_hours = total_hours
max_tier = 5
if is_new_employee and work_days < 30:
# 新入职折算：日均 * 30
daily_avg = total_hours / max(work_days, 1)
effective_hours = daily_avg * 30
# 25日后入职最高3档
if entry_date.day >= 25:
max_tier = 3
# 查询档位配置
tier = query_tier_config(effective_hours, max_tier)
return tier
4.3.2 工资计算算法
def calculate_salary(assistant_data: dict, tier: dict,
level_price: dict, bonus_rules: list) -> dict:
"""
计算助教月度工资
公式（以中级助教185小时为例）：
基础课收入 = 基础课时 * (客户单价 - 档位抽成)
附加课收入 = 附加课原价 * (1 - 档位抽成比例)
冲刺奖 = 达到阈值取最高
排名奖 = 按排名取值
最终工资 = 基础课收入 + 附加课收入 + 冲刺奖 + 排名奖
"""
# 基础课收入
base_income = (
assistant_data['base_hours'] *
(level_price['base_price'] - tier['base_deduction'])
)
# 附加课收入
bonus_income = (
assistant_data['bonus_income_original'] *
(1 - tier['bonus_ratio'])
)
# 冲刺奖（取高不叠加）
sprint_bonus = calculate_sprint_bonus(
assistant_data['total_hours'], bonus_rules
)
# 排名奖
rank_bonus = calculate_rank_bonus(
assistant_data['monthly_rank'], bonus_rules
)
final_salary = base_income + bonus_income + sprint_bonus + rank_bonus
return {
'base_income': base_income,
'bonus_income': bonus_income,
'sprint_bonus': sprint_bonus,
'rank_bonus': rank_bonus,
'final_salary': final_salary
}
4.4 SQL 构建示例
4.4.1 助教月度汇总 SQL
-- 助教月度业绩汇总构建SQL
WITH
-- 1. 有效服务记录（排除废除）
valid_services AS (
SELECT
asl.site_id,
asl.site_assistant_id AS assistant_id,
DATE_TRUNC('month', asl.start_use_time)::date AS stat_month,
asl.skill_name,
-- 判断课程类型：基础课/附加课
CASE
WHEN asl.skill_name IN ('超休', '激励', '打赏') THEN 'BONUS'
ELSE 'BASE'
END AS service_type,
asl.income_seconds,
asl.ledger_amount,
asl.projected_income,
asl.tenant_member_id
FROM billiards_dwd.dwd_assistant_service_log asl
WHERE COALESCE(asl.is_delete, 0) = 0
AND asl.start_use_time >= %(start_date)s
AND asl.start_use_time < %(end_date)s
-- 排除已废除的记录
AND NOT EXISTS (
SELECT 1 FROM billiards_dwd.dwd_assistant_trash_event ate
WHERE ate.assistant_service_id = asl.assistant_service_id
)
),
-- 2. 按助教+月份+类型汇总
service_summary AS (
SELECT
site_id,
assistant_id,
stat_month,
service_type,
COUNT(*) AS service_count,
SUM(income_seconds) AS service_seconds,
SUM(income_seconds) / 3600.0 AS service_hours,
SUM(ledger_amount) AS income_original,
COUNT(DISTINCT tenant_member_id) AS unique_customers
FROM valid_services
GROUP BY site_id, assistant_id, stat_month, service_type
),
-- 3. 废除统计
trash_summary AS (
SELECT
site_id,
assistant_id, -- 需要从关联表获取
DATE_TRUNC('month', create_time)::date AS stat_month,
COUNT(*) AS trashed_count,
SUM(charge_minutes_raw * 60) AS trashed_seconds,
SUM(abolish_amount) AS trashed_amount
FROM billiards_dwd.dwd_assistant_trash_event
WHERE create_time >= %(start_date)s
AND create_time < %(end_date)s
GROUP BY site_id, assistant_id, DATE_TRUNC('month', create_time)
),
-- 4. 助教维度信息
assistant_info AS (
SELECT
assistant_id,
site_id,
level,
CASE level
WHEN 1 THEN '初级'
WHEN 2 THEN '中级'
WHEN 3 THEN '高级'
WHEN 4 THEN '星级'
WHEN 8 THEN '助教管理'
ELSE '未知'
END AS level_name,
entry_time::date AS entry_date
FROM billiards_dwd.dim_assistant
WHERE SCD2_is_current = 1
)
-- 5. 最终汇总
SELECT
ss_base.site_id,
ss_base.assistant_id,
ss_base.stat_month,
-- 基础课
COALESCE(ss_base.service_count, 0) AS base_service_count,
COALESCE(ss_base.service_seconds, 0) AS base_service_seconds,
COALESCE(ss_base.service_hours, 0) AS base_service_hours,
COALESCE(ss_base.income_original, 0) AS base_income_original,
-- 附加课
COALESCE(ss_bonus.service_count, 0) AS bonus_service_count,
COALESCE(ss_bonus.service_seconds, 0) AS bonus_service_seconds,
COALESCE(ss_bonus.service_hours, 0) AS bonus_service_hours,
COALESCE(ss_bonus.income_original, 0) AS bonus_income_original,
-- 汇总
COALESCE(ss_base.service_count, 0) + COALESCE(ss_bonus.service_count, 0) AS total_service_count,
COALESCE(ss_base.service_hours, 0) + COALESCE(ss_bonus.service_hours, 0) AS total_service_hours,
COALESCE(ss_base.income_original, 0) + COALESCE(ss_bonus.income_original, 0) AS total_income_original,
-- 废除
COALESCE(ts.trashed_count, 0) AS trashed_service_count,
COALESCE(ts.trashed_seconds, 0) AS trashed_service_seconds,
COALESCE(ts.trashed_amount, 0) AS trashed_amount,
-- 助教信息
ai.level AS assistant_level,
ai.level_name AS assistant_level_name,
ai.entry_date
FROM service_summary ss_base
LEFT JOIN service_summary ss_bonus
ON ss_base.site_id = ss_bonus.site_id
AND ss_base.assistant_id = ss_bonus.assistant_id
AND ss_base.stat_month = ss_bonus.stat_month
AND ss_bonus.service_type = 'BONUS'
LEFT JOIN trash_summary ts
ON ss_base.site_id = ts.site_id
AND ss_base.assistant_id = ts.assistant_id
AND ss_base.stat_month = ts.stat_month
LEFT JOIN assistant_info ai
ON ss_base.assistant_id = ai.assistant_id
WHERE ss_base.service_type = 'BASE'
;
五、实施任务清单
5.1 阶段一：基础设施（预计 1-2 天）
| 序号 | 任务 | 优先级 | 依赖 | 产出物 |
|---|---|---|---|---|
| 1.1 | 更新 schema_dws.sql DDL |
P0 | - | DDL 文件 |
| 1.2 | 创建 seed_dws_config.sql 初始数据 |
P0 | 1.1 | 种子数据文件 |
| 1.3 | 更新 InitDwsSchemaTask |
P0 | 1.1,1.2 | 任务代码 |
| 1.4 | 创建 BaseDwsTask 基类 |
P0 | - | 基类代码 |
| 1.5 | 注册新任务到 task_registry.py |
P0 | 1.3,1.4 | 注册代码 |
5.2 阶段二：配置表与助教维度（预计 2-3 天）
| 序号 | 任务 | 优先级 | 依赖 | 产出物 |
|---|---|---|---|---|
| 2.1 | 实现 CfgInitTask 配置初始化 |
P0 | 1.* | 任务代码 |
| 2.2 | 实现 AssistantDailyTask 日度明细 |
P0 | 1.4 | 任务代码 |
| 2.3 | 实现 AssistantMonthlyTask 月度汇总 |
P0 | 2.2 | 任务代码 |
| 2.4 | 实现档位计算与工资计算模块 | P0 | 2.3 | 算法模块 |
| 2.5 | 实现 AssistantCustomerTask 客户统计 |
P1 | 2.2 | 任务代码 |
5.3 阶段三：客户维度（预计 1-2 天）
| 序号 | 任务 | 优先级 | 依赖 | 产出物 |
|---|---|---|---|---|
| 3.1 | 实现 MemberConsumptionTask |
P0 | 1.4 | 任务代码 |
| 3.2 | 实现多时间窗口查询视图 | P1 | 3.1 | SQL 视图 |
5.4 阶段四：财务维度（预计 2-3 天）
| 序号 | 任务 | 优先级 | 依赖 | 产出物 |
|---|---|---|---|---|
| 4.1 | 实现 FinanceDailySummaryTask |
P0 | 1.4 | 任务代码 |
| 4.2 | 实现 FinanceIncomeStructureTask |
P0 | 4.1 | 任务代码 |
| 4.3 | 实现 AssistantFinanceTask |
P0 | 2.3 | 任务代码 |
| 4.4 | 实现充值与预收统计逻辑 | P1 | 4.1 | 模块代码 |
5.5 阶段五：数据分层与维护（预计 1-2 天）
| 序号 | 任务 | 优先级 | 依赖 | 产出物 |
|---|---|---|---|---|
| 5.1 | 实现分区表管理任务 | P1 | 1.1 | 任务代码 |
| 5.2 | 实现数据清理/归档任务 | P1 | 5.1 | 任务代码 |
| 5.3 | 配置定时调度 | P1 | 2-4.* | 调度配置 |
5.6 阶段六：文档与测试（预计 1-2 天）
| 序号 | 任务 | 优先级 | 依赖 | 产出物 |
|---|---|---|---|---|
| 6.1 | 编写 dws_tables_dictionary.md |
P0 | 1-5.* | 数据字典 |
| 6.2 | 更新 README.md |
P0 | 1-5.* | README |
| 6.3 | 编写单元测试 | P1 | 2-4.* | 测试代码 |
| 6.4 | 编写集成测试 | P1 | 6.3 | 测试代码 |
六、风险与注意事项
6.1 数据一致性
- 废除记录处理：计算助教业绩时必须排除已废除的服务记录
- 课程类型判断：根据
skill_name字段区分基础课和附加课，需确认枚举值完整性 - 新入职折算：需要准确获取入职日期和在职天数
6.2 性能优化
- 索引设计：所有汇总表按常用查询维度建立复合索引
- 增量更新：日常运行采用增量模式，仅全量刷新月初或异常恢复场景
- 分区策略：大表按月份分区，历史数据可归档
6.3 编码注意
- 中文注释：所有 SQL 和 Python 代码需要详尽的中文注释
- UTF-8 编码：确保文件编码为 UTF-8，DDL 中使用
COMMENT ON添加中文说明 - 日志输出：关键步骤输出中文日志便于排查
6.4 业务规则确认
以下内容需与业务方确认：
- 附加课识别规则：
skill_name的具体枚举值（超休/激励/打赏等） - 助教等级映射：
dim_assistant.level的值域（1=初级/2=中级/3=高级/4=星级?） - 充值提成规则：当前需求文档为空，需补充
- 赠送卡分类：台费卡/酒水卡/抵用券的区分字段
七、验收标准
7.1 功能验收
- 所有 DWS 表可正常建表、查询
- 助教月度汇总数据准确（与手工计算对比）
- 档位计算逻辑符合业务规则
- 工资计算结果正确
- 客户统计支持多时间窗口
- 财务汇总数据与 DWD 明细一致
7.2 文档验收
dws_tables_dictionary.md完整记录所有表和字段README.md包含 DWS 使用说明- 代码注释覆盖率 > 80%
7.3 性能验收
- 日增量刷新 < 5 分钟
- 月全量刷新 < 30 分钟
- 常用查询响应 < 1 秒
八、附录
A. 配置表初始数据
-- 绩效档位初始数据
INSERT INTO billiards_dws.cfg_performance_tier
(tier_code, tier_name, tier_reason, min_hours, max_hours, base_deduction, bonus_ratio, vacation_days)
VALUES
(0, '0档 淘汰压力', '淘汰压力', 0, 100, 28, 0.50, 3),
(1, '1档 及格档', '重点激励', 100, 130, 18, 0.40, 4),
(2, '2档 良好档', '重点激励', 130, 160, 15, 0.38, 4),
(3, '3档 优秀档', '优秀标准', 160, 190, 13, 0.35, 5),
(4, '4档 卓越加速档', '高端人才倾斜', 190, 220, 10, 0.33, 6),
(5, '5档 冠军加速档', '高端人才倾斜', 220, NULL, 8, 0.30, NULL);
-- 助教等级定价初始数据
INSERT INTO billiards_dws.cfg_assistant_level_price
(level_code, level_name, base_price, bonus_price)
VALUES
(1, '初级', 98, 190),
(2, '中级', 108, 190),
(3, '高级', 118, 190),
(4, '星级', 138, 190),
(8, '助教管理', 0, 0);
-- 奖金规则初始数据
INSERT INTO billiards_dws.cfg_bonus_rules
(rule_type, rule_name, condition_type, condition_value, bonus_amount, priority, is_stackable)
VALUES
('SPRINT', '冲刺奖-190小时', 'HOURS_GTE', 190, 300, 1, FALSE),
('SPRINT', '冲刺奖-220小时', 'HOURS_GTE', 220, 800, 2, FALSE),
('TOP_RANK', 'Top1奖金', 'RANK_EQ', 1, 1000, 1, TRUE),
('TOP_RANK', 'Top2奖金', 'RANK_EQ', 2, 600, 2, TRUE),
('TOP_RANK', 'Top3奖金', 'RANK_EQ', 3, 400, 3, TRUE);
B. CLI 命令示例
# 初始化 DWS Schema
python -m cli.main --pipeline-flow INGEST_ONLY --tasks INIT_DWS_SCHEMA
# 初始化配置数据
python -m cli.main --pipeline-flow INGEST_ONLY --tasks DWS_INIT_CONFIG
# 构建助教日度明细（指定日期）
python -m cli.main --pipeline-flow INGEST_ONLY --tasks DWS_ASSISTANT_DAILY \
--window-start "2026-01-01" --window-end "2026-01-31"
# 构建助教月度汇总
python -m cli.main --pipeline-flow INGEST_ONLY --tasks DWS_ASSISTANT_MONTHLY \
--window-start "2026-01-01" --window-end "2026-02-01"
# 构建财务日度汇总
python -m cli.main --pipeline-flow INGEST_ONLY --tasks DWS_FINANCE_DAILY \
--window-start "2026-01-01" --window-end "2026-01-31"
# 一键刷新所有 DWS 表
python -m cli.main --pipeline-flow INGEST_ONLY --tasks DWS_REFRESH_ALL
文档版本历史
| 版本 | 日期 | 作者 | 变更说明 |
|---|---|---|---|
| 1.0 | 2026-02-01 | AI Assistant | 初始版本 |