1361 lines
126 KiB
Plaintext
1361 lines
126 KiB
Plaintext
2. 数仓分层结构总览
|
||
|
||
本项目采用典型的三层数仓分层体系：
|
||
|
||
- **ODS（Operational Data Store，操作数据层）**：
|
||
以“贴源”方式承接上游 JSON 数据，一条记录尽量对应上游一条业务记录，仅做最轻度的数据类型规范与字段补充，不做业务口径改造。
|
||
- **DWD（Data Warehouse Detail，明细数据层）**：
|
||
在 ODS 基础上进行清洗、标准化与轻度汇总，形成标准的「事实表 + 维度表」模型，作为后续 DWS 和报表的统一明细数据源。
|
||
- **DWS（Data Warehouse Service，汇总服务层）**：
|
||
面向具体经营分析场景的汇总宽表或主题汇总表，本期重点为「订单粒度」的汇总（每笔结账一行），后续可扩展日粒度、助教绩效粒度等宽表。
|
||
|
||
### 2.1 各层表清单
|
||
|
||
#### 2.1.1 ODS 层表（源数据镜像）
|
||
|
||
ODS 层按上游 JSON 文件一一映射，保留源系统字段，字段命名尽量对齐源字段。
|
||
|
||
| 表名 | 粒度描述 | 主要来源 JSON / 说明 |
|
||
|------------------------------|------------------------------------|------------------------------------|
|
||
| `ods_member_profile` | 会员档案，1 行 = 1 个会员账号 | 会员档案.JSON |
|
||
| `ods_member_card` | 会员卡记录，1 行 = 1 张卡 | 储值卡列表.JSON |
|
||
| `ods_balance_change` | 余额变更记录，1 行 = 1 次余额变动 | 余额变动.JSON |
|
||
| `ods_recharge_record` | 充值记录，1 行 = 1 笔充值交易 | 充值记录.JSON |
|
||
| `ods_product` | 租户级商品档案 | 商品档案.JSON |
|
||
| `ods_store_product` | 门店商品档案，门店维度的商品配置 | 门店商品档案.JSON |
|
||
| `ods_store_sale_item` | 商品销售明细，1 行 = 1 个销售行 | 门店销售记录.JSON |
|
||
| `ods_table_info` | 台桌档案，1 行 = 1 张台/包厢 | 台桌列表.JSON |
|
||
| `ods_table_use_log` | 台费流水，1 行 = 1 笔台桌使用记录 | 台费流水.JSON |
|
||
| `ods_table_fee_adjust` | 台费打折记录，1 行 = 1 次人工折扣 | 台费打折.JSON |
|
||
| `ods_assistant_account` | 助教账号档案，1 行 = 1 个助教账号 | 助教账号.JSON |
|
||
| `ods_assistant_service_log` | 助教服务流水，1 行 = 1 条助教服务 | 助教流水.JSON |
|
||
| `ods_assistant_cancel_log` | 助教废除记录，1 行 = 1 次服务取消 | 助教废除.JSON |
|
||
| `ods_group_package` | 团购套餐定义 | 团购套餐定义.JSON |
|
||
| `ods_group_package_log` | 团购套餐使用流水 | 团购套餐使用.JSON |
|
||
| `ods_platform_coupon_log` | 平台验券记录 | 平台验券.JSON |
|
||
| `ods_payment_record` | 支付记录，1 行 = 1 个支付渠道流水 | 支付记录.JSON |
|
||
| `ods_refund_record` | 退款记录，1 行 = 1 个退款流水 | 退款记录.JSON |
|
||
| `ods_order_receipt_detail` | 结账小票详情，1 行 = 1 张结算小票 | 小票详情 JSON（含 siteProfile 快照） |
|
||
|
||
> 说明：未来如果上游新增 JSON 类型（如库存明细、套餐定义等），按上述命名规范扩展对应 ODS 表即可。
|
||
|
||
#### 2.1.2 DWD 层表（维度 + 明细事实）
|
||
|
||
DWD 层在 ODS 基础上做清洗、归一化命名与业务规则整理，形成本期的核心事实与维度。
|
||
|
||
**维度表（Dim）：**
|
||
|
||
| 表名 | 描述 | 说明 |
|
||
|------------------------|------------------------------|-------------------------------|
|
||
| `dim_tenant` | 租户/品牌维度 | 品牌级主数据（如朗朗桌球） |
|
||
| `dim_site` | 门店维度 | 统一承载门店主数据（新增） |
|
||
| `dim_member` | 会员维度 | 由会员档案 + 卡信息归并而来 |
|
||
| `dim_member_card_type` | 会员卡类型维度 | 卡种/等级等枚举维 |
|
||
| `dim_product` | 商品维度 | 商品主数据（品牌级） |
|
||
| `dim_product_category` | 商品分类维度 | 从商品分类配置 JSON 构建 |
|
||
| `dim_table` | 台桌维度 | 台号、区域、台型等 |
|
||
| `dim_assistant` | 助教维度 | 助教/陪练账号主数据 |
|
||
| `dim_assistant_team` | 助教团队维度（如使用） | 班组/团队信息 |
|
||
| `dim_pay_method` | 支付方式维度 | 支付渠道枚举（现金/微信等） |
|
||
| `dim_order_assist_type`| 助教服务类型维度 | 基础课/附加课等类型 |
|
||
| `dim_coupon_platform` | 券平台维度（美团/点评等） | 团购券来源渠道 |
|
||
| `dim_date` 等 | 时间维度 | 自然日粒度，不做营业日切分 |
|
||
|
||
**事实表（Fact）：**
|
||
|
||
| 表名 | 粒度描述 |
|
||
|----------------------------|-------------------------------------------|
|
||
| `fact_sale_item` | 商品销售明细：1 行 = 1 个商品销售行 |
|
||
| `fact_table_usage` | 台费使用明细：1 行 = 1 笔台桌使用记录 |
|
||
| `fact_assistant_service` | 助教服务明细：1 行 = 1 条助教服务记录 |
|
||
| `fact_coupon_usage` | 团购券使用明细：1 行 = 1 次券核销 |
|
||
| `fact_payment` | 支付明细：1 行 = 1 笔支付流水 |
|
||
| `fact_refund` | 退款明细：1 行 = 1 笔退款流水 |
|
||
| `fact_balance_change` | 余额变动明细：1 行 = 1 次余额增减事件 |
|
||
|
||
> 说明：本期 DWD 不再保留“siteProfile”等门店快照字段，而是统一沉淀到 `dim_site`，所有事实表通过 `site_id` 关联门店。
|
||
|
||
#### 2.1.3 DWS 层表（汇总宽表）
|
||
|
||
DWS 层面向经营分析场景输出汇总结果，本期重点如下：
|
||
|
||
| 表名 | 粒度描述 |
|
||
|-------------------------|----------------------------------------------------|
|
||
| `dws_order_summary` | 订单汇总宽表：1 行 = 1 次结账 / 单笔消费 |
|
||
| （后续可扩展） | （按日/按助教/按门店的各类经营日报、绩效宽表等） |
|
||
|
||
> 说明：
|
||
> - `dws_order_summary` 是本期最核心的 DWS 表，所有“订单有效流水、记账流水、台费/商品/助教收入拆解”等指标均在此表定义。
|
||
> - 本期暂不输出按日/按助教等 DWS 宽表，如后续有需求，可基于 DWD 和 `dws_order_summary` 再行建设。
|
||
2. ODS层表定义（源数据表）
|
||
ODS层直接存储业务系统各数据源的明细记录，结构与源 JSON 基本对应。一条ODS记录通常对应源系统中的一条业务记录（或业务对象快照）。字段命名以来源字段英文为准，数据类型贴近源数据类型。每个字段均标注来源JSON路径，便于ETL开发准确取数。以下按照业务主题逐一列出ODS表设计：
|
||
ODS_MEMBER_PROFILE（会员档案表）
|
||
描述： 存储会员账户基本信息，每条记录对应一个租户内的会员账户（通常也对应一张会员卡）。该表是会员维度的基础数据，提供会员的身份标识及联系信息等。
|
||
字段定义：
|
||
• id (BIGINT) - 会员账户ID，唯一标识会员（租户范围内）。来源JSON路径: $.id
|
||
• tenant_id (BIGINT) - 租户ID（品牌ID）。当前数据中固定为单一值，但设计保留此字段以支持多品牌。来源: $.tenant_id
|
||
• member_name (STRING) - 会员姓名。如会员未实名，可能为空或为默认值。来源: $.name（若JSON无明确name字段，则使用真实姓名字段）
|
||
• nickname (STRING) - 会员昵称。若无单独昵称则可能与姓名相同或留空。来源: $.nickname
|
||
• mobile (STRING) - 会员手机号。11位手机号码字符串，唯一标识会员联系方式。【重复的手机号应为数据异常，同一号码重复出现需在ETL时考虑合并或清洗】来源: $.mobile
|
||
• gender (STRING) - 会员性别。来源: $.sex（例如 "M"/"F" 或其他标记，如JSON为数字需转换为描述，可在DWD阶段关联性别维表）
|
||
• birthday (DATE) - 会员生日。来源: $.birthday（如源为字符串需转换为日期）
|
||
• register_time (DATETIME) - 会员入会时间/开户时间。来源: $.register_time
|
||
• member_type_id (BIGINT) - 会员卡类型ID。表示会员所持卡的类型/级别，与卡种定义相关联。来源: $.cardTypeId（如JSON中以具体字段提供）
|
||
• member_type_name (STRING) - 会员卡类型名称。如“黄金卡”、“白银卡”等，描述会员级别或卡种权益。来源: $.cardTypeName（如果JSON中提供；如未提供则需在DWD从卡列表或配置推导）
|
||
• status (STRING) - 会员账户状态。例如“正常”、“停用”等。来源: $.status 或 $.state（按JSON实际字段）
|
||
• balance (DECIMAL(10,2)) - 当前储值余额。单位：元。来源: $.balance（如果会员档案JSON提供当前余额）
|
||
• points (DECIMAL(10,2)) - 当前积分或点数。单位：分。来源: $.point 或 $.points（如有）
|
||
• last_visit_time (DATETIME) - 最近一次到店/消费时间。来源: $.lastVisitTime（如有）
|
||
• wechat_id (STRING) - 微信ID或绑定的微信识别码。来源: $.wechatId（如有）
|
||
• alipay_id (STRING) - 支付宝ID或绑定账号。来源: $.alipayId（如有）
|
||
• member_card_no (STRING) - 会员卡号。用于实体卡场景下的卡片编号。来源: $.cardNo（如有）
|
||
• remarks (STRING) - 会员备注。来源: $.remark（如有备注字段）
|
||
(说明: 原始“会员档案.json”中出现了一例重复记录（相同id的记录重复），ETL需过滤此类重复，以id唯一为准。上述字段可能分散于会员档案和储值卡列表两个源，在ODS阶段暂以会员档案JSON为准，如某些字段缺失可在DWD阶段结合“储值卡列表”补充。)
|
||
ODS_MEMBER_CARD（会员卡列表表）
|
||
描述： 存储会员卡开卡记录及卡状态信息。每条记录对应一张已开通的会员卡，包含卡的定义属性和当前状态。即便当前系统仅有储值卡，也统一按卡片视角记录。
|
||
字段定义：
|
||
• card_id (BIGINT) - 会员卡实例ID，唯一标识一张具体会员卡。来源JSON路径: $.id（储值卡列表JSON中的卡片主键）
|
||
• member_id (BIGINT) - 持卡会员ID。对应ODS_MEMBER_PROFILE表的id，用于关联会员基本信息。来源: $.member_id 或 $.memberCardId（根据实际字段，如出现member_card_id应对应会员档案id）
|
||
• card_type_id (BIGINT) - 卡种ID。标识此卡对应的卡种定义，例如储值卡、次卡等类别。来源: $.cardTypeId
|
||
• card_type_name (STRING) - 卡种名称。来源: $.cardTypeName（例如“储值卡”“次卡”等）
|
||
• card_balance (DECIMAL(10,2)) - 卡当前余额。单位：元。来源: $.balance
|
||
• discount_rate (DECIMAL(5,2)) - 折扣率。如持此卡消费可享受的折扣，90%则存0.90。来源: $.discount（字段含义需确认，可能JSON中提供折扣规则）
|
||
• valid_start_date (DATE) - 卡有效期起始日期。来源: $.validStart 或类似字段
|
||
• valid_end_date (DATE) - 卡有效期截止日期。来源: $.validEnd
|
||
• last_consume_time (DATETIME) - 最近一次使用该卡的消费时间。来源: $.lastConsumeTime
|
||
• status (STRING) - 卡状态。如“正常”、“已过期”、“已挂失”等。来源: $.status（若为数字代码将在DWD关联维表）
|
||
• activate_time (DATETIME) - 开卡时间/激活时间。来源: $.activateTime
|
||
• deactivate_time (DATETIME) - 注销时间（如适用）。来源: $.cancelTime（如有）
|
||
• issuer_name (STRING) - 开卡操作员姓名。来源: $.issuerName（如有）
|
||
• issuer_id (BIGINT) - 开卡操作员ID。来源: $.issuerId
|
||
(说明: 如果每个会员只有一张卡，ODS_MEMBER_PROFILE与ODS_MEMBER_CARD记录将一一对应；但模型设计仍区分两表，以支持一人多卡场景。例如未来可能推出不同类型的会员卡、或旧卡更换新卡等。ETL从“储值卡列表.json”提取上述字段。)
|
||
ODS_BALANCE_CHANGE（余额变更记录表）
|
||
描述: 存储会员账户余额每次变动的流水记录。包括会员充值、消费扣款、后台调整等导致储值余额增减的事件。
|
||
字段定义：
|
||
• id (BIGINT) - 余额变更记录ID，唯一标识一次余额变动事件。来源JSON路径: $.id
|
||
• member_id (BIGINT) - 会员账户ID。本次余额变动关联的会员，引用ODS_MEMBER_PROFILE.id。来源: $.memberId 或 $.member_card_id（根据JSON字段，如出现member_card_id则对应会员档案id）
|
||
• change_amount (DECIMAL(10,2)) - 变动金额。正数表示账户充值/增加，负数表示消费扣减。单位：元。来源: $.change_amount（若JSON将充值与扣款统一正负表示）
|
||
• balance_before (DECIMAL(10,2)) - 变动前余额。来源: $.before_balance（如有）
|
||
• balance_after (DECIMAL(10,2)) - 变动后余额。来源: $.after_balance（如有）
|
||
• change_type (INT) - 变动类型代码。用于区分余额变动原因/来源，例如1=充值、2=消费、3=调整等。【在DWD将关联维表获取类型描述】来源: $.from_type 或 $.type
|
||
• relate_id (BIGINT) - 相关业务单据ID。用于关联产生此次余额变动的业务记录，例如充值记录ID、订单结算ID、券核销ID等。若为0或空表示无对应单（如纯后台调整）。来源: $.relate_id
|
||
• pay_method (INT) - 支付方式代码。仅在充值或消费通过某支付渠道时有意义，表示此次变动涉及的付款渠道（如现金、微信等）。来源: $.pay_type（如果余额变动记录中包含支付方式信息）
|
||
• remark (STRING) - 备注信息。对此次余额变更的说明文字。来源: $.remark（如有）
|
||
• operator_id (BIGINT) - 操作员ID。执行此次余额变动操作的员工账号。来源: $.operatorId
|
||
• operator_name (STRING) - 操作员姓名。来源: $.operatorName
|
||
• change_time (DATETIME) - 变动时间。记录余额实际变更发生的时间。来源: $.create_time 或 $.changeTime
|
||
• tenant_id (BIGINT) - 租户ID（品牌ID），来源: $.tenant_id（通常固定值）
|
||
• site_id (BIGINT) - 门店ID，指明发生此变动的门店。来源: $.site_id（通常固定值，但预留多门店）
|
||
• is_deleted (TINYINT) - 记录是否被删除标志。0=正常，1=已删除（可能用于软删）。来源: $.is_delete 或 $.is_deleted（如有）
|
||
(说明: 通过 change_type 和 relate_id，可以在DWD层进一步将余额变动与具体业务事实（充值、消费结算等）关联起来。例如 change_type=充值时，relate_id对应 ODS_RECHARGE.id；change_type=消费扣款时，relate_id对应 ODS_ORDER_SETTLEMENT.id 等。)
|
||
ODS_RECHARGE_RECORD（充值记录表）
|
||
描述: 存储会员储值卡充值的记录。每条记录表示一笔会员充值（增值）交易，通常对应会员支付一定金额并增加账户余额。
|
||
字段定义：
|
||
• id (BIGINT) - 充值记录ID，唯一标识一笔充值交易。来源JSON路径: $.id
|
||
• member_id (BIGINT) - 会员账户ID，指明充值到账的会员。来源: $.memberId 或 $.member_card_id
|
||
• recharge_amount (DECIMAL(10,2)) - 充值金额。会员实际支付的金额。单位：元。来源: $.amount
|
||
• bonus_amount (DECIMAL(10,2)) - 赠送金额。若有充值优惠活动赠送的额外金额。来源: $.gift_amount 或 $.bonus（如有）
|
||
• total_amount (DECIMAL(10,2)) - 充值总额。即充值金额 + 赠送金额，账户实际增加的余额。单位：元。来源: 无（可ETL计算得到）
|
||
• pay_method (INT) - 支付方式代码。会员通过何种渠道支付了此次充值金额，如1=现金、2=微信等。来源: $.pay_type（如JSON提供）
|
||
• pay_status (INT) - 支付状态代码。如0=未支付，1=已支付，2=支付完成/成功。来源: $.pay_status（样本中充值一般直接成功）
|
||
• transaction_id (STRING) - 第三方支付流水号。如通过微信/支付宝充值对应的支付交易号。来源: $.transactionId（如有）
|
||
• recharge_time (DATETIME) - 充值时间。来源: $.create_time 或 $.rechargeTime
|
||
• operator_id (BIGINT) - 操作员ID。执行或登记此次充值的工作人员账号。来源: $.operatorId
|
||
• operator_name (STRING) - 操作员姓名。来源: $.operatorName
|
||
• tenant_id (BIGINT) - 租户ID。来源: $.tenant_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
(说明: 充值成功后会员账户余额增加，对应的余额变更记录也会有一条与之关联。通过 pay_method 可以分析会员喜欢的充值渠道分布。)
|
||
ODS_PRODUCT（商品档案表）
|
||
描述: 存储品牌级商品档案信息。每条记录唯一标识一个由总部/品牌定义的商品（商品基本信息），不区分具体门店。商品可以是酒水、小吃等卖品，也可能包括服务类项目（如按次收费的服务）。
|
||
字段定义：
|
||
• goods_id (BIGINT) - 商品ID，商品档案主键，唯一标识一个商品定义。来源JSON路径: $.id
|
||
• tenant_id (BIGINT) - 租户ID（品牌ID）。同一品牌的商品在各门店共享此tenant_id。来源: $.tenant_id
|
||
• goods_name (STRING) - 商品名称。来源: $.name 或 $.goodsName
|
||
• goods_code (STRING) - 商品编码。用于内部管理的编码，如条码或自定义码。来源: $.code（如有）
|
||
• goods_type_id (BIGINT) - 商品类别ID。引用商品分类维度，例如酒水、小吃、服务等类别。来源: $.category_id（如“库存变化记录2”中定义的分类ID）
|
||
• goods_type_name (STRING) - 商品类别名称。来源: $.category_name（如有提供）
|
||
• unit (STRING) - 计量单位。如“瓶”、“份”、“小时”等。来源: $.unit
|
||
• price (DECIMAL(10,2)) - 标准售价。品牌建议零售价。来源: $.price
|
||
• cost_price (DECIMAL(10,2)) - 成本价。来源: $.cost_price（如有）
|
||
• is_service (TINYINT) - 是否服务类项目标识。1表示服务（如计时收费项目），0表示商品。来源: $.is_service（如商品档案区分了实物与服务）
|
||
• status (STRING) - 商品状态。如“上架”、“下架”。来源: $.status（若为枚举码将在维表映射）
|
||
• created_time (DATETIME) - 商品档案创建时间。来源: $.create_time
|
||
• updated_time (DATETIME) - 最近修改时间。来源: $.update_time
|
||
(说明: 部分字段如商品分类可能需要与另一个JSON（库存变化记录2.json）的商品分类树对应，ODS_PRODUCT中可直接存分类ID，分类名称可在维度表中维护以避免重复。商品档案主要用于提供商品的标准信息。)
|
||
ODS_STORE_PRODUCT（门店商品档案表）
|
||
描述: 存储门店级商品配置，每条记录表示某门店中的一种商品及其设定（例如售价、上下架状态等）。多个门店可各自有不同的商品列表或配置。本次数据由于仅一店，ODS_STORE_PRODUCT记录即为该店商品明细。
|
||
字段定义：
|
||
• site_goods_id (BIGINT) - 门店商品ID，唯一标识某门店的一条商品记录。来源JSON路径: $.id（门店商品档案JSON中的主键）
|
||
• goods_id (BIGINT) - 商品ID（品牌商品ID）。关联ODS_PRODUCT.goods_id，表示此门店商品所属的商品档案定义。来源: $.tenant_goods_id（字段含义：租户级商品ID）
|
||
• tenant_id (BIGINT) - 租户ID。来源: $.tenant_id（应与ODS_PRODUCT保持一致）
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
• goods_name (STRING) - 商品名称。在门店中的展示名称（通常与品牌商品名一致，可有细微差异）。来源: $.goodsName
|
||
• category_id (BIGINT) - 商品分类ID。来源: $.goodsCategoryId
|
||
• category_name (STRING) - 商品分类名称。来源: $.goodsCategoryName（如有）
|
||
• selling_price (DECIMAL(10,2)) - 门店售卖价。此店实际销售此商品的单价。来源: $.sellingPrice
|
||
• member_price (DECIMAL(10,2)) - 会员价。如对会员有优惠价则记录，否则可能与售卖价相同。来源: $.memberPrice（如有字段）
|
||
• stock_quantity (DECIMAL(10,2)) - 当前库存数量。来源: $.stock（如有库存字段）
|
||
• min_stock_alert (DECIMAL) - 库存警戒值。低于此数可能需要补货。来源: $.minStock（如有）
|
||
• unit (STRING) - 计量单位。来源: $.unit（如未提供则继承ODS_PRODUCT.unit）
|
||
• is_weighable (TINYINT) - 是否称重商品。1是称重商品（如水果按斤卖），0非称重。来源: $.isWeigh（如有）
|
||
• status (STRING) - 上架状态。例：“on”上架、“off”下架。来源: $.status
|
||
• created_time (DATETIME) - 记录创建时间。来源: $.create_time
|
||
• updated_time (DATETIME) - 记录最近修改时间。来源: $.update_time
|
||
(说明: 门店商品档案在当前数据中 tenant_id、site_id 固定各一值，但在模型中保留，以支持多店场景下同一商品在不同店有不同site_goods_id。库存相关字段如stock_quantity可用于库存分析，但销售主题中可能不重点使用。若数据不足，此类字段可以保留但在ETL时为空。)
|
||
ODS_STORE_SALE_ITEM（门店商品销售记录表）
|
||
描述: 存储商品销售明细流水，每条记录代表订单中售出的某种商品行项目。也称为销售行项目表。例如一笔订单买了3瓶饮料，则有3条对应记录（或一条记录含数量3）。这是销售事实的细粒度数据。
|
||
字段定义：
|
||
• id (BIGINT) - 销售流水ID，唯一标识一条销售明细记录。来源JSON路径: $.id
|
||
• order_trade_no (BIGINT) - 订单交易号。表示此销售所属的订单号，用于将多个明细归属同一订单。【不同模块间订单交易号相同，用于串联台费、商品、助教、套餐等消费明细】来源: $.order_trade_no
|
||
• order_settle_id (BIGINT) - 订单结算ID。对应结账单号的内部主键。如果订单已结算，可用于关联结账记录或小票详情。来源: $.order_settle_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
• site_goods_id (BIGINT) - 门店商品ID。标识卖出的商品，关联ODS_STORE_PRODUCT.site_goods_id获取商品信息。来源: $.site_goods_id
|
||
• goods_id (BIGINT) - 商品ID（品牌商品ID）。方便直接关联商品维度。来源: $.tenant_goods_id（通常与site_goods_id对应的品牌商品ID）
|
||
• goods_name (STRING) - 商品名称。销售记录中的冗余商品名。来源: $.goodsName
|
||
• quantity (DECIMAL(10,2)) - 销售数量。卖出商品的数量（或重量）。来源: $.quantity
|
||
• unit_price (DECIMAL(10,2)) - 单价。商品原本单价，可能为标准售价。来源: $.price 或 $.unit_price
|
||
• discount_amount (DECIMAL(10,2)) - 折扣金额。该明细获得的总折扣减免额（会员折扣或手工优惠）。正值表示优惠金额，0表示无折扣。来源: $.discount_money（JSON中如有）
|
||
• actual_amount (DECIMAL(10,2)) - 实收金额。即此商品行实际收取金额 = 单价*数量 - 折扣金额。来源: 无（ETL计算，或JSON中已有如$.total_fee）
|
||
• is_gift (TINYINT) - 是否赠送品标识。1表示该商品作为赠品未计价，0表示正常销售。来源: $.is_gift（如有）
|
||
• operator_id (BIGINT) - 操作员ID。登记/录入此销售的员工。来源: $.operator_id
|
||
• operator_name (STRING) - 操作员姓名。来源: $.operator_name
|
||
• sales_time (DATETIME) - 销售时间。一般取订单创建时间，对于商品明细等同于订单开单时间。来源: $.create_time
|
||
• salesman_user_id (BIGINT) - 推销员用户ID。指如有业务员提成，此字段记录关联的销售人员账号ID。来源: $.salesman_user_id（如有）
|
||
• salesman_name (STRING) - 推销员姓名。来源: $.salesman_name（如有）
|
||
• is_refunded (TINYINT) - 是否已退款标志。1表示该销售后续有退款撤销，0表示正常。来源: $.is_refund 或根据退款记录在DWD衍生。
|
||
• remark (STRING) - 备注。对该销售明细的额外说明。来源: $.remark（如有）
|
||
(说明: 商品销售记录在订单级汇总时会汇入订单总额。若有退款，可能需要在DWD层与退款事实关联以标识已退款项。discount_amount字段综合了会员折扣和手工折让，若需分别分析可在DWD阶段依据规则拆分。当前ODS数据中 salesm相关字段样本为0或空，如无推销员则留空即可。)
|
||
ODS_TABLE_INFO（台桌列表表）
|
||
描述: 存储台桌信息维度，每条记录代表门店中的一个台桌/包厢。比如台球桌、KTV包房、麻将房等。此为典型维表，为各类流水（台费、助教等）提供台号、区域等静态属性。
|
||
字段定义：
|
||
• site_table_id (BIGINT) - 台桌ID，主键，唯一标识一个台桌。来源JSON路径: $.id
|
||
• tenant_id (BIGINT) - 租户ID（品牌ID）。来源: $.tenant_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
• table_name (STRING) - 台桌名称/编号。供业务展示的名称，如“A1”、“B3”、“VIP1”、“斯1”（斯诺克1号桌）等。来源: $.tableName 或合成自区域+编号
|
||
• table_no (STRING) - 台桌编号（纯数字部分）。如果有单独编号字段则记录，没有则可与table_name相同。来源: $.tableNo（如有）
|
||
• area_id (BIGINT) - 台桌区域ID。用于区分台桌所属的区域/厅，例如A区、VIP区等。来源: $.tenant_site_region_id 或 $.areaId（如有）
|
||
• area_name (STRING) - 台桌区域名称。如“A区”“VIP厅”。来源: $.area_name 或通过区域ID关联获得。
|
||
• table_type (STRING) - 台桌类型。标识此台用于何种项目，如“美式台球”、“斯诺克”、“KTV包房”、“麻将房”等。【不写死枚举，DWD设计维表】来源: $.type 或从名称/配置推断（例如名称中“斯”开头表示斯诺克）
|
||
• is_vip (TINYINT) - VIP专属标志。1表示该台为VIP专用，0表示普通。来源: 由名称或配置（如名称包含VIP）确定（若源数据有字段更好）
|
||
• status (STRING) - 台桌状态。如“启用”、“停用”。来源: $.status（如有枚举，在维度表映射）
|
||
• rate (DECIMAL(10,2)) - 台费费率。若每台桌可能有不同计费单价（每小时价格），则记录该基础费率。来源: $.rate（如有）
|
||
• description (STRING) - 备注描述。对台桌的额外说明，如包厢大小、球台规格等。来源: $.description（如有）
|
||
(说明: 台桌列表通常相对静态，ODS层保存现有台桌配置。表中示例值如“董事办”、“666”表明部分台号可使用自定义命名。area和type等若未在JSON中明确提供，DWD层可通过维护维表或规则补全。)
|
||
ODS_TABLE_USE_LOG（台费流水表）
|
||
描述: 记录台桌使用（计费）流水，每条记录对应一笔台桌使用明细（通常与一个订单绑定）。这相当于客人使用一张台从开始到结束所产生的台费账单。包含计费开始结束时间、时长、应收费用以及折扣减免等信息。
|
||
字段定义：
|
||
• id (BIGINT) - 台费流水ID，唯一标识一条台桌使用记录。来源JSON路径: $.id
|
||
• order_trade_no (BIGINT) - 订单交易号。此台费所属订单的编号。来源: $.order_trade_no
|
||
• order_settle_id (BIGINT) - 订单结算ID。订单结账单主键，用于关联结账/小票详情。来源: $.order_settle_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
• site_table_id (BIGINT) - 台桌ID。关联ODS_TABLE_INFO.site_table_id，标识哪张台产生此费用。来源: $.site_table_id
|
||
• table_name (STRING) - 台桌名称。冗余存储便于直接查看。来源: $.tableName
|
||
• start_use_time (DATETIME) - 开台时间。顾客开始使用该台桌的时间。来源: $.start_use_time
|
||
• last_use_time (DATETIME) - 结束时间。顾客结束使用/结账时间。来源: $.last_use_time
|
||
• use_duration (INT) - 使用时长，单位秒或分钟。可由开始结束时间计算得到，或源数据直接提供。来源: 计算(last_use_time - start_use_time)，如JSON中有 real_table_use_seconds 则直接用。
|
||
• billing_unit_price (DECIMAL(10,2)) - 计费单价。使用时的收费标准（每小时/每半小时金额）。来源: $.ledger_unit_price（ledger单价）
|
||
• billing_count (DECIMAL(10,2)) - 计费数量。按计费单位累计的数量。例如按小时计费的总小时数。来源: $.ledger_count
|
||
• ledger_amount (DECIMAL(10,2)) - 原始台费金额。根据时长和单价计算的未折扣费用。来源: $.ledger_amount
|
||
• member_discount_amount (DECIMAL(10,2)) - 会员折扣金额。因会员卡折扣减免的金额。来源: $.member_discount_amount
|
||
• coupon_discount_amount (DECIMAL(10,2)) - 券抵扣金额。使用团购券等促销抵扣的金额。来源: $.coupon_promotion_amount
|
||
• manual_adjust_amount (DECIMAL(10,2)) - 手工调整金额。通过台费打折流水记录的人工减免金额（正值表示减免了多少钱）。来源: $.adjust_amount
|
||
• service_fee (DECIMAL(10,2)) - 附加服务费。若有额外服务费（例如夜场服务费）计入台费。来源: $.mgmt_fee（字段意义推测，样本中如有非0值则表示附加费）
|
||
• final_table_fee (DECIMAL(10,2)) - 台费实收金额。即顾客最终为该台支付的费用 = ledger_amount - 所有折扣减免 + 附加费。来源: $.real_table_charge_money（如JSON提供；否则ETL计算）
|
||
• member_id (BIGINT) - 会员ID。若该次开台绑定了会员，则记录会员账户ID，否则为空。来源: $.member_id
|
||
• operator_id (BIGINT) - 操作员ID。办理开台/结账的员工账号。来源: $.operator_id
|
||
• operator_name (STRING) - 操作员姓名。来源: $.operator_name
|
||
• salesman_user_id (BIGINT) - 销售员ID。比如球台服务过程中的跟单员/营销员。来源: $.salesman_user_id
|
||
• salesman_name (STRING) - 销售员姓名。来源: $.salesman_name
|
||
• is_single_order (TINYINT) - 是否单独订单标志。1表示此台仅该订单使用，0表示可能有订单拆分（极少情况）。来源: $.is_single_order
|
||
• is_deleted (TINYINT) - 记录删除标志。1表示该记录被作废（例如订单取消），0正常。来源: $.is_delete
|
||
(说明: 台费流水表详细记录每笔台桌使用账目，包含各种折扣拆分，ETL时应尽量保留这些字段。在DWD汇总订单时，final_table_fee汇总进入订单总金额；各类折扣额也可汇总统计。manual_adjust_amount字段对应台费打折记录中的操作，如果为0表示未做人工减免。)
|
||
ODS_TABLE_FEE_ADJUST（台费打折记录表）
|
||
描述: 记录针对台费账单的人工调整（打折或减免费用）行为。每条记录表示一次对某订单台费的手工减免操作，并非台的使用记录本身，而是附加的一条调整流水。通常用于记录收银员给予顾客的台费折扣。
|
||
字段定义：
|
||
• id (BIGINT) - 台费调整记录ID，唯一标识一次台费打折操作。来源JSON路径: $.id
|
||
• order_trade_no (BIGINT) - 订单交易号。本调整所属的订单。来源: $.order_trade_no
|
||
• order_settle_id (BIGINT) - 订单结算ID。本调整所属订单的结算单ID。来源: $.order_settle_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id（可能通过siteProfile获取）
|
||
• site_table_id (BIGINT) - 台桌ID。指明此次调整针对哪张台桌的费用。来源: $.site_table_id（如JSON提供）
|
||
• table_name (STRING) - 台桌名称。来源: $.tableName
|
||
• adjust_amount (DECIMAL(10,2)) - 减免金额。正值表示减免了多少钱（即台费降低额）。来源: $.adjust_fee 或 $.reduce_amount（按实际字段）
|
||
• reason (STRING) - 打折原因。对本次人工调整的说明，如“会员投诉补偿”或“店长特批”。来源: $.reason（如有）
|
||
• operator_id (BIGINT) - 操作员ID。执行此打折操作的员工账号。来源: $.operator_id
|
||
• operator_name (STRING) - 操作员姓名。来源: $.operator_name
|
||
• adjust_time (DATETIME) - 调整时间。来源: $.create_time
|
||
(说明: 台费打折记录在ODS_TABLE_USE_LOG中已反映为manual_adjust_amount，但为了保留操作原因、经办人等细节，ODS仍存此表。DWD建模时，可将adjust_amount合并计入台费最终金额计算。reason字段若为有限枚举，可建立维表维护各种折扣原因。)
|
||
ODS_ASSISTANT_ACCOUNT（助教账号表）
|
||
描述: 存储门店下所有助教（教练/服务人员，包括管理账号）的基础配置和档案信息。每条记录对应一名助教账号，属于人事维度表。字段涵盖助教的个人信息、账号状态、计费策略等。
|
||
字段定义：
|
||
• id (BIGINT) - 助教账号ID，主键。唯一标识一个助教/员工账号。在助教服务流水等事实表中以site_assistant_id引用该ID关联。来源JSON路径: $.id
|
||
• user_id (BIGINT) - 系统用户ID。助教账号对应的系统登录账号ID，用于在不同模块下统一识别人员。区别于岗位级的id。来源: $.user_id
|
||
• assistant_no (STRING) - 助教工号/编号。用于业务识别的编号，可能不唯一（不同助教可能编号相同但id不同）。来源: $.assistantNo
|
||
• job_num (STRING) - 助教工作证编号。备用工号字段，当前门店可能未使用。来源: $.job_num
|
||
• serial_number (INT) - 序号。系统内部生成的排序号或迁移用的序列值。来源: $.serial_number
|
||
• real_name (STRING) - 助教真实姓名。来源: $.real_name
|
||
• nickname (STRING) - 助教昵称。前台展示用的绰号，例如“佳怡”、“周周”。来源: $.nickname
|
||
• gender (STRING) - 性别。来源: $.sex（例如“M”/“F”或“男”/“女”）
|
||
• birthday (DATE) - 出生日期。来源: $.birthday
|
||
• mobile (STRING) - 手机号。助教联系手机。来源: $.mobile
|
||
• id_card (STRING) - 身份证号。来源: $.id_card（如有）
|
||
• hire_date (DATE) - 入职日期。来源: $.hire_date（如有字段）
|
||
• resign_date (DATE) - 离职日期。如账号已废除则记录离职时间。来源: $.resign_date（如有）
|
||
• status (STRING) - 在职状态。示例：“在职”、“离职”、“停用”等。来源: $.status（若源为数字，在维表映射）
|
||
• visible (TINYINT) - 前台可见性。0表示不在前台展示，1表示可被顾客看到选择。来源: $.visible（如有）
|
||
• billing_mode (STRING) - 计费策略。助教服务计费方式，如“按小时计费”或固定场次。来源: $.billing_mode（推测字段，如无明确则可能通过关联配置决定）
|
||
• skill_name (STRING) - 服务技能名称。例如“基础课”“附加课”等，表示该助教提供服务的类别。来源: $.skillName
|
||
• skill_level (STRING) - 技能级别/等级。如“初级”“高级”。来源: $.levelName 或 $.assistant_level
|
||
• team_id (BIGINT) - 助教团队ID。将助教分组的团队/班组标识。来源: $.assistant_team_id
|
||
• team_name (STRING) - 助教团队名称。来源: $.ledger_group_name（猜测对应团队名称字段）
|
||
• service_rate (DECIMAL(10,2)) - 服务费标准。助教服务的费率或提成标准。来源: $.service_rate（如有）
|
||
• comment (STRING) - 备注。其他补充说明。来源: $.remark（如有）
|
||
• tenant_id (BIGINT) - 租户ID。来源: $.tenant_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
(说明: 助教账号作为维表，将通过id或user_id关联到助教服务流水、助教排班等事实。在当前数据中包含管理类账号，visible字段用于控制其是否提供给顾客选择服务。billing_mode/skill等决定其服务计费类别，例如区分基础课和附加课，在助教流水中也有对应枚举值。)
|
||
ODS_ASSISTANT_SERVICE_LOG（助教流水表）
|
||
描述: 记录助教服务/陪练的明细流水，每条记录代表一次助教为顾客提供服务的过程，通常对应订单中的一项助教服务消费。可能包含开始结束时间、服务费用、助教评价等。若一次订单中更换或多次聘请助教，会产生多条记录。
|
||
字段定义：
|
||
• id (BIGINT) - 助教流水ID，唯一标识一条助教服务记录。来源JSON路径: $.id
|
||
• order_trade_no (BIGINT) - 订单交易号。标识此助教服务所属的订单。来源: $.order_trade_no
|
||
• order_settle_id (BIGINT) - 订单结算ID。关联结账单，用于与小票详情等对应。【当前导出结账记录为空，此字段依然保留用于关联】来源: $.order_settle_id
|
||
• order_assistant_id (BIGINT) - 订单助教项ID。用于区分同一订单中多条助教服务项的内部ID。来源: $.order_assistant_id
|
||
• order_assistant_type (INT) - 助教服务类型编码。枚举值，示例：1=常规助教服务（基础课），2=附加助教服务（加课）。对应的含义可从系统配置获取。【模型中通过维表解析】来源: $.order_assistant_type
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
• site_table_id (BIGINT) - 台桌ID。助教服务所在的台桌，如教练在哪张球台服务。来源: $.site_table_id
|
||
• table_name (STRING) - 台桌名称。来源: $.tableName
|
||
• site_assistant_id (BIGINT) - 助教ID。提供服务的助教账号ID，关联ODS_ASSISTANT_ACCOUNT.id。来源: $.site_assistant_id
|
||
• assistant_name (STRING) - 助教姓名。冗余字段，来源: $.assistantName
|
||
• assistant_no (STRING) - 助教工号。来源: $.assistantNo
|
||
• skill_name (STRING) - 服务技能名称。例：“基础课”或“附加课”，与order_assistant_type相对应。来源: $.skillName
|
||
• assistant_level (STRING) - 助教等级/级别。如“初级”“中级”。来源: $.assistant_level 或 $.levelName
|
||
• start_time (DATETIME) - 服务开始时间。来源: $.start_use_time
|
||
• end_time (DATETIME) - 服务结束时间。来源: $.last_use_time
|
||
• service_duration (INT) - 服务时长（秒）。来源: $.income_seconds 或 $.real_use_seconds（如有）
|
||
• ledger_unit_price (DECIMAL(10,2)) - 计费单价。助教服务费率（每单位时间费用）。来源: $.ledger_unit_price
|
||
• ledger_amount (DECIMAL(10,2)) - 原始服务金额。按时长和单价计算的费用。来源: $.ledger_amount
|
||
• member_discount_amount (DECIMAL(10,2)) - 会员折扣金额。会员价优惠的金额。来源: $.member_discount_amount
|
||
• coupon_deduct_amount (DECIMAL(10,2)) - 券抵扣金额。使用促销券抵扣的金额。来源: $.coupon_deduct_money
|
||
• manual_discount_amount (DECIMAL(10,2)) - 手工减免金额。人工给予的减免费用。来源: $.manual_discount_amount
|
||
• final_service_fee (DECIMAL(10,2)) - 助教服务实收金额。= ledger_amount - 折扣 - 券抵扣 - 手工减免。来源: $.service_money（或ETL计算）
|
||
• member_id (BIGINT) - 会员ID。若顾客为会员，此服务绑定的会员账号。来源: $.tenant_member_id 或 $.member_id（根据字段，样本中可能存在system_member_id/tenant_member_id用于区别品牌会员）
|
||
• is_confirm (TINYINT) - 顾客确认标志。1表示顾客已确认服务（可能有签字确认流程），0未确认。来源: $.is_confirm
|
||
• grade_status (TINYINT) - 评分状态。标识顾客是否已对本次服务评分/评价。来源: $.grade_status
|
||
• service_grade (INT) - 服务态度评分。可能范围1-5。来源: $.service_grade（如有评分功能）
|
||
• skill_grade (INT) - 技能专业度评分。来源: $.skill_grade
|
||
• sum_grade (INT) - 综合评分。可能是平均或总分。来源: $.composite_grade
|
||
• grade_time (DATETIME) - 评价时间。来源: $.composite_grade_time
|
||
• get_grade_times (INT) - 获评次数。也许表示该助教被评价的总次数（字段意义存疑）。来源: $.get_grade_times
|
||
• is_not_responding (TINYINT) - 助教未响应标志。1表示助教未及时应答服务请求。来源: $.is_not_responding
|
||
• is_trash (TINYINT) - 作废标志。1表示该服务记录被废弃（可能由于助教更换/取消），0正常。来源: $.is_trash
|
||
• trash_reason (STRING) - 废除原因。若is_trash=1，记录取消服务的原因说明。来源: $.trash_reason
|
||
• trash_applicant_id (BIGINT) - 废除申请人ID。谁发起了取消（顾客或员工）。来源: $.trash_applicant_id
|
||
• trash_applicant_name (STRING) - 废除申请人姓名。来源: $.trash_applicant_name
|
||
• operator_id (BIGINT) - 操作员ID。登记此服务的操作员账号。来源: $.operator_id
|
||
• operator_name (STRING) - 操作员姓名。来源: $.operator_name
|
||
• salesman_user_id (BIGINT) - 销售员/推介人ID。如有业务员推荐此服务，则记录。来源: $.salesman_user_id
|
||
• salesman_name (STRING) - 销售员姓名。来源: $.salesman_name
|
||
• remark (STRING) - 备注。额外说明。来源: $.remark（如有）
|
||
(说明: 助教服务流水包含一些服务质量相关字段（评分等），这是超出纯消费计费的内容，可供人员绩效分析。is_trash=1表示该记录对应一次助教服务被取消或废除，与助教废除表的数据相关。trash_reason等提供取消原因。在DWD层可结合助教废除记录进一步分析。)
|
||
|
||
计费与废除语义说明（新增）
|
||
助教流水记录中涉及以下与计费和废除相关的时间与金额字段（字段名以 JSON / ODS 实际字段为准）：
|
||
• real_use_seconds：本次助教服务实际发生的服务时长，单位秒。
|
||
• income_seconds：本次助教服务中，可计入收入的服务时长，单位秒。
|
||
o 当服务正常完成时，通常 income_seconds = real_use_seconds。
|
||
o 当服务中途被废除时，可能只对其中一部分时间计费，此时 income_seconds <= real_use_seconds。
|
||
• ledger_amount（或类似字段）：按计划服务时长与费率计算得到的原始应收金额（不考虑废除）。
|
||
• projected_income（或 service_money 等）：本条助教服务最终计入营收的金额，已考虑会员折扣、手工减免以及废除逻辑。
|
||
• is_trash：助教流水是否被废除的标记。
|
||
o is_trash = 0 表示正常完成的服务。
|
||
o is_trash = 1 表示该条助教服务被废除（可能是未开始就废除，或者中途废除）。
|
||
废除逻辑在 ODS 层的语义约定如下：
|
||
1. 未实际发生服务的废除：
|
||
o 情况：顾客点了助教，但助教尚未真正服务（例如马上撤回）。
|
||
o 表征：real_use_seconds = 0，income_seconds = 0，projected_income = 0，is_trash = 1。
|
||
o 口径：该条记录不产生任何收入，仅作为业务流程记录存在。
|
||
2. 已部分服务后废除：
|
||
o 情况：助教服务了一段时间后被顾客或门店废除，例如服务 100 分钟，只按 60 分钟计费。
|
||
o 表征：real_use_seconds > income_seconds >= 0，is_trash = 1。
|
||
o 口径：
|
||
已计费部分：income_seconds 对应的金额计入 projected_income，视为有效营收。
|
||
未计费部分：real_use_seconds - income_seconds 对应的时间不计入营收，仅作为服务过程信息。
|
||
3. 正常完成的服务：
|
||
o 情况：助教服务按计划正常完成。
|
||
o 表征：is_trash = 0，通常 income_seconds = real_use_seconds。
|
||
o 口径：按 projected_income 全额计入营收。
|
||
后续在 DWD / DWS 层关于“助教收入”相关指标，统一以 projected_income（或相应的实收金额字段）为准，并结合 is_trash、income_seconds 与 real_use_seconds 做口径控制，避免把未计费部分误算入营业收入。
|
||
|
||
|
||
ODS_ASSISTANT_CANCEL_LOG（助教废除记录表）
|
||
描述: 记录助教服务被取消/废除的事件。每条记录对应一次助教服务未完成的情况（例如顾客要求更换助教或提前结束服务）。这些记录通常与助教服务流水的is_trash标志对应，用于详细描述废除原因和过程。
|
||
字段定义：
|
||
• id (BIGINT) - 助教废除记录ID，唯一标识一次助教服务取消事件。来源JSON路径: $.id
|
||
• order_trade_no (BIGINT) - 订单交易号。被取消服务所在订单的编号。来源: $.order_trade_no
|
||
• order_settle_id (BIGINT) - 订单结算ID。来源: $.order_settle_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.siteProfile.id（通过siteProfile对象获取）
|
||
• site_table_id (BIGINT) - 台桌ID。此次涉及的台桌。来源: $.site_table_id
|
||
• table_name (STRING) - 台桌名称。来源: $.tableName
|
||
• site_assistant_id (BIGINT) - 助教ID。被取消的助教账号。来源: $.site_assistant_id
|
||
• assistant_name (STRING) - 助教姓名。来源: $.assistantName
|
||
• assistant_no (STRING) - 助教工号。来源: $.assistantNo
|
||
• cancel_time (DATETIME) - 废除时间。记录服务被取消的时间。来源: $.create_time（推测为记录创建时间）
|
||
• cancel_reason (STRING) - 废除原因描述。如“顾客不满意”、“超时未响应”等。来源: $.reason 或组装自 $.trash_reason
|
||
• applicant_type (STRING) - 申请方类型。标识由谁发起取消，例如“customer”或“staff”。来源: 无直接字段，可能依据 $.trash_applicant_id 是否为顾客ID推断，在DWD处理。
|
||
• applicant_name (STRING) - 取消发起人姓名。来源: $.trash_applicant_name
|
||
• operator_id (BIGINT) - 操作员ID。处理取消的工作人员账号。来源: $.operator_id（如有）
|
||
• operator_name (STRING) - 操作员姓名。来源: $.operator_name（如有）
|
||
(说明: 助教废除记录与助教服务流水存在对应关系：当某助教服务被废除时，助教流水标记is_trash=1，同时产生一条废除记录说明详情。在ETL中，可利用order_trade_no和site_assistant_id将两者关联，确保不会重复计算费用（废除的服务通常不计费）。该表为业务监控服务取消情况提供数据支持。)
|
||
|
||
与助教流水的关联及废除口径（新增）
|
||
助教废除记录表与助教流水表之间的关系：
|
||
• 一条助教服务流水（助教流水表中的一条记录），在被废除时，会在助教废除表中产生一条对应记录。
|
||
• 关联键通常包括：
|
||
o order_trade_no（订单交易号）；
|
||
o site_assistant_id（助教ID）；
|
||
o 必要时还可结合台桌ID、时间窗口进行匹配。
|
||
废除记录主要承担原因说明和责任归属的作用，例如：
|
||
• trash_reason / cancel_reason：废除原因，例如“顾客更换助教”、“顾客临时离场”、“服务不满意”等。
|
||
• trash_applicant_id / trash_applicant_name：发起废除的一方（顾客、值班经理等）。
|
||
在计费口径上：
|
||
• 是否计费、计多少，以助教流水中的 projected_income、income_seconds、real_use_seconds 字段为准。
|
||
• 助教废除表本身不直接存金额，只是补充说明“为何部分原始应收未计入营业收入”。
|
||
后续在 DWD / DWS 层进行收入分析时，出现“原始应收高于实际入账”的差异，可通过关联助教废除表查看原因分布（例如：顾客原因 vs 助教原因）。
|
||
|
||
ODS_GROUP_PACKAGE（团购套餐定义表）
|
||
描述: 存储门店可用的团购套餐列表，即以团购形式售卖的优惠套餐的配置。每条记录定义一种团购套餐的规则，包括名称、面值、有效期、适用时段、限定台桌类型、上架状态等。
|
||
字段定义：
|
||
• package_id (BIGINT) - 团购套餐ID，主键。唯一标识一种套餐。来源JSON路径: $.id
|
||
• tenant_id (BIGINT) - 租户ID。来源: $.tenant_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
• package_name (STRING) - 套餐名称。例：“早场1小时”“斯诺克两小时”“KTV四小时”。来源: $.name
|
||
• coupon_value (DECIMAL(10,2)) - 套餐面值。该套餐可抵扣的金额或服务时长对应的价值（通常为原价）。来源: $.coupon_money
|
||
• selling_price (DECIMAL(10,2)) - 销售价。消费者购买此套餐所付金额（通常低于面值）。来源: $.sell_price（如有）
|
||
• valid_from (DATE) - 套餐有效期起始日期。来源: $.start_date
|
||
• valid_to (DATE) - 套餐有效期结束日期。来源: $.end_date
|
||
• valid_time_period (STRING) - 每日可用时段描述。如“每日09:00-18:00可用”。来源: $.daily_time（JSON可能提供起止时间字段，此处可存文本描述或拆分字段）
|
||
• limit_table_type (STRING) - 限定台桌类型。说明此券可用的台桌范围，例如“仅限斯诺克区”，“KTV包厢适用”。来源: $.limit_table_type（可能需要根据JSON字段组合，如限定的区域/台类型）
|
||
• limit_table_count (INT) - 每次可用台数限制。如一个订单一次只能用一张券对应一张台。来源: $.limit_table_count（如有）
|
||
• status (STRING) - 上架状态。示例：“active”在售，“inactive”下架，“expired”已过期。来源: $.status（若为布尔或代码，DWD将维表映射）
|
||
• is_expired (TINYINT) - 是否已过期标志。1表示套餐已过期（当前日期超过valid_to），0尚在有效期。来源: 依据valid_to由ETL判断得到（源数据如有直接字段则使用）
|
||
• platform (STRING) - 销售平台。指该套餐发布的平台，如“美团”、“大众点评”，如果仅门店自有则为“SELF”。来源: 如果JSON提供 platform 字段或通过dealId前缀推断，在DWD处理。
|
||
• deal_id (STRING) - 外部平台Deal编号。对应第三方平台上的套餐ID。来源: $.dealId（如有）
|
||
• created_time (DATETIME) - 套餐定义创建时间。来源: $.create_time
|
||
• updated_time (DATETIME) - 最后修改时间。来源: $.update_time
|
||
(说明: 团购套餐定义用于分析各套餐的使用和销售情况。limit_table_type等可以在DWD阶段细化为关联台桌类型维度。platform若有多个渠道，可在维表中枚举。)
|
||
ODS_GROUP_PACKAGE_LOG（团购套餐使用流水表）
|
||
描述: 记录团购套餐券被使用（核销）的流水。每条记录表示一张团购券在门店的一次使用，通常对应一个订单的一部分（例如使用券抵扣了特定台费时长）。包含订单信息、所用券的ID/名称、抵扣金额、以及操作员等信息。
|
||
字段定义：
|
||
• id (BIGINT) - 团购券使用记录ID，唯一标识一次券使用流水。来源JSON路径: $.id
|
||
• order_trade_no (BIGINT) - 订单交易号。使用该券的订单编号。来源: $.order_trade_no
|
||
• order_settle_id (BIGINT) - 订单结算ID。来源: $.order_settle_id
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id
|
||
• site_table_id (BIGINT) - 台桌ID。本券使用关联的台桌（通常券对应某张台的费用）。来源: $.site_table_id
|
||
• table_name (STRING) - 台桌名称。来源: $.tableName
|
||
• package_id (BIGINT) - 团购套餐ID。标识所使用券属于哪种套餐定义，关联ODS_GROUP_PACKAGE.package_id。来源: $.deal_id 或 $.package_id（根据字段含义，可能deal_id需匹配套餐定义）
|
||
• package_name (STRING) - 套餐名称。冗余记录券名称，来源: $.deal_name 或 $.package_name（如有）
|
||
• coupon_code (STRING) - 券码。消费者购买后收到的券编号。来源: $.coupon_code（如有）
|
||
• deduct_amount (DECIMAL(10,2)) - 券抵扣金额。使用该券抵扣的金额价值。来源: $.deduct_money 或 $.coupon_deduct_money
|
||
• remaining_amount (DECIMAL(10,2)) - 券剩余金额/价值。若券可部分使用，多次核销则记录剩余可用价值。来源: $.remain_money（如有）
|
||
• status (STRING) - 券使用状态。可能统一为“used”（已核销），如有异常则标记。如源数据提供，用于确认券成功使用。来源: $.status（如全部已用可不细分）
|
||
• used_time (DATETIME) - 券使用时间。来源: $.create_time
|
||
• salesman_user_id (BIGINT) - 销售员ID。引导售券或核销的员工。来源: $.salesman_user_id
|
||
• salesman_name (STRING) - 销售员姓名。来源: $.salesman_name
|
||
• operator_id (BIGINT) - 核销操作员ID。实际在系统中操作验券的员工账号。来源: $.operator_id
|
||
• operator_name (STRING) - 核销操作员姓名。来源: $.operator_name
|
||
(说明: 团购套餐使用记录可以帮助分析团购券对收入的贡献。deduct_amount在订单汇总中会体现为一种折扣/预付部分。coupon_code通常对应第三方平台发放的券码，但由于还有平台验券记录表，内部记录可能未展示具体券码。package_id/名称确保可关联到套餐定义维度。)
|
||
ODS_PLATFORM_COUPON_LOG（平台验券记录表）
|
||
描述: 记录来自第三方平台的团购券在本门店被核销的事件。每条记录对应一次第三方券码的验证使用，携带平台相关信息和门店内部关联信息。通常与团购套餐流水存在一一对应关系。
|
||
字段定义：
|
||
• id (BIGINT) - 平台验券记录ID，唯一标识一次第三方券核销事件。来源JSON路径: $.id
|
||
• platform (STRING) - 券来源平台名称。如“美团”、“大众点评”。来源: 通过$.dealId前缀或配置推断平台，在DWD映射到维表。
|
||
• deal_id (STRING) - 平台Deal ID。对应第三方平台团购项目标识。来源: $.dealId
|
||
• certificate_id (STRING) - 券码ID。第三方券的唯一编码。来源: $.certificateId
|
||
• order_id (BIGINT) - 门店内部订单ID。此券关联的店内订单ID（非交易号，可能对应siteOrderId）。来源: $.orderId 或 $.order_id
|
||
• order_trade_no (BIGINT) - 订单交易号。来源: 若JSON未直接给出，可通过内部订单ID在DWD阶段关联得到；如有字段直接提供则填入。
|
||
• site_id (BIGINT) - 门店ID。来源: $.siteId
|
||
• site_table_id (BIGINT) - 台桌ID。此券使用所对应的台桌。来源: $.site_table_id（如有）
|
||
• table_name (STRING) - 台桌名称。来源: $.tableName（如有）
|
||
• assistant_id (BIGINT) - 助教ID。如券涉及助教服务，可记录相关助教账号。来源: $.assistant_id（猜测可能没有此字段，若有则填）
|
||
• used_time (DATETIME) - 券核销时间。来源: $.create_time
|
||
• operator_id (BIGINT) - 核销操作员ID。来源: $.operator_id
|
||
• operator_name (STRING) - 核销操作员姓名。来源: $.operator_name
|
||
(说明: 平台验券记录重点在外部券信息（platform, certificate_id等）。它与内部团购套餐使用记录通过订单或券码关联，实际分析时可结合两表数据。模型中保留该表以完整记录第三方平台券的使用明细。platform字段可能不直接给出，在ETL时可依据deal_id或certificate_id编码规则判断，或通过配置表（如只有美团一种则固定为美团）。)
|
||
ODS_PAYMENT_RECORD（支付记录表）
|
||
描述: 存储订单支付流水，每条记录对应订单的一种付款渠道的收款情况。一笔订单可能拆分多种支付方式（如部分用会员卡余额，部分微信），则会有多条支付记录。该表反映收银端每笔实际收款。
|
||
字段定义：
|
||
• id (BIGINT) - 支付记录ID，唯一标识一条支付流水。来源JSON路径: $.id
|
||
• order_trade_no (BIGINT) - 订单交易号。此支付对应的订单编号。来源: $.order_trade_no
|
||
• order_settle_id (BIGINT) - 订单结算ID。来源: $.order_settle_id
|
||
• pay_amount (DECIMAL(10,2)) - 支付金额。此支付流水涉及的金额。正值表示店内收款，负值（在退款记录中出现）表示店内支出。来源: $.pay_amount
|
||
• pay_method (INT) - 支付方式代码。表示付款渠道类型，例如1=现金、2=微信、3=支付宝、4=储值卡等。【需通过维表映射具体名称】来源: $.paymentMethod 或 $.pay_type
|
||
• pay_method_name (STRING) - 支付方式名称。可以在ODS中直接存储文字，如“微信”、“现金”（如果源直接给出了），否则在维度表中维护映射。来源: $.payTypeName（如有）
|
||
• pay_status (INT) - 支付状态码。2表示完成，可能0/1表示处理中/失败等（本数据集中均为2完成）。来源: $.pay_status
|
||
• transaction_id (STRING) - 外部支付流水号。第三方支付平台交易ID。来源: $.transaction_id（如有）
|
||
• pay_time (DATETIME) - 支付时间。来源: $.pay_time 或 $.create_time
|
||
• site_id (BIGINT) - 门店ID。来源: $.siteProfile.id（支付记录JSON带有siteProfile快照）
|
||
• operator_id (BIGINT) - 收银操作员ID。来源: $.operator_id（如有）
|
||
• operator_name (STRING) - 收银操作员姓名。来源: $.operator_name
|
||
(说明: 支付记录表中的每条记录通常应对应于订单的某个支付方式实际到账金额。对账和分析各支付渠道收入时，用此表的数据。对于储值卡支付，pay_method可能对应会员卡扣款；其金额在店内不形成新资金流入，而是内部结转，但仍作为支付方式之一记录。pay_status基本都会是“完成”，额外状态可忽略或用于异常监控。)
|
||
ODS_REFUND_RECORD（退款记录表）
|
||
描述: 存储发生的退款流水，每条记录代表一笔退款操作（将钱退还给顾客的交易）。通常与支付记录对应，用于财务核对退款支出。
|
||
字段定义：
|
||
• id (BIGINT) - 退款记录ID，唯一标识一次退款交易。来源JSON路径: $.id
|
||
• order_trade_no (BIGINT) - 订单交易号。此退款所属的原订单编号。来源: $.order_trade_no
|
||
• order_settle_id (BIGINT) - 订单结算ID。来源: $.order_settle_id
|
||
• refund_amount (DECIMAL(10,2)) - 退款金额。该笔退款支付给顾客的金额，通常为负值表示流出。来源: $.pay_amount（样本中均为负数）
|
||
• pay_method (INT) - 退款渠道代码。对应原支付的渠道。来源: $.paymentMethod 或 $.pay_type（应与支付记录的code一致）
|
||
• pay_method_name (STRING) - 退款渠道名称。来源: $.payTypeName（如有）
|
||
• pay_status (INT) - 退款状态码。值为2表示退款完成（样本全为2），0/1可能表示未处理/处理中。来源: $.pay_status
|
||
• original_pay_id (BIGINT) - 原支付流水ID。指明本次退款对应哪笔支付记录。来源: $.relate_pay_id 或类似字段（如有）
|
||
• refund_time (DATETIME) - 退款时间。来源: $.create_time
|
||
• site_id (BIGINT) - 门店ID。来源: $.site_id（通过siteProfile等）
|
||
• operator_id (BIGINT) - 操作员ID。执行退款的员工账号。来源: $.operator_id
|
||
• operator_name (STRING) - 操作员姓名。来源: $.operator_name
|
||
(说明: 退款记录可用于与支付记录核对应收支平衡。在订单汇总分析时，退款通常不计入当日收入，而是冲减历史订单，因此DWS层会根据需要处理退款影响。模型中仍将退款事实表独立存储，方便查询退款总额、退款率等指标。)
|
||
ODS_ORDER_RECEIPT_DETAIL（结账小票详情表）
|
||
描述: 存储结算小票的完整快照，每条记录对应一张结账单（一次订单结算）。其中包含门店信息、订单整单金额汇总、会员使用情况、以及订单内各分项明细（台费、商品、券、支付）等结构化子对象。可视为对结账记录的扩展详细版。
|
||
字段定义：
|
||
• order_settle_id (BIGINT) - 订单结算ID，主键。唯一标识一张结算单。来源JSON路径: $.orderSettleId
|
||
• site_id (BIGINT) - 门店ID。来源: $.data.data.siteId（通过嵌套data获取）
|
||
• tenant_id (BIGINT) - 租户ID。来源: $.data.data.tenantId
|
||
• site_name (STRING) - 门店名称。来源: $.data.data.shop_name
|
||
• site_address (STRING) - 门店地址。来源: $.data.data.full_address
|
||
• site_tel (STRING) - 门店电话。来源: $.data.data.business_tel
|
||
• cashier_name (STRING) - 收银员姓名。小票上打印的收银员，如“收银员:张三”。来源: $.data.data.cashierName
|
||
• member_name (STRING) - 会员姓名快照。若结账时绑定了会员，则记录会员名称，未绑定则可能显示“匿名用户”。来源: $.data.data.memberName
|
||
• member_phone (STRING) - 会员手机号快照。来源: $.data.data.memberPhone
|
||
• member_point (DECIMAL(10,2)) - 会员积分快照。结账时会员积分余额。来源: $.data.data.memberPoint
|
||
• total_amount (DECIMAL(10,2)) - 订单总金额（原价总计）。即所有消费项未折扣前合计金额。来源: $.data.data.orderItem[*].price等汇总（如无直接字段则ETL计算）
|
||
• total_discount (DECIMAL(10,2)) - 整单折扣总额。包含会员折扣和人工优惠的总和。来源: 各明细折扣之和（如无直接字段，ETL汇总计算）
|
||
• coupon_deduct_total (DECIMAL(10,2)) - 券抵扣总额。使用团购券等抵扣的总金额。来源: $.data.data.couponItem[*].couponMoney等汇总
|
||
• prepaid_deduct (DECIMAL(10,2)) - 会员卡储值抵扣金额。本单使用会员储值卡支付的金额。来源: $.data.data.memberDeductAmount
|
||
• amount_receivable (DECIMAL(10,2)) - 应收金额。即扣除所有折扣券后顾客需支付的金额。来源: 计算得到（total_amount - total_discount - coupon_deduct_total）或 $.data.data.ledgerAmount
|
||
• amount_paid (DECIMAL(10,2)) - 实收金额。顾客实际支付的金额总计（包括储值卡支付和其他支付）。来源: $.data.data.actualPayment
|
||
• pay_method_count (INT) - 支付方式种类数。该订单使用的支付渠道数量（如用两种方式支付则为2）。来源: 支付明细count
|
||
• pay_details (STRING) - 支付详情摘要。将各支付方式及金额拼接描述，如“微信10元+会员卡20元”。来源: 来自支付明细列表拼接（ETL组装）
|
||
• refund_amount (DECIMAL(10,2)) - 退款金额。若该订单发生退款，则记录退款总额（一般负值）。来源: 关联退款记录计算
|
||
• settle_time (DATETIME) - 结账时间。订单完成结算的时间。来源: $.data.data.createTime 或 $.data.data.payTime（需确认JSON字段）
|
||
• order_items_json (JSON) - 订单商品明细JSON。嵌入原始商品列表结构，以JSON保存（包含每个商品名称、数量、价格等）。来源: $.data.data.orderItem 对象
|
||
• table_fee_json (JSON) - 台费明细JSON。嵌入原始台费使用明细。来源: $.data.data.taiFeeItem
|
||
• coupon_json (JSON) - 券使用明细JSON。嵌入原始券项列表。来源: $.data.data.couponItem
|
||
• payment_json (JSON) - 支付明细JSON。嵌入原始支付列表。来源: $.data.data.paymentItem 或 $.data.data.payList
|
||
(说明: 结账小票详情ODS表由于结构复杂，部分字段以嵌入JSON形式保存以防信息丢失，但在DWD层会拆解整合同步到相应事实表。该表主要用于核对和补充订单汇总信息。例如支付详情可以解析但此处也保留文本方便查询。total_amount、total_discount等可以ETL阶段从各子项汇总得到，以确保准确。refund_amount需结合退款表计算，因为小票详情可能不直接体现退款。此ODS表在ETL中也用于生成DWS订单汇总宽表。)
|
||
________________________________________
|
||
以上列出了主要ODS层表及字段。如有源数据新增模块，ODS层可按类似方式扩展。
|
||
3. DWD层表定义（明细层，维度表与事实表）
|
||
DWD层在ODS的基础上进行清洗加工，得到企业业务分析的维度表和事实表。维度表（DIM）整理主数据，如门店、会员、商品、助教等，事实表（FACT）保存业务事务明细，如销售、台费、助教服务、收款等。DWD层字段命名规范统一、含义清晰，并已剔除无效数据、关联到相应维度ID。所有枚举类字段在此层通过维表翻译或以维表ID替代，避免直接存储代码。以下分别列出维度表和明细事实表设计：
|
||
维度表字段来源总体遵循：
|
||
• 若维表是 ODS 同步的“主数据”（如 dim_member, dim_product, dim_table, dim_assistant），则 DWD 字段与 ODS 字段基本同名一一对应，字段级 JSON 路径以 ODS 章节为准；
|
||
• DWD 只在以下情况增加/重命名字段：统一门店/租户主键（tenant_id, site_id）、抽取分类/团队等枚举到专门维表、增加状态描述字段等。
|
||
可以在 PRD 中统一加一句约定，例如：
|
||
除本章节特别注明的“衍生/重命名字段”外，所有 DWD 维度表字段均与对应 ODS 表字段同名一一映射；字段的 JSON 来源路径以 ODS 表章节定义为准，不在 DWD 重复。
|
||
|
||
3.1 维度表定义
|
||
DIM_SITE（门店维度表）
|
||
业务定义
|
||
|
||
`dim_site` 统一承载门店主数据，是所有事实表和 DWS 宽表的门店归属维度。
|
||
|
||
- 业务层面的**核心实体是门店（site）**；
|
||
- **所有事实表、汇总表均通过 `site_id` 关联到 dim_site**；
|
||
- 租户/品牌（tenant）信息**不再单独建维表**，仅作为 `dim_site` 的属性存在（如 `tenant_name`）。
|
||
|
||
上游各类 JSON 中出现的 `site_id`、`siteProfile` 等门店信息，在 ODS 层以原始快照保留，在 DWD 层统一抽取到 `dim_site`，避免在事实层重复嵌入 `siteProfile` 结构。
|
||
|
||
字段设计与 JSON 映射
|
||
|
||
| 字段名 | 类型 | 说明 | 典型 JSON 来源示例 |
|
||
|----------------|--------------------|------------------------------------------------------------|---------------------------------------------------------|
|
||
| `site_id` | BIGINT | 门店 ID，主键 | 小票详情：`$.data.data.siteId`；<br>其它含 siteProfile 的 JSON 中的 `siteProfile.id` |
|
||
| `tenant_name` | VARCHAR(200) | 品牌/租户名称，仅作为描述属性，不作为事实层关联键 | 可由配置或首个门店快照人工指定，如“朗朗桌球”等 |
|
||
| `site_code` | VARCHAR(50) | 门店编码（如上游有单独编码，可为空） | 小票详情：`$.data.data.shop_code` 或 siteProfile 中同义字段 |
|
||
| `site_name` | VARCHAR(200) | 门店名称 | 小票详情：`$.data.data.shop_name`；siteProfile.name |
|
||
| `full_address` | VARCHAR(500) | 门店完整地址 | 小票详情：`$.data.data.full_address` |
|
||
| `city_name` | VARCHAR(100) | 城市名称（可由地址或区域 ID 解析，解析不到则为空） | 由 `full_address` 或 `tenant_site_region_id` 解析 |
|
||
| `district_name`| VARCHAR(100) | 区/县名称（可选） | 同上 |
|
||
| `longitude` | DECIMAL(10,6) | 经度 | siteProfile：`$.longitude`（如有） |
|
||
| `latitude` | DECIMAL(10,6) | 纬度 | siteProfile：`$.latitude`（如有） |
|
||
| `business_tel` | VARCHAR(50) | 门店电话 | 小票详情：`$.data.data.business_tel` |
|
||
| `open_date` | DATE | 开店日期（如无则为空，后续可人工维护） | 无直接字段时由业务侧补录 |
|
||
| `status` | VARCHAR(50) | 门店状态：营业中 / 停业 / 筹备等 | 如上游有门店状态配置则映射；当前可默认“营业中” |
|
||
| `org_id` | BIGINT | 组织/区域 ID（如有，如租户侧的区域树） | siteProfile：`$.tenant_site_region_id` 或 `$.org_id` |
|
||
| `remark` | VARCHAR(500) | 备注 | 数仓侧维护 |
|
||
| `created_at` | TIMESTAMP | 记录创建时间（数仓） | 数仓 ETL 生成 |
|
||
| `updated_at` | TIMESTAMP | 记录最后更新时间（数仓） | 数仓 ETL 生成 |
|
||
|
||
> 说明：
|
||
> - 上游 JSON 中的 `tenant_id` 在 DWD 层**不再用于建模关联**，如有需要可在 DIM_SITE 增加一个 `tenant_code` 字段作为纯描述/对照用；
|
||
> - 任何品牌/租户维度分析，统一通过：`site_id -> dim_site.tenant_name` 实现。
|
||
|
||
#### 建表 SQL 草案（PostgreSQL）
|
||
|
||
```sql
|
||
CREATE TABLE dim_site (
|
||
site_id BIGINT PRIMARY KEY, -- 门店ID，对应各业务表中的 site_id
|
||
tenant_name VARCHAR(200) NOT NULL, -- 品牌/租户名称，仅作为属性
|
||
site_code VARCHAR(50), -- 门店编码（选填）
|
||
site_name VARCHAR(200) NOT NULL, -- 门店名称
|
||
full_address VARCHAR(500), -- 完整地址
|
||
city_name VARCHAR(100), -- 城市
|
||
district_name VARCHAR(100), -- 区/县
|
||
longitude DECIMAL(10,6), -- 经度
|
||
latitude DECIMAL(10,6), -- 纬度
|
||
business_tel VARCHAR(50), -- 门店电话
|
||
open_date DATE, -- 开店日期（可为空）
|
||
status VARCHAR(50) DEFAULT '营业中', -- 门店状态（默认营业中）
|
||
org_id BIGINT, -- 组织/区域ID（如有）
|
||
remark VARCHAR(500), -- 备注
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 记录创建时间（数仓）
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 记录更新时间（数仓）
|
||
);
|
||
|
||
COMMENT ON TABLE dim_site IS '门店维度表，统一存放门店主数据，支持多门店扩展；租户/品牌信息仅作为属性存在';
|
||
|
||
COMMENT ON COLUMN dim_site.site_id IS '门店ID，上游各JSON中的site_id统一映射到此';
|
||
COMMENT ON COLUMN dim_site.tenant_name IS '品牌/租户名称，仅作为描述属性，事实表不直接关联租户维';
|
||
COMMENT ON COLUMN dim_site.site_code IS '门店编码（如上游有单独编码，可为空）';
|
||
COMMENT ON COLUMN dim_site.site_name IS '门店名称';
|
||
COMMENT ON COLUMN dim_site.full_address IS '门店完整地址';
|
||
COMMENT ON COLUMN dim_site.city_name IS '城市名称';
|
||
COMMENT ON COLUMN dim_site.district_name IS '区/县名称';
|
||
COMMENT ON COLUMN dim_site.longitude IS '经度';
|
||
COMMENT ON COLUMN dim_site.latitude IS '纬度';
|
||
COMMENT ON COLUMN dim_site.business_tel IS '门店电话';
|
||
COMMENT ON COLUMN dim_site.open_date IS '开店日期';
|
||
COMMENT ON COLUMN dim_site.status IS '门店状态：营业中/停业/筹备等';
|
||
COMMENT ON COLUMN dim_site.org_id IS '门店所属组织/区域ID（如上游有tenant_site_region_id）';
|
||
COMMENT ON COLUMN dim_site.remark IS '备注';
|
||
COMMENT ON COLUMN dim_site.created_at IS '记录创建时间（数仓侧）';
|
||
COMMENT ON COLUMN dim_site.updated_at IS '记录最后更新时间（数仓侧）';
|
||
DIM_MEMBER（会员维度表）
|
||
描述： 会员维度，记录会员个人及账户属性。将ODS会员档案和会员卡信息合并，提供完整会员画像。
|
||
主键: member_id（会员账户ID）
|
||
字段:
|
||
• member_id (BIGINT) - 会员ID，主键。对应ODS_MEMBER_PROFILE.id。
|
||
• member_name (STRING) - 会员姓名。来自ODS_MEMBER_PROFILE.real_name，如为空则用昵称代替。
|
||
• nickname (STRING) - 会员昵称。
|
||
• gender (STRING) - 性别（“男”/“女”）。由ODS_MEMBER_PROFILE.gender代码翻译得到。
|
||
• birthday (DATE) - 出生日期。
|
||
• mobile (STRING) - 手机号码。
|
||
• member_type_id (BIGINT) - 会员卡类型ID，外键参照DIM_MEMBER_CARD_TYPE.card_type_id。
|
||
• member_type_name (STRING) - 会员卡类型名称。如“储值卡”、“银卡”、“金卡”等。
|
||
• status (STRING) - 会员状态。翻译ODS_MEMBER_PROFILE.status枚举，如“正常”、“停用”。
|
||
• register_time (DATETIME) - 注册时间/开卡时间。
|
||
• valid_from (DATE) - 会员卡有效期开始。
|
||
• valid_to (DATE) - 会员卡有效期截止。
|
||
• last_visit_time (DATETIME) - 最近消费时间。
|
||
• balance (DECIMAL(10,2)) - 当前储值余额（元）。
|
||
• total_recharge_amount (DECIMAL(10,2)) - 历史充值总额（元）。(可以在ETL初始化时计算或后续累计)
|
||
• total_consumed_amount (DECIMAL(10,2)) - 历史消费总额（储值部分，元）。
|
||
• wechat_id (STRING) - 绑定微信号ID。
|
||
• alipay_id (STRING) - 绑定支付宝号ID。
|
||
• remark (STRING) - 备注。
|
||
(来源: 主要来自ODS_MEMBER_PROFILE字段，member_type相关则结合ODS_MEMBER_CARD或配置表确定。如卡类型和有效期从ODS_MEMBER_CARD提取。余额取ODS_MEMBER_PROFILE.balance，但在分析中更可靠的是通过余额变更流水计算，这里保留一个快照值。充值/消费总额可在ETL或BI层通过事实表汇总得到，不一定在维表存储。初始可不填或通过历史计算更新。DIM_MEMBER与事实表关联时，通过member_id连接。)
|
||
DIM_MEMBER_CARD_TYPE（会员卡类型维度表）
|
||
描述： 会员卡种类维度。描述各种会员卡级别或类型的定义属性，例如名称、折扣、权益。避免在模型中硬编码会员卡枚举。
|
||
主键: card_type_id
|
||
字段:
|
||
• card_type_id (BIGINT) - 卡种ID，主键。
|
||
• card_type_name (STRING) - 卡种名称。
|
||
• discount_rate (DECIMAL(5,2)) - 默认折扣率。如0.90表示打九折。
|
||
• description (STRING) - 卡种描述/权益说明。
|
||
• remark (STRING) - 备注。
|
||
(来源: 结合业务配置。当前可从ODS_MEMBER_CARD中汇总不同card_type_id及名称，填充进此维表，例如储值卡一类，次卡另一类等，并记录其折扣规则。如无更多信息可暂存空描述，由业务补充。)
|
||
DIM_PRODUCT（商品维度表）
|
||
描述： 商品维度，描述品牌商品的属性。由ODS_PRODUCT清洗而来，并关联商品类别等信息。
|
||
主键: goods_id
|
||
字段:
|
||
• goods_id (BIGINT) - 商品ID，主键。
|
||
• goods_name (STRING) - 商品名称。
|
||
• goods_code (STRING) - 商品编码。
|
||
• category_id (BIGINT) - 商品类别ID，外键参照DIM_PRODUCT_CATEGORY.category_id。
|
||
• category_name (STRING) - 商品类别名称。
|
||
• unit (STRING) - 计量单位。
|
||
• is_service (TINYINT) - 是否服务类商品（1服务/0商品）。
|
||
• status (STRING) - 商品状态（“上架”/“下架”）。
|
||
• site_id (BIGINT) – 门店ID。
|
||
(来源: ODS_PRODUCT，去除不需要分析的字段，仅保留核心属性。category_id对应DIM_PRODUCT_CATEGORY以获得类别名称。status由代码转成文本。site_id为了链路完整也存储。)
|
||
DIM_PRODUCT_CATEGORY（商品类别维度表）
|
||
描述： 商品分类维度。存储商品的类别层次信息，如大类、子类名称，用于按品类分析销售。
|
||
主键: category_id
|
||
字段:
|
||
• category_id (BIGINT) - 分类ID，主键。
|
||
• category_name (STRING) - 分类名称。
|
||
• parent_category_id (BIGINT) - 父类别ID（如有上下级）。
|
||
• parent_category_name (STRING) - 父类别名称。
|
||
• is_active (TINYINT) - 是否在用（1是/0否）。
|
||
(来源: 库存变化记录2.json 中解析 goodsCategoryList 获取。由于库存变化记录2实际是一份商品分类配置树，其每条记录为一个分类节点。ETL需遍历提取category_id、name和parent_id构建维表。分类层级关系可用于按大类/小类汇总销售。当前数据所有分类 is_warehousing=1 表示参与库存管理，均可视为有效分类。)
|
||
DIM_TABLE（台桌维度表）
|
||
描述： 台桌维度，描述门店内各台桌/包厢的属性，如编号、区域、类型等。由ODS_TABLE_INFO加工得到。
|
||
主键: site_table_id
|
||
字段:
|
||
• site_table_id (BIGINT) - 台桌ID，主键。
|
||
• table_name (STRING) - 台桌名称。
|
||
• table_no (STRING) - 台桌编号（纯数字序号，如有的话）。
|
||
• area_name (STRING) - 区域名称。
|
||
• table_type (STRING) - 台桌类型（例如“美式桌”、“斯诺克桌”、“KTV包间”、“麻将房”）。
|
||
• is_vip (TINYINT) - 是否VIP专属台。
|
||
• status (STRING) - 台桌状态（“启用”/“停用”）。
|
||
• site_id (BIGINT) - 门店ID。
|
||
(来源: 主要字段来自ODS_TABLE_INFO，area_name和table_type可能需要结合配置或名称规则填充。例如 area_name 通过ODS_TABLE_INFO.area_id关联门店区域配置表(如果有)获取名称，没有则从table_name解析。table_type可基于table_name中关键字映射，如含“斯”字的为斯诺克桌，在维表中统一管理映射关系避免硬编码。)
|
||
DIM_ASSISTANT（助教维度表）
|
||
描述： 助教（服务人员）维度，记录员工/助教的属性。由ODS_ASSISTANT_ACCOUNT清洗得到，包括其个人信息和状态等。
|
||
主键: assistant_id
|
||
字段:
|
||
• assistant_id (BIGINT) - 助教ID（账号ID），主键。
|
||
• user_id (BIGINT) - 系统用户ID（登录账号ID）。
|
||
• assistant_no (STRING) - 助教工号。
|
||
• real_name (STRING) - 助教姓名。
|
||
• nickname (STRING) - 助教昵称。
|
||
• gender (STRING) - 性别。
|
||
• mobile (STRING) - 手机号。
|
||
• status (STRING) - 在职状态（“在职”/“离职”等）。
|
||
• visible_flag (TINYINT) - 前台可见标志（1展示/0隐藏）。
|
||
• skill_category (STRING) - 服务技能类别。比如“基础课教练”、“高级课教练”等，对应其主要服务类型。
|
||
• level (STRING) - 助教等级。
|
||
• team_name (STRING) - 所属团队/班组名称。
|
||
• billing_mode (STRING) - 计费模式（按小时/按场次等）。
|
||
• site_id (BIGINT) - 门店ID。
|
||
(来源: ODS_ASSISTANT_ACCOUNT字段整理，剔除不必要字段如serial_number等。status由码表转换为文本。skill_category可基于助教具备的技能类型确定，例如若助教有基础课技能则标为“基础课教练”；如一个助教账号有多种服务类型，可以填写主要类型或“多技能”。团队名称team_name若ODS没有可通过assistant_team_id关联TEAM维表获取。)
|
||
DIM_ASSISTANT_TEAM（助教团队维度表）
|
||
描述： 助教团队/组织维度。如果有将助教划分团队或部门的需求，则建立此维表。当前数据有assistant_team_id但未给名称，可后续补充。
|
||
主键: team_id
|
||
字段:
|
||
• team_id (BIGINT) - 团队ID，主键。
|
||
• team_name (STRING) - 团队名称。
|
||
• site_id (BIGINT) - 门店ID/组织归属ID。
|
||
• remark (STRING) - 备注。
|
||
(来源: 如业务配置有定义团队，则填入。当前可从assistant_team_id不同值枚举填默认名称，例如都属于“教练组”暂时。此维度在数据中信息缺乏，可不强制使用。)
|
||
DIM_PAY_METHOD（支付方式维度表）
|
||
描述： 支付方式维度。维护支付渠道编码与名称的对应关系。用于翻译支付记录中的pay_method。
|
||
主键: pay_method_code
|
||
字段:
|
||
• pay_method_code (INT) - 支付方式代码，主键。
|
||
• pay_method_name (STRING) - 支付方式名称。例如“现金”、“微信”、“支付宝”、“储值卡”。
|
||
• method_type (STRING) - 支付类型大类。例如“现金类”、“第三方支付”、“会员账户”等分类。
|
||
• remark (STRING) - 备注。
|
||
(来源: 业务系统支付方式配置。由于导出数据中仅出现2种pay_method代码且未给文字，需与业务确认编码。例如常见映射：1=现金,2=微信,3=支付宝,4=银联,5=储值卡 等。ETL初始化时可根据已知支付方式填充，后续若有新方式通过配置增补。此维表确保分析报表显示支付方式时用名称而非代码。)
|
||
DIM_ORDER_ASSISTANT_TYPE（助教服务类型维度表）
|
||
描述： 助教服务类型维度。描述订单中助教服务的类型编码含义。
|
||
主键: type_code
|
||
字段:
|
||
• type_code (INT) - 助教服务类型码，主键。
|
||
• type_name (STRING) - 类型名称。如“基础课服务”、“附加课服务”。
|
||
• description (STRING) - 描述说明。
|
||
(来源: 由业务配置或数据推断。本案例根据助教流水中出现的1和2推定：1=基础服务，2=附加服务。维表需由业务确认实际含义后维护。)
|
||
DIM_COUPON_PLATFORM（券平台维度表）
|
||
描述： 券所属平台维度。标识团购券来源的平台，例如美团、大众点评等。用于分析各渠道券使用情况。
|
||
主键: platform_code
|
||
字段:
|
||
• platform_code (STRING) - 平台代码，主键。如“MT”代表美团。
|
||
• platform_name (STRING) - 平台名称。如“美团”。
|
||
• company (STRING) - 平台公司/所属集团。
|
||
• remark (STRING) - 备注。
|
||
(来源: 业务维护。可预设常见团购平台。ETL可根据券dealId或certificateId格式判断平台，如以 “MT”开头的券码归类美团。为稳妥起见应人工建立维表，platform_code可用拼音缩写或英文。)
|
||
其他维度说明：
|
||
除上述主要维度外，数据模型还包括时间维度（DIM_DATE 等）用于日期的属性分析，每日可维护一条自然日期记录，包括年、月、日、星期、节假日标识等，用于按日统计（本次未详列字段）。由于要求按自然日汇总，不考虑营业时段划分，因此时间维度只需按日历日粒度设计，无需闲时/忙时等属性。
|
||
3.2 明细事实表定义
|
||
FACT_SALE_ITEM（商品销售明细事实表）
|
||
描述： 记录商品销售的最细粒度明细，每条记录代表订单中售出的一个商品项（SKU行）。粒度：一件商品销售记录。
|
||
主键: (order_trade_no, site_goods_id, sequence_no) 复合键，或系统内部生成的流水ID。
|
||
外键关联: 关联维度表 DIM_SITE、DIM_PRODUCT、DIM_TABLE（若需要知道在哪张台消费，可通过订单关联台费表获取，不直接关联table）、DIM_MEMBER（如果订单有关联会员，通过订单汇总可拿到，不直接在行关联）。
|
||
字段:
|
||
DWD 字段名 业务含义 来源 ODS 字段 / 计算公式
|
||
sale_item_id 商品销售明细ID，主键 ods_store_sale_item.id
|
||
order_trade_no 订单交易号 ods_store_sale_item.order_trade_no
|
||
order_settle_id 订单结算ID ods_store_sale_item.order_settle_id
|
||
site_id 门店ID ods_store_sale_item.site_id
|
||
site_goods_id 门店商品ID ods_store_sale_item.site_goods_id
|
||
goods_id 品牌商品ID ods_store_sale_item.goods_id（tenant_goods_id）
|
||
goods_name 商品名称（销售时冗余） ods_store_sale_item.goods_name
|
||
category_id 商品分类ID ods_store_sale_item.category_id 或通过 site_goods_id 关联 ods_store_product 取 category_id
|
||
quantity 销售数量 ods_store_sale_item.quantity
|
||
unit_price 销售单价 ods_store_sale_item.unit_price
|
||
discount_amount 折扣金额（会员折扣+手工优惠混合） ods_store_sale_item.discount_amount（discount_money）
|
||
final_amount 实收金额 优先用 ods_store_sale_item.actual_amount；如无，则 unit_price * quantity - discount_amount
|
||
is_gift 是否赠品 ods_store_sale_item.is_gift
|
||
sale_time 销售时间 默认：ods_store_sale_item.sales_time；如需统一以结账时间，可通过 order_settle_id 关联 ods_order_receipt_detail.settle_time 覆盖
|
||
member_id 会员ID（统一从订单维度获取） ods_order_receipt_detail.member_id（按 order_settle_id 关联）
|
||
salesman_id 推销员ID ods_store_sale_item.salesman_user_id
|
||
operator_id 操作员ID ods_store_sale_item.operator_id
|
||
is_refunded 是否已退款（标记为后续被退款的销售） 若 ods_store_sale_item.is_refunded 字段有效则直接映射，否则依据 fact_refund 中关联的 sale_item_id 衍生
|
||
|
||
• order_trade_no (BIGINT) - 订单交易号，标识属于哪个订单。
|
||
• order_settle_id (BIGINT) - 订单结算ID，便于与结算/支付关联。
|
||
• site_id (BIGINT) - 门店ID，关联DIM_SITE。
|
||
• goods_id (BIGINT) - 品牌商品ID，关联DIM_PRODUCT.goods_id。
|
||
• site_goods_id (BIGINT) - 门店商品ID，关联DIM_PRODUCT（可通过该ID找到商品，也可不重复存储site_goods_id，因已能通过goods_id+site_id定位，这里保留方便直接连接ODS参考）。
|
||
• goods_name (STRING) - 商品名称冗余。
|
||
• category_id (BIGINT) - 商品类别ID，关联DIM_PRODUCT_CATEGORY，用于方便按类别统计。
|
||
• quantity (DECIMAL(10,2)) - 销售数量。
|
||
• unit_price (DECIMAL(10,2)) - 原单价。该商品原价（未折扣时的单价）。
|
||
• discount_amount (DECIMAL(10,2)) - 折扣金额。该行商品享受的折扣总额。
|
||
• final_amount (DECIMAL(10,2)) - 实收金额。= unit_price*quantity - discount_amount。
|
||
• is_gift (TINYINT) - 是否赠品标志。1=赠送，0=正常销售。赠品的final_amount通常为0。
|
||
• sale_time (DATETIME) - 销售时间/订单时间。取订单的结账时间或商品加入订单时间。用于日粒度分析。
|
||
• member_id (BIGINT) - 会员ID。若订单有会员，则此项归属该会员，否则为空。
|
||
• salesman_id (BIGINT) - 推销员ID。如有指定的销售人员。
|
||
• operator_id (BIGINT) - 操作员ID。录入此销售的收银员。
|
||
来源及逻辑： 由ODS_STORE_SALE_ITEM清洗得到。直接映射大部分字段：quantity, unit_price, discount_amount 来自ODS对应字段；final_amount在ODS中可计算或已提供。member_id并未直接在ODS销售行中出现，但若该订单绑定会员，则通过订单汇总关联到所有行，ETL可以在加载时填入同一order_trade_no下的member_id（这要求ETL在处理订单时有会员映射）。sale_time可取订单结账时间，以便所有行使用同一结算时间便于对齐日期。若有退款发生，fact_sale_item不删除原销售记录，而是在FACT_REFUND中记录退款，或可增加一个标记is_refunded。这里保留is_refund标志以供过滤退货项，但金额不调整，净销售额的计算将在汇总层考虑退款。
|
||
FACT_TABLE_USAGE（台费使用明细事实表）
|
||
描述: 记录台桌使用产生的台费明细。每条记录代表顾客使用一张台桌一次的收费情况。粒度：一笔台费账单（通常对应一个订单的台费部分）。
|
||
主键: id（台费流水ID）。
|
||
外键关联: site_id→DIM_SITE，site_table_id→DIM_TABLE，member_id→DIM_MEMBER（如适用），assistant_id无直接关联这里，因为助教收费独立记录。
|
||
字段:
|
||
DWD 字段名 业务含义 来源 ODS 字段 / 计算公式
|
||
table_usage_id 台费流水ID，主键 ods_table_use_log.id
|
||
order_trade_no 订单交易号 ods_table_use_log.order_trade_no
|
||
order_settle_id 订单结算ID ods_table_use_log.order_settle_id
|
||
site_id 门店ID ods_table_use_log.site_id
|
||
site_table_id 台桌ID ods_table_use_log.site_table_id
|
||
table_name 台桌名称 ods_table_use_log.table_name
|
||
start_time 开台时间 ods_table_use_log.start_use_time
|
||
end_time 离台时间 ods_table_use_log.last_use_time
|
||
duration_minutes 使用时长（分钟） 若 real_table_use_seconds 有值：real_table_use_seconds / 60；否则：EXTRACT(EPOCH FROM (last_use_time - start_use_time))/60
|
||
billing_unit_price 计费单价（可选保留） ods_table_use_log.billing_unit_price（即 ledger_unit_price）
|
||
数据仓库建模与 ETL 对接 PRD
|
||
billing_count 计费数量（可选保留） ods_table_use_log.billing_count（即 ledger_count）
|
||
base_fee 台费原价小计（未折扣） ods_table_use_log.ledger_amount
|
||
member_discount 会员折扣金额 ods_table_use_log.member_discount_amount
|
||
coupon_deduction 券抵扣金额 ods_table_use_log.coupon_discount_amount（coupon_promotion_amount）
|
||
manual_discount 人工减免金额 优先：ods_table_use_log.manual_adjust_amount；如无，则 Σ ods_table_fee_adjust.adjust_amount（按 order_settle_id+site_table_id 汇总）
|
||
数据仓库建模与 ETL 对接 PRD
|
||
service_fee 附加服务费/管理费 ods_table_use_log.service_fee（mgmt_fee）
|
||
final_table_fee 台费实收金额 优先用 ods_table_use_log.final_table_fee（real_table_charge_money）；如无，则 base_fee - member_discount - coupon_deduction - manual_discount + service_fee
|
||
member_id 关联会员ID（可空） ods_table_use_log.member_id
|
||
operator_id 操作员ID ods_table_use_log.operator_id
|
||
salesman_id 推销员ID（如有） ods_table_use_log.salesman_user_id
|
||
is_adjusted 是否做过人工调整（0/1） CASE WHEN manual_discount > 0 THEN 1 ELSE 0 END
|
||
is_canceled 台费记录是否作废/删除（0/1） CASE WHEN ods_table_use_log.is_deleted = 1 THEN 1 ELSE 0 END
|
||
|
||
|
||
• table_usage_id (BIGINT) - 台费流水ID（FACT主键，对应ODS_TABLE_USE_LOG.id）。
|
||
• order_trade_no (BIGINT) - 订单交易号。
|
||
• order_settle_id (BIGINT) - 订单结算ID。
|
||
• site_id (BIGINT) - 门店ID。
|
||
• site_table_id (BIGINT) - 台桌ID，关联DIM_TABLE。
|
||
• table_name (STRING) - 台桌名称。冗余方便查阅。
|
||
• start_time (DATETIME) - 开台时间。
|
||
• end_time (DATETIME) - 离台时间。
|
||
• duration_minutes (INT) - 使用时长（分钟）。可由start/end计算或直接取ODS real_table_use_seconds换算。
|
||
• base_fee (DECIMAL(10,2)) - 原台费金额（未扣减前费用）。
|
||
• member_discount (DECIMAL(10,2)) - 会员折扣减免金额。
|
||
• coupon_deduction (DECIMAL(10,2)) - 券抵扣金额。
|
||
• manual_discount (DECIMAL(10,2)) - 手工减免费用。
|
||
• service_fee (DECIMAL(10,2)) - 附加服务费。
|
||
• final_table_fee (DECIMAL(10,2)) - 实收台费金额。= base_fee + service_fee - member_discount - coupon_deduction - manual_discount。
|
||
• member_id (BIGINT) - 会员ID（如果该开台绑定会员）。
|
||
• operator_id (BIGINT) - 操作员ID（开台/结账经办人）。
|
||
• salesman_id (BIGINT) - 营销员ID（如果有在场服务员）。
|
||
• is_adjusted (TINYINT) - 是否有人工调整。1表示有手工打折（manual_discount>0），0无。
|
||
• is_canceled (TINYINT) - 是否作废。1表示此台费记录被取消未收费（由于订单取消等），0正常。
|
||
|
||
|
||
|
||
来源及逻辑： 由ODS_TABLE_USE_LOG直接整理。base_fee对应ODS的ledger_amount，member_discount, coupon_deduction, manual_discount, service_fee直接来自同名字段，final_table_fee取ODS.real_table_charge_money校验计算。is_adjusted标志=1当manual_discount>0或adjust_amount有值。is_canceled可根据ODS.is_delete或订单取消情况设定（若订单未结算导致此记录无效）。注意： manual_discount来自单独的ODS_TABLE_FEE_ADJUST记录，如果ODS_USE_LOG里已有adjust_amount字段，则ETL需要与调整表核对确保一致。所有金额字段单位均为元。FACT_TABLE_USAGE为订单汇总时台费总额的来源。
|
||
FACT_ASSISTANT_SERVICE（助教服务明细事实表）
|
||
描述: 记录助教服务/教练陪练的明细。每条记录对应顾客享受的一次助教服务。粒度：一条助教服务记录。
|
||
主键: id（助教流水ID）。
|
||
外键关联: site_id→DIM_SITE，assistant_id→DIM_ASSISTANT，member_id→DIM_MEMBER，site_table_id→DIM_TABLE（如分析需要，可保留字段但不一定强关联），service_type→DIM_ORDER_ASSISTANT_TYPE。
|
||
字段:
|
||
DWD 字段名 业务含义 来源 ODS 字段 / 计算公式
|
||
assistant_service_id 助教服务流水ID，主键 ods_assistant_service_log.id
|
||
order_trade_no 订单交易号 ods_assistant_service_log.order_trade_no
|
||
order_settle_id 订单结算ID ods_assistant_service_log.order_settle_id
|
||
site_id 门店ID ods_assistant_service_log.site_id
|
||
site_assistant_id 门店助教账号ID ods_assistant_service_log.site_assistant_id
|
||
assistant_name 助教名称冗余 ods_assistant_service_log.assistant_name 或关联 ods_assistant_account.real_name/nickname
|
||
member_id 会员ID ods_assistant_service_log.member_id
|
||
service_start_time 服务开始时间 ods_assistant_service_log.start_time
|
||
service_end_time 服务结束时间 ods_assistant_service_log.end_time
|
||
service_duration_minutes 服务时长（分钟） 若 ODS 有总秒数字段：seconds/60；否则 EXTRACT(EPOCH FROM (end_time - start_time))/60
|
||
service_type_id 助教服务类型（基础课/附加课等） ods_assistant_service_log.service_type（在 DWD 关联 dim_order_assist_type 转义）
|
||
base_fee 助教基础课/单价计费金额（原价） ods_assistant_service_log.base_fee，或按单价×时长计算（见样本字段）
|
||
member_discount 助教服务会员折扣金额 ods_assistant_service_log.member_discount_amount
|
||
manual_discount 助教服务人工折扣金额 ods_assistant_service_log.manual_discount_amount
|
||
coupon_deduction 券抵扣金额（如团购券分摊到助教） 若 ODS 有对应字段则直接映射；若无则默认 0，由 fact_coupon_usage 不再分摊到助教
|
||
final_service_fee 助教服务实收金额 优先：ods_assistant_service_log.final_service_fee；否则 base_fee - member_discount - manual_discount - coupon_deduction
|
||
operator_id 操作员ID ods_assistant_service_log.operator_id
|
||
salesman_id 推销员ID ods_assistant_service_log.salesman_user_id（如有）
|
||
is_canceled 该条助教服务是否被“废除” CASE WHEN (ods_assistant_service_log.is_trash = 1 OR 在 ods_assistant_cancel_log 有匹配记录) THEN 1 ELSE 0 END
|
||
数据仓库建模与 ETL 对接 PRD
|
||
cancel_reason 废除原因 若存在 ods_assistant_cancel_log 记录，则取其 reason；否则为空
|
||
service_score 服务评分 ods_assistant_service_log.service_grade
|
||
skill_score 技能评分 ods_assistant_service_log.skill_grade
|
||
overall_score 综合评分 ods_assistant_service_log.sum_grade
|
||
|
||
• assistant_service_id (BIGINT) - 助教服务流水ID（主键）。
|
||
• order_trade_no (BIGINT) - 订单交易号。
|
||
• order_settle_id (BIGINT) - 订单结算ID。
|
||
• site_id (BIGINT) - 门店ID。
|
||
• site_table_id (BIGINT) - 台桌ID（助教服务发生的台桌）。
|
||
• assistant_id (BIGINT) - 助教ID，关联DIM_ASSISTANT。
|
||
• assistant_name (STRING) - 助教姓名冗余。
|
||
• service_type (INT) - 助教服务类型代码，关联DIM_ORDER_ASSISTANT_TYPE获取类型名称。
|
||
• start_time (DATETIME) - 服务开始时间。
|
||
• end_time (DATETIME) - 服务结束时间。
|
||
• duration_minutes (INT) - 服务时长（分钟）。
|
||
• base_fee (DECIMAL(10,2)) - 原服务金额。
|
||
• member_discount (DECIMAL(10,2)) - 会员折扣金额。
|
||
• coupon_deduction (DECIMAL(10,2)) - 券抵扣金额。
|
||
• manual_discount (DECIMAL(10,2)) - 手工减免金额。
|
||
• final_fee (DECIMAL(10,2)) - 实收服务费金额。= base_fee - 各类折扣减免。
|
||
• member_id (BIGINT) - 会员ID（享受服务的顾客，如有会员绑定）。
|
||
• is_canceled (TINYINT) - 是否被取消。1表示该服务实际未完成（被废除），0正常完成。
|
||
• cancel_reason (STRING) - 取消原因描述（如有）。
|
||
• operator_id (BIGINT) - 操作员ID（录入/确认此服务的员工）。
|
||
• salesman_id (BIGINT) - 推销员ID（如有营销人员促成此服务）。
|
||
• service_score (INT) - 服务评分（顾客对服务态度的评分）。
|
||
• skill_score (INT) - 技能评分。
|
||
• overall_score (INT) - 综合评分。
|
||
• score_time (DATETIME) - 评价时间。
|
||
来源及逻辑： 由ODS_ASSISTANT_SERVICE_LOG整理。大部分字段直接映射：base_fee=ledger_amount, member_discount/manual_discount/coupon_deduction来源对应字段，final_fee=service_money。duration计算秒数转换分钟。service_type以ODS.order_assistant_type填充。is_canceled基于ODS.is_trash判断，如为1则本记录在业务上被取消，不计入最终收费（final_fee应该为0或忽略）。对于取消记录，可进一步参考ODS_ASSISTANT_CANCEL_LOG以获取cancel_reason等，在ETL中通过order_trade_no和assistant_id匹配将原因填入。评分字段直接取ODS中的service_grade/skill_grade/sum_grade，如有的话。注意： 已取消的助教服务不应计入订单实际支出，因此在汇总时应排除或final_fee=0。FACT_ASSISTANT_SERVICE为订单汇总层助教费部分来源，也用于人力绩效统计（如单个助教服务次数、评分）。
|
||
|
||
废除计费逻辑与派生字段（新增）
|
||
在 DWD 层，我们将 ODS 助教流水中的时间与金额字段标准化为以下核心指标（字段名以本事实表实际命名为准）：
|
||
• service_duration_sec：服务实际发生时长（秒），来源于 ODS real_use_seconds。
|
||
• charge_duration_sec：计费时长（秒），来源于 ODS income_seconds。
|
||
• base_fee：原始应收金额，来源于 ODS ledger_amount。
|
||
• final_fee：本条服务最终计入营收的金额，来源于 ODS projected_income / service_money。
|
||
• is_canceled：是否废除标志，来源于 ODS is_trash。
|
||
DWD 层统一约定：
|
||
1. 收入统计口径：
|
||
o 所有“助教收入”类指标，统一使用 final_fee 作为计收入口径。
|
||
o 不再直接使用 base_fee 汇总，以避免将废除的未计费部分算入收入。
|
||
2. 废除服务的处理：
|
||
o is_canceled = 1 的记录仍保留在事实表中，用于分析“被废除的服务数量 / 时长 / 原始金额”。
|
||
o 在“营收类报表”中，若不特别说明，统一按 final_fee 汇总，因此废除服务中未计费部分不会进入营业收入。
|
||
o 如需统计“废除导致的收入损失”，则可使用：lost_fee = base_fee - final_fee（仅对 is_canceled = 1 的记录统计）。
|
||
3. 服务时长相关分析：
|
||
o 实际服务投入人力时长使用 service_duration_sec；
|
||
o 可计费时长使用 charge_duration_sec；
|
||
o 两者差值 service_duration_sec - charge_duration_sec 可用于衡量“非计费投入”（例如免费服务、投诉补偿等）。
|
||
|
||
|
||
FACT_COUPON_USAGE（团购券使用明细事实表）
|
||
描述: 记录团购优惠券（团购套餐券）的使用情况。每条记录代表一张券被使用一次（核销）。粒度：一次团购券核销事件。
|
||
主键: id（券使用流水ID）。
|
||
外键关联: site_id→DIM_SITE，package_id→DIM_GROUP_PACKAGE（需在DWD建立团购套餐维度），platform_code→DIM_COUPON_PLATFORM。
|
||
字段:
|
||
|
||
DWD 字段名 业务含义 来源 ODS 字段 / 计算公式
|
||
coupon_usage_id 团购券使用流水ID ods_group_package_log.id
|
||
order_trade_no 订单交易号 ods_group_package_log.order_trade_no
|
||
order_settle_id 订单结算ID ods_group_package_log.order_settle_id
|
||
site_id 门店ID ods_group_package_log.site_id
|
||
coupon_code 券码/核销码 ods_platform_coupon_log.coupon_code 或 verify_code（按实际字段）
|
||
coupon_platform_id 券平台ID（美团/点评等） ods_platform_coupon_log.platform_id（在 DWD 关联 dim_coupon_platform）
|
||
group_package_id 团购套餐定义ID ods_group_package_log.group_package_id
|
||
group_package_name 团购套餐名称 ods_group_package.group_package_name（通过 ID 关联）
|
||
coupon_face_value 券标称原价（市场价） ods_group_package.face_value 或平台记录中的 original_price
|
||
coupon_settle_price 券结算价（门店实际从平台收到的金额） ods_platform_coupon_log.settle_price
|
||
deduct_amount 本次核销抵扣到订单的金额（用于订单记账流水） 一般等于 coupon_settle_price；如有差额（例如多券叠加）可取 ods_group_package_log.deduct_amount
|
||
verify_time 核销时间 ods_platform_coupon_log.verify_time
|
||
member_id 绑定会员ID（如有） 若券核销与会员绑定，则取 ods_group_package_log.member_id；无则为空
|
||
|
||
• coupon_usage_id (BIGINT) - 券使用记录ID（主键）。
|
||
• order_trade_no (BIGINT) - 订单交易号。
|
||
• order_settle_id (BIGINT) - 订单结算ID。
|
||
• site_id (BIGINT) - 门店ID。
|
||
• site_table_id (BIGINT) - 台桌ID（券对应的台桌消费）。
|
||
• assistant_id (BIGINT) - 助教ID（如券包含助教服务，可记录关联，否则为空）。
|
||
• package_id (BIGINT) - 套餐ID，关联DIM_GROUP_PACKAGE。
|
||
• package_name (STRING) - 套餐名称冗余。
|
||
• platform_code (STRING) - 券来源平台代码，关联DIM_COUPON_PLATFORM获得名称。
|
||
• coupon_code (STRING) - 券码编号。
|
||
• deduct_amount (DECIMAL(10,2)) - 抵扣金额。该券核销抵扣的金额价值。
|
||
• used_time (DATETIME) - 券使用时间。
|
||
• operator_id (BIGINT) - 核销操作员ID。
|
||
• salesman_id (BIGINT) - 销售员ID（如果有）。
|
||
• status (STRING) - 券使用状态（通常为“已使用”，如有需要可标识异常）。
|
||
来源及逻辑： 综合ODS_GROUP_PACKAGE_LOG和ODS_PLATFORM_COUPON_LOG的数据。考虑到一张券使用会在两张表各有一条记录，DWD层可将两者合并：以ODS_GROUP_PACKAGE_LOG为主，因为它包含抵扣金额等门店内部信息，补充平台信息（platform_code, coupon_code等从ODS_PLATFORM_COUPON_LOG获取）。合并逻辑：匹配条件可以是订单号+券ID或券码。由于coupon_code在ODS_GROUP_PACKAGE_LOG未必有，而在平台表有certificateId，需要通过deal_id或order关联核对。如果匹配困难，也可不合并，两张事实表分别建；但为方便分析，合并成一张FACT_COUPON_USAGE包含主要字段。deduct_amount直接取套餐流水的deduct_money；platform_code通过平台表或deal_id映射；coupon_code取platform表的certificateId（券码）。如果有未匹配的平台券记录，也可独立分析，此处以业务成功使用为主。FACT_COUPON_USAGE的deduct_amount会参与订单汇总的优惠总额计算，也可用于统计团购贡献收入。
|
||
FACT_PAYMENT（支付事实表）
|
||
描述: 记录订单的收款明细，每条记录代表店内收到一笔款项。与ODS_PAYMENT_RECORD一致，但在DWD层将支付方式代码转译，关联订单等。粒度：一笔支付流水。
|
||
主键: id（支付流水ID）。
|
||
外键关联: site_id→DIM_SITE，pay_method_code→DIM_PAY_METHOD，member_id→DIM_MEMBER（如支付方式为储值卡扣款，可关联对应会员）。
|
||
字段:
|
||
|
||
DWD 字段名 业务含义 来源 ODS 字段 / 计算公式
|
||
payment_id 支付流水ID，主键 ods_payment_record.id
|
||
order_trade_no 订单交易号 ods_payment_record.order_trade_no
|
||
order_settle_id 订单结算ID ods_payment_record.order_settle_id
|
||
site_id 门店ID ods_payment_record.site_id
|
||
member_id 会员ID（如支付与会员绑定） ods_payment_record.member_id
|
||
pay_method_code 支付方式编码（现金/微信/储值卡等） ods_payment_record.pay_type（在 DWD 关联 dim_pay_method）
|
||
pay_amount 支付金额 ods_payment_record.amount
|
||
pay_time 支付时间 ods_payment_record.pay_time
|
||
transaction_id 第三方交易流水号 ods_payment_record.third_transaction_id（按实际字段）
|
||
operator_id 操作员ID ods_payment_record.operator_id
|
||
remark 备注 ods_payment_record.remark
|
||
|
||
• payment_id (BIGINT) - 支付流水ID（主键）。
|
||
• order_trade_no (BIGINT) - 订单交易号。
|
||
• order_settle_id (BIGINT) - 订单结算ID。
|
||
• site_id (BIGINT) - 门店ID。
|
||
• pay_method_code (INT) - 支付方式代码，关联DIM_PAY_METHOD获取名称。
|
||
• pay_method_name (STRING) - 支付方式名称冗余。
|
||
• pay_amount (DECIMAL(10,2)) - 支付金额（正值，元）。
|
||
• pay_time (DATETIME) - 支付完成时间。
|
||
• transaction_id (STRING) - 第三方支付流水号。
|
||
• member_id (BIGINT) - 会员ID（如果是储值卡支付，此处记录会员账号，否则为空）。
|
||
• operator_id (BIGINT) - 操作员ID（收款收银员）。
|
||
来源及逻辑： 直接来源ODS_PAYMENT_RECORD清洗。pay_method_code替换原有支付方式字段，pay_method_name可通过维表填入或直接保留ODS中的文本。member_id一般不在支付记录出现，但当支付方式是“储值卡”时，可以推导对应哪个会员：支付记录本身或订单有会员关联。ETL可以在遇到储值卡支付（例如pay_method_code=5储值卡）时，将该订单的member_id填充此字段，表示这笔支付从该会员账户扣款。其他支付方式则member_id留空。FACT_PAYMENT数据用于分析付款方式占比，也用于订单汇总计算实收金额校验。
|
||
FACT_REFUND（退款事实表）
|
||
描述: 记录退款交易明细，每条记录代表店里退出一笔款给顾客。与ODS_REFUND_RECORD对应。粒度：一笔退款流水。
|
||
主键: id（退款流水ID）。
|
||
外键关联: site_id→DIM_SITE，pay_method_code→DIM_PAY_METHOD。
|
||
字段:
|
||
|
||
DWD 字段名 业务含义 来源 ODS 字段 / 计算公式
|
||
refund_id 退款流水ID，主键 ods_refund_record.id
|
||
order_trade_no 原订单交易号 ods_refund_record.order_trade_no
|
||
order_settle_id 原订单结算ID ods_refund_record.order_settle_id
|
||
site_id 门店ID ods_refund_record.site_id
|
||
member_id 会员ID ods_refund_record.member_id
|
||
refund_amount 退款金额 ods_refund_record.amount
|
||
pay_method_code 原支付方式编码 ods_refund_record.pay_type
|
||
refund_time 退款时间 ods_refund_record.refund_time
|
||
operator_id 操作员ID ods_refund_record.operator_id
|
||
reason 退款原因 ods_refund_record.reason
|
||
related_payment_id 关联支付流水ID（如有） ods_refund_record.payment_id 或通过第三方流水号匹配
|
||
|
||
|
||
• refund_id (BIGINT) - 退款流水ID（主键）。
|
||
• order_trade_no (BIGINT) - 原订单交易号。
|
||
• order_settle_id (BIGINT) - 原订单结算ID。
|
||
• site_id (BIGINT) - 门店ID。
|
||
• pay_method_code (INT) - 退款渠道代码，关联DIM_PAY_METHOD。
|
||
• pay_method_name (STRING) - 退款渠道名称冗余。
|
||
• refund_amount (DECIMAL(10,2)) - 退款金额（正值表示退还给顾客的钱，元）。
|
||
• refund_time (DATETIME) - 退款时间。
|
||
• original_payment_id (BIGINT) - 原支付流水ID。
|
||
• operator_id (BIGINT) - 操作员ID（执行退款的员工）。
|
||
来源及逻辑： 由ODS_REFUND_RECORD清洗。refund_amount取绝对值（因为ODS可能存为负值，我们在fact中用正值字段表示退还金额，但在汇总计算时会作为减项）。pay_method_code译码，同支付方式维表。original_payment_id可用于关联找出是哪笔支付被退（但分析用处不大，可选保留）。FACT_REFUND主要用于财务核对和计算净收入=收款-退款。对订单汇总的影响在于如果某订单当场退款，DWS订单汇总可减去退款额；如果退款发生在不同日则视分析口径决定如何处理。
|
||
FACT_BALANCE_CHANGE（余额变动事实表）
|
||
描述: 记录会员储值余额的每次变动明细。每条记录对应余额增减事件。与ODS_BALANCE_CHANGE类似，但进行清洗和关联。粒度：一次余额变动。
|
||
主键: id（余额变动ID）。
|
||
外键关联: member_id→DIM_MEMBER，site_id→DIM_SITE，change_type→DIM_BALANCE_CHANGE_TYPE（如果需要，对类型进行描述映射）。
|
||
字段:
|
||
|
||
DWD 字段名 业务含义 来源 ODS 字段 / 计算公式
|
||
balance_change_id 余额变动记录ID，主键 ods_balance_change.id
|
||
member_id 会员ID ods_balance_change.member_id
|
||
site_id 门店ID ods_balance_change.site_id
|
||
change_amount 变动金额（正=增加，负=减少） ods_balance_change.change_amount
|
||
balance_before 变动前余额 ods_balance_change.balance_before
|
||
balance_after 变动后余额 ods_balance_change.balance_after
|
||
change_type_code 变动类型编码（充值/消费/调整…） ods_balance_change.change_type，DWD 关联 dim_balance_change_type
|
||
relate_id 关联业务记录ID ods_balance_change.relate_id
|
||
pay_method_code 涉及支付方式（仅对充值类有意义） ods_balance_change.pay_method 或由 ods_recharge_record.pay_method 回填
|
||
change_time 变动时间 ods_balance_change.change_time
|
||
operator_id 操作员ID ods_balance_change.operator_id
|
||
remark 备注 ods_balance_change.remark
|
||
|
||
• change_id (BIGINT) - 余额变动ID（主键）。
|
||
• member_id (BIGINT) - 会员ID，关联DIM_MEMBER。
|
||
• change_type_code (INT) - 变动类型编码，关联DIM_BALANCE_CHANGE_TYPE维表。
|
||
• change_type_name (STRING) - 变动类型名称（如“充值到账”、“消费扣款”、“后台调整”）。
|
||
• related_id (BIGINT) - 关联业务ID（充值记录ID或订单结算ID等）。
|
||
• change_amount (DECIMAL(10,2)) - 变动金额（+增加/-减少）。
|
||
• balance_after (DECIMAL(10,2)) - 变动后余额。
|
||
• change_time (DATETIME) - 变动时间。
|
||
• pay_method_code (INT) - 支付方式编码（仅当change_type为充值时有意义）。
|
||
• pay_method_name (STRING) - 支付方式名称。
|
||
• operator_id (BIGINT) - 操作员ID。
|
||
来源及逻辑： 由ODS_BALANCE_CHANGE清洗。由于余额变动本身不是核心分析指标（大部分可以从充值和消费推导），此fact可选。如果保留：change_type_code统一编码，需建立DIM_BALANCE_CHANGE_TYPE，如1=充值、2=消费、3=调整等。变动类型名称通过维表给出或直接写明。related_id用于灵活关联对应事实，如充值对应FACT_RECHARGE.id，消费对应FACT_PAYMENT/FACT_ORDER_SUMMARY.id（可能比较复杂映射，可在ETL中部分实现，对应不上则留存ID备用）。支付方式仅对充值型的有意义，直接从ODS取。FACT_BALANCE_CHANGE有助于核对会员资金流，但其数据基本可以由其他事实推算，因此在数据量不大时保留以备检查。*
|
||
________________________________________
|
||
以上事实表涵盖会员消费的主要明细。所有事实表均包含site_id以支持多店分开统计，以及包含相应的维度ID来连接维度表，从而获得丰富维度属性。
|
||
在事实数据清洗过程中，需要确保：
|
||
- 所有关联键有效（如销售记录里商品ID、订单号能找到对应维表或事实）。
|
||
- 金额字段单位统一为元，类型统一使用DECIMAL(10,2)。
|
||
- 时间字段标准化为Timestamp或DATETIME，且对应本地时区（日切按自然日00:00）。
|
||
4. DWS层表定义（汇总层）
|
||
DWS 层主要面向分析需求，对明细事实（DWD）进行汇总和重构，形成便于直接取用的订单级宽表和主题汇总表。本方案重点设计订单结算汇总宽表 DWS_ORDER_SUMMARY，以「每笔结账记录一行」为粒度，整合一张订单/结算单的主要指标，用于从订单视角分析：
|
||
• 客单价与消费结构（台费/商品/助教/团购）
|
||
• 优惠构成（会员折扣、手工折扣、团购券）
|
||
• 支付与资金口径（含储值 / 非储值、退款、有效流水）
|
||
• 会员贡献与行为（会员标识、会员快照等）
|
||
如需日报等其他汇总，可在本表基础上按日期、门店等维度进行二次汇总（本次不展开）。
|
||
________________________________________
|
||
4.1 DWS_ORDER_SUMMARY（订单结算汇总表）概述
|
||
• 表名：DWS_ORDER_SUMMARY（订单结算汇总表）
|
||
• 粒度：一笔订单结算 / 结账单（order_settle_id）
|
||
• 主键：order_settle_id（订单结算 ID）
|
||
• 聚合键：site_id + order_settle_id（门店维度下唯一）
|
||
• 主要用途：
|
||
o 订单级经营分析：客单价、消费结构、折扣结构、支付结构、会员贡献
|
||
o 资金视角分析：有效流水（含充值）、净收入
|
||
o 配合 DWD 事实和维表，支撑多维 OLAP 与报表
|
||
时间与金额口径约定
|
||
• 订单日期：order_date = DATE(settle_time)，按自然日划分，不再做 4 点切日。
|
||
• 支付统计：所有“支付金额”统计基于 FACT_PAYMENT，仅包含支付明细，不含退款；
|
||
退款单独通过 refund_amount 字段和退款事实表体现。
|
||
• 金额字段：除特别说明外，均为“订单级汇总值”，即同一订单下相关明细金额的汇总结果。
|
||
• 优惠拆分：台费、商品、助教明细中涉及会员折扣、手工折扣、团购券抵扣等，均在订单级进行归并。
|
||
来源优先级约定
|
||
• 若某订单级指标可同时从小票 ODS 表与 DWD fact 汇总得到：
|
||
o 推荐以 DWD fact 为主（建模统一口径）；
|
||
o ODS 小票金额用于对账与校验；
|
||
o 对账不一致时，以实际业务约定为准并在 ETL 规则中固化。
|
||
________________________________________
|
||
4.2 字段设计
|
||
以下按功能分组列出字段。字段类型可根据目标数仓实现微调，默认金额类为 DECIMAL(18,2)，数量类为 INT 或 DECIMAL(18,2)，标志类为 TINYINT。
|
||
4.2.1 基础维度与主键信息
|
||
• order_settle_id (BIGINT)
|
||
订单结算 ID，主键。一笔订单结算的唯一标识。
|
||
• order_trade_no (BIGINT)
|
||
订单交易号。与 order_settle_id 一一对应（当前假定一单一结，如后续存在多次结算需再明确规则）。
|
||
• order_type (STRING)
|
||
订单类型标识。'消费单' = 存在台费的消费 ；'助教单' =仅助教服务；’充值单’=仅充值无消费。用于在 ETL 中决定有效流水的分摊方式。
|
||
• site_id (BIGINT)
|
||
门店 ID，关联 DIM_SITE 获取门店名称、区域等信息。
|
||
• order_date (DATE)
|
||
订单日期（自然日）。从 settle_time 提取日期部分，用于按日汇总。
|
||
• settle_time (DATETIME)
|
||
结账完成时间，即订单实际关闭时间。
|
||
• member_id (BIGINT, 可空)
|
||
订单绑定的会员 ID，无会员则为空。
|
||
• member_flag (TINYINT)
|
||
会员标识；1 表示会员单（member_id 非空），0 表示非会员单，便于筛选统计。
|
||
4.2.2 订单级金额字段（消费与优惠拆分）
|
||
• total_table_fee (DECIMAL(18,2))
|
||
台费实收总额。该订单所有台费实际收取金额之和（已扣除折扣与券抵扣）。
|
||
• total_product_amount (DECIMAL(18,2))
|
||
商品实收总额。订单中所有商品实收金额之和（不含退款/作废行）。
|
||
• total_assistant_fee (DECIMAL(18,2))
|
||
助教服务实收总额。订单中所有未作废助教服务实际收费总额。
|
||
• total_coupon_deduction (DECIMAL(18,2))
|
||
团购券抵扣总额。按券“抵扣金额”（记账侧的用券抵扣）汇总。
|
||
• manual_discount_amount (DECIMAL(18,2))
|
||
手工优惠总额。包含台费、助教（以及如有可识别的商品手工折扣）的人工减免金额。
|
||
• member_discount_amount (DECIMAL(18,2))
|
||
会员折扣总额。因会员身份享受的优惠总和（可含台费、助教、商品的会员折扣）。
|
||
• order_original_amount (DECIMAL(18,2))
|
||
订单原价总额（未扣减任何优惠/券的应付金额），定义为：
|
||
order_original_amount = total_table_fee + total_product_amount + total_assistant_fee + member_discount_amount + manual_discount_amount + total_coupon_deduction。
|
||
• order_final_amount (DECIMAL(18,2))
|
||
订单应收金额。扣除所有优惠与券抵扣后的实收金额，理论上应满足：
|
||
order_final_amount = total_table_fee + total_product_amount + total_assistant_fee，
|
||
并与小票 ODS 中的订单应收金额字段保持对齐，如存在差异，以小票实际口径为准并在 ETL 层进行调整。
|
||
4.2.3 支付拆分与退款、净收入字段
|
||
• total_paid_amount (DECIMAL(18,2))
|
||
实付金额总计。顾客实际支付的总金额（包括现金/电子支付 + 储值卡支付）。
|
||
• stored_card_deduct (DECIMAL(18,2))
|
||
储值卡支付金额。该订单由会员储值余额支付的金额。
|
||
• external_paid_amount (DECIMAL(18,2))
|
||
外部支付金额（非储值类支付） = total_paid_amount - stored_card_deduct。
|
||
• pay_cash (DECIMAL(18,2), 可选)
|
||
现金支付金额。
|
||
• pay_wechat (DECIMAL(18,2), 可选)
|
||
微信支付金额。
|
||
• pay_alipay (DECIMAL(18,2), 可选)
|
||
支付宝支付金额。
|
||
• pay_other (DECIMAL(18,2), 可选)
|
||
其他支付渠道金额（如银联卡、其他第三方支付等），可作为除现金/微信/支付宝/储值卡之外的汇总。
|
||
说明：出于“避免写死支付方式枚举”的原则，具体支付渠道列（pay_xxx）可按实际需要选择是否在宽表固化，如支付方式变化频繁，可仅保留关键区分（如储值/非储值），其余在报表层通过关联支付维表透视。
|
||
• pay_method_count (INT)
|
||
本单使用的支付方式种类数（DISTINCT 支付方式编码）。
|
||
• refund_amount (DECIMAL(18,2))
|
||
退款金额。本订单相关退款总额（按正数存储），包含全额或部分退款。
|
||
• net_income (DECIMAL(18,2))
|
||
净收款金额。标准口径为：
|
||
net_income = total_paid_amount - refund_amount。
|
||
表示该订单在收款视角上的净入账金额。
|
||
4.2.4 数量类与辅助分析字段
|
||
• item_count (INT)
|
||
商品行数。订单中不同商品行的数量（去重后的行数）。
|
||
• total_item_quantity (DECIMAL(18,2))
|
||
商品总数量。按件数/重量等单位汇总的总数。
|
||
• assistant_count (INT)
|
||
助教服务总次数。订单中发生的未作废助教服务记录数。
|
||
• table_count (INT)
|
||
使用的台桌数量。订单涉及的不同台桌数（换台场景可能大于 1）。
|
||
• duration_total (INT)
|
||
台桌使用总时长（分钟）。如有多台桌，会进行累加。
|
||
• avg_item_price (DECIMAL(18,2))
|
||
商品平均单价（实收 / 数量），定义为：
|
||
CASE WHEN total_item_quantity > 0 THEN total_product_amount / total_item_quantity ELSE NULL END。
|
||
4.2.5 订单级快照维度字段
|
||
• member_name_snapshot (STRING)
|
||
结账时会员姓名快照。来自小票 ODS；用于反映当时会员信息（会员资料后续可能变更）。
|
||
• member_mobile_snapshot (STRING)
|
||
结账时会员手机号快照。
|
||
• cashier_name (STRING)
|
||
收银员姓名。处理此订单结账的操作员，可由 ODS 收银员字段清洗得到。
|
||
• remark (STRING)
|
||
订单备注。如小票或订单中的附加说明。
|
||
4.2.6 记账流水字段（Book Flow）
|
||
用于从“经营结构 / 记账科目”视角，分析台费、助教、商品、团购在营业额中的构成，不关注支付方式。
|
||
• book_table_flow (DECIMAL(18,2))
|
||
台费记账流水。该订单在“台费”科目下的营业收入（含储值支付，不含作废/取消）。
|
||
• book_assistant_flow (DECIMAL(18,2))
|
||
助教记账流水。该订单在“助教服务”科目下的营业收入（仅统计未作废且实际计费的助教服务）。
|
||
• book_goods_flow (DECIMAL(18,2))
|
||
商品记账流水。该订单在“商品销售”科目下的营业收入（不含赠品）。
|
||
• book_group_flow (DECIMAL(18,2))
|
||
团购记账流水。该订单使用团购券形成的收入，按团购“平台结算价”入账。
|
||
• book_order_flow (DECIMAL(18,2))
|
||
订单记账总流水，用于经营结构分析。定义为：
|
||
book_order_flow = book_table_flow + book_assistant_flow + book_goods_flow + book_group_flow。
|
||
4.2.7 订单有效流水字段（Effective Order Flow）
|
||
从“资金视角 + 含充值”的财务口径定义订单对门店实际资金流入的贡献。
|
||
• order_effective_consume_cash (DECIMAL(18,2))
|
||
非储值类支付中，用于消费（台费 + 助教 + 商品 + 团购）的金额。
|
||
• order_effective_recharge_cash (DECIMAL(18,2))
|
||
非储值类支付中，用于充值的金额（纯充值单）。
|
||
• order_effective_flow (DECIMAL(18,2))
|
||
订单有效流水。定义为：
|
||
order_effective_flow = order_effective_consume_cash + order_effective_recharge_cash + book_group_flow。
|
||
说明：
|
||
• 按约定，“现金支付”理解为“非储值类支付”（现金 / 微信 / 支付宝 / 银行卡等）；
|
||
储值卡扣款仅视为内部结转，不视为新增资金流入。
|
||
• 团购使用按“平台结算价”计入有效流水（通过 book_group_flow 体现），不按券面值。
|
||
________________________________________
|
||
4.3 来源及汇总逻辑说明
|
||
本表主要通过订单 ID / 交易号在各 DWD 事实表间关联，并按订单聚合得到。以下为关键字段的来源与口径。
|
||
4.3.1 消费与优惠类金额字段
|
||
1. total_table_fee（台费实收合计）
|
||
o 来源：FACT_TABLE_USAGE.final_table_fee
|
||
o 过滤：is_canceled = 0（未作废台费记录）
|
||
o 聚合：按 site_id, order_settle_id 或 order_trade_no 分组求和。
|
||
o 如 ODS 小票中有台费汇总字段，可用于对账。
|
||
2. total_product_amount（商品实收合计）
|
||
o 来源：FACT_SALE_ITEM.final_amount
|
||
o 建议过滤：is_refunded = 0 或按实际业务标识排除已退款/作废行。
|
||
o 聚合：按订单维度求和。
|
||
3. total_assistant_fee（助教服务实收合计）
|
||
o 来源：FACT_ASSISTANT_SERVICE.final_service_fee / final_fee
|
||
o 过滤：is_canceled = 0（排除助教废除记录）
|
||
o 聚合：按订单维度求和。
|
||
助教相关金额口径说明（新增）
|
||
DWS 层所有“助教相关金额”字段，统一遵循以下口径：
|
||
• 订单维度的助教金额（如“助教基础课本笔订单总额”、“助教附加课本笔订单总额”）均由 DWD 助教服务事实表中 final_fee 汇总而来：
|
||
o 基础课金额：汇总 service_type = 基础课 且 final_fee 的金额；
|
||
o 附加课金额：汇总 service_type = 附加课 且 final_fee 的金额。
|
||
• is_canceled = 1 的废除记录：
|
||
o 其 final_fee 可能为 0（完全未计费）或为部分计费金额（已服务部分计费，未服务部分不计费）；
|
||
o DWS 订单层仅按 final_fee 汇总，因此废除未计费部分不会计入订单有效流水。
|
||
如需分析“助教服务原本应收与实际入账”的差额，可在专题报表中使用 DWD 助教事实表的 base_fee 与 final_fee 差值，并结合助教废除表中的原因字段进行拆解。
|
||
|
||
4. total_coupon_deduction（团购券抵扣金额）
|
||
o 来源：FACT_COUPON_USAGE.deduct_amount
|
||
o 过滤：状态为正常核销（如存在 status 字段需排除异常状态）
|
||
o 聚合：按订单维度求和。
|
||
5. member_discount_amount 与 manual_discount_amount
|
||
o 台费、助教层面通常存在明确的会员折扣字段和手工折扣字段；
|
||
o 商品层面若无法区分折扣类型，可采用保守规则：
|
||
将商品 discount_amount 统一计入 member_discount_amount（或由业务口径统一归类）；
|
||
manual_discount_amount 至少包括台费与助教的手工折扣；
|
||
o 若未来商品折扣可拆分为“会员折扣 vs 手工折扣”，对应字段可再调整拆分逻辑。
|
||
6. order_original_amount、order_final_amount
|
||
o 按公式计算，确保与明细和小票金额一致；
|
||
o 优先以 DWD 汇总为主，小票 ODS 用于对账和异常检查。
|
||
4.3.2 记账流水口径（Book Flow）
|
||
记账流水只关注“消费结构科目”，不关心支付方式与资金来源，不包含充值，也不直接回滚退款。
|
||
1. book_table_flow（台费记账流水）
|
||
o 来源表：FACT_TABLE_USAGE
|
||
o 过滤：order_trade_no = 当前订单，is_canceled = 0
|
||
o 聚合：book_table_flow = SUM(final_table_fee)
|
||
o 含义：已经扣除所有优惠和券抵扣后记入“台费”科目的营业收入。
|
||
2. book_assistant_flow（助教记账流水）
|
||
o 来源表：FACT_ASSISTANT_SERVICE
|
||
o 过滤：order_trade_no = 当前订单，is_canceled = 0
|
||
o 聚合：book_assistant_flow = SUM(final_fee)
|
||
o 含义：仅统计实际完成并计费的助教服务。
|
||
3. book_goods_flow（商品记账流水）
|
||
o 来源表：FACT_SALE_ITEM
|
||
o 过滤：order_trade_no = 当前订单，is_gift = 0（排除赠品）
|
||
o 聚合：book_goods_flow = SUM(final_amount)
|
||
o 含义：不计入赠送商品的金额，赠品不构成营业收入。
|
||
4. book_group_flow（团购记账流水）
|
||
o 来源表：FACT_COUPON_USAGE
|
||
o 过滤：order_trade_no = 当前订单，状态为正常核销
|
||
o 聚合：book_group_flow = SUM(settle_price)
|
||
o 含义：按团购“平台结算价”入账，而非券面值。多张券累加。
|
||
5. book_order_flow（订单记账总流水）
|
||
o 定义：
|
||
book_order_flow = book_table_flow + book_assistant_flow + book_goods_flow + book_group_flow
|
||
o 用途：
|
||
回答“这笔订单在台费 / 助教 / 商品 / 团购各记入了多少营业收入”；
|
||
为各科目营业结构占比分析提供基础。
|
||
o 退款：记账流水不直接冲减退款；退款在 FACT_REFUND 和 refund_amount 中单独体现。如需“记账净流水”口径，可在报表层：
|
||
记账净流水 = SUM(book_order_flow) - 对应退款折返金额。
|
||
4.3.3 订单有效流水口径（Effective Order Flow）
|
||
订单有效流水从资金实际流入视角定义，关注“新增外部资金 + 团购结算收入”。
|
||
基本原则
|
||
• 储值卡支付：视为内部资金在储值账户与消费之间转移，不计入“新增资金流入”，但其对应的消费仍计入各 book_* 流水。
|
||
• 非储值类支付（现金、微信、支付宝、银行卡等）：视为门店真实流入资金。
|
||
• 充值：
|
||
o 充值发生时（非储值支付充值到储值账户）计入有效流水；
|
||
o 之后使用储值卡消费时不再重复计入有效流水。
|
||
• 团购：按平台结算价计入有效流水（通过 book_group_flow 反映）。
|
||
1）非储值消费款 order_effective_consume_cash
|
||
• 来源：FACT_PAYMENT + 当前订单类型判断。
|
||
• 支付过滤：
|
||
o 仅统计本订单中支付方式为“非储值类”的支付记录（如 pay_method_code ∈【现金、微信、支付宝、银行卡…】），排除储值卡。
|
||
• 业务前提：
|
||
o 当前项目约定单笔订单不会同时包含“消费 + 充值”，即订单要么是消费单，要么是充值单；
|
||
o 在此前提下可采用简单口径：
|
||
若订单为消费单（存在台费/商品/助教等消费，且无充值行）：
|
||
order_effective_consume_cash = 该订单所有非储值支付总额
|
||
order_effective_recharge_cash = 0
|
||
如未来出现同单既消费又充值的复杂场景，则需在 ETL 中按明细拆分非储值金额并按消费与充值比例分摊。
|
||
2）非储值充值款 order_effective_recharge_cash
|
||
• 来源：FACT_PAYMENT + 充值类事实（如 FACT_RECHARGE 或 FACT_BALANCE_CHANGE 中的充值记录）。
|
||
• 对于标记为“充值业务”的订单（无消费，仅充值）：
|
||
o order_effective_recharge_cash = 本订单所有非储值类支付总额
|
||
o order_effective_consume_cash = 0
|
||
• 含义：表示门店通过本订单向会员账户“存入”的真实资金，在充值时计入有效流水，后续消费只在消费端反映 book_* 流水。
|
||
3）订单有效流水 order_effective_flow
|
||
• 定义：
|
||
order_effective_flow = order_effective_consume_cash + order_effective_recharge_cash + book_group_flow
|
||
• 解释：
|
||
o order_effective_consume_cash：非储值支付直接形成的消费收入；
|
||
o order_effective_recharge_cash：非储值支付形成的充值收入；
|
||
o book_group_flow：团购平台以结算价方式为门店带来的收入（本质上是平台代收后结算）。
|
||
• 含义：
|
||
o 从财务角度，可简单理解为“本订单为门店带来的对外现金收入”，包含消费类收入与充值类收入，并将团购结算价纳入视为一类外部资金流入。
|
||
• 聚合使用：
|
||
o 某一周期（自然日 / 自然月等）的有效流水：
|
||
SUM(order_effective_flow)
|
||
o 若需净有效流水口径，可在报表层按：
|
||
净有效流水 = SUM(order_effective_flow) - SUM(refund_amount)
|
||
是否将退款冲减有效流水取决于财务要求，可在指标定义中进一步固化。
|
||
4) 充值单识别：
|
||
若某 order_settle_id 在 FACT_TABLE_USAGE / FACT_SALE_ITEM / FACT_ASSISTANT_SERVICE 中均无记录，
|
||
但在 FACT_RECHARGE 或 BALANCE_CHANGE（type=充值）中存在记录，则将该订单标记为 '充值订单'；
|
||
其对应的非储值支付金额归入 order_effective_recharge_cash。
|
||
________________________________________
|
||
4.4 指标示例与分析口径
|
||
基于 DWS_ORDER_SUMMARY，可直接或衍生出常用分析指标，包括但不限于：
|
||
• 客单价：
|
||
o 可用 order_final_amount 或 total_paid_amount 做为客单消费额；
|
||
o 周期客单价 = 周期内消费总额 / 订单数。
|
||
• 会员贡献率：
|
||
o 会员订单占比：member_flag = 1 的订单数 / 总订单数；
|
||
o 会员金额贡献：SUM(order_final_amount WHERE member_flag = 1) / SUM(order_final_amount)。
|
||
• 折扣率：
|
||
o 订单折扣率：
|
||
(member_discount_amount + manual_discount_amount + total_coupon_deduction) / order_original_amount。
|
||
• 商品丰富度与平均件数：
|
||
o 每单商品数：AVG(total_item_quantity)；
|
||
o 商品行数与平均行数：AVG(item_count)。
|
||
• 支付结构：
|
||
o 非储值 vs 储值：external_paid_amount 与 stored_card_deduct 的占比；
|
||
o 若保留 pay_xxx 字段，可直接分析各支付渠道结构。
|
||
• 经营结构（记账流水视角）：
|
||
o 台费 / 商品 / 助教 / 团购占比：
|
||
各 book_*_flow / book_order_flow。
|
||
• 有效流水与净收入：
|
||
o 有效流水：SUM(order_effective_flow)；
|
||
o 净收入（资金口径）：SUM(order_effective_flow) - SUM(refund_amount) 或直接使用 SUM(net_income)，两者口径需在报表层统一。
|
||
________________________________________
|
||
4.5 注意事项
|
||
• 时间口径统一按自然日（order_date）进行日粒度汇总，暂不区分时段（闲时/忙时）类别。
|
||
• 支付方式、订单状态等枚举值应通过维表转义，DWS 层只保留必要的代码与少量关键标签，避免在宽表中写死业务枚举。
|
||
• 多门店扩展：site_id 是主键和分组维度的一部分，可直接按门店、城市、区域等维度进行汇总分析。
|
||
• 退款：
|
||
o 不直接回滚记账流水字段（book_*）和有效流水字段；
|
||
o 通过 refund_amount 在报表层定义“净收入 / 净有效流水”等指标实现冲减。
|
||
|