95 lines
4.0 KiB
SQL
95 lines
4.0 KiB
SQL
-- ============================================================================
|
||
-- 表：ods.group_buy_package_details
|
||
-- 来源：QueryPackageCouponInfo 详情接口
|
||
-- 说明：团购套餐详情 ODS 层，存储每个 couponId 的详情原始数据
|
||
-- ============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS ods.group_buy_package_details (
|
||
-- 主键
|
||
coupon_id BIGINT NOT NULL,
|
||
|
||
-- 结构化业务字段（来自 data.groupPurchasePackage）
|
||
package_name TEXT,
|
||
duration INTEGER,
|
||
start_time TIMESTAMPTZ,
|
||
end_time TIMESTAMPTZ,
|
||
add_start_clock TEXT,
|
||
add_end_clock TEXT,
|
||
is_enabled INTEGER,
|
||
is_delete INTEGER,
|
||
site_id BIGINT,
|
||
tenant_id BIGINT,
|
||
create_time TIMESTAMPTZ,
|
||
creator_name TEXT,
|
||
|
||
-- JSONB 数组字段
|
||
table_area_ids JSONB,
|
||
table_area_names JSONB,
|
||
assistant_services JSONB,
|
||
groupon_site_infos JSONB,
|
||
package_services JSONB,
|
||
coupon_details_list JSONB,
|
||
|
||
-- ETL 元数据
|
||
content_hash TEXT,
|
||
payload JSONB,
|
||
fetched_at TIMESTAMPTZ DEFAULT now(),
|
||
|
||
-- 约束
|
||
CONSTRAINT pk_group_buy_package_details PRIMARY KEY (coupon_id)
|
||
);
|
||
|
||
-- 表注释
|
||
COMMENT ON TABLE ods.group_buy_package_details
|
||
IS '团购套餐详情 ODS：QueryPackageCouponInfo 原始数据';
|
||
|
||
-- 主键 / 结构化字段注释
|
||
COMMENT ON COLUMN ods.group_buy_package_details.coupon_id
|
||
IS '团购套餐 ID（= groupPurchasePackage.id），主键';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.package_name
|
||
IS '团购套餐名称';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.duration
|
||
IS '台费计时时长（秒）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.start_time
|
||
IS '可用日期开始';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.end_time
|
||
IS '可用日期结束';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.add_start_clock
|
||
IS '可用时段开始（如 "00:00:00"）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.add_end_clock
|
||
IS '可用时段结束（如 "1.00:00:00"）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.is_enabled
|
||
IS '是否启用（1=启用, 0=禁用）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.is_delete
|
||
IS '是否已删除（1=已删除, 0=正常）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.site_id
|
||
IS '店铺 ID';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.tenant_id
|
||
IS '租户 ID';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.create_time
|
||
IS '创建时间';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.creator_name
|
||
IS '创建人';
|
||
|
||
-- JSONB 数组字段注释
|
||
COMMENT ON COLUMN ods.group_buy_package_details.table_area_ids
|
||
IS '可用台区 ID 列表（来自 groupPurchasePackage.tableAreaId）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.table_area_names
|
||
IS '可用台区名称列表（来自 groupPurchasePackage.tableAreaNameList）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.assistant_services
|
||
IS '助教服务关联数组（来自 packageCouponAssistants，含 skillId/assistantLevel/assistantDuration）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.groupon_site_infos
|
||
IS '关联门店信息数组（来自 grouponSiteInfos，含 siteId/siteName）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.package_services
|
||
IS '套餐服务数组（来自 packagePackageService，待调研，可能为空）';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.coupon_details_list
|
||
IS '券明细数组（来自 packageCouponDetailsList，待调研，可能为空）';
|
||
|
||
-- ETL 元数据字段注释
|
||
COMMENT ON COLUMN ods.group_buy_package_details.content_hash
|
||
IS '业务字段内容哈希，用于变更检测';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.payload
|
||
IS '详情接口完整原始 JSON 响应';
|
||
COMMENT ON COLUMN ods.group_buy_package_details.fetched_at
|
||
IS 'ETL 拉取时间戳';
|