1354 lines
65 KiB
Markdown
1354 lines
65 KiB
Markdown
# DWS 数据层实施任务计划 v2.0
|
||
|
||
> **版本**: 2.0
|
||
> **创建日期**: 2026-02-01
|
||
> **更新日期**: 2026-02-01
|
||
> **项目路径**: `C:\dev\LLTQ\ETL\feiqiu-ETL`
|
||
> **目标**: 在已完成的 DWD 层数据基础上，完成 DWS（数据汇总层）的数据库设计与 ETL 实现
|
||
|
||
---
|
||
|
||
## 一、项目背景与现状分析
|
||
|
||
### 1.1 已有基础设施
|
||
|
||
| 层级 | 状态 | Schema | 说明 |
|
||
|------|------|--------|------|
|
||
| ODS（操作数据层） | ✅ 完成 | `billiards_ods` | 原始数据落地，20+ 张表 |
|
||
| DWD（明细数据层） | ✅ 完成 | `billiards_dwd` | 维度表 9 张 + 事实表 12 张 |
|
||
| DWS（汇总数据层） | 🔄 部分完成 | `billiards_dws` | 仅有 `dws_order_summary` 1 张 |
|
||
|
||
### 1.2 DWD 核心表与关键字段
|
||
|
||
#### 1.2.1 助教服务相关
|
||
|
||
**dwd_assistant_service_log** - 助教服务流水表
|
||
|
||
| 字段 | 说明 | 枚举值 |
|
||
|------|------|--------|
|
||
| `skill_id` | 技能ID（用于判断课程类型） | 2790683529513797=基础课, 2790683529513798=附加课/激励课, 3039912271463941=包厢课 |
|
||
| `skill_name` | 技能名称 | "基础课", "附加课", "包厢课" |
|
||
| `assistant_level` | 助教等级 | 8=助教管理, 10=初级, 20=中级, 30=高级, 40=星级 |
|
||
| `order_assistant_type` | 服务类型 | 1=基础课或包厢课, 2=附加课/激励课 |
|
||
| `ledger_unit_price` | 单价（元/小时） | 98/108/118/138/190 |
|
||
|
||
#### 1.2.2 会员卡相关
|
||
|
||
**dim_member_card_account** - 会员卡账户表
|
||
|
||
| card_type_id | 卡类型 | 说明 | 分类 |
|
||
|--------------|--------|------|------|
|
||
| 2793249295533893 | 储值卡 | 充值获得，可抵扣任意费用 | **现金卡**（充值） |
|
||
| 2791990152417157 | 台费卡 | 充值赠送，仅可抵扣台费 | **赠送卡** |
|
||
| 2793266846533445 | 活动抵用券 | 充值赠送，不可抵扣助教费 | **赠送卡** |
|
||
| 2794699703437125 | 酒水卡 | 充值赠送，仅可抵扣酒水饮料食品 | **赠送卡** |
|
||
| 2793306611533637 | 月卡 | 充值获得，时长卡，仅可抵扣台费 | **时长卡** |
|
||
| 2791987095408517 | 年卡 | 充值获得，时长卡，仅可抵扣台费 | **时长卡** |
|
||
|
||
#### 1.2.3 台区分布
|
||
|
||
**dim_table** - 台桌维度表
|
||
|
||
| 台区名称 | 台桌数 | 大类/索引 |
|
||
|----------|--------|-----------|
|
||
| A区 | 18 | 台球/打球/中八/追分 |
|
||
| B区 | 15 | 台球/打球/中八/追分 |
|
||
| C区 | 6 | 台球/打球/中八/追分 |
|
||
| VIP包厢 | 4 | 台球/打球/中八(V5为斯诺克) |
|
||
| 斯诺克区 | 4 | 台球/打球/斯诺克 |
|
||
| TV台 | 1 | 台球/打球/中八/追分 |
|
||
| 麻将房 | 5 | 麻将/麻将棋牌 |
|
||
| M7/M8/666/发财 | 6 | 麻将/麻将棋牌 |
|
||
| K包/k包活动区/幸会158 | 8 | K包/K歌/KTV |
|
||
| 补时长 | 7 | 补时长 |
|
||
|
||
---
|
||
|
||
## 二、数据口径定义（数据来源矩阵）
|
||
|
||
### 2.1 发生额/正价口径
|
||
|
||
| 项目 | DWD来源 | 计算公式 | 说明 |
|
||
|------|---------|----------|------|
|
||
| **台费正价** | dwd_settlement_head | `table_charge_money` | 结账单中的台费原价 |
|
||
| **商品正价** | dwd_settlement_head | `goods_money` | 结账单中的商品原价 |
|
||
| **助教基础课正价** | dwd_settlement_head | `assistant_pd_money` | 陪打费用（PD=陪打） |
|
||
| **助教激励课正价** | dwd_settlement_head | `assistant_cx_money` | 促销课费用（CX=促销/超休） |
|
||
| **团购台费正价** | dwd_groupbuy_redemption | `ledger_amount` | 团购券对应的台费正价 |
|
||
| **发生额合计** | dwd_settlement_head | `table_charge_money + goods_money + assistant_pd_money + assistant_cx_money` | 各项正价之和 |
|
||
|
||
### 2.2 优惠分类口径
|
||
|
||
| 优惠类型 | DWD来源 | 计算公式 | 说明 |
|
||
|----------|---------|----------|------|
|
||
| **团购优惠** | dwd_groupbuy_redemption | `ledger_amount - coupon_money` | 正价与券面值差额 |
|
||
| **赠送卡抵扣** | dwd_member_balance_change | `SUM(change_amount) WHERE card_type_id IN (台费卡, 酒水卡, 活动抵用券) AND from_type=1` | 三类赠送卡消费合计 |
|
||
| **大客户优惠** | dwd_table_fee_log / dwd_store_goods_sale | `adjust_amount`（需抽样分析） | 手动调账产生的优惠 |
|
||
| **会员折扣** | dwd_settlement_head | `member_discount_amount` | 会员身份折扣 |
|
||
| **抹零** | dwd_settlement_head | `rounding_amount` | 抹零金额 |
|
||
| **其他优惠** | 待确认 | 抽样100单分析 | 订单折扣、台桌折扣、商品折扣、手动优惠的关系 |
|
||
| **优惠合计** | dwd_settlement_head | `consume_money - pay_amount` | 消费总额与实付差额 |
|
||
|
||
### 2.3 支付方式口径
|
||
|
||
| 支付类型 | DWD来源 | 计算公式 | 说明 |
|
||
|----------|---------|----------|------|
|
||
| **储值卡支付** | dwd_settlement_head | `balance_amount + recharge_card_amount` | 余额+储值卡 |
|
||
| **赠送卡支付** | dwd_settlement_head | `gift_card_amount` | 礼品卡（赠送卡）支付 |
|
||
| **团购核销** | dwd_groupbuy_redemption | `coupon_money` | 团购券面值（核销价） |
|
||
| **现金/线上支付** | dwd_settlement_head | `pay_amount - balance_amount - recharge_card_amount - gift_card_amount - coupon_amount` | 扣除卡支付后的实付 |
|
||
|
||
### 2.4 充值与赠送口径
|
||
|
||
| 类型 | DWD来源 | 筛选条件 | 说明 |
|
||
|------|---------|----------|------|
|
||
| **储值卡充值** | dwd_recharge_order | `member_card_type_name='储值卡' AND settle_type=5` | 现金充值到储值卡 |
|
||
| **首充金额** | dwd_recharge_order | `is_first=1` | 首次充值 |
|
||
| **续费金额** | dwd_recharge_order | `is_first=2` | 非首次充值 |
|
||
| **赠送卡新增** | dwd_member_balance_change | `card_type_id IN (台费卡,酒水卡,活动抵用券) AND from_type=4` | 活动赠送 |
|
||
| **赠送卡消费** | dwd_member_balance_change | `card_type_id IN (...) AND from_type=1 AND change_amount<0` | 消费扣款 |
|
||
| **各类卡余额** | dim_member_card_account | `SUM(balance) WHERE scd2_is_current=1 GROUP BY card_type_id` | 当前余额快照 |
|
||
|
||
### 2.5 助教业绩口径
|
||
|
||
| 指标 | DWD来源 | 筛选条件 | 说明 |
|
||
|------|---------|----------|------|
|
||
| **基础课时长** | dwd_assistant_service_log | `skill_id=2790683529513797 AND is_delete=0` | 基础课秒数 |
|
||
| **附加课时长** | dwd_assistant_service_log | `skill_id=2790683529513798 AND is_delete=0` | 附加课/激励课秒数 |
|
||
| **包厢课时长** | dwd_assistant_service_log | `skill_id=3039912271463941 AND is_delete=0` | 包厢课秒数 |
|
||
| **有效服务** | dwd_assistant_service_log | 排除 dwd_assistant_trash_event 中已废除的记录 | 需LEFT JOIN排除 |
|
||
| **废除记录** | dwd_assistant_trash_event | 全量 | 废除的服务不计入业绩 |
|
||
|
||
### 2.6 SCD2 维度取数说明
|
||
|
||
> **重要**：历史月份统计需要按业务时间点做 as-of join，而非直接使用 `scd2_is_current=1`
|
||
|
||
```sql
|
||
-- 正确做法：按业务时间获取当时的维度版本
|
||
SELECT asl.*, da.level, da.level_name
|
||
FROM dwd_assistant_service_log asl
|
||
LEFT JOIN dim_assistant da
|
||
ON asl.site_assistant_id = da.assistant_id
|
||
AND asl.start_use_time >= da.scd2_start_time
|
||
AND asl.start_use_time < COALESCE(da.scd2_end_time, '9999-12-31')
|
||
WHERE ...
|
||
|
||
-- 错误做法：历史月份会套用当前等级
|
||
SELECT asl.*, da.level
|
||
FROM dwd_assistant_service_log asl
|
||
LEFT JOIN dim_assistant da ON ... AND da.scd2_is_current = 1 -- 错误！
|
||
```
|
||
|
||
---
|
||
|
||
## 三、DWS 表设计
|
||
|
||
### 3.1 表清单总览
|
||
|
||
#### 3.1.1 配置表（4张）
|
||
|
||
| 表名 | 说明 | 数据来源 |
|
||
|------|------|----------|
|
||
| `cfg_performance_tier` | 绩效档位配置（6档） | 初始化+手动维护 |
|
||
| `cfg_assistant_level_price` | 助教等级定价 | 初始化+手动维护 |
|
||
| `cfg_bonus_rules` | 奖金规则配置 | 初始化+手动维护 |
|
||
| `cfg_area_category` | 台区分类映射 | 初始化+手动维护 |
|
||
|
||
#### 3.1.2 助教维度表（5张）
|
||
|
||
| 表名 | 说明 | 更新频率 |
|
||
|------|------|----------|
|
||
| `dws_assistant_monthly_summary` | 月度业绩汇总 | 每日增量 |
|
||
| `dws_assistant_daily_detail` | 日度业绩明细 | 每小时增量 |
|
||
| `dws_assistant_customer_stats` | 服务客户统计 | 每日全量 |
|
||
| `dws_assistant_salary_calc` | 月度工资计算 | 月初全量 |
|
||
| `dws_assistant_recharge_commission` | 充值提成记录 | **手动导入** |
|
||
|
||
#### 3.1.3 客户维度表（2张）
|
||
|
||
| 表名 | 说明 | 更新频率 |
|
||
|------|------|----------|
|
||
| `dws_member_consumption_summary` | 消费情况统计 | 每日全量 |
|
||
| `dws_member_visit_detail` | 来店消费明细 | 每日增量 |
|
||
|
||
#### 3.1.4 财务维度表（7张）
|
||
|
||
| 表名 | 说明 | 更新频率 |
|
||
|------|------|----------|
|
||
| `dws_finance_daily_summary` | 日度财务汇总 | 每小时增量 |
|
||
| `dws_finance_income_structure` | 收入结构 | 每日增量 |
|
||
| `dws_finance_discount_detail` | 优惠明细 | 每日增量 |
|
||
| `dws_finance_recharge_summary` | 充值与预收 | 每日增量 |
|
||
| `dws_finance_expense_summary` | 支出结构 | **手动导入** |
|
||
| `dws_assistant_finance_analysis` | 助教收支分析 | 每日增量 |
|
||
| `dws_platform_settlement` | 平台回款/服务费 | **手动导入** |
|
||
|
||
### 3.2 配置表 DDL
|
||
|
||
#### 3.2.1 `cfg_performance_tier` - 绩效档位配置
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: cfg_performance_tier
|
||
-- 用途: 助教绩效档位配置表，定义6个档位的阈值与分成规则
|
||
-- 业务规则:
|
||
-- - 过档后，所有时长按新档位进行计算
|
||
-- - 总业绩小时数 = 基础课 + 附加课 + 包厢课
|
||
-- 数据来源: 初始化脚本 + 手动维护
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_performance_tier (
|
||
tier_id SERIAL PRIMARY KEY, -- 档位主键
|
||
tier_code INTEGER NOT NULL UNIQUE, -- 档位代码：0-5
|
||
tier_name VARCHAR(50) NOT NULL, -- 档位名称
|
||
tier_reason VARCHAR(100), -- 档位设计原因
|
||
min_hours NUMERIC(10,2) NOT NULL, -- 最小小时数阈值（含）
|
||
max_hours NUMERIC(10,2), -- 最大小时数阈值（不含），NULL=无上限
|
||
base_deduction NUMERIC(10,2) NOT NULL, -- 基础课球房抽成（元/小时）
|
||
bonus_ratio NUMERIC(5,4) NOT NULL, -- 附加课球房抽成比例（如0.50表示50%）
|
||
vacation_days INTEGER, -- 次月休假天数，NULL=休假自由
|
||
effective_from DATE NOT NULL DEFAULT '2026-01-01', -- 生效开始日期
|
||
effective_to DATE DEFAULT '9999-12-31', -- 生效结束日期
|
||
is_active BOOLEAN DEFAULT TRUE, -- 是否启用
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE billiards_dws.cfg_performance_tier IS '助教绩效档位配置表：定义各档位的业绩阈值与分成规则';
|
||
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.tier_code IS '档位代码：0=淘汰压力，1=及格档，2=良好档，3=优秀档，4=卓越加速档，5=冠军加速档';
|
||
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.base_deduction IS '基础课球房抽成金额（元/小时），助教实得=客户单价-此值';
|
||
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.bonus_ratio IS '附加课球房抽成比例，助教实得=客户支付*(1-此值)';
|
||
```
|
||
|
||
#### 3.2.2 `cfg_assistant_level_price` - 助教等级定价
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: cfg_assistant_level_price
|
||
-- 用途: 助教等级对应的客户收费标准
|
||
-- 数据来源: 初始化脚本 + 手动维护
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_assistant_level_price (
|
||
price_id SERIAL PRIMARY KEY,
|
||
level_code INTEGER NOT NULL, -- 等级代码（对应dim_assistant.assistant_level）
|
||
level_name VARCHAR(20) NOT NULL, -- 等级名称：初级/中级/高级/星级
|
||
base_price NUMERIC(10,2) NOT NULL, -- 基础课客户收费（元/小时）
|
||
bonus_price NUMERIC(10,2) NOT NULL DEFAULT 190, -- 附加课客户收费（元/小时），统一190
|
||
effective_from DATE NOT NULL DEFAULT '2026-01-01',
|
||
effective_to DATE DEFAULT '9999-12-31',
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
UNIQUE(level_code, effective_from)
|
||
);
|
||
|
||
COMMENT ON TABLE billiards_dws.cfg_assistant_level_price IS '助教等级定价表：定义各等级对客户的收费标准';
|
||
COMMENT ON COLUMN billiards_dws.cfg_assistant_level_price.level_code IS '等级代码：8=助教管理，10=初级，20=中级，30=高级，40=星级';
|
||
```
|
||
|
||
#### 3.2.3 `cfg_bonus_rules` - 奖金规则配置
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: cfg_bonus_rules
|
||
-- 用途: 冲刺奖、Top3奖等额外奖金规则配置
|
||
-- 业务规则:
|
||
-- - 冲刺奖：H>=190得300元，H>=220得800元（取高不叠加）
|
||
-- - Top3奖：第1名1000元，第2名600元，第3名400元
|
||
-- 数据来源: 初始化脚本 + 手动维护
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_bonus_rules (
|
||
rule_id SERIAL PRIMARY KEY,
|
||
rule_type VARCHAR(20) NOT NULL, -- 规则类型：SPRINT(冲刺奖)/TOP_RANK(排名奖)
|
||
rule_name VARCHAR(50) NOT NULL, -- 规则名称
|
||
condition_type VARCHAR(20) NOT NULL, -- 条件类型：HOURS_GTE(时长>=)/RANK_EQ(排名=)
|
||
condition_value NUMERIC(10,2) NOT NULL, -- 条件值
|
||
bonus_amount NUMERIC(10,2) NOT NULL, -- 奖金金额（元）
|
||
priority INTEGER DEFAULT 0, -- 优先级（同类型取高优先级）
|
||
is_stackable BOOLEAN DEFAULT FALSE, -- 是否可叠加
|
||
effective_from DATE NOT NULL DEFAULT '2026-01-01',
|
||
effective_to DATE DEFAULT '9999-12-31',
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE billiards_dws.cfg_bonus_rules IS '奖金规则配置表：定义冲刺奖和Top3排名奖';
|
||
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.is_stackable IS '是否可叠加：FALSE表示同类型奖金取高不叠加';
|
||
```
|
||
|
||
#### 3.2.4 `cfg_area_category` - 台区分类映射
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: cfg_area_category
|
||
-- 用途: 台区名称到业务大类的映射，用于财务报表分类筛选
|
||
-- 数据来源: 基于 dim_table.site_table_area_name 初始化 + 手动维护
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.cfg_area_category (
|
||
category_id SERIAL PRIMARY KEY,
|
||
area_name VARCHAR(50) NOT NULL UNIQUE, -- 原始台区名称（来自dim_table）
|
||
category_l1 VARCHAR(30) NOT NULL, -- 一级分类：台球/麻将/K包/其他
|
||
category_l2 VARCHAR(30), -- 二级分类：大厅/包厢/斯诺克等
|
||
display_name VARCHAR(50), -- 显示名称（用于报表）
|
||
sort_order INTEGER DEFAULT 0, -- 排序序号
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now()
|
||
);
|
||
|
||
COMMENT ON TABLE billiards_dws.cfg_area_category IS '台区分类映射表：将自由文本的台区名称映射到规范分类';
|
||
```
|
||
|
||
### 3.3 助教维度表 DDL
|
||
|
||
#### 3.3.1 `dws_assistant_monthly_summary` - 月度业绩汇总
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_assistant_monthly_summary
|
||
-- 用途: 助教月度业绩汇总表，核心统计表
|
||
-- 更新策略: 每日增量更新当月数据，月初全量刷新上月
|
||
-- 数据来源矩阵:
|
||
-- base_service_* → dwd_assistant_service_log WHERE skill_id=2790683529513797
|
||
-- bonus_service_* → dwd_assistant_service_log WHERE skill_id=2790683529513798
|
||
-- room_service_* → dwd_assistant_service_log WHERE skill_id=3039912271463941
|
||
-- trashed_* → dwd_assistant_trash_event
|
||
-- tier_* → cfg_performance_tier
|
||
-- assistant_level → dim_assistant（按业务时间as-of join）
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_monthly_summary (
|
||
summary_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL, -- 门店ID
|
||
assistant_id BIGINT NOT NULL, -- 助教ID（dim_assistant.assistant_id）
|
||
stat_month DATE NOT NULL, -- 统计月份（每月1日）
|
||
|
||
-- ========== 基础课统计 ==========
|
||
base_service_count INTEGER DEFAULT 0, -- 基础课服务次数
|
||
base_service_seconds INTEGER DEFAULT 0, -- 基础课服务秒数
|
||
base_service_hours NUMERIC(10,4) DEFAULT 0, -- 基础课服务小时数（秒数/3600，精确到分钟）
|
||
base_income_original NUMERIC(12,2) DEFAULT 0, -- 基础课原始收入（客户支付）
|
||
base_income_deduction NUMERIC(12,2) DEFAULT 0, -- 基础课球房抽成
|
||
base_income_actual NUMERIC(12,2) DEFAULT 0, -- 基础课助教实得
|
||
|
||
-- ========== 附加课/激励课统计 ==========
|
||
bonus_service_count INTEGER DEFAULT 0, -- 附加课服务次数
|
||
bonus_service_seconds INTEGER DEFAULT 0, -- 附加课服务秒数
|
||
bonus_service_hours NUMERIC(10,4) DEFAULT 0, -- 附加课服务小时数
|
||
bonus_income_original NUMERIC(12,2) DEFAULT 0, -- 附加课原始收入
|
||
bonus_income_ratio_ded NUMERIC(12,2) DEFAULT 0, -- 附加课球房按比例抽成
|
||
bonus_income_actual NUMERIC(12,2) DEFAULT 0, -- 附加课助教实得
|
||
|
||
-- ========== 包厢课统计 ==========
|
||
room_service_count INTEGER DEFAULT 0, -- 包厢课服务次数
|
||
room_service_seconds INTEGER DEFAULT 0, -- 包厢课服务秒数
|
||
room_service_hours NUMERIC(10,4) DEFAULT 0, -- 包厢课服务小时数
|
||
room_income_original NUMERIC(12,2) DEFAULT 0, -- 包厢课原始收入
|
||
room_income_actual NUMERIC(12,2) DEFAULT 0, -- 包厢课助教实得
|
||
|
||
-- ========== 汇总统计 ==========
|
||
total_service_count INTEGER DEFAULT 0, -- 总服务次数
|
||
total_service_hours NUMERIC(10,4) DEFAULT 0, -- 总服务小时数（用于档位计算）
|
||
total_income_original NUMERIC(12,2) DEFAULT 0, -- 总原始收入
|
||
total_income_actual NUMERIC(12,2) DEFAULT 0, -- 总助教实得
|
||
|
||
-- ========== 废除影响 ==========
|
||
trashed_service_count INTEGER DEFAULT 0, -- 被废除服务次数
|
||
trashed_service_seconds INTEGER DEFAULT 0, -- 被废除服务秒数
|
||
trashed_amount NUMERIC(12,2) DEFAULT 0, -- 被废除金额
|
||
|
||
-- ========== 档位信息（根据total_service_hours计算）==========
|
||
tier_code INTEGER, -- 当前档位代码（0-5）
|
||
tier_name VARCHAR(50), -- 档位名称
|
||
tier_base_deduction NUMERIC(10,2), -- 档位基础课抽成
|
||
tier_bonus_ratio NUMERIC(5,4), -- 档位附加课抽成比例
|
||
|
||
-- ========== 奖金 ==========
|
||
sprint_bonus NUMERIC(10,2) DEFAULT 0, -- 冲刺奖金额
|
||
rank_bonus NUMERIC(10,2) DEFAULT 0, -- 排名奖金额
|
||
recharge_commission NUMERIC(10,2) DEFAULT 0, -- 充值提成（关联dws_assistant_recharge_commission）
|
||
other_bonus NUMERIC(10,2) DEFAULT 0, -- 其他奖金
|
||
total_bonus NUMERIC(10,2) DEFAULT 0, -- 奖金合计
|
||
|
||
-- ========== 最终工资 ==========
|
||
final_salary NUMERIC(12,2) DEFAULT 0, -- 最终应发工资
|
||
|
||
-- ========== 助教快照信息（按stat_month首日as-of join）==========
|
||
assistant_level INTEGER, -- 助教等级（8/10/20/30/40）
|
||
assistant_level_name VARCHAR(20), -- 等级名称
|
||
assistant_level_price NUMERIC(10,2), -- 等级对应客户单价
|
||
entry_date DATE, -- 入职日期
|
||
is_new_employee BOOLEAN DEFAULT FALSE, -- 是否本月新入职
|
||
work_days_in_month INTEGER, -- 本月在职天数
|
||
|
||
-- ========== 排名信息 ==========
|
||
monthly_rank INTEGER, -- 当月排名（按total_service_hours）
|
||
|
||
-- ========== 元数据 ==========
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, assistant_id, stat_month)
|
||
);
|
||
|
||
-- 索引优化
|
||
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_site_month
|
||
ON billiards_dws.dws_assistant_monthly_summary(site_id, stat_month);
|
||
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_assistant
|
||
ON billiards_dws.dws_assistant_monthly_summary(assistant_id, stat_month);
|
||
CREATE INDEX IF NOT EXISTS idx_dws_asst_monthly_month
|
||
ON billiards_dws.dws_assistant_monthly_summary(stat_month);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_assistant_monthly_summary IS '助教月度业绩汇总表：统计每位助教每月的业绩、收入、档位、工资';
|
||
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.total_service_hours IS '总服务小时数=基础课+附加课+包厢课，用于档位计算';
|
||
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.assistant_level IS '按stat_month首日通过as-of join获取的历史等级，非当前等级';
|
||
```
|
||
|
||
#### 3.3.2 `dws_assistant_daily_detail` - 日度业绩明细
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_assistant_daily_detail
|
||
-- 用途: 助教日度业绩明细，用于月中进度追踪
|
||
-- 更新策略: 每小时增量更新
|
||
-- 数据来源: dwd_assistant_service_log + dwd_assistant_trash_event
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_daily_detail (
|
||
detail_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
assistant_id BIGINT NOT NULL,
|
||
stat_date DATE NOT NULL, -- 统计日期
|
||
|
||
-- 基础课日统计
|
||
base_service_count INTEGER DEFAULT 0,
|
||
base_service_seconds INTEGER DEFAULT 0,
|
||
base_service_hours NUMERIC(10,4) DEFAULT 0,
|
||
base_income_original NUMERIC(12,2) DEFAULT 0,
|
||
|
||
-- 附加课日统计
|
||
bonus_service_count INTEGER DEFAULT 0,
|
||
bonus_service_seconds INTEGER DEFAULT 0,
|
||
bonus_service_hours NUMERIC(10,4) DEFAULT 0,
|
||
bonus_income_original NUMERIC(12,2) DEFAULT 0,
|
||
|
||
-- 包厢课日统计
|
||
room_service_count INTEGER DEFAULT 0,
|
||
room_service_seconds INTEGER DEFAULT 0,
|
||
room_service_hours NUMERIC(10,4) DEFAULT 0,
|
||
room_income_original NUMERIC(12,2) DEFAULT 0,
|
||
|
||
-- 汇总
|
||
total_service_count INTEGER DEFAULT 0,
|
||
total_service_hours NUMERIC(10,4) DEFAULT 0,
|
||
total_income_original NUMERIC(12,2) DEFAULT 0,
|
||
|
||
-- 废除
|
||
trashed_count INTEGER DEFAULT 0,
|
||
trashed_seconds INTEGER DEFAULT 0,
|
||
trashed_amount NUMERIC(12,2) DEFAULT 0,
|
||
|
||
-- 累计（当月至今）
|
||
mtd_total_hours NUMERIC(10,4) DEFAULT 0, -- Month-To-Date 累计小时数
|
||
mtd_tier_code INTEGER, -- 累计后档位
|
||
|
||
-- 服务客户统计
|
||
unique_customers INTEGER DEFAULT 0, -- 当日服务不同客户数
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, assistant_id, stat_date)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_dws_asst_daily_site_date
|
||
ON billiards_dws.dws_assistant_daily_detail(site_id, stat_date);
|
||
CREATE INDEX IF NOT EXISTS idx_dws_asst_daily_assistant
|
||
ON billiards_dws.dws_assistant_daily_detail(assistant_id, stat_date);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_assistant_daily_detail IS '助教日度业绩明细表：便于实时追踪月中业绩进度';
|
||
```
|
||
|
||
#### 3.3.3 `dws_assistant_customer_stats` - 服务客户统计
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_assistant_customer_stats
|
||
-- 用途: 助教服务客户统计（多时间窗口）
|
||
-- 更新策略: 每日凌晨全量刷新
|
||
-- 数据来源: dwd_assistant_service_log
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_customer_stats (
|
||
stats_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
assistant_id BIGINT NOT NULL,
|
||
stat_date DATE NOT NULL, -- 统计基准日期
|
||
window_days INTEGER NOT NULL, -- 时间窗口天数：7/10/15/30/60/90
|
||
|
||
-- 客户统计
|
||
unique_customers INTEGER DEFAULT 0, -- 服务不同客户数
|
||
total_service_count INTEGER DEFAULT 0, -- 总服务次数
|
||
total_service_hours NUMERIC(10,2) DEFAULT 0, -- 总服务小时数
|
||
|
||
-- 按课程类型
|
||
base_customers INTEGER DEFAULT 0, -- 基础课客户数
|
||
base_service_count INTEGER DEFAULT 0,
|
||
bonus_customers INTEGER DEFAULT 0, -- 附加课客户数
|
||
bonus_service_count INTEGER DEFAULT 0,
|
||
|
||
-- 收入
|
||
total_income NUMERIC(12,2) DEFAULT 0,
|
||
|
||
-- 客户复购
|
||
repeat_customers INTEGER DEFAULT 0, -- 复购客户数（服务>=2次）
|
||
repeat_rate NUMERIC(5,4) DEFAULT 0, -- 复购率
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, assistant_id, stat_date, window_days)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_dws_asst_cust_window
|
||
ON billiards_dws.dws_assistant_customer_stats(assistant_id, stat_date, window_days);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_assistant_customer_stats IS '助教服务客户统计表：支持7/10/15/30/60/90天多时间窗口';
|
||
```
|
||
|
||
#### 3.3.4 `dws_assistant_salary_calc` - 月度工资计算
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_assistant_salary_calc
|
||
-- 用途: 助教月度工资计算详情，记录计算过程便于核对
|
||
-- 更新策略: 月初全量计算上月工资
|
||
-- 数据来源: dws_assistant_monthly_summary + cfg_* 配置表
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_salary_calc (
|
||
calc_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
assistant_id BIGINT NOT NULL,
|
||
stat_month DATE NOT NULL, -- 统计月份
|
||
|
||
-- ========== 输入参数 ==========
|
||
assistant_level INTEGER, -- 助教等级
|
||
level_base_price NUMERIC(10,2), -- 等级基础课单价
|
||
tier_code INTEGER, -- 最终档位
|
||
tier_base_deduction NUMERIC(10,2), -- 档位抽成
|
||
tier_bonus_ratio NUMERIC(5,4), -- 附加课抽成比例
|
||
|
||
-- ========== 时长输入 ==========
|
||
base_hours NUMERIC(10,4), -- 基础课小时数
|
||
bonus_hours NUMERIC(10,4), -- 附加课小时数
|
||
room_hours NUMERIC(10,4), -- 包厢课小时数
|
||
total_hours NUMERIC(10,4), -- 总小时数
|
||
|
||
-- ========== 计算过程 ==========
|
||
-- 基础课收入 = base_hours * (level_base_price - tier_base_deduction)
|
||
base_calc_unit_income NUMERIC(10,2), -- 基础课单小时助教收入
|
||
base_calc_income NUMERIC(12,2), -- 基础课收入
|
||
|
||
-- 附加课收入 = bonus_原始收入 * (1 - tier_bonus_ratio)
|
||
bonus_original_income NUMERIC(12,2), -- 附加课原始收入
|
||
bonus_calc_income NUMERIC(12,2), -- 附加课助教收入
|
||
|
||
-- 包厢课收入
|
||
room_calc_income NUMERIC(12,2), -- 包厢课收入
|
||
|
||
-- ========== 奖金计算 ==========
|
||
sprint_rule_matched VARCHAR(50), -- 匹配的冲刺奖规则
|
||
sprint_bonus NUMERIC(10,2) DEFAULT 0, -- 冲刺奖
|
||
rank_value INTEGER, -- 排名
|
||
rank_rule_matched VARCHAR(50), -- 匹配的排名奖规则
|
||
rank_bonus NUMERIC(10,2) DEFAULT 0, -- 排名奖
|
||
recharge_commission NUMERIC(10,2) DEFAULT 0, -- 充值提成
|
||
other_bonus NUMERIC(10,2) DEFAULT 0, -- 其他奖金
|
||
|
||
-- ========== 新入职折算 ==========
|
||
is_new_employee BOOLEAN DEFAULT FALSE, -- 是否新入职
|
||
entry_date DATE, -- 入职日期
|
||
work_days INTEGER, -- 在职天数
|
||
projected_hours NUMERIC(10,4), -- 折算后小时数（用于定档）
|
||
max_tier_limit INTEGER, -- 最高档位限制（25日后入职限3档）
|
||
|
||
-- ========== 最终结果 ==========
|
||
total_service_income NUMERIC(12,2), -- 服务收入合计
|
||
total_bonus NUMERIC(10,2), -- 奖金合计
|
||
final_salary NUMERIC(12,2), -- 最终应发工资
|
||
|
||
-- ========== 元数据 ==========
|
||
calc_time TIMESTAMPTZ DEFAULT now(), -- 计算时间
|
||
calc_version INTEGER DEFAULT 1, -- 计算版本
|
||
remark TEXT, -- 备注
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, assistant_id, stat_month, calc_version)
|
||
);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_assistant_salary_calc IS '助教月度工资计算详情表：记录计算过程便于核对和审计';
|
||
```
|
||
|
||
#### 3.3.5 `dws_assistant_recharge_commission` - 充值提成记录
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_assistant_recharge_commission
|
||
-- 用途: 助教充值提成记录（手动导入）
|
||
-- 更新策略: 通过Excel等方式手动导入
|
||
-- 数据来源: 外部Excel文件
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_recharge_commission (
|
||
commission_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
assistant_id BIGINT NOT NULL, -- 助教ID
|
||
stat_month DATE NOT NULL, -- 统计月份
|
||
|
||
-- 充值关联
|
||
recharge_order_id BIGINT, -- 充值订单ID（如有）
|
||
member_card_id BIGINT, -- 储值卡ID
|
||
member_name VARCHAR(100), -- 会员名称
|
||
member_phone VARCHAR(50), -- 会员电话
|
||
|
||
-- 充值信息
|
||
recharge_time TIMESTAMPTZ, -- 充值时间
|
||
recharge_amount NUMERIC(12,2) NOT NULL, -- 充值金额
|
||
commission_rate NUMERIC(5,4), -- 提成比例
|
||
commission_amount NUMERIC(10,2) NOT NULL, -- 提成金额
|
||
|
||
-- 导入信息
|
||
import_batch VARCHAR(50), -- 导入批次号
|
||
import_time TIMESTAMPTZ DEFAULT now(), -- 导入时间
|
||
import_source VARCHAR(100), -- 来源文件名
|
||
remark TEXT,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_dws_asst_recharge_month
|
||
ON billiards_dws.dws_assistant_recharge_commission(assistant_id, stat_month);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_assistant_recharge_commission IS '助教充值提成记录表：通过Excel等方式手动导入';
|
||
```
|
||
|
||
### 3.4 客户维度表 DDL
|
||
|
||
#### 3.4.1 `dws_member_consumption_summary` - 消费情况统计
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_member_consumption_summary
|
||
-- 用途: 会员消费情况统计（多时间窗口）
|
||
-- 更新策略: 每日凌晨全量刷新
|
||
-- 数据来源矩阵:
|
||
-- visit_count → dwd_settlement_head（按order_settle_id去重）
|
||
-- table_fee_* → dwd_table_fee_log
|
||
-- assistant_* → dwd_assistant_service_log
|
||
-- goods_* → dwd_store_goods_sale
|
||
-- 金额字段 → dwd_settlement_head
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_member_consumption_summary (
|
||
summary_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
member_id BIGINT NOT NULL, -- dim_member.member_id
|
||
stat_date DATE NOT NULL, -- 统计基准日期
|
||
window_days INTEGER NOT NULL, -- 7/10/15/30/60/90
|
||
|
||
-- 到店统计
|
||
visit_count INTEGER DEFAULT 0, -- 到店次数（结账单数）
|
||
visit_days INTEGER DEFAULT 0, -- 到店天数
|
||
|
||
-- 台费消费
|
||
table_fee_count INTEGER DEFAULT 0, -- 开台次数
|
||
table_fee_seconds INTEGER DEFAULT 0, -- 台费时长（秒）
|
||
table_fee_hours NUMERIC(10,2) DEFAULT 0, -- 台费小时数
|
||
table_fee_amount NUMERIC(12,2) DEFAULT 0, -- 台费金额（正价）
|
||
table_fee_actual NUMERIC(12,2) DEFAULT 0, -- 台费实付
|
||
|
||
-- 助教服务
|
||
assistant_service_count INTEGER DEFAULT 0, -- 助教服务次数
|
||
assistant_service_hours NUMERIC(10,2) DEFAULT 0, -- 助教服务小时数
|
||
assistant_service_amt NUMERIC(12,2) DEFAULT 0, -- 助教服务金额
|
||
unique_assistants INTEGER DEFAULT 0, -- 服务过的不同助教数
|
||
|
||
-- 商品消费
|
||
goods_order_count INTEGER DEFAULT 0, -- 商品订单数
|
||
goods_item_count INTEGER DEFAULT 0, -- 商品项数
|
||
goods_amount NUMERIC(12,2) DEFAULT 0, -- 商品金额
|
||
|
||
-- 汇总金额（来源：dwd_settlement_head）
|
||
total_consume_money NUMERIC(12,2) DEFAULT 0, -- 消费总额（consume_money）
|
||
total_discount_amount NUMERIC(12,2) DEFAULT 0, -- 优惠总额
|
||
total_pay_amount NUMERIC(12,2) DEFAULT 0, -- 实付总额（pay_amount）
|
||
|
||
-- 支付方式
|
||
cash_payment NUMERIC(12,2) DEFAULT 0, -- 现金/线上支付
|
||
stored_card_payment NUMERIC(12,2) DEFAULT 0, -- 储值卡支付
|
||
gift_card_payment NUMERIC(12,2) DEFAULT 0, -- 赠送卡支付
|
||
coupon_payment NUMERIC(12,2) DEFAULT 0, -- 团购券支付
|
||
|
||
-- 客户价值指标
|
||
avg_visit_amount NUMERIC(10,2) DEFAULT 0, -- 单次到店平均消费
|
||
avg_visit_duration_min INTEGER DEFAULT 0, -- 单次平均停留时长（分钟）
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, member_id, stat_date, window_days)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_dws_member_cons_window
|
||
ON billiards_dws.dws_member_consumption_summary(member_id, stat_date, window_days);
|
||
CREATE INDEX IF NOT EXISTS idx_dws_member_cons_site
|
||
ON billiards_dws.dws_member_consumption_summary(site_id, stat_date, window_days);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_member_consumption_summary IS '会员消费汇总表：支持7/10/15/30/60/90天多时间窗口';
|
||
```
|
||
|
||
#### 3.4.2 `dws_member_visit_detail` - 来店消费明细
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_member_visit_detail
|
||
-- 用途: 会员来店消费明细表，记录每次到店的详细消费
|
||
-- 更新策略: 每日增量更新
|
||
-- 数据来源: dwd_settlement_head + 各明细表JOIN
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_member_visit_detail (
|
||
visit_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
member_id BIGINT NOT NULL,
|
||
order_settle_id BIGINT NOT NULL, -- 结账单ID
|
||
order_trade_no BIGINT, -- 订单号
|
||
|
||
-- 时间信息
|
||
visit_date DATE NOT NULL, -- 到店日期
|
||
start_time TIMESTAMPTZ, -- 开台时间
|
||
end_time TIMESTAMPTZ, -- 结账时间
|
||
duration_minutes INTEGER, -- 停留时长（分钟）
|
||
|
||
-- 台桌信息
|
||
table_id BIGINT, -- 台桌ID
|
||
table_name VARCHAR(50), -- 台桌名称
|
||
area_name VARCHAR(50), -- 台区名称
|
||
area_category VARCHAR(30), -- 台区分类（映射后）
|
||
|
||
-- 台费明细
|
||
table_fee_seconds INTEGER DEFAULT 0,
|
||
table_fee_amount NUMERIC(12,2) DEFAULT 0, -- 台费正价
|
||
table_fee_actual NUMERIC(12,2) DEFAULT 0, -- 台费实付
|
||
|
||
-- 助教服务明细
|
||
assistant_count INTEGER DEFAULT 0, -- 助教服务人次
|
||
assistant_names TEXT, -- 助教姓名列表（逗号分隔）
|
||
assistant_hours NUMERIC(10,2) DEFAULT 0, -- 助教总时长
|
||
assistant_amount NUMERIC(12,2) DEFAULT 0, -- 助教费用
|
||
|
||
-- 商品明细
|
||
goods_count INTEGER DEFAULT 0, -- 商品种类数
|
||
goods_names TEXT, -- 商品名称列表（Top5）
|
||
goods_amount NUMERIC(12,2) DEFAULT 0, -- 商品金额
|
||
|
||
-- 金额汇总
|
||
consume_money NUMERIC(12,2) DEFAULT 0, -- 消费总额
|
||
discount_amount NUMERIC(12,2) DEFAULT 0, -- 优惠总额
|
||
pay_amount NUMERIC(12,2) DEFAULT 0, -- 实付金额
|
||
|
||
-- 支付方式
|
||
payment_method_desc VARCHAR(100), -- 支付方式描述
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, member_id, order_settle_id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_dws_member_visit_member
|
||
ON billiards_dws.dws_member_visit_detail(member_id, visit_date);
|
||
CREATE INDEX IF NOT EXISTS idx_dws_member_visit_date
|
||
ON billiards_dws.dws_member_visit_detail(site_id, visit_date);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_member_visit_detail IS '会员来店消费明细表：记录每次到店的详细消费情况';
|
||
```
|
||
|
||
### 3.5 财务维度表 DDL
|
||
|
||
#### 3.5.1 `dws_finance_daily_summary` - 日度财务汇总
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_finance_daily_summary
|
||
-- 用途: 门店日度财务汇总（核心财务报表数据源）
|
||
-- 更新策略: 每小时增量更新
|
||
-- 数据来源矩阵:
|
||
-- gross_* → dwd_settlement_head (table_charge_money, goods_money, assistant_pd_money, assistant_cx_money)
|
||
-- discount_* → dwd_settlement_head + dwd_member_balance_change + dwd_groupbuy_redemption
|
||
-- payment_* → dwd_settlement_head (balance_amount, recharge_card_amount, gift_card_amount, coupon_amount)
|
||
-- recharge_* → dwd_recharge_order
|
||
-- refund_* → dwd_refund
|
||
-- stored_card_* → dwd_member_balance_change WHERE card_type_id=储值卡
|
||
-- gift_card_* → dwd_member_balance_change WHERE card_type_id IN (台费卡,酒水卡,活动抵用券)
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_daily_summary (
|
||
summary_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
stat_date DATE NOT NULL,
|
||
|
||
-- ========== 经营链：发生额 → 优惠 → 确认收入 ==========
|
||
-- 发生额（正价）- 来源：dwd_settlement_head
|
||
gross_revenue NUMERIC(14,2) DEFAULT 0, -- 发生额/正价总计
|
||
gross_table_fee NUMERIC(14,2) DEFAULT 0, -- 台费发生额 ← table_charge_money
|
||
gross_assistant_base NUMERIC(14,2) DEFAULT 0, -- 助教基础课发生额 ← assistant_pd_money
|
||
gross_assistant_bonus NUMERIC(14,2) DEFAULT 0, -- 助教激励课发生额 ← assistant_cx_money
|
||
gross_goods NUMERIC(14,2) DEFAULT 0, -- 食品酒水发生额 ← goods_money
|
||
|
||
-- 优惠明细
|
||
discount_total NUMERIC(14,2) DEFAULT 0, -- 优惠总额 = gross_revenue - confirmed_revenue
|
||
discount_groupbuy NUMERIC(14,2) DEFAULT 0, -- 团购优惠 ← dwd_groupbuy_redemption(ledger_amount - coupon_money)
|
||
discount_vip NUMERIC(14,2) DEFAULT 0, -- 大客户优惠（预留，需抽样分析）
|
||
discount_gift_card NUMERIC(14,2) DEFAULT 0, -- 赠送卡抵扣 ← dwd_member_balance_change(台费卡+酒水卡+活动抵用券消费)
|
||
discount_member NUMERIC(14,2) DEFAULT 0, -- 会员折扣 ← member_discount_amount
|
||
discount_manual NUMERIC(14,2) DEFAULT 0, -- 手动调整 ← adjust_amount
|
||
discount_free NUMERIC(14,2) DEFAULT 0, -- 免单（预留）
|
||
discount_rounding NUMERIC(14,2) DEFAULT 0, -- 抹零 ← rounding_amount
|
||
|
||
-- 成交/确认收入
|
||
confirmed_revenue NUMERIC(14,2) DEFAULT 0, -- 成交/确认收入（不含充值）← pay_amount汇总
|
||
|
||
-- ========== 支付方式构成 ==========
|
||
payment_stored_card NUMERIC(14,2) DEFAULT 0, -- 储值卡结算冲销 ← balance_amount + recharge_card_amount
|
||
payment_gift_card NUMERIC(14,2) DEFAULT 0, -- 赠送卡支付 ← gift_card_amount
|
||
payment_cash_online NUMERIC(14,2) DEFAULT 0, -- 现金/线上支付 = pay_amount - 卡支付
|
||
payment_groupbuy_deal NUMERIC(14,2) DEFAULT 0, -- 团购核销确认收入 ← dwd_groupbuy_redemption.coupon_money
|
||
|
||
-- ========== 现金流 ==========
|
||
-- 现金流入
|
||
cash_inflow_consume NUMERIC(14,2) DEFAULT 0, -- 消费现金流入（现金+线上-退款）
|
||
cash_inflow_recharge NUMERIC(14,2) DEFAULT 0, -- 充值到账 ← dwd_recharge_order.pay_amount
|
||
cash_inflow_platform NUMERIC(14,2) DEFAULT 0, -- 平台回款（预留，手动导入）
|
||
cash_inflow_total NUMERIC(14,2) DEFAULT 0, -- 现金流入合计
|
||
|
||
-- 现金支出（预留，通过 dws_finance_expense_summary 手动导入）
|
||
cash_outflow_total NUMERIC(14,2) DEFAULT 0, -- 现金支出合计
|
||
|
||
-- 现金结余
|
||
cash_balance NUMERIC(14,2) DEFAULT 0, -- 现金结余 = inflow - outflow
|
||
cash_balance_rate NUMERIC(5,4) DEFAULT 0, -- 结余率 = balance / inflow
|
||
|
||
-- ========== 充值与预收 ==========
|
||
recharge_first NUMERIC(14,2) DEFAULT 0, -- 首充金额 ← is_first=1
|
||
recharge_renew NUMERIC(14,2) DEFAULT 0, -- 续费金额 ← is_first=2
|
||
recharge_total NUMERIC(14,2) DEFAULT 0, -- 充值合计
|
||
recharge_count INTEGER DEFAULT 0, -- 充值笔数
|
||
|
||
-- 储值卡（card_type_id=2793249295533893）
|
||
stored_card_new NUMERIC(14,2) DEFAULT 0, -- 储值卡充值（from_type=3）
|
||
stored_card_consume NUMERIC(14,2) DEFAULT 0, -- 储值卡消耗（from_type=1，change_amount<0）
|
||
stored_card_balance_eod NUMERIC(14,2) DEFAULT 0, -- 储值卡余额（截止当日）
|
||
|
||
-- 赠送卡（台费卡+酒水卡+活动抵用券）
|
||
gift_card_new NUMERIC(14,2) DEFAULT 0, -- 赠送卡新增（from_type=4）
|
||
gift_card_consume NUMERIC(14,2) DEFAULT 0, -- 赠送卡消耗
|
||
gift_card_balance_eod NUMERIC(14,2) DEFAULT 0, -- 赠送卡余额
|
||
|
||
-- 细分赠送卡
|
||
gift_card_table_new NUMERIC(14,2) DEFAULT 0, -- 台费卡新增
|
||
gift_card_table_consume NUMERIC(14,2) DEFAULT 0, -- 台费卡消耗
|
||
gift_card_table_balance NUMERIC(14,2) DEFAULT 0, -- 台费卡余额
|
||
gift_card_drink_new NUMERIC(14,2) DEFAULT 0, -- 酒水卡新增
|
||
gift_card_drink_consume NUMERIC(14,2) DEFAULT 0, -- 酒水卡消耗
|
||
gift_card_drink_balance NUMERIC(14,2) DEFAULT 0, -- 酒水卡余额
|
||
gift_card_coupon_new NUMERIC(14,2) DEFAULT 0, -- 活动抵用券新增
|
||
gift_card_coupon_consume NUMERIC(14,2) DEFAULT 0, -- 活动抵用券消耗
|
||
gift_card_coupon_balance NUMERIC(14,2) DEFAULT 0, -- 活动抵用券余额
|
||
|
||
-- ========== 订单统计 ==========
|
||
order_count INTEGER DEFAULT 0, -- 订单数
|
||
order_avg_amount NUMERIC(10,2) DEFAULT 0, -- 客单价
|
||
member_order_count INTEGER DEFAULT 0, -- 会员订单数
|
||
member_order_rate NUMERIC(5,4) DEFAULT 0, -- 会员订单占比
|
||
|
||
-- ========== 退款统计 ==========
|
||
refund_count INTEGER DEFAULT 0, -- 退款笔数
|
||
refund_amount NUMERIC(14,2) DEFAULT 0, -- 退款金额
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, stat_date)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_dws_finance_daily_site_date
|
||
ON billiards_dws.dws_finance_daily_summary(site_id, stat_date);
|
||
CREATE INDEX IF NOT EXISTS idx_dws_finance_daily_date
|
||
ON billiards_dws.dws_finance_daily_summary(stat_date);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_finance_daily_summary IS '门店日度财务汇总表：核心财务报表数据源';
|
||
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.gross_revenue IS '发生额/正价：table_charge_money+goods_money+assistant_pd_money+assistant_cx_money';
|
||
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.confirmed_revenue IS '成交/确认收入：扣除优惠后的营业收入，不含充值';
|
||
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.discount_gift_card IS '赠送卡抵扣：台费卡+酒水卡+活动抵用券的消费金额';
|
||
```
|
||
|
||
#### 3.5.2 `dws_finance_income_structure` - 收入结构
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_finance_income_structure
|
||
-- 用途: 收入结构按区域/类型细分
|
||
-- 更新策略: 每日增量更新
|
||
-- 数据来源: dwd_table_fee_log + dwd_assistant_service_log + dwd_store_goods_sale
|
||
-- JOIN cfg_area_category 获取标准分类
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_income_structure (
|
||
structure_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
stat_date DATE NOT NULL,
|
||
|
||
-- 分类维度
|
||
income_category VARCHAR(20) NOT NULL, -- 收入大类：TABLE/ASSISTANT_BASE/ASSISTANT_BONUS/GOODS
|
||
area_name VARCHAR(50), -- 原始台区名称（dim_table.site_table_area_name）
|
||
area_category_l1 VARCHAR(30), -- 一级分类（cfg_area_category.category_l1）
|
||
area_category_l2 VARCHAR(30), -- 二级分类
|
||
|
||
-- 金额（三列式）
|
||
gross_amount NUMERIC(14,2) DEFAULT 0, -- 发生额
|
||
discount_amount NUMERIC(14,2) DEFAULT 0, -- 优惠金额
|
||
confirmed_amount NUMERIC(14,2) DEFAULT 0, -- 确认收入
|
||
|
||
-- 数量/时长
|
||
item_count INTEGER DEFAULT 0, -- 项目数/次数
|
||
duration_seconds INTEGER DEFAULT 0, -- 时长（秒）
|
||
duration_hours NUMERIC(10,2) DEFAULT 0, -- 时长（小时）
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, stat_date, income_category, COALESCE(area_name, ''))
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_dws_finance_structure_date
|
||
ON billiards_dws.dws_finance_income_structure(site_id, stat_date);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_finance_income_structure IS '收入结构表：按大类和区域细分的收入明细';
|
||
```
|
||
|
||
#### 3.5.3 `dws_finance_discount_detail` - 优惠明细
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_finance_discount_detail
|
||
-- 用途: 优惠类型明细统计
|
||
-- 更新策略: 每日增量更新
|
||
-- 数据来源矩阵:
|
||
-- GROUPBUY → dwd_groupbuy_redemption
|
||
-- GIFT_CARD_* → dwd_member_balance_change
|
||
-- MEMBER_DISCOUNT → dwd_settlement_head.member_discount_amount
|
||
-- MANUAL_ADJUST → dwd_settlement_head.adjust_amount
|
||
-- ROUNDING → dwd_settlement_head.rounding_amount
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_discount_detail (
|
||
detail_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
stat_date DATE NOT NULL,
|
||
|
||
-- 优惠类型
|
||
discount_type VARCHAR(30) NOT NULL, -- 优惠类型代码
|
||
discount_type_name VARCHAR(50) NOT NULL, -- 优惠类型名称
|
||
|
||
-- 金额
|
||
discount_amount NUMERIC(14,2) DEFAULT 0, -- 优惠金额
|
||
order_count INTEGER DEFAULT 0, -- 涉及订单数
|
||
|
||
-- 占比
|
||
discount_rate NUMERIC(5,4) DEFAULT 0, -- 占总优惠比例
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, stat_date, discount_type)
|
||
);
|
||
|
||
-- 优惠类型枚举：
|
||
-- GROUPBUY: 团购优惠
|
||
-- GIFT_CARD_TABLE: 台费卡抵扣
|
||
-- GIFT_CARD_DRINK: 酒水卡抵扣
|
||
-- GIFT_CARD_COUPON: 活动抵用券抵扣
|
||
-- MEMBER_DISCOUNT: 会员折扣
|
||
-- VIP_DISCOUNT: 大客户优惠
|
||
-- MANUAL_ADJUST: 手动调整
|
||
-- FREE_ORDER: 免单
|
||
-- ROUNDING: 抹零
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_finance_discount_detail IS '优惠明细统计表：按优惠类型细分的优惠金额统计';
|
||
```
|
||
|
||
#### 3.5.4 `dws_finance_recharge_summary` - 充值与预收汇总
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_finance_recharge_summary
|
||
-- 用途: 充值与会员卡余额汇总
|
||
-- 更新策略: 每日增量更新
|
||
-- 数据来源:
|
||
-- 充值 → dwd_recharge_order
|
||
-- 余额变动 → dwd_member_balance_change
|
||
-- 期末余额 → dim_member_card_account
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_recharge_summary (
|
||
summary_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
stat_date DATE NOT NULL,
|
||
card_type VARCHAR(20) NOT NULL, -- 卡类型：STORED/TABLE_FEE/DRINK/COUPON/MONTH/YEAR
|
||
card_type_name VARCHAR(30) NOT NULL, -- 卡类型名称
|
||
|
||
-- 充值/新增
|
||
recharge_count INTEGER DEFAULT 0, -- 充值/新增笔数
|
||
recharge_amount NUMERIC(14,2) DEFAULT 0, -- 充值/新增金额
|
||
first_recharge_count INTEGER DEFAULT 0, -- 首充笔数
|
||
first_recharge_amount NUMERIC(14,2) DEFAULT 0, -- 首充金额
|
||
|
||
-- 消耗
|
||
consume_count INTEGER DEFAULT 0, -- 消费笔数
|
||
consume_amount NUMERIC(14,2) DEFAULT 0, -- 消费金额
|
||
|
||
-- 退款/调整
|
||
refund_amount NUMERIC(14,2) DEFAULT 0, -- 退款金额
|
||
adjust_amount NUMERIC(14,2) DEFAULT 0, -- 调整金额
|
||
|
||
-- 期末余额
|
||
balance_eod NUMERIC(14,2) DEFAULT 0, -- 期末余额
|
||
card_count INTEGER DEFAULT 0, -- 有效卡数量
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, stat_date, card_type)
|
||
);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_finance_recharge_summary IS '充值与会员卡汇总表：按卡类型统计充值、消耗、余额';
|
||
```
|
||
|
||
#### 3.5.5 `dws_finance_expense_summary` - 支出结构（手动导入）
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_finance_expense_summary
|
||
-- 用途: 支出结构汇总（手动导入）
|
||
-- 更新策略: 通过Excel等方式手动导入
|
||
-- 数据来源: 外部Excel文件
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_finance_expense_summary (
|
||
expense_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
stat_month DATE NOT NULL, -- 统计月份（每月1日）
|
||
|
||
-- 支出类型
|
||
expense_type VARCHAR(30) NOT NULL, -- 支出类型代码
|
||
expense_type_name VARCHAR(50) NOT NULL, -- 支出类型名称
|
||
|
||
-- 金额
|
||
expense_amount NUMERIC(14,2) NOT NULL, -- 支出金额
|
||
|
||
-- 导入信息
|
||
import_batch VARCHAR(50), -- 导入批次号
|
||
import_time TIMESTAMPTZ DEFAULT now(), -- 导入时间
|
||
import_source VARCHAR(100), -- 来源文件名
|
||
remark TEXT,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, stat_month, expense_type)
|
||
);
|
||
|
||
-- 支出类型枚举：
|
||
-- RENT: 房租
|
||
-- UTILITY: 水电
|
||
-- PROPERTY: 物业费
|
||
-- PURCHASE: 进货成本
|
||
-- CONSUMABLE: 耗材
|
||
-- REIMBURSE: 报销
|
||
-- ASSISTANT_COMMISSION: 助教分成
|
||
-- ASSISTANT_BONUS: 助教奖惩
|
||
-- SALARY: 固定人员工资
|
||
-- PLATFORM_MEITUAN: 美团服务费
|
||
-- PLATFORM_DOUYIN: 抖音服务费
|
||
-- PLATFORM_HUILAIMI: 汇来米平台服务费
|
||
-- OTHER: 其他费用
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_finance_expense_summary IS '支出结构汇总表：通过Excel等方式手动导入';
|
||
```
|
||
|
||
#### 3.5.6 `dws_assistant_finance_analysis` - 助教收支分析
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_assistant_finance_analysis
|
||
-- 用途: 助教收支分析（财务视角）
|
||
-- 更新策略: 每日增量更新
|
||
-- 数据来源: dwd_assistant_service_log + cfg_performance_tier + cfg_assistant_level_price
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_assistant_finance_analysis (
|
||
analysis_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
stat_date DATE NOT NULL,
|
||
assistant_level INTEGER, -- 助教等级（NULL表示全部）
|
||
assistant_level_name VARCHAR(20),
|
||
|
||
-- 基础课
|
||
base_customer_payment NUMERIC(14,2) DEFAULT 0, -- 客户支付
|
||
base_shop_commission NUMERIC(14,2) DEFAULT 0, -- 球房抽成
|
||
base_hours NUMERIC(10,2) DEFAULT 0, -- 小时数
|
||
base_avg_commission NUMERIC(10,2) DEFAULT 0, -- 均小时抽成
|
||
base_service_count INTEGER DEFAULT 0, -- 服务次数
|
||
|
||
-- 激励课
|
||
bonus_customer_payment NUMERIC(14,2) DEFAULT 0,
|
||
bonus_shop_commission NUMERIC(14,2) DEFAULT 0,
|
||
bonus_hours NUMERIC(10,2) DEFAULT 0,
|
||
bonus_avg_commission NUMERIC(10,2) DEFAULT 0,
|
||
bonus_service_count INTEGER DEFAULT 0,
|
||
|
||
-- 包厢课
|
||
room_customer_payment NUMERIC(14,2) DEFAULT 0,
|
||
room_shop_commission NUMERIC(14,2) DEFAULT 0,
|
||
room_hours NUMERIC(10,2) DEFAULT 0,
|
||
room_service_count INTEGER DEFAULT 0,
|
||
|
||
-- 汇总
|
||
total_customer_payment NUMERIC(14,2) DEFAULT 0,
|
||
total_shop_commission NUMERIC(14,2) DEFAULT 0,
|
||
total_hours NUMERIC(10,2) DEFAULT 0,
|
||
|
||
-- 助教数量
|
||
assistant_count INTEGER DEFAULT 0,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, stat_date, COALESCE(assistant_level, 0))
|
||
);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_assistant_finance_analysis IS '助教收支分析表：财务视角的助教业绩与抽成统计';
|
||
```
|
||
|
||
#### 3.5.7 `dws_platform_settlement` - 平台回款/服务费（手动导入）
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 表名: dws_platform_settlement
|
||
-- 用途: 平台回款与服务费记录（手动导入）
|
||
-- 更新策略: 通过Excel等方式手动导入
|
||
-- 数据来源: 外部Excel文件（美团/抖音/汇来米等平台结算单）
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS billiards_dws.dws_platform_settlement (
|
||
settlement_id BIGSERIAL PRIMARY KEY,
|
||
site_id BIGINT NOT NULL,
|
||
stat_month DATE NOT NULL, -- 结算月份
|
||
|
||
-- 平台信息
|
||
platform_code VARCHAR(20) NOT NULL, -- 平台代码：MEITUAN/DOUYIN/HUILAIMI/OTHER
|
||
platform_name VARCHAR(50) NOT NULL, -- 平台名称
|
||
|
||
-- 核销统计
|
||
redemption_count INTEGER DEFAULT 0, -- 核销笔数
|
||
redemption_amount NUMERIC(14,2) DEFAULT 0, -- 核销券面值
|
||
|
||
-- 回款
|
||
settlement_amount NUMERIC(14,2) DEFAULT 0, -- 平台回款金额
|
||
settlement_date DATE, -- 回款日期
|
||
|
||
-- 服务费
|
||
service_fee NUMERIC(14,2) DEFAULT 0, -- 平台服务费
|
||
service_fee_rate NUMERIC(5,4), -- 服务费率
|
||
|
||
-- 差价
|
||
price_diff NUMERIC(14,2) DEFAULT 0, -- 团购差价（正价-核销价）
|
||
|
||
-- 导入信息
|
||
import_batch VARCHAR(50),
|
||
import_time TIMESTAMPTZ DEFAULT now(),
|
||
import_source VARCHAR(100),
|
||
remark TEXT,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT now(),
|
||
updated_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
UNIQUE(site_id, stat_month, platform_code)
|
||
);
|
||
|
||
COMMENT ON TABLE billiards_dws.dws_platform_settlement IS '平台回款与服务费表：通过Excel等方式手动导入';
|
||
```
|
||
|
||
---
|
||
|
||
## 四、数据分层策略
|
||
|
||
### 4.1 时间分层设计
|
||
|
||
| 分层 | 时间范围 | 用途 | 实现方式 | 财务支持 |
|
||
|------|----------|------|----------|----------|
|
||
| L1 热数据 | 最近2天 | 实时监控/当日报表 | 物化视图 + 每小时刷新 | ✓ |
|
||
| L2 近期 | 最近1月 | 周报/月报 | 分区表 | ✓ |
|
||
| L3 中期 | 最近3月 | 季度分析 | 分区表 | ✓ |
|
||
| L4 全量 | 最近6月 | 半年分析 | 分区表 | ✓ 财务特殊需求 |
|
||
| L5 历史 | 全部 | 年度汇总/深度分析 | 归档表 | ✓ |
|
||
|
||
### 4.2 财务时间筛选支持
|
||
|
||
| UI筛选项 | 实现方式 | 环比支持 |
|
||
|----------|----------|----------|
|
||
| 本月 | 当前月 | vs 上月 |
|
||
| 上个月 | 上月全月 | vs 上上月 |
|
||
| 前3个月不加本月 | 过去3个完整月 | vs 更早3个月 |
|
||
| 前3个月+本月 | 过去3月+本月 | vs 更早4个月 |
|
||
| 最近半年不含本月 | 过去6个完整月 | vs 更早6个月 |
|
||
| 本季度含本月 | 当前季度 | vs 上季度 |
|
||
| 上个季度 | 上季度全季 | vs 上上季度 |
|
||
| 本周 | 当前周 | vs 上周 |
|
||
| 上周 | 上周全周 | vs 上上周 |
|
||
|
||
---
|
||
|
||
## 五、实施任务清单
|
||
|
||
### 5.1 阶段一：基础设施（预计 1-2 天）
|
||
|
||
| 序号 | 任务 | 产出物 |
|
||
|------|------|--------|
|
||
| 1.1 | 更新 `schema_dws.sql` 完整DDL | DDL文件 |
|
||
| 1.2 | 创建 `seed_dws_config.sql` 配置初始数据 | 种子数据 |
|
||
| 1.3 | 创建 `cfg_area_category` 台区映射数据 | 映射数据 |
|
||
| 1.4 | 更新 `InitDwsSchemaTask` | 任务代码 |
|
||
| 1.5 | 创建 `BaseDwsTask` 基类 | 基类代码 |
|
||
|
||
### 5.2 阶段二：助教维度（预计 2-3 天）
|
||
|
||
| 序号 | 任务 | 产出物 |
|
||
|------|------|--------|
|
||
| 2.1 | 实现 `AssistantDailyTask` | 日度明细 |
|
||
| 2.2 | 实现 `AssistantMonthlyTask` | 月度汇总 |
|
||
| 2.3 | 实现档位计算模块（含新入职折算） | 算法模块 |
|
||
| 2.4 | 实现工资计算模块 | 算法模块 |
|
||
| 2.5 | 实现 `AssistantCustomerTask` | 客户统计 |
|
||
| 2.6 | 实现充值提成导入功能 | 导入脚本 |
|
||
|
||
### 5.3 阶段三：客户维度（预计 1-2 天）
|
||
|
||
| 序号 | 任务 | 产出物 |
|
||
|------|------|--------|
|
||
| 3.1 | 实现 `MemberConsumptionTask` | 消费统计 |
|
||
| 3.2 | 实现 `MemberVisitDetailTask` | 来店明细 |
|
||
|
||
### 5.4 阶段四：财务维度（预计 2-3 天）
|
||
|
||
| 序号 | 任务 | 产出物 |
|
||
|------|------|--------|
|
||
| 4.1 | 实现 `FinanceDailySummaryTask` | 日度汇总 |
|
||
| 4.2 | 实现 `FinanceIncomeStructureTask` | 收入结构 |
|
||
| 4.3 | 实现 `FinanceDiscountDetailTask` | 优惠明细 |
|
||
| 4.4 | 实现 `FinanceRechargeSummaryTask` | 充值统计 |
|
||
| 4.5 | 实现 `AssistantFinanceTask` | 助教收支 |
|
||
| 4.6 | 实现支出/平台结算导入功能 | 导入脚本 |
|
||
|
||
### 5.5 阶段五：文档与测试（预计 1-2 天）
|
||
|
||
| 序号 | 任务 | 产出物 |
|
||
|------|------|--------|
|
||
| 5.1 | 编写 `dws_tables_dictionary.md` | 数据字典 |
|
||
| 5.2 | 更新 `README.md` | README |
|
||
| 5.3 | 抽样100单分析优惠口径 | 分析报告 |
|
||
| 5.4 | 编写单元测试 | 测试代码 |
|
||
|
||
---
|
||
|
||
## 六、配置表初始数据
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 文件: seed_dws_config.sql
|
||
-- 用途: DWS 配置表初始数据
|
||
-- ============================================================
|
||
|
||
-- 绩效档位配置
|
||
INSERT INTO billiards_dws.cfg_performance_tier
|
||
(tier_code, tier_name, tier_reason, min_hours, max_hours, base_deduction, bonus_ratio, vacation_days)
|
||
VALUES
|
||
(0, '0档 淘汰压力', '淘汰压力', 0, 100, 28, 0.50, 3),
|
||
(1, '1档 及格档', '重点激励', 100, 130, 18, 0.40, 4),
|
||
(2, '2档 良好档', '重点激励', 130, 160, 15, 0.38, 4),
|
||
(3, '3档 优秀档', '优秀标准', 160, 190, 13, 0.35, 5),
|
||
(4, '4档 卓越加速档', '高端人才倾斜', 190, 220, 10, 0.33, 6),
|
||
(5, '5档 冠军加速档', '高端人才倾斜', 220, NULL, 8, 0.30, NULL);
|
||
|
||
-- 助教等级定价（注意：level_code对应dim_assistant.assistant_level）
|
||
INSERT INTO billiards_dws.cfg_assistant_level_price
|
||
(level_code, level_name, base_price, bonus_price)
|
||
VALUES
|
||
(10, '初级', 98, 190),
|
||
(20, '中级', 108, 190),
|
||
(30, '高级', 118, 190),
|
||
(40, '星级', 138, 190),
|
||
(8, '助教管理', 0, 0);
|
||
|
||
-- 奖金规则
|
||
INSERT INTO billiards_dws.cfg_bonus_rules
|
||
(rule_type, rule_name, condition_type, condition_value, bonus_amount, priority, is_stackable)
|
||
VALUES
|
||
('SPRINT', '冲刺奖-190小时', 'HOURS_GTE', 190, 300, 1, FALSE),
|
||
('SPRINT', '冲刺奖-220小时', 'HOURS_GTE', 220, 800, 2, FALSE),
|
||
('TOP_RANK', 'Top1奖金', 'RANK_EQ', 1, 1000, 1, TRUE),
|
||
('TOP_RANK', 'Top2奖金', 'RANK_EQ', 2, 600, 2, TRUE),
|
||
('TOP_RANK', 'Top3奖金', 'RANK_EQ', 3, 400, 3, TRUE);
|
||
|
||
-- 台区分类映射
|
||
INSERT INTO billiards_dws.cfg_area_category
|
||
(area_name, category_l1, category_l2, display_name, sort_order)
|
||
VALUES
|
||
('A区', '台球', '大厅', 'A区', 1),
|
||
('B区', '台球', '大厅', 'B区', 2),
|
||
('C区', '台球', '大厅', 'C区', 3),
|
||
('斯诺克区', '台球', '斯诺克', '斯诺克区', 4),
|
||
('VIP包厢', '台球', '包厢', 'VIP包厢', 5),
|
||
('TV台', '台球', '大厅', 'TV台', 6),
|
||
('麻将房', '麻将', '麻将房', '麻将房', 10),
|
||
('M7', '麻将', '麻将房', 'M7', 11),
|
||
('M8', '麻将', '麻将房', 'M8', 12),
|
||
('666', '麻将', '麻将房', '666', 13),
|
||
('发财', '麻将', '麻将房', '发财', 14),
|
||
('K包', 'K包', 'KTV', 'K包', 20),
|
||
('k包活动区', 'K包', 'KTV', 'K包活动区', 21),
|
||
('幸会158', 'K包', 'KTV', '幸会158', 22),
|
||
('补时长', '其他', '补时长', '补时长', 99);
|
||
```
|
||
|
||
---
|
||
|
||
## 七、待确认事项
|
||
|
||
### 7.1 需抽样分析
|
||
|
||
1. **优惠口径分析**：抽取100个订单样本，分析 `adjust_amount` / `member_discount_amount` / `coupon_amount` 等字段的关系，明确"大客户优惠"和"其他优惠"的区分规则。
|
||
|
||
### 7.2 已确认事项
|
||
|
||
| 事项 | 结论 |
|
||
|------|------|
|
||
| 课程类型判断 | 使用 `skill_id` 而非 `skill_name`：基础课=2790683529513797，附加课=2790683529513798，包厢课=3039912271463941 |
|
||
| 助教等级枚举 | 8=助教管理，10=初级，20=中级，30=高级，40=星级 |
|
||
| 赠送卡定义 | 台费卡+酒水卡+活动抵用券 |
|
||
| 储值卡定义 | card_type_id=2793249295533893 的"储值卡" |
|
||
| 支出数据 | 数据库预留结构，后期通过Excel手动导入 |
|
||
| 平台回款 | 数据库预留结构，后期通过Excel手动导入 |
|
||
| 充值提成 | 数据库预留结构，后期通过Excel手动导入 |
|
||
| 历史等级取数 | 使用 as-of join 按业务时间获取当时的助教等级 |
|
||
|
||
---
|
||
|
||
**文档版本历史**
|
||
|
||
| 版本 | 日期 | 变更说明 |
|
||
|------|------|----------|
|
||
| 1.0 | 2026-02-01 | 初始版本 |
|
||
| 2.0 | 2026-02-01 | 根据反馈更新：补全DDL、明确数据口径、增加数据来源矩阵、更新skill_id枚举、增加手动导入表结构 |
|