1291 lines
51 KiB
SQL
1291 lines
51 KiB
SQL
-- =============================================================================
|
||
-- etl_feiqiu / dwd（明细数据层）
|
||
-- 生成日期：2026-02-23
|
||
-- 来源：测试库（通过脚本自动导出）
|
||
-- =============================================================================
|
||
|
||
CREATE SCHEMA IF NOT EXISTS dwd;
|
||
|
||
-- 表
|
||
CREATE TABLE dwd.dim_assistant (
|
||
assistant_id bigint NOT NULL,
|
||
user_id bigint,
|
||
assistant_no text,
|
||
real_name text,
|
||
nickname text,
|
||
mobile text,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
team_id bigint,
|
||
team_name text,
|
||
level integer,
|
||
entry_time timestamp with time zone,
|
||
resign_time timestamp with time zone,
|
||
leave_status integer,
|
||
assistant_status integer,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_assistant_ex (
|
||
assistant_id bigint NOT NULL,
|
||
gender integer,
|
||
birth_date timestamp with time zone,
|
||
avatar text,
|
||
introduce text,
|
||
video_introduction_url text,
|
||
height numeric(5,2),
|
||
weight numeric(5,2),
|
||
shop_name text,
|
||
group_id bigint,
|
||
group_name text,
|
||
person_org_id bigint,
|
||
staff_id bigint,
|
||
staff_profile_id bigint,
|
||
assistant_grade double precision,
|
||
sum_grade double precision,
|
||
get_grade_times integer,
|
||
charge_way integer,
|
||
allow_cx integer,
|
||
is_guaranteed integer,
|
||
salary_grant_enabled integer,
|
||
entry_type integer,
|
||
entry_sign_status integer,
|
||
resign_sign_status integer,
|
||
work_status integer,
|
||
show_status integer,
|
||
show_sort integer,
|
||
online_status integer,
|
||
is_delete integer,
|
||
criticism_status integer,
|
||
create_time timestamp with time zone,
|
||
update_time timestamp with time zone,
|
||
start_time timestamp with time zone,
|
||
end_time timestamp with time zone,
|
||
last_table_id bigint,
|
||
last_table_name text,
|
||
last_update_name text,
|
||
order_trade_no bigint,
|
||
ding_talk_synced integer,
|
||
site_light_cfg_id bigint,
|
||
light_equipment_id text,
|
||
light_status integer,
|
||
is_team_leader integer,
|
||
serial_number bigint,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer,
|
||
system_role_id bigint,
|
||
job_num text,
|
||
cx_unit_price numeric(18,2),
|
||
pd_unit_price numeric(18,2)
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_goods_category (
|
||
category_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
category_name character varying(50),
|
||
alias_name character varying(50),
|
||
parent_category_id bigint,
|
||
business_name character varying(50),
|
||
tenant_goods_business_id bigint,
|
||
category_level integer,
|
||
is_leaf integer,
|
||
open_salesman integer,
|
||
sort_order integer,
|
||
is_warehousing integer,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_groupbuy_package (
|
||
groupbuy_package_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
package_name character varying(200),
|
||
package_template_id bigint,
|
||
selling_price numeric(10,2),
|
||
coupon_face_value numeric(10,2),
|
||
duration_seconds integer,
|
||
start_time timestamp with time zone,
|
||
end_time timestamp with time zone,
|
||
table_area_name character varying(100),
|
||
is_enabled integer,
|
||
is_delete integer,
|
||
create_time timestamp with time zone,
|
||
tenant_table_area_id_list character varying(512),
|
||
card_type_ids character varying(255),
|
||
sort integer,
|
||
is_first_limit boolean,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_groupbuy_package_ex (
|
||
groupbuy_package_id bigint NOT NULL,
|
||
site_name character varying(100),
|
||
usable_count integer,
|
||
date_type integer,
|
||
usable_range character varying(255),
|
||
date_info character varying(255),
|
||
start_clock character varying(16),
|
||
end_clock character varying(16),
|
||
add_start_clock character varying(16),
|
||
add_end_clock character varying(16),
|
||
area_tag_type integer,
|
||
table_area_id bigint,
|
||
tenant_table_area_id bigint,
|
||
table_area_id_list character varying(512),
|
||
group_type integer,
|
||
system_group_type integer,
|
||
package_type integer,
|
||
effective_status integer,
|
||
max_selectable_categories integer,
|
||
creator_name character varying(100),
|
||
tenant_coupon_sale_order_item_id bigint,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_member (
|
||
member_id bigint NOT NULL,
|
||
system_member_id bigint,
|
||
tenant_id bigint,
|
||
register_site_id bigint,
|
||
mobile text,
|
||
nickname text,
|
||
member_card_grade_code bigint,
|
||
member_card_grade_name text,
|
||
create_time timestamp with time zone,
|
||
update_time timestamp with time zone,
|
||
pay_money_sum numeric(18,2),
|
||
recharge_money_sum numeric(18,2),
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer,
|
||
birthday date
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_member_card_account (
|
||
member_card_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
register_site_id bigint,
|
||
tenant_member_id bigint,
|
||
system_member_id bigint,
|
||
card_type_id bigint,
|
||
member_card_grade_code bigint,
|
||
member_card_grade_code_name text,
|
||
member_card_type_name text,
|
||
member_name text,
|
||
member_mobile text,
|
||
balance numeric(18,2),
|
||
start_time timestamp with time zone,
|
||
end_time timestamp with time zone,
|
||
last_consume_time timestamp with time zone,
|
||
status integer,
|
||
is_delete integer,
|
||
principal_balance numeric(18,2),
|
||
member_grade bigint,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_member_card_account_ex (
|
||
member_card_id bigint NOT NULL,
|
||
site_name text,
|
||
tenant_name character varying(64),
|
||
tenantavatar text,
|
||
effect_site_id bigint,
|
||
able_cross_site integer,
|
||
card_physics_type integer,
|
||
card_no text,
|
||
bind_password text,
|
||
use_scene text,
|
||
denomination numeric(18,2),
|
||
create_time timestamp with time zone,
|
||
disable_start_time timestamp with time zone,
|
||
disable_end_time timestamp with time zone,
|
||
is_allow_give integer,
|
||
is_allow_order_deduct integer,
|
||
sort integer,
|
||
table_discount numeric(10,2),
|
||
goods_discount numeric(10,2),
|
||
assistant_discount numeric(10,2),
|
||
assistant_reward_discount numeric(10,2),
|
||
table_service_discount numeric(10,2),
|
||
goods_service_discount numeric(10,2),
|
||
assistant_service_discount numeric(10,2),
|
||
coupon_discount numeric(10,2),
|
||
table_discount_sub_switch integer,
|
||
goods_discount_sub_switch integer,
|
||
assistant_discount_sub_switch integer,
|
||
assistant_reward_discount_sub_switch integer,
|
||
goods_discount_range_type integer,
|
||
table_deduct_radio numeric(10,2),
|
||
goods_deduct_radio numeric(10,2),
|
||
assistant_deduct_radio numeric(10,2),
|
||
table_service_deduct_radio numeric(10,2),
|
||
goods_service_deduct_radio numeric(10,2),
|
||
assistant_service_deduct_radio numeric(10,2),
|
||
assistant_reward_deduct_radio numeric(10,2),
|
||
coupon_deduct_radio numeric(10,2),
|
||
cardsettlededuct numeric(18,2),
|
||
tablecarddeduct numeric(18,2),
|
||
tableservicecarddeduct numeric(18,2),
|
||
goodscardeduct numeric(18,2),
|
||
goodsservicecarddeduct numeric(18,2),
|
||
assistantcarddeduct numeric(18,2),
|
||
assistantservicecarddeduct numeric(18,2),
|
||
assistantrewardcarddeduct numeric(18,2),
|
||
couponcarddeduct numeric(18,2),
|
||
deliveryfeededuct numeric(18,2),
|
||
tableareaid text,
|
||
goodscategoryid text,
|
||
pdassisnatlevel text,
|
||
cxassisnatlevel text,
|
||
able_share_member_discount boolean,
|
||
electricity_deduct_radio numeric(18,4),
|
||
electricity_discount numeric(18,4),
|
||
electricity_card_deduct boolean,
|
||
recharge_freeze_balance numeric(18,2),
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_member_ex (
|
||
member_id bigint NOT NULL,
|
||
referrer_member_id bigint,
|
||
point numeric(18,2),
|
||
register_site_name text,
|
||
growth_value numeric(18,2),
|
||
user_status integer,
|
||
status integer,
|
||
person_tenant_org_id bigint,
|
||
person_tenant_org_name text,
|
||
register_source text,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_site (
|
||
site_id bigint NOT NULL,
|
||
org_id bigint,
|
||
tenant_id bigint,
|
||
shop_name text,
|
||
site_label text,
|
||
full_address text,
|
||
address text,
|
||
longitude numeric(10,6),
|
||
latitude numeric(10,6),
|
||
tenant_site_region_id bigint,
|
||
business_tel text,
|
||
site_type integer,
|
||
shop_status integer,
|
||
scd2_start_time timestamp with time zone DEFAULT now() NOT NULL,
|
||
scd2_end_time timestamp with time zone DEFAULT '9999-12-31 00:00:00+08'::timestamp with time zone,
|
||
scd2_is_current integer DEFAULT 1,
|
||
scd2_version integer DEFAULT 1
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_site_ex (
|
||
site_id bigint NOT NULL,
|
||
avatar text,
|
||
address text,
|
||
longitude numeric(9,6),
|
||
latitude numeric(9,6),
|
||
tenant_site_region_id bigint,
|
||
auto_light integer,
|
||
light_status integer,
|
||
light_type integer,
|
||
light_token text,
|
||
site_type integer,
|
||
site_label text,
|
||
attendance_enabled integer,
|
||
attendance_distance integer,
|
||
customer_service_qrcode text,
|
||
customer_service_wechat text,
|
||
fixed_pay_qrcode text,
|
||
prod_env text,
|
||
shop_status integer,
|
||
create_time timestamp with time zone,
|
||
update_time timestamp with time zone,
|
||
scd2_start_time timestamp with time zone DEFAULT now() NOT NULL,
|
||
scd2_end_time timestamp with time zone DEFAULT '9999-12-31 00:00:00+08'::timestamp with time zone,
|
||
scd2_is_current integer DEFAULT 1,
|
||
scd2_version integer DEFAULT 1
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_staff (
|
||
staff_id bigint NOT NULL,
|
||
staff_name text,
|
||
alias_name text,
|
||
mobile text,
|
||
gender integer,
|
||
job text,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
system_role_id integer,
|
||
staff_identity integer,
|
||
status integer,
|
||
leave_status integer,
|
||
entry_time timestamp with time zone,
|
||
resign_time timestamp with time zone,
|
||
is_delete integer,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_staff_ex (
|
||
staff_id bigint NOT NULL,
|
||
avatar text,
|
||
job_num text,
|
||
account_status integer,
|
||
rank_id integer,
|
||
rank_name text,
|
||
new_rank_id integer,
|
||
new_staff_identity integer,
|
||
is_reserve integer,
|
||
shop_name text,
|
||
site_label text,
|
||
tenant_org_id bigint,
|
||
system_user_id bigint,
|
||
cashier_point_id bigint,
|
||
cashier_point_name text,
|
||
group_id bigint,
|
||
group_name text,
|
||
staff_profile_id bigint,
|
||
auth_code text,
|
||
auth_code_create timestamp with time zone,
|
||
ding_talk_synced integer,
|
||
salary_grant_enabled integer,
|
||
entry_type integer,
|
||
entry_sign_status integer,
|
||
resign_sign_status integer,
|
||
criticism_status integer,
|
||
create_time timestamp with time zone,
|
||
user_roles jsonb,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_store_goods (
|
||
site_goods_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
tenant_goods_id bigint,
|
||
goods_name text,
|
||
goods_category_id bigint,
|
||
goods_second_category_id bigint,
|
||
category_level1_name text,
|
||
category_level2_name text,
|
||
batch_stock_qty integer,
|
||
sale_qty integer,
|
||
total_sales_qty integer,
|
||
sale_price numeric(18,2),
|
||
created_at timestamp with time zone,
|
||
updated_at timestamp with time zone,
|
||
avg_monthly_sales numeric(18,4),
|
||
goods_state integer,
|
||
enable_status integer,
|
||
send_state integer,
|
||
is_delete integer,
|
||
commodity_code text,
|
||
not_sale integer,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_store_goods_ex (
|
||
site_goods_id bigint NOT NULL,
|
||
site_name text,
|
||
unit text,
|
||
goods_barcode text,
|
||
goods_cover_url text,
|
||
pinyin_initial text,
|
||
stock_qty integer,
|
||
stock_secondary_qty integer,
|
||
safety_stock_qty integer,
|
||
cost_price numeric(18,4),
|
||
cost_price_type integer,
|
||
provisional_total_cost numeric(18,2),
|
||
total_purchase_cost numeric(18,2),
|
||
min_discount_price numeric(18,2),
|
||
is_discountable integer,
|
||
days_on_shelf integer,
|
||
audit_status integer,
|
||
sale_channel integer,
|
||
is_warehousing integer,
|
||
freeze_status integer,
|
||
forbid_sell_status integer,
|
||
able_site_transfer integer,
|
||
custom_label_type integer,
|
||
option_required integer,
|
||
remark text,
|
||
sort_order integer,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer,
|
||
batch_stock_quantity numeric,
|
||
time_slot_sale integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_table (
|
||
table_id bigint NOT NULL,
|
||
site_id bigint,
|
||
table_name text,
|
||
site_table_area_id bigint,
|
||
site_table_area_name text,
|
||
tenant_table_area_id bigint,
|
||
table_price numeric(18,2),
|
||
order_id bigint,
|
||
scd2_start_time timestamp with time zone DEFAULT now() NOT NULL,
|
||
scd2_end_time timestamp with time zone DEFAULT '9999-12-31 00:00:00+08'::timestamp with time zone,
|
||
scd2_is_current integer DEFAULT 1,
|
||
scd2_version integer DEFAULT 1
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_table_ex (
|
||
table_id bigint NOT NULL,
|
||
show_status integer,
|
||
is_online_reservation integer,
|
||
table_cloth_use_time integer,
|
||
table_cloth_use_cycle integer,
|
||
table_status integer,
|
||
scd2_start_time timestamp with time zone DEFAULT now() NOT NULL,
|
||
scd2_end_time timestamp with time zone DEFAULT '9999-12-31 00:00:00+08'::timestamp with time zone,
|
||
scd2_is_current integer DEFAULT 1,
|
||
scd2_version integer DEFAULT 1,
|
||
create_time timestamp with time zone,
|
||
light_status integer,
|
||
tablestatusname text,
|
||
sitename text,
|
||
applet_qr_code_url text,
|
||
audit_status integer,
|
||
charge_free integer,
|
||
delay_lights_time integer,
|
||
is_rest_area integer,
|
||
only_allow_groupon integer,
|
||
order_delay_time integer,
|
||
self_table integer,
|
||
temporary_light_second integer,
|
||
virtual_table integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_tenant_goods (
|
||
tenant_goods_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
supplier_id bigint,
|
||
category_name character varying(64),
|
||
goods_category_id bigint,
|
||
goods_second_category_id bigint,
|
||
goods_name character varying(128),
|
||
goods_number character varying(64),
|
||
unit character varying(16),
|
||
market_price numeric(18,2),
|
||
goods_state integer,
|
||
create_time timestamp with time zone,
|
||
update_time timestamp with time zone,
|
||
is_delete integer,
|
||
not_sale integer,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dim_tenant_goods_ex (
|
||
tenant_goods_id bigint NOT NULL,
|
||
remark_name character varying(128),
|
||
pinyin_initial character varying(128),
|
||
goods_cover character varying(512),
|
||
goods_bar_code character varying(64),
|
||
commodity_code character varying(64),
|
||
commodity_code_list _text,
|
||
min_discount_price numeric(18,2),
|
||
cost_price numeric(18,2),
|
||
cost_price_type integer,
|
||
able_discount integer,
|
||
sale_channel integer,
|
||
is_warehousing integer,
|
||
is_in_site boolean,
|
||
able_site_transfer integer,
|
||
common_sale_royalty integer,
|
||
point_sale_royalty integer,
|
||
out_goods_id bigint,
|
||
scd2_start_time timestamp with time zone NOT NULL,
|
||
scd2_end_time timestamp with time zone,
|
||
scd2_is_current integer,
|
||
scd2_version integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_assistant_service_log (
|
||
assistant_service_id bigint NOT NULL,
|
||
order_trade_no bigint,
|
||
order_settle_id bigint,
|
||
order_pay_id bigint,
|
||
order_assistant_id bigint,
|
||
order_assistant_type integer,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
site_table_id bigint,
|
||
tenant_member_id bigint,
|
||
system_member_id bigint,
|
||
assistant_no character varying(64),
|
||
nickname character varying(64),
|
||
site_assistant_id bigint,
|
||
user_id bigint,
|
||
assistant_team_id bigint,
|
||
person_org_id bigint,
|
||
assistant_level integer,
|
||
level_name character varying(64),
|
||
skill_id bigint,
|
||
skill_name character varying(64),
|
||
ledger_unit_price numeric(10,2),
|
||
ledger_amount numeric(10,2),
|
||
projected_income numeric(10,2),
|
||
coupon_deduct_money numeric(10,2),
|
||
income_seconds integer,
|
||
real_use_seconds integer,
|
||
add_clock integer,
|
||
create_time timestamp with time zone,
|
||
start_use_time timestamp with time zone,
|
||
last_use_time timestamp with time zone,
|
||
is_delete integer,
|
||
real_service_money numeric(18,2)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_assistant_service_log_ex (
|
||
assistant_service_id bigint NOT NULL,
|
||
table_name character varying(64),
|
||
assistant_name character varying(64),
|
||
ledger_name character varying(128),
|
||
ledger_group_name character varying(128),
|
||
ledger_count integer,
|
||
member_discount_amount numeric(10,2),
|
||
manual_discount_amount numeric(10,2),
|
||
service_money numeric(10,2),
|
||
returns_clock integer,
|
||
ledger_start_time timestamp with time zone,
|
||
ledger_end_time timestamp with time zone,
|
||
ledger_status integer,
|
||
is_confirm integer,
|
||
is_single_order integer,
|
||
is_not_responding integer,
|
||
is_trash integer,
|
||
trash_applicant_id bigint,
|
||
trash_applicant_name character varying(64),
|
||
trash_reason character varying(255),
|
||
salesman_user_id bigint,
|
||
salesman_name character varying(64),
|
||
salesman_org_id bigint,
|
||
skill_grade integer,
|
||
service_grade integer,
|
||
composite_grade numeric(5,2),
|
||
sum_grade numeric(10,2),
|
||
get_grade_times integer,
|
||
grade_status integer,
|
||
composite_grade_time timestamp with time zone,
|
||
assistant_team_name text,
|
||
operator_id bigint,
|
||
operator_name text
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_assistant_trash_event (
|
||
assistant_trash_event_id bigint NOT NULL,
|
||
site_id bigint,
|
||
table_id bigint,
|
||
table_area_id bigint,
|
||
assistant_no character varying(32),
|
||
assistant_name character varying(64),
|
||
charge_minutes_raw integer,
|
||
abolish_amount numeric(18,2),
|
||
trash_reason character varying(255),
|
||
create_time timestamp with time zone,
|
||
tenant_id bigint
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_assistant_trash_event_ex (
|
||
assistant_trash_event_id bigint NOT NULL,
|
||
table_name character varying(64),
|
||
table_area_name character varying(64),
|
||
assistant_no_int integer
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_goods_stock_movement (
|
||
site_goods_stock_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
site_goods_id bigint,
|
||
goods_name text,
|
||
goods_category_id bigint,
|
||
goods_second_category_id bigint,
|
||
unit text,
|
||
price numeric(18,4),
|
||
stock_type integer,
|
||
change_num numeric(18,4),
|
||
start_num numeric(18,4),
|
||
end_num numeric(18,4),
|
||
change_num_a numeric(18,4),
|
||
start_num_a numeric(18,4),
|
||
end_num_a numeric(18,4),
|
||
remark text,
|
||
operator_name text,
|
||
create_time timestamp with time zone,
|
||
fetched_at timestamp with time zone
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_goods_stock_summary (
|
||
site_goods_id bigint NOT NULL,
|
||
goods_name text,
|
||
goods_unit text,
|
||
goods_category_id bigint,
|
||
goods_category_second_id bigint,
|
||
category_name text,
|
||
range_start_stock numeric(18,4),
|
||
range_end_stock numeric(18,4),
|
||
range_in numeric(18,4),
|
||
range_out numeric(18,4),
|
||
range_sale numeric(18,4),
|
||
range_sale_money numeric(18,2),
|
||
range_inventory numeric(18,4),
|
||
current_stock numeric(18,4),
|
||
site_id bigint,
|
||
tenant_id bigint,
|
||
fetched_at timestamp with time zone NOT NULL
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_groupbuy_redemption (
|
||
redemption_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
table_id bigint,
|
||
tenant_table_area_id bigint,
|
||
table_charge_seconds integer,
|
||
order_trade_no bigint,
|
||
order_settle_id bigint,
|
||
order_coupon_id bigint,
|
||
coupon_origin_id bigint,
|
||
promotion_activity_id bigint,
|
||
promotion_coupon_id bigint,
|
||
order_coupon_channel integer,
|
||
ledger_unit_price numeric(18,2),
|
||
ledger_count integer,
|
||
ledger_amount numeric(18,2),
|
||
coupon_money numeric(18,2),
|
||
promotion_seconds integer,
|
||
coupon_code character varying(64),
|
||
is_single_order integer,
|
||
is_delete integer,
|
||
ledger_name character varying(128),
|
||
create_time timestamp with time zone,
|
||
member_discount_money numeric(18,2),
|
||
coupon_sale_id bigint
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_groupbuy_redemption_ex (
|
||
redemption_id bigint NOT NULL,
|
||
site_name character varying(64),
|
||
table_name character varying(64),
|
||
table_area_name character varying(64),
|
||
order_pay_id bigint,
|
||
goods_option_price numeric(18,2),
|
||
goods_promotion_money numeric(18,2),
|
||
table_service_promotion_money numeric(18,2),
|
||
assistant_promotion_money numeric(18,2),
|
||
assistant_service_promotion_money numeric(18,2),
|
||
reward_promotion_money numeric(18,2),
|
||
recharge_promotion_money numeric(18,2),
|
||
offer_type integer,
|
||
ledger_status integer,
|
||
operator_id bigint,
|
||
operator_name character varying(64),
|
||
salesman_user_id bigint,
|
||
salesman_name character varying(64),
|
||
salesman_role_id bigint,
|
||
salesman_org_id bigint,
|
||
ledger_group_name character varying(128),
|
||
table_share_money numeric(18,2),
|
||
table_service_share_money numeric(18,2),
|
||
goods_share_money numeric(18,2),
|
||
good_service_share_money numeric(18,2),
|
||
assistant_share_money numeric(18,2),
|
||
assistant_service_share_money numeric(18,2),
|
||
recharge_share_money numeric(18,2)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_member_balance_change (
|
||
balance_change_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
register_site_id bigint,
|
||
tenant_member_id bigint,
|
||
system_member_id bigint,
|
||
tenant_member_card_id bigint,
|
||
card_type_id bigint,
|
||
card_type_name character varying(32),
|
||
member_name character varying(64),
|
||
member_mobile character varying(20),
|
||
balance_before numeric(18,2),
|
||
change_amount numeric(18,2),
|
||
balance_after numeric(18,2),
|
||
from_type integer,
|
||
payment_method integer,
|
||
change_time timestamp with time zone,
|
||
is_delete integer,
|
||
remark character varying(255),
|
||
principal_before numeric(18,2),
|
||
principal_after numeric(18,2),
|
||
principal_change_amount numeric(18,2)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_member_balance_change_ex (
|
||
balance_change_id bigint NOT NULL,
|
||
pay_site_name character varying(64),
|
||
register_site_name character varying(64),
|
||
refund_amount numeric(18,2),
|
||
operator_id bigint,
|
||
operator_name character varying(64),
|
||
principal_data text,
|
||
relate_id bigint
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_payment (
|
||
payment_id bigint NOT NULL,
|
||
site_id bigint,
|
||
relate_type integer,
|
||
relate_id bigint,
|
||
pay_amount numeric(18,2),
|
||
pay_status integer,
|
||
payment_method integer,
|
||
online_pay_channel integer,
|
||
create_time timestamp with time zone,
|
||
pay_time timestamp with time zone,
|
||
pay_date date,
|
||
tenant_id bigint
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_platform_coupon_redemption (
|
||
platform_coupon_redemption_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
coupon_code character varying(64),
|
||
coupon_channel integer,
|
||
coupon_name character varying(200),
|
||
sale_price numeric(10,2),
|
||
coupon_money numeric(10,2),
|
||
coupon_free_time integer,
|
||
channel_deal_id bigint,
|
||
deal_id bigint,
|
||
group_package_id bigint,
|
||
site_order_id bigint,
|
||
table_id bigint,
|
||
certificate_id character varying(64),
|
||
verify_id character varying(64),
|
||
use_status integer,
|
||
is_delete integer,
|
||
create_time timestamp with time zone,
|
||
consume_time timestamp with time zone
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_platform_coupon_redemption_ex (
|
||
platform_coupon_redemption_id bigint NOT NULL,
|
||
coupon_cover character varying(255),
|
||
coupon_remark character varying(255),
|
||
groupon_type integer,
|
||
operator_id bigint,
|
||
operator_name character varying(50)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_recharge_order (
|
||
recharge_order_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
member_id bigint,
|
||
member_name_snapshot text,
|
||
member_phone_snapshot text,
|
||
tenant_member_card_id bigint,
|
||
member_card_type_name text,
|
||
settle_relate_id bigint,
|
||
settle_type integer,
|
||
settle_name text,
|
||
is_first integer,
|
||
pay_amount numeric(18,2),
|
||
refund_amount numeric(18,2),
|
||
point_amount numeric(18,2),
|
||
cash_amount numeric(18,2),
|
||
payment_method integer,
|
||
create_time timestamp with time zone,
|
||
pay_time timestamp with time zone,
|
||
pl_coupon_sale_amount numeric(18,2),
|
||
mervou_sales_amount numeric(18,2),
|
||
electricity_money numeric(18,2),
|
||
real_electricity_money numeric(18,2),
|
||
electricity_adjust_money numeric(18,2)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_recharge_order_ex (
|
||
recharge_order_id bigint NOT NULL,
|
||
site_name_snapshot text,
|
||
settle_status integer,
|
||
is_bind_member boolean,
|
||
is_activity boolean,
|
||
is_use_coupon boolean,
|
||
is_use_discount boolean,
|
||
can_be_revoked boolean,
|
||
online_amount numeric(18,2),
|
||
balance_amount numeric(18,2),
|
||
card_amount numeric(18,2),
|
||
coupon_amount numeric(18,2),
|
||
recharge_card_amount numeric(18,2),
|
||
gift_card_amount numeric(18,2),
|
||
prepay_money numeric(18,2),
|
||
consume_money numeric(18,2),
|
||
goods_money numeric(18,2),
|
||
real_goods_money numeric(18,2),
|
||
table_charge_money numeric(18,2),
|
||
service_money numeric(18,2),
|
||
activity_discount numeric(18,2),
|
||
all_coupon_discount numeric(18,2),
|
||
goods_promotion_money numeric(18,2),
|
||
assistant_promotion_money numeric(18,2),
|
||
assistant_pd_money numeric(18,2),
|
||
assistant_cx_money numeric(18,2),
|
||
assistant_manual_discount numeric(18,2),
|
||
coupon_sale_amount numeric(18,2),
|
||
member_discount_amount numeric(18,2),
|
||
point_discount_price numeric(18,2),
|
||
point_discount_cost numeric(18,2),
|
||
adjust_amount numeric(18,2),
|
||
rounding_amount numeric(18,2),
|
||
operator_id bigint,
|
||
operator_name_snapshot text,
|
||
salesman_user_id bigint,
|
||
salesman_name text,
|
||
order_remark text,
|
||
table_id integer,
|
||
serial_number integer,
|
||
revoke_order_id bigint,
|
||
revoke_order_name text,
|
||
revoke_time timestamp with time zone
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_refund (
|
||
refund_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
relate_type integer,
|
||
relate_id bigint,
|
||
pay_amount numeric(18,2),
|
||
channel_fee numeric(18,2),
|
||
pay_time timestamp with time zone,
|
||
create_time timestamp with time zone,
|
||
payment_method integer,
|
||
member_id bigint,
|
||
member_card_id bigint
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_refund_ex (
|
||
refund_id bigint NOT NULL,
|
||
tenant_name character varying(64),
|
||
pay_sn bigint,
|
||
refund_amount numeric(18,2),
|
||
round_amount numeric(18,2),
|
||
balance_frozen_amount numeric(18,2),
|
||
card_frozen_amount numeric(18,2),
|
||
pay_status integer,
|
||
action_type integer,
|
||
is_revoke integer,
|
||
is_delete integer,
|
||
check_status integer,
|
||
online_pay_channel integer,
|
||
online_pay_type integer,
|
||
pay_terminal integer,
|
||
pay_config_id integer,
|
||
cashier_point_id integer,
|
||
operator_id bigint,
|
||
channel_payer_id character varying(128),
|
||
channel_pay_no character varying(128)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_settlement_head (
|
||
order_settle_id bigint NOT NULL,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
site_name character varying(100),
|
||
table_id bigint,
|
||
settle_name character varying(100),
|
||
order_trade_no bigint,
|
||
create_time timestamp with time zone,
|
||
pay_time timestamp with time zone,
|
||
settle_type integer,
|
||
revoke_order_id bigint,
|
||
member_id bigint,
|
||
member_name character varying(100),
|
||
member_phone character varying(50),
|
||
member_card_account_id bigint,
|
||
member_card_type_name character varying(100),
|
||
is_bind_member boolean,
|
||
member_discount_amount numeric(18,2),
|
||
consume_money numeric(18,2),
|
||
table_charge_money numeric(18,2),
|
||
goods_money numeric(18,2),
|
||
real_goods_money numeric(18,2),
|
||
assistant_pd_money numeric(18,2),
|
||
assistant_cx_money numeric(18,2),
|
||
adjust_amount numeric(18,2),
|
||
pay_amount numeric(18,2),
|
||
balance_amount numeric(18,2),
|
||
recharge_card_amount numeric(18,2),
|
||
gift_card_amount numeric(18,2),
|
||
coupon_amount numeric(18,2),
|
||
rounding_amount numeric(18,2),
|
||
point_amount numeric(18,2),
|
||
electricity_money numeric(18,2),
|
||
real_electricity_money numeric(18,2),
|
||
electricity_adjust_money numeric(18,2),
|
||
pl_coupon_sale_amount numeric(18,2),
|
||
mervou_sales_amount numeric(18,2)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_settlement_head_ex (
|
||
order_settle_id bigint NOT NULL,
|
||
serial_number integer,
|
||
settle_status integer,
|
||
can_be_revoked boolean,
|
||
revoke_order_name character varying(100),
|
||
revoke_time timestamp with time zone,
|
||
is_first_order boolean,
|
||
service_money numeric(18,2),
|
||
cash_amount numeric(18,2),
|
||
card_amount numeric(18,2),
|
||
online_amount numeric(18,2),
|
||
refund_amount numeric(18,2),
|
||
prepay_money numeric(18,2),
|
||
payment_method integer,
|
||
coupon_sale_amount numeric(18,2),
|
||
all_coupon_discount numeric(18,2),
|
||
goods_promotion_money numeric(18,2),
|
||
assistant_promotion_money numeric(18,2),
|
||
activity_discount numeric(18,2),
|
||
assistant_manual_discount numeric(18,2),
|
||
point_discount_price numeric(18,2),
|
||
point_discount_cost numeric(18,2),
|
||
is_use_coupon boolean,
|
||
is_use_discount boolean,
|
||
is_activity boolean,
|
||
operator_name character varying(100),
|
||
salesman_name character varying(100),
|
||
order_remark character varying(255),
|
||
operator_id bigint,
|
||
salesman_user_id bigint
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_store_goods_sale (
|
||
store_goods_sale_id bigint NOT NULL,
|
||
order_trade_no bigint,
|
||
order_settle_id bigint,
|
||
order_pay_id bigint,
|
||
order_goods_id bigint,
|
||
site_id bigint,
|
||
tenant_id bigint,
|
||
site_goods_id bigint,
|
||
tenant_goods_id bigint,
|
||
tenant_goods_category_id bigint,
|
||
tenant_goods_business_id bigint,
|
||
site_table_id bigint,
|
||
ledger_name character varying(200),
|
||
ledger_group_name character varying(100),
|
||
ledger_unit_price numeric(18,2),
|
||
ledger_count integer,
|
||
ledger_amount numeric(18,2),
|
||
discount_money numeric(18,2),
|
||
real_goods_money numeric(18,2),
|
||
cost_money numeric(18,2),
|
||
ledger_status integer,
|
||
is_delete integer,
|
||
create_time timestamp with time zone,
|
||
coupon_share_money numeric(18,2),
|
||
discount_price numeric(18,2)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_store_goods_sale_ex (
|
||
store_goods_sale_id bigint NOT NULL,
|
||
legacy_order_goods_id bigint,
|
||
site_name text,
|
||
legacy_site_id bigint,
|
||
goods_remark text,
|
||
option_value_name text,
|
||
operator_name text,
|
||
open_salesman_flag integer,
|
||
salesman_user_id bigint,
|
||
salesman_name text,
|
||
salesman_role_id bigint,
|
||
salesman_org_id bigint,
|
||
discount_money numeric(18,2),
|
||
returns_number integer,
|
||
coupon_deduct_money numeric(18,2),
|
||
member_discount_amount numeric(18,2),
|
||
point_discount_money numeric(18,2),
|
||
point_discount_money_cost numeric(18,2),
|
||
package_coupon_id bigint,
|
||
order_coupon_id bigint,
|
||
member_coupon_id bigint,
|
||
option_price numeric(18,2),
|
||
option_member_discount_money numeric(18,2),
|
||
option_coupon_deduct_money numeric(18,2),
|
||
push_money numeric(18,2),
|
||
is_single_order integer,
|
||
sales_type integer,
|
||
operator_id bigint
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_table_fee_adjust (
|
||
table_fee_adjust_id bigint NOT NULL,
|
||
order_trade_no bigint,
|
||
order_settle_id bigint,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
table_id bigint,
|
||
table_area_id bigint,
|
||
table_area_name character varying(64),
|
||
tenant_table_area_id bigint,
|
||
ledger_amount numeric(18,2),
|
||
ledger_status integer,
|
||
is_delete integer,
|
||
adjust_time timestamp with time zone,
|
||
table_name text,
|
||
table_price numeric(18,2),
|
||
charge_free boolean
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_table_fee_adjust_ex (
|
||
table_fee_adjust_id bigint NOT NULL,
|
||
adjust_type integer,
|
||
ledger_count integer,
|
||
ledger_name character varying(128),
|
||
applicant_name character varying(64),
|
||
operator_name character varying(64),
|
||
applicant_id bigint,
|
||
operator_id bigint,
|
||
area_type_id bigint,
|
||
site_table_area_id bigint,
|
||
site_table_area_name text,
|
||
site_name text,
|
||
tenant_name text
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_table_fee_log (
|
||
table_fee_log_id bigint NOT NULL,
|
||
order_trade_no bigint,
|
||
order_settle_id bigint,
|
||
order_pay_id bigint,
|
||
tenant_id bigint,
|
||
site_id bigint,
|
||
site_table_id bigint,
|
||
site_table_area_id bigint,
|
||
site_table_area_name character varying(64),
|
||
tenant_table_area_id bigint,
|
||
member_id bigint,
|
||
ledger_name character varying(64),
|
||
ledger_unit_price numeric(18,2),
|
||
ledger_count integer,
|
||
ledger_amount numeric(18,2),
|
||
real_table_charge_money numeric(18,2),
|
||
coupon_promotion_amount numeric(18,2),
|
||
member_discount_amount numeric(18,2),
|
||
adjust_amount numeric(18,2),
|
||
real_table_use_seconds integer,
|
||
add_clock_seconds integer,
|
||
start_use_time timestamp with time zone,
|
||
ledger_end_time timestamp with time zone,
|
||
create_time timestamp with time zone,
|
||
ledger_status integer,
|
||
is_single_order integer,
|
||
is_delete integer,
|
||
activity_discount_amount numeric(18,2),
|
||
real_service_money numeric(18,2)
|
||
);
|
||
|
||
CREATE TABLE dwd.dwd_table_fee_log_ex (
|
||
table_fee_log_id bigint NOT NULL,
|
||
operator_name character varying(64),
|
||
salesman_name character varying(64),
|
||
used_card_amount numeric(18,2),
|
||
service_money numeric(18,2),
|
||
mgmt_fee numeric(18,2),
|
||
fee_total numeric(18,2),
|
||
ledger_start_time timestamp with time zone,
|
||
last_use_time timestamp with time zone,
|
||
operator_id bigint,
|
||
salesman_user_id bigint,
|
||
salesman_org_id bigint,
|
||
order_consumption_type integer
|
||
);
|
||
|
||
-- 约束（主键 / 唯一 / 外键）
|
||
ALTER TABLE dwd.dim_assistant ADD CONSTRAINT dim_assistant_pkey PRIMARY KEY (assistant_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_assistant_ex ADD CONSTRAINT dim_assistant_ex_pkey PRIMARY KEY (assistant_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_goods_category ADD CONSTRAINT dim_goods_category_pkey PRIMARY KEY (category_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_groupbuy_package ADD CONSTRAINT dim_groupbuy_package_pkey PRIMARY KEY (groupbuy_package_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_groupbuy_package_ex ADD CONSTRAINT dim_groupbuy_package_ex_pkey PRIMARY KEY (groupbuy_package_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_member ADD CONSTRAINT dim_member_pkey PRIMARY KEY (member_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_member_card_account ADD CONSTRAINT dim_member_card_account_pkey PRIMARY KEY (member_card_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_member_card_account_ex ADD CONSTRAINT dim_member_card_account_ex_pkey PRIMARY KEY (member_card_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_member_ex ADD CONSTRAINT dim_member_ex_pkey PRIMARY KEY (member_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_site ADD CONSTRAINT dim_site_pkey PRIMARY KEY (site_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_site_ex ADD CONSTRAINT dim_site_ex_pkey PRIMARY KEY (site_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_staff ADD CONSTRAINT dim_staff_pkey PRIMARY KEY (staff_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_staff_ex ADD CONSTRAINT dim_staff_ex_pkey PRIMARY KEY (staff_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_store_goods ADD CONSTRAINT dim_store_goods_pkey PRIMARY KEY (site_goods_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_store_goods_ex ADD CONSTRAINT dim_store_goods_ex_pkey PRIMARY KEY (site_goods_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_table ADD CONSTRAINT dim_table_pkey PRIMARY KEY (table_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_table_ex ADD CONSTRAINT dim_table_ex_pkey PRIMARY KEY (table_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_tenant_goods ADD CONSTRAINT dim_tenant_goods_pkey PRIMARY KEY (tenant_goods_id, scd2_start_time);
|
||
ALTER TABLE dwd.dim_tenant_goods_ex ADD CONSTRAINT dim_tenant_goods_ex_pkey PRIMARY KEY (tenant_goods_id, scd2_start_time);
|
||
ALTER TABLE dwd.dwd_assistant_service_log ADD CONSTRAINT dwd_assistant_service_log_pkey PRIMARY KEY (assistant_service_id);
|
||
ALTER TABLE dwd.dwd_assistant_service_log_ex ADD CONSTRAINT dwd_assistant_service_log_ex_pkey PRIMARY KEY (assistant_service_id);
|
||
ALTER TABLE dwd.dwd_assistant_trash_event ADD CONSTRAINT dwd_assistant_trash_event_pkey PRIMARY KEY (assistant_trash_event_id);
|
||
ALTER TABLE dwd.dwd_assistant_trash_event_ex ADD CONSTRAINT dwd_assistant_trash_event_ex_pkey PRIMARY KEY (assistant_trash_event_id);
|
||
ALTER TABLE dwd.dwd_goods_stock_movement ADD CONSTRAINT dwd_goods_stock_movement_pkey PRIMARY KEY (site_goods_stock_id);
|
||
ALTER TABLE dwd.dwd_goods_stock_summary ADD CONSTRAINT dwd_goods_stock_summary_pkey PRIMARY KEY (site_goods_id, fetched_at);
|
||
ALTER TABLE dwd.dwd_groupbuy_redemption ADD CONSTRAINT dwd_groupbuy_redemption_pkey PRIMARY KEY (redemption_id);
|
||
ALTER TABLE dwd.dwd_groupbuy_redemption_ex ADD CONSTRAINT dwd_groupbuy_redemption_ex_pkey PRIMARY KEY (redemption_id);
|
||
ALTER TABLE dwd.dwd_member_balance_change ADD CONSTRAINT dwd_member_balance_change_pkey PRIMARY KEY (balance_change_id);
|
||
ALTER TABLE dwd.dwd_member_balance_change_ex ADD CONSTRAINT dwd_member_balance_change_ex_pkey PRIMARY KEY (balance_change_id);
|
||
ALTER TABLE dwd.dwd_payment ADD CONSTRAINT dwd_payment_pkey PRIMARY KEY (payment_id);
|
||
ALTER TABLE dwd.dwd_platform_coupon_redemption ADD CONSTRAINT dwd_platform_coupon_redemption_pkey PRIMARY KEY (platform_coupon_redemption_id);
|
||
ALTER TABLE dwd.dwd_platform_coupon_redemption_ex ADD CONSTRAINT dwd_platform_coupon_redemption_ex_pkey PRIMARY KEY (platform_coupon_redemption_id);
|
||
ALTER TABLE dwd.dwd_recharge_order ADD CONSTRAINT dwd_recharge_order_pkey PRIMARY KEY (recharge_order_id);
|
||
ALTER TABLE dwd.dwd_recharge_order_ex ADD CONSTRAINT dwd_recharge_order_ex_pkey PRIMARY KEY (recharge_order_id);
|
||
ALTER TABLE dwd.dwd_refund ADD CONSTRAINT dwd_refund_pkey PRIMARY KEY (refund_id);
|
||
ALTER TABLE dwd.dwd_refund_ex ADD CONSTRAINT dwd_refund_ex_pkey PRIMARY KEY (refund_id);
|
||
ALTER TABLE dwd.dwd_settlement_head ADD CONSTRAINT dwd_settlement_head_pkey PRIMARY KEY (order_settle_id);
|
||
ALTER TABLE dwd.dwd_settlement_head_ex ADD CONSTRAINT dwd_settlement_head_ex_pkey PRIMARY KEY (order_settle_id);
|
||
ALTER TABLE dwd.dwd_store_goods_sale ADD CONSTRAINT dwd_store_goods_sale_pkey PRIMARY KEY (store_goods_sale_id);
|
||
ALTER TABLE dwd.dwd_store_goods_sale_ex ADD CONSTRAINT dwd_store_goods_sale_ex_pkey PRIMARY KEY (store_goods_sale_id);
|
||
ALTER TABLE dwd.dwd_table_fee_adjust ADD CONSTRAINT dwd_table_fee_adjust_pkey PRIMARY KEY (table_fee_adjust_id);
|
||
ALTER TABLE dwd.dwd_table_fee_adjust_ex ADD CONSTRAINT dwd_table_fee_adjust_ex_pkey PRIMARY KEY (table_fee_adjust_id);
|
||
ALTER TABLE dwd.dwd_table_fee_log ADD CONSTRAINT dwd_table_fee_log_pkey PRIMARY KEY (table_fee_log_id);
|
||
ALTER TABLE dwd.dwd_table_fee_log_ex ADD CONSTRAINT dwd_table_fee_log_ex_pkey PRIMARY KEY (table_fee_log_id);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_dim_assistant_pk_current_bc634b74 ON dwd.dim_assistant USING btree (assistant_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_assistant_time_pk_0ac14454 ON dwd.dim_assistant USING btree (scd2_start_time, assistant_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_assistant_time_scd2_start_time ON dwd.dim_assistant USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_assistant_ex_pk_current_51808ec0 ON dwd.dim_assistant_ex USING btree (assistant_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_assistant_ex_time_create_time ON dwd.dim_assistant_ex USING btree (create_time);
|
||
CREATE INDEX idx_dim_assistant_ex_time_pk_6e7ff61a ON dwd.dim_assistant_ex USING btree (create_time, assistant_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_assistant_ex_time_pk_9ad390fc ON dwd.dim_assistant_ex USING btree (scd2_start_time, assistant_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_assistant_ex_time_scd2_start_time ON dwd.dim_assistant_ex USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_goods_category_pk_current_7262274e ON dwd.dim_goods_category USING btree (category_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_goods_category_time_pk_e2ede123 ON dwd.dim_goods_category USING btree (scd2_start_time, category_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_goods_category_time_scd2_start_time ON dwd.dim_goods_category USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_groupbuy_package_pk_current_45dd4183 ON dwd.dim_groupbuy_package USING btree (groupbuy_package_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_groupbuy_package_time_create_time ON dwd.dim_groupbuy_package USING btree (create_time);
|
||
CREATE INDEX idx_dim_groupbuy_package_time_pk_4e2995ea ON dwd.dim_groupbuy_package USING btree (create_time, groupbuy_package_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_groupbuy_package_time_pk_7323ec4c ON dwd.dim_groupbuy_package USING btree (scd2_start_time, groupbuy_package_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_groupbuy_package_time_scd2_start_time ON dwd.dim_groupbuy_package USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_groupbuy_package_ex_pk_current_22d563ef ON dwd.dim_groupbuy_package_ex USING btree (groupbuy_package_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_groupbuy_package_ex_time_pk_13a40726 ON dwd.dim_groupbuy_package_ex USING btree (scd2_start_time, groupbuy_package_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_groupbuy_package_ex_time_scd2_start_time ON dwd.dim_groupbuy_package_ex USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_member_pk_current_af4e2da3 ON dwd.dim_member USING btree (member_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_member_time_create_time ON dwd.dim_member USING btree (create_time);
|
||
CREATE INDEX idx_dim_member_time_pk_6d25fa90 ON dwd.dim_member USING btree (scd2_start_time, member_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_member_time_pk_create_time_member_id_scd2_start_time ON dwd.dim_member USING btree (create_time, member_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_member_time_scd2_start_time ON dwd.dim_member USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_member_card_account_pk_current_18015198 ON dwd.dim_member_card_account USING btree (member_card_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_member_card_account_time_pk_7edba1df ON dwd.dim_member_card_account USING btree (scd2_start_time, member_card_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_member_card_account_time_scd2_start_time ON dwd.dim_member_card_account USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_member_card_account_ex_pk_current_51b822d3 ON dwd.dim_member_card_account_ex USING btree (member_card_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_member_card_account_ex_time_create_time ON dwd.dim_member_card_account_ex USING btree (create_time);
|
||
CREATE INDEX idx_dim_member_card_account_ex_time_pk_51225636 ON dwd.dim_member_card_account_ex USING btree (create_time, member_card_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_member_card_account_ex_time_pk_e69470a8 ON dwd.dim_member_card_account_ex USING btree (scd2_start_time, member_card_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_member_card_account_ex_time_scd2_start_time ON dwd.dim_member_card_account_ex USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_member_ex_pk_current_517c3be8 ON dwd.dim_member_ex USING btree (member_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_member_ex_time_pk_59b898b6 ON dwd.dim_member_ex USING btree (scd2_start_time, member_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_member_ex_time_scd2_start_time ON dwd.dim_member_ex USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_site_pk_current_site_id_scd2_start_time_scd2_is_current ON dwd.dim_site USING btree (site_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_site_time_pk_scd2_start_time_site_id_scd2_start_time ON dwd.dim_site USING btree (scd2_start_time, site_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_site_time_scd2_start_time ON dwd.dim_site USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_site_ex_pk_current_14063084 ON dwd.dim_site_ex USING btree (site_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_site_ex_time_create_time ON dwd.dim_site_ex USING btree (create_time);
|
||
CREATE INDEX idx_dim_site_ex_time_pk_create_time_site_id_scd2_start_time ON dwd.dim_site_ex USING btree (create_time, site_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_site_ex_time_pk_scd2_start_time_site_id_scd2_start_time ON dwd.dim_site_ex USING btree (scd2_start_time, site_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_site_ex_time_scd2_start_time ON dwd.dim_site_ex USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_store_goods_pk_current_7bb17340 ON dwd.dim_store_goods USING btree (site_goods_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_store_goods_time_pk_6b7a7c68 ON dwd.dim_store_goods USING btree (scd2_start_time, site_goods_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_store_goods_time_scd2_start_time ON dwd.dim_store_goods USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_store_goods_ex_pk_current_71893ecc ON dwd.dim_store_goods_ex USING btree (site_goods_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_store_goods_ex_time_pk_febe826d ON dwd.dim_store_goods_ex USING btree (scd2_start_time, site_goods_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_store_goods_ex_time_scd2_start_time ON dwd.dim_store_goods_ex USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_table_pk_current_b3f86378 ON dwd.dim_table USING btree (table_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_table_time_pk_scd2_start_time_table_id_scd2_start_time ON dwd.dim_table USING btree (scd2_start_time, table_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_table_time_scd2_start_time ON dwd.dim_table USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_table_ex_pk_current_2c58ddb2 ON dwd.dim_table_ex USING btree (table_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_table_ex_time_pk_0f61836a ON dwd.dim_table_ex USING btree (scd2_start_time, table_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_table_ex_time_scd2_start_time ON dwd.dim_table_ex USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_tenant_goods_pk_current_0bcfd7cc ON dwd.dim_tenant_goods USING btree (tenant_goods_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_tenant_goods_time_create_time ON dwd.dim_tenant_goods USING btree (create_time);
|
||
CREATE INDEX idx_dim_tenant_goods_time_pk_67f20fc0 ON dwd.dim_tenant_goods USING btree (create_time, tenant_goods_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_tenant_goods_time_pk_9f389a4c ON dwd.dim_tenant_goods USING btree (scd2_start_time, tenant_goods_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_tenant_goods_time_scd2_start_time ON dwd.dim_tenant_goods USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dim_tenant_goods_ex_pk_current_87cb525a ON dwd.dim_tenant_goods_ex USING btree (tenant_goods_id, scd2_start_time, scd2_is_current);
|
||
CREATE INDEX idx_dim_tenant_goods_ex_time_pk_464de091 ON dwd.dim_tenant_goods_ex USING btree (scd2_start_time, tenant_goods_id, scd2_start_time);
|
||
CREATE INDEX idx_dim_tenant_goods_ex_time_scd2_start_time ON dwd.dim_tenant_goods_ex USING btree (scd2_start_time);
|
||
CREATE INDEX idx_dwd_assistant_service_log_time_create_time ON dwd.dwd_assistant_service_log USING btree (create_time);
|
||
CREATE INDEX idx_dwd_assistant_service_log_time_pk_118fd0d3 ON dwd.dwd_assistant_service_log USING btree (create_time, assistant_service_id);
|
||
CREATE INDEX idx_dwd_assistant_service_log_time_pk_3fb2dede ON dwd.dwd_assistant_service_log USING btree (start_use_time, assistant_service_id);
|
||
CREATE INDEX idx_dwd_assistant_service_log_time_start_use_time ON dwd.dwd_assistant_service_log USING btree (start_use_time);
|
||
CREATE INDEX idx_dwd_assistant_trash_event_time_create_time ON dwd.dwd_assistant_trash_event USING btree (create_time);
|
||
CREATE INDEX idx_dwd_assistant_trash_event_time_pk_0b64af2a ON dwd.dwd_assistant_trash_event USING btree (create_time, assistant_trash_event_id);
|
||
CREATE INDEX idx_dwd_groupbuy_redemption_time_create_time ON dwd.dwd_groupbuy_redemption USING btree (create_time);
|
||
CREATE INDEX idx_dwd_groupbuy_redemption_time_pk_create_time_redemption_id ON dwd.dwd_groupbuy_redemption USING btree (create_time, redemption_id);
|
||
CREATE INDEX idx_dwd_payment_time_create_time ON dwd.dwd_payment USING btree (create_time);
|
||
CREATE INDEX idx_dwd_payment_time_pay_time ON dwd.dwd_payment USING btree (pay_time);
|
||
CREATE INDEX idx_dwd_payment_time_pk_create_time_payment_id ON dwd.dwd_payment USING btree (create_time, payment_id);
|
||
CREATE INDEX idx_dwd_payment_time_pk_pay_time_payment_id ON dwd.dwd_payment USING btree (pay_time, payment_id);
|
||
CREATE INDEX idx_dwd_platform_coupon_redemption_time_create_time ON dwd.dwd_platform_coupon_redemption USING btree (create_time);
|
||
CREATE INDEX idx_dwd_platform_coupon_redemption_time_pk_d92b2b46 ON dwd.dwd_platform_coupon_redemption USING btree (create_time, platform_coupon_redemption_id);
|
||
CREATE INDEX idx_dwd_recharge_order_time_create_time ON dwd.dwd_recharge_order USING btree (create_time);
|
||
CREATE INDEX idx_dwd_recharge_order_time_pay_time ON dwd.dwd_recharge_order USING btree (pay_time);
|
||
CREATE INDEX idx_dwd_recharge_order_time_pk_create_time_recharge_order_id ON dwd.dwd_recharge_order USING btree (create_time, recharge_order_id);
|
||
CREATE INDEX idx_dwd_recharge_order_time_pk_pay_time_recharge_order_id ON dwd.dwd_recharge_order USING btree (pay_time, recharge_order_id);
|
||
CREATE INDEX idx_dwd_refund_time_create_time ON dwd.dwd_refund USING btree (create_time);
|
||
CREATE INDEX idx_dwd_refund_time_pay_time ON dwd.dwd_refund USING btree (pay_time);
|
||
CREATE INDEX idx_dwd_refund_time_pk_create_time_refund_id ON dwd.dwd_refund USING btree (create_time, refund_id);
|
||
CREATE INDEX idx_dwd_refund_time_pk_pay_time_refund_id ON dwd.dwd_refund USING btree (pay_time, refund_id);
|
||
CREATE INDEX idx_dwd_settlement_head_time_create_time ON dwd.dwd_settlement_head USING btree (create_time);
|
||
CREATE INDEX idx_dwd_settlement_head_time_pay_time ON dwd.dwd_settlement_head USING btree (pay_time);
|
||
CREATE INDEX idx_dwd_settlement_head_time_pk_create_time_order_settle_id ON dwd.dwd_settlement_head USING btree (create_time, order_settle_id);
|
||
CREATE INDEX idx_dwd_settlement_head_time_pk_pay_time_order_settle_id ON dwd.dwd_settlement_head USING btree (pay_time, order_settle_id);
|
||
CREATE INDEX idx_dwd_store_goods_sale_time_create_time ON dwd.dwd_store_goods_sale USING btree (create_time);
|
||
CREATE INDEX idx_dwd_store_goods_sale_time_pk_dcadf6d1 ON dwd.dwd_store_goods_sale USING btree (create_time, store_goods_sale_id);
|
||
CREATE INDEX idx_dwd_table_fee_log_time_create_time ON dwd.dwd_table_fee_log USING btree (create_time);
|
||
CREATE INDEX idx_dwd_table_fee_log_time_pk_create_time_table_fee_log_id ON dwd.dwd_table_fee_log USING btree (create_time, table_fee_log_id);
|
||
CREATE INDEX idx_dwd_table_fee_log_time_pk_start_use_time_table_fee_log_id ON dwd.dwd_table_fee_log USING btree (start_use_time, table_fee_log_id);
|
||
CREATE INDEX idx_dwd_table_fee_log_time_start_use_time ON dwd.dwd_table_fee_log USING btree (start_use_time);
|
||
|