-- ============================================================================= -- etl_feiqiu / dws（汇总数据层） -- 生成日期：2026-03-15 -- 来源：测试库（通过脚本自动导出） -- ============================================================================= CREATE SCHEMA IF NOT EXISTS dws; -- 序列 CREATE SEQUENCE IF NOT EXISTS dws.cfg_area_category_category_id_seq AS integer; CREATE SEQUENCE IF NOT EXISTS dws.cfg_assistant_level_price_price_id_seq AS integer; CREATE SEQUENCE IF NOT EXISTS dws.cfg_bonus_rules_rule_id_seq AS integer; CREATE SEQUENCE IF NOT EXISTS dws.cfg_index_parameters_param_id_seq AS integer; CREATE SEQUENCE IF NOT EXISTS dws.cfg_performance_tier_tier_id_seq AS integer; CREATE SEQUENCE IF NOT EXISTS dws.cfg_skill_type_skill_type_id_seq AS integer; CREATE SEQUENCE IF NOT EXISTS dws.dws_assistant_customer_stats_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_assistant_daily_detail_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_assistant_finance_analysis_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_assistant_monthly_summary_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_assistant_order_contribution_contribution_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_assistant_project_tag_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_assistant_recharge_commission_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_assistant_salary_calc_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_finance_daily_summary_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_finance_discount_detail_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_finance_expense_summary_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_finance_income_structure_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_finance_recharge_summary_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_index_percentile_history_history_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_assistant_intimacy_intimacy_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_assistant_relation_index_relation_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_consumption_summary_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_newconv_index_newconv_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_project_tag_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_recall_index_recall_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_spending_power_index_spi_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_visit_detail_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_member_winback_index_winback_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_ml_manual_order_alloc_alloc_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_ml_manual_order_source_source_id_seq AS bigint; CREATE SEQUENCE IF NOT EXISTS dws.dws_platform_settlement_id_seq AS bigint; -- 表 CREATE TABLE dws.cfg_area_category ( category_id integer DEFAULT nextval('dws.cfg_area_category_category_id_seq'::regclass) NOT NULL, source_area_name character varying(100) NOT NULL, category_code character varying(20) NOT NULL, category_name character varying(50) NOT NULL, match_type character varying(10) DEFAULT 'EXACT'::character varying NOT NULL, match_priority integer DEFAULT 100 NOT NULL, is_active boolean DEFAULT true NOT NULL, description text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, source_table_name character varying(100) DEFAULT NULL::character varying, display_name character varying(50) DEFAULT NULL::character varying, short_name character varying(20) DEFAULT NULL::character varying ); CREATE TABLE dws.cfg_assistant_level_price ( price_id integer DEFAULT nextval('dws.cfg_assistant_level_price_price_id_seq'::regclass) NOT NULL, level_code integer NOT NULL, level_name character varying(20) NOT NULL, base_course_price numeric(10,2) NOT NULL, bonus_course_price numeric(10,2) NOT NULL, effective_from date DEFAULT '2000-01-01'::date NOT NULL, effective_to date DEFAULT '9999-12-31'::date NOT NULL, description text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.cfg_bonus_rules ( rule_id integer DEFAULT nextval('dws.cfg_bonus_rules_rule_id_seq'::regclass) NOT NULL, rule_type character varying(20) NOT NULL, rule_code character varying(30) NOT NULL, rule_name character varying(50) NOT NULL, threshold_hours numeric(10,2), rank_position integer, bonus_amount numeric(12,2) NOT NULL, is_cumulative boolean DEFAULT false NOT NULL, priority integer DEFAULT 0 NOT NULL, effective_from date DEFAULT '2000-01-01'::date NOT NULL, effective_to date DEFAULT '9999-12-31'::date NOT NULL, description text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.cfg_index_parameters ( param_id integer DEFAULT nextval('dws.cfg_index_parameters_param_id_seq'::regclass) NOT NULL, index_type character varying(50) NOT NULL, param_name character varying(100) NOT NULL, param_value numeric(14,6) NOT NULL, description text, effective_from date DEFAULT CURRENT_DATE NOT NULL, effective_to date, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.cfg_performance_tier ( tier_id integer DEFAULT nextval('dws.cfg_performance_tier_tier_id_seq'::regclass) NOT NULL, tier_code character varying(20) NOT NULL, tier_name character varying(50) NOT NULL, tier_level integer NOT NULL, min_hours numeric(10,2) NOT NULL, max_hours numeric(10,2), base_deduction numeric(10,2) DEFAULT 0 NOT NULL, bonus_deduction_ratio numeric(7,4) DEFAULT 0 NOT NULL, vacation_days integer DEFAULT 0 NOT NULL, vacation_unlimited boolean DEFAULT false NOT NULL, is_new_hire_tier boolean DEFAULT false NOT NULL, effective_from date DEFAULT '2000-01-01'::date NOT NULL, effective_to date DEFAULT '9999-12-31'::date NOT NULL, description text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.cfg_skill_type ( skill_type_id integer DEFAULT nextval('dws.cfg_skill_type_skill_type_id_seq'::regclass) NOT NULL, skill_id bigint NOT NULL, skill_name character varying(50), course_type_code character varying(10) NOT NULL, course_type_name character varying(20) NOT NULL, is_active boolean DEFAULT true NOT NULL, description text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_assistant_customer_stats ( id bigint DEFAULT nextval('dws.dws_assistant_customer_stats_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, assistant_id bigint NOT NULL, assistant_nickname character varying(50), member_id bigint NOT NULL, member_nickname character varying(100), member_mobile character varying(20), stat_date date NOT NULL, first_service_date date, last_service_date date, total_service_count integer DEFAULT 0 NOT NULL, total_service_hours numeric(10,2) DEFAULT 0 NOT NULL, total_service_amount numeric(12,2) DEFAULT 0 NOT NULL, service_count_7d integer DEFAULT 0 NOT NULL, service_count_10d integer DEFAULT 0 NOT NULL, service_count_15d integer DEFAULT 0 NOT NULL, service_count_30d integer DEFAULT 0 NOT NULL, service_count_60d integer DEFAULT 0 NOT NULL, service_count_90d integer DEFAULT 0 NOT NULL, service_hours_7d numeric(10,2) DEFAULT 0 NOT NULL, service_hours_10d numeric(10,2) DEFAULT 0 NOT NULL, service_hours_15d numeric(10,2) DEFAULT 0 NOT NULL, service_hours_30d numeric(10,2) DEFAULT 0 NOT NULL, service_hours_60d numeric(10,2) DEFAULT 0 NOT NULL, service_hours_90d numeric(10,2) DEFAULT 0 NOT NULL, service_amount_7d numeric(12,2) DEFAULT 0 NOT NULL, service_amount_10d numeric(12,2) DEFAULT 0 NOT NULL, service_amount_15d numeric(12,2) DEFAULT 0 NOT NULL, service_amount_30d numeric(12,2) DEFAULT 0 NOT NULL, service_amount_60d numeric(12,2) DEFAULT 0 NOT NULL, service_amount_90d numeric(12,2) DEFAULT 0 NOT NULL, days_since_last integer, is_active_7d boolean DEFAULT false NOT NULL, is_active_30d boolean DEFAULT false NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_assistant_daily_detail ( id bigint DEFAULT nextval('dws.dws_assistant_daily_detail_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, assistant_id bigint NOT NULL, assistant_nickname character varying(50), stat_date date NOT NULL, assistant_level_code integer, assistant_level_name character varying(20), total_service_count integer DEFAULT 0 NOT NULL, base_service_count integer DEFAULT 0 NOT NULL, bonus_service_count integer DEFAULT 0 NOT NULL, room_service_count integer DEFAULT 0 NOT NULL, total_seconds integer DEFAULT 0 NOT NULL, base_seconds integer DEFAULT 0 NOT NULL, bonus_seconds integer DEFAULT 0 NOT NULL, room_seconds integer DEFAULT 0 NOT NULL, total_hours numeric(10,2) DEFAULT 0 NOT NULL, base_hours numeric(10,2) DEFAULT 0 NOT NULL, bonus_hours numeric(10,2) DEFAULT 0 NOT NULL, room_hours numeric(10,2) DEFAULT 0 NOT NULL, total_ledger_amount numeric(12,2) DEFAULT 0 NOT NULL, base_ledger_amount numeric(12,2) DEFAULT 0 NOT NULL, bonus_ledger_amount numeric(12,2) DEFAULT 0 NOT NULL, room_ledger_amount numeric(12,2) DEFAULT 0 NOT NULL, unique_customers integer DEFAULT 0 NOT NULL, unique_tables integer DEFAULT 0 NOT NULL, trashed_seconds integer DEFAULT 0 NOT NULL, trashed_count integer DEFAULT 0 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, penalty_minutes numeric(10,2) DEFAULT 0, penalty_reason text, is_exempt boolean DEFAULT false, per_hour_contribution numeric(14,2) ); CREATE TABLE dws.dws_assistant_finance_analysis ( id bigint DEFAULT nextval('dws.dws_assistant_finance_analysis_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, stat_date date NOT NULL, assistant_id bigint NOT NULL, assistant_nickname character varying(50), revenue_total numeric(14,2) DEFAULT 0 NOT NULL, revenue_base numeric(14,2) DEFAULT 0 NOT NULL, revenue_bonus numeric(14,2) DEFAULT 0 NOT NULL, revenue_room numeric(14,2) DEFAULT 0 NOT NULL, cost_daily numeric(14,2) DEFAULT 0 NOT NULL, gross_profit numeric(14,2) DEFAULT 0 NOT NULL, gross_margin numeric(7,4) DEFAULT 0 NOT NULL, service_count integer DEFAULT 0 NOT NULL, service_hours numeric(10,2) DEFAULT 0 NOT NULL, room_service_count integer DEFAULT 0 NOT NULL, room_service_hours numeric(10,2) DEFAULT 0 NOT NULL, unique_customers integer DEFAULT 0 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_assistant_monthly_summary ( id bigint DEFAULT nextval('dws.dws_assistant_monthly_summary_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, assistant_id bigint NOT NULL, assistant_nickname character varying(50), stat_month date NOT NULL, assistant_level_code integer, assistant_level_name character varying(20), hire_date date, is_new_hire boolean DEFAULT false NOT NULL, work_days integer DEFAULT 0 NOT NULL, total_service_count integer DEFAULT 0 NOT NULL, base_service_count integer DEFAULT 0 NOT NULL, bonus_service_count integer DEFAULT 0 NOT NULL, room_service_count integer DEFAULT 0 NOT NULL, total_hours numeric(10,2) DEFAULT 0 NOT NULL, base_hours numeric(10,2) DEFAULT 0 NOT NULL, bonus_hours numeric(10,2) DEFAULT 0 NOT NULL, room_hours numeric(10,2) DEFAULT 0 NOT NULL, effective_hours numeric(10,2) DEFAULT 0 NOT NULL, trashed_hours numeric(10,2) DEFAULT 0 NOT NULL, total_ledger_amount numeric(12,2) DEFAULT 0 NOT NULL, base_ledger_amount numeric(12,2) DEFAULT 0 NOT NULL, bonus_ledger_amount numeric(12,2) DEFAULT 0 NOT NULL, room_ledger_amount numeric(12,2) DEFAULT 0 NOT NULL, unique_customers integer DEFAULT 0 NOT NULL, unique_tables integer DEFAULT 0 NOT NULL, avg_service_seconds numeric(10,2) DEFAULT 0 NOT NULL, tier_id integer, tier_code character varying(20), tier_name character varying(50), rank_by_hours integer, rank_with_ties integer, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_assistant_order_contribution ( contribution_id bigint DEFAULT nextval('dws.dws_assistant_order_contribution_contribution_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, assistant_id bigint NOT NULL, assistant_nickname character varying(100), stat_date date NOT NULL, order_gross_revenue numeric(14,2) DEFAULT 0, order_net_revenue numeric(14,2) DEFAULT 0, time_weighted_revenue numeric(14,2) DEFAULT 0, time_weighted_net_revenue numeric(14,2) DEFAULT 0, order_count integer DEFAULT 0, total_service_seconds integer DEFAULT 0, created_at timestamp with time zone DEFAULT now(), updated_at timestamp with time zone DEFAULT now() ); CREATE TABLE dws.dws_assistant_project_tag ( id bigint DEFAULT nextval('dws.dws_assistant_project_tag_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, assistant_id bigint NOT NULL, time_window character varying(40) NOT NULL, category_code character varying(30) NOT NULL, category_name character varying(50) NOT NULL, short_name character varying(10) NOT NULL, duration_seconds bigint DEFAULT 0 NOT NULL, total_seconds bigint DEFAULT 0 NOT NULL, percentage numeric(5,4) DEFAULT 0 NOT NULL, is_tagged boolean DEFAULT false NOT NULL, computed_at timestamp with time zone DEFAULT now() NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_assistant_recharge_commission ( id bigint DEFAULT nextval('dws.dws_assistant_recharge_commission_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, assistant_id bigint NOT NULL, assistant_nickname character varying(50), commission_month date NOT NULL, recharge_order_id bigint, recharge_order_no character varying(50), recharge_amount numeric(12,2) DEFAULT 0 NOT NULL, commission_amount numeric(12,2) DEFAULT 0 NOT NULL, commission_ratio numeric(7,4), import_batch_no character varying(50), import_file_name character varying(200), import_time timestamp with time zone, import_user character varying(50), remark text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_assistant_salary_calc ( id bigint DEFAULT nextval('dws.dws_assistant_salary_calc_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, assistant_id bigint NOT NULL, assistant_nickname character varying(50), salary_month date NOT NULL, assistant_level_code integer, assistant_level_name character varying(20), hire_date date, is_new_hire boolean DEFAULT false NOT NULL, effective_hours numeric(10,2) DEFAULT 0 NOT NULL, base_hours numeric(10,2) DEFAULT 0 NOT NULL, bonus_hours numeric(10,2) DEFAULT 0 NOT NULL, room_hours numeric(10,2) DEFAULT 0 NOT NULL, tier_id integer, tier_code character varying(20), tier_name character varying(50), rank_with_ties integer, base_course_price numeric(10,2) DEFAULT 0 NOT NULL, bonus_course_price numeric(10,2) DEFAULT 0 NOT NULL, base_deduction numeric(10,2) DEFAULT 0 NOT NULL, bonus_deduction_ratio numeric(7,4) DEFAULT 0 NOT NULL, base_income numeric(12,2) DEFAULT 0 NOT NULL, bonus_income numeric(12,2) DEFAULT 0 NOT NULL, room_income numeric(12,2) DEFAULT 0 NOT NULL, total_course_income numeric(12,2) DEFAULT 0 NOT NULL, sprint_bonus numeric(12,2) DEFAULT 0 NOT NULL, top_rank_bonus numeric(12,2) DEFAULT 0 NOT NULL, recharge_commission numeric(12,2) DEFAULT 0 NOT NULL, other_bonus numeric(12,2) DEFAULT 0 NOT NULL, total_bonus numeric(12,2) DEFAULT 0 NOT NULL, gross_salary numeric(12,2) DEFAULT 0 NOT NULL, vacation_days integer DEFAULT 0 NOT NULL, vacation_unlimited boolean DEFAULT false NOT NULL, calc_notes text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_finance_daily_summary ( id bigint DEFAULT nextval('dws.dws_finance_daily_summary_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, stat_date date NOT NULL, gross_amount numeric(14,2) DEFAULT 0 NOT NULL, table_fee_amount numeric(14,2) DEFAULT 0 NOT NULL, goods_amount numeric(14,2) DEFAULT 0 NOT NULL, assistant_pd_amount numeric(14,2) DEFAULT 0 NOT NULL, assistant_cx_amount numeric(14,2) DEFAULT 0 NOT NULL, discount_total numeric(14,2) DEFAULT 0 NOT NULL, discount_groupbuy numeric(14,2) DEFAULT 0 NOT NULL, discount_vip numeric(14,2) DEFAULT 0 NOT NULL, discount_gift_card numeric(14,2) DEFAULT 0 NOT NULL, discount_manual numeric(14,2) DEFAULT 0 NOT NULL, discount_rounding numeric(14,2) DEFAULT 0 NOT NULL, discount_other numeric(14,2) DEFAULT 0 NOT NULL, confirmed_income numeric(14,2) DEFAULT 0 NOT NULL, cash_inflow_total numeric(14,2) DEFAULT 0 NOT NULL, cash_pay_amount numeric(14,2) DEFAULT 0 NOT NULL, groupbuy_pay_amount numeric(14,2) DEFAULT 0 NOT NULL, platform_settlement_amount numeric(14,2) DEFAULT 0 NOT NULL, platform_fee_amount numeric(14,2) DEFAULT 0 NOT NULL, recharge_cash_inflow numeric(14,2) DEFAULT 0 NOT NULL, card_consume_total numeric(14,2) DEFAULT 0 NOT NULL, recharge_card_consume numeric(14,2) DEFAULT 0 NOT NULL, gift_card_consume numeric(14,2) DEFAULT 0 NOT NULL, cash_outflow_total numeric(14,2) DEFAULT 0 NOT NULL, cash_balance_change numeric(14,2) DEFAULT 0 NOT NULL, recharge_count integer DEFAULT 0 NOT NULL, recharge_total numeric(14,2) DEFAULT 0 NOT NULL, recharge_cash numeric(14,2) DEFAULT 0 NOT NULL, recharge_gift numeric(14,2) DEFAULT 0 NOT NULL, first_recharge_count integer DEFAULT 0 NOT NULL, first_recharge_amount numeric(14,2) DEFAULT 0 NOT NULL, renewal_count integer DEFAULT 0 NOT NULL, renewal_amount numeric(14,2) DEFAULT 0 NOT NULL, order_count integer DEFAULT 0 NOT NULL, member_order_count integer DEFAULT 0 NOT NULL, guest_order_count integer DEFAULT 0 NOT NULL, avg_order_amount numeric(12,2) DEFAULT 0 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_finance_discount_detail ( id bigint DEFAULT nextval('dws.dws_finance_discount_detail_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, stat_date date NOT NULL, discount_type_code character varying(30) NOT NULL, discount_type_name character varying(50) NOT NULL, discount_amount numeric(14,2) DEFAULT 0 NOT NULL, discount_ratio numeric(7,4) DEFAULT 0 NOT NULL, usage_count integer DEFAULT 0 NOT NULL, affected_orders integer DEFAULT 0 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_finance_expense_summary ( id bigint DEFAULT nextval('dws.dws_finance_expense_summary_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, expense_month date NOT NULL, expense_type_code character varying(30) NOT NULL, expense_type_name character varying(50) NOT NULL, expense_category character varying(20), expense_amount numeric(14,2) DEFAULT 0 NOT NULL, expense_detail text, import_batch_no character varying(50), import_file_name character varying(200), import_time timestamp with time zone, import_user character varying(50), remark text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_finance_income_structure ( id bigint DEFAULT nextval('dws.dws_finance_income_structure_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, stat_date date NOT NULL, structure_type character varying(20) NOT NULL, category_code character varying(30) NOT NULL, category_name character varying(50) NOT NULL, income_amount numeric(14,2) DEFAULT 0 NOT NULL, income_ratio numeric(7,4) DEFAULT 0 NOT NULL, order_count integer DEFAULT 0 NOT NULL, duration_minutes integer DEFAULT 0 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_finance_recharge_summary ( id bigint DEFAULT nextval('dws.dws_finance_recharge_summary_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, stat_date date NOT NULL, recharge_count integer DEFAULT 0 NOT NULL, recharge_total numeric(14,2) DEFAULT 0 NOT NULL, recharge_cash numeric(14,2) DEFAULT 0 NOT NULL, recharge_gift numeric(14,2) DEFAULT 0 NOT NULL, first_recharge_count integer DEFAULT 0 NOT NULL, first_recharge_cash numeric(14,2) DEFAULT 0 NOT NULL, first_recharge_gift numeric(14,2) DEFAULT 0 NOT NULL, first_recharge_total numeric(14,2) DEFAULT 0 NOT NULL, renewal_count integer DEFAULT 0 NOT NULL, renewal_cash numeric(14,2) DEFAULT 0 NOT NULL, renewal_gift numeric(14,2) DEFAULT 0 NOT NULL, renewal_total numeric(14,2) DEFAULT 0 NOT NULL, recharge_member_count integer DEFAULT 0 NOT NULL, new_member_count integer DEFAULT 0 NOT NULL, total_card_balance numeric(14,2) DEFAULT 0 NOT NULL, cash_card_balance numeric(14,2) DEFAULT 0 NOT NULL, gift_card_balance numeric(14,2) DEFAULT 0 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_goods_stock_daily_summary ( site_id bigint NOT NULL, tenant_id bigint, stat_date date NOT NULL, 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, range_end_stock numeric, range_in numeric, range_out numeric, range_sale numeric, range_sale_money numeric(12,2), range_inventory numeric, current_stock numeric, stat_period text DEFAULT 'daily'::text NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_goods_stock_monthly_summary ( site_id bigint NOT NULL, tenant_id bigint, stat_date date NOT NULL, 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, range_end_stock numeric, range_in numeric, range_out numeric, range_sale numeric, range_sale_money numeric(12,2), range_inventory numeric, current_stock numeric, stat_period text DEFAULT 'monthly'::text NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_goods_stock_weekly_summary ( site_id bigint NOT NULL, tenant_id bigint, stat_date date NOT NULL, 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, range_end_stock numeric, range_in numeric, range_out numeric, range_sale numeric, range_sale_money numeric(12,2), range_inventory numeric, current_stock numeric, stat_period text DEFAULT 'weekly'::text NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_index_percentile_history ( history_id bigint DEFAULT nextval('dws.dws_index_percentile_history_history_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, index_type character varying(50) NOT NULL, calc_time timestamp with time zone NOT NULL, percentile_5 numeric(14,6), percentile_95 numeric(14,6), percentile_5_smoothed numeric(14,6), percentile_95_smoothed numeric(14,6), record_count integer, min_raw_score numeric(14,6), max_raw_score numeric(14,6), avg_raw_score numeric(14,6), created_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_member_assistant_intimacy ( intimacy_id bigint DEFAULT nextval('dws.dws_member_assistant_intimacy_intimacy_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, member_id bigint NOT NULL, assistant_id bigint NOT NULL, session_count integer DEFAULT 0 NOT NULL, total_duration_minutes integer DEFAULT 0 NOT NULL, basic_session_count integer DEFAULT 0 NOT NULL, incentive_session_count integer DEFAULT 0 NOT NULL, days_since_last_session integer, attributed_recharge_count integer DEFAULT 0 NOT NULL, attributed_recharge_amount numeric(14,2) DEFAULT 0 NOT NULL, score_frequency numeric(10,4), score_recency numeric(10,4), score_recharge numeric(10,4), score_duration numeric(10,4), burst_multiplier numeric(7,4), raw_score numeric(14,6), display_score numeric(4,2), calc_time timestamp with time zone DEFAULT now() NOT NULL, calc_version integer DEFAULT 1 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_member_assistant_relation_index ( relation_id bigint DEFAULT nextval('dws.dws_member_assistant_relation_index_relation_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, member_id bigint NOT NULL, assistant_id bigint NOT NULL, session_count integer DEFAULT 0 NOT NULL, total_duration_minutes integer DEFAULT 0 NOT NULL, basic_session_count integer DEFAULT 0 NOT NULL, incentive_session_count integer DEFAULT 0 NOT NULL, days_since_last_session integer, rs_f numeric(14,6) DEFAULT 0 NOT NULL, rs_d numeric(14,6) DEFAULT 0 NOT NULL, rs_r numeric(14,6) DEFAULT 0 NOT NULL, rs_raw numeric(14,6) DEFAULT 0 NOT NULL, rs_display numeric(4,2) DEFAULT 0 NOT NULL, os_share numeric(10,6) DEFAULT 0 NOT NULL, os_label character varying(20) DEFAULT 'POOL'::character varying NOT NULL, os_rank integer, ms_f_short numeric(14,6) DEFAULT 0 NOT NULL, ms_f_long numeric(14,6) DEFAULT 0 NOT NULL, ms_raw numeric(14,6) DEFAULT 0 NOT NULL, ms_display numeric(4,2) DEFAULT 0 NOT NULL, ml_order_count integer DEFAULT 0 NOT NULL, ml_allocated_amount numeric(14,2) DEFAULT 0 NOT NULL, ml_raw numeric(14,6) DEFAULT 0 NOT NULL, ml_display numeric(4,2) DEFAULT 0 NOT NULL, calc_time timestamp with time zone DEFAULT now() NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_member_consumption_summary ( id bigint DEFAULT nextval('dws.dws_member_consumption_summary_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, member_id bigint NOT NULL, stat_date date NOT NULL, member_nickname character varying(100), member_mobile character varying(20), card_grade_name character varying(50), register_date date, first_consume_date date, last_consume_date date, total_visit_count integer DEFAULT 0 NOT NULL, total_consume_amount numeric(14,2) DEFAULT 0 NOT NULL, total_recharge_amount numeric(14,2) DEFAULT 0 NOT NULL, total_table_fee numeric(14,2) DEFAULT 0 NOT NULL, total_goods_amount numeric(14,2) DEFAULT 0 NOT NULL, total_assistant_amount numeric(14,2) DEFAULT 0 NOT NULL, visit_count_7d integer DEFAULT 0 NOT NULL, visit_count_10d integer DEFAULT 0 NOT NULL, visit_count_15d integer DEFAULT 0 NOT NULL, visit_count_30d integer DEFAULT 0 NOT NULL, visit_count_60d integer DEFAULT 0 NOT NULL, visit_count_90d integer DEFAULT 0 NOT NULL, consume_amount_7d numeric(14,2) DEFAULT 0 NOT NULL, consume_amount_10d numeric(14,2) DEFAULT 0 NOT NULL, consume_amount_15d numeric(14,2) DEFAULT 0 NOT NULL, consume_amount_30d numeric(14,2) DEFAULT 0 NOT NULL, consume_amount_60d numeric(14,2) DEFAULT 0 NOT NULL, consume_amount_90d numeric(14,2) DEFAULT 0 NOT NULL, cash_card_balance numeric(14,2) DEFAULT 0 NOT NULL, gift_card_balance numeric(14,2) DEFAULT 0 NOT NULL, total_card_balance numeric(14,2) DEFAULT 0 NOT NULL, days_since_last integer, is_active_7d boolean DEFAULT false NOT NULL, is_active_30d boolean DEFAULT false NOT NULL, is_active_90d boolean DEFAULT false NOT NULL, customer_tier character varying(20), created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, recharge_count_30d integer DEFAULT 0, recharge_count_60d integer DEFAULT 0, recharge_count_90d integer DEFAULT 0, recharge_amount_30d numeric(14,2) DEFAULT 0, recharge_amount_60d numeric(14,2) DEFAULT 0, recharge_amount_90d numeric(14,2) DEFAULT 0, avg_ticket_amount numeric(14,2) DEFAULT 0 ); CREATE TABLE dws.dws_member_newconv_index ( newconv_id bigint DEFAULT nextval('dws.dws_member_newconv_index_newconv_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, member_id bigint NOT NULL, status character varying(30), segment character varying(10), member_create_time timestamp with time zone, first_visit_time timestamp with time zone, last_visit_time timestamp with time zone, last_recharge_time timestamp with time zone, t_v numeric(6,2), t_r numeric(6,2), t_a numeric(6,2), visits_14d integer DEFAULT 0 NOT NULL, visits_60d integer DEFAULT 0 NOT NULL, visits_total integer DEFAULT 0 NOT NULL, spend_30d numeric(14,2) DEFAULT 0 NOT NULL, spend_180d numeric(14,2) DEFAULT 0 NOT NULL, sv_balance numeric(14,2) DEFAULT 0 NOT NULL, recharge_60d_amt numeric(14,2) DEFAULT 0 NOT NULL, interval_count integer DEFAULT 0 NOT NULL, need_new numeric(10,4), salvage_new numeric(10,4), recharge_new numeric(10,4), value_new numeric(10,4), welcome_new numeric(10,4), raw_score_welcome numeric(14,6), raw_score_convert numeric(14,6), raw_score numeric(14,6), display_score_welcome numeric(4,2), display_score_convert numeric(4,2), display_score numeric(4,2), last_wechat_touch_time timestamp with time zone, calc_time timestamp with time zone DEFAULT now() NOT NULL, calc_version integer DEFAULT 1 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_member_project_tag ( id bigint DEFAULT nextval('dws.dws_member_project_tag_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, member_id bigint NOT NULL, time_window character varying(40) NOT NULL, category_code character varying(30) NOT NULL, category_name character varying(50) NOT NULL, short_name character varying(10) NOT NULL, duration_seconds bigint DEFAULT 0 NOT NULL, total_seconds bigint DEFAULT 0 NOT NULL, percentage numeric(5,4) DEFAULT 0 NOT NULL, is_tagged boolean DEFAULT false NOT NULL, computed_at timestamp with time zone DEFAULT now() NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_member_recall_index ( recall_id bigint DEFAULT nextval('dws.dws_member_recall_index_recall_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, member_id bigint NOT NULL, days_since_last_visit integer, visit_interval_median numeric(10,2), visit_interval_mad numeric(10,2), days_since_first_visit integer, days_since_last_recharge integer, visits_last_14_days integer DEFAULT 0 NOT NULL, visits_last_60_days integer DEFAULT 0 NOT NULL, score_overdue numeric(10,4), score_new_bonus numeric(10,4), score_recharge_bonus numeric(10,4), score_hot_drop numeric(10,4), raw_score numeric(14,6), display_score numeric(4,2), calc_time timestamp with time zone DEFAULT now() NOT NULL, calc_version integer DEFAULT 1 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_member_spending_power_index ( spi_id bigint DEFAULT nextval('dws.dws_member_spending_power_index_spi_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, member_id bigint NOT NULL, spend_30 numeric(14,2) DEFAULT 0, spend_90 numeric(14,2) DEFAULT 0, recharge_90 numeric(14,2) DEFAULT 0, orders_30 integer DEFAULT 0, orders_90 integer DEFAULT 0, visit_days_30 integer DEFAULT 0, visit_days_90 integer DEFAULT 0, avg_ticket_90 numeric(14,2) DEFAULT 0, active_weeks_90 integer DEFAULT 0, daily_spend_ewma_90 numeric(14,2) DEFAULT 0, score_level_raw numeric(10,4) DEFAULT 0, score_speed_raw numeric(10,4) DEFAULT 0, score_stability_raw numeric(10,4) DEFAULT 0, score_level_display numeric(5,2) DEFAULT 0, score_speed_display numeric(5,2) DEFAULT 0, score_stability_display numeric(5,2) DEFAULT 0, raw_score numeric(10,4) DEFAULT 0, display_score numeric(5,2) DEFAULT 0, calc_time timestamp with time zone DEFAULT now(), created_at timestamp with time zone DEFAULT now(), updated_at timestamp with time zone DEFAULT now() ); CREATE TABLE dws.dws_member_visit_detail ( id bigint DEFAULT nextval('dws.dws_member_visit_detail_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, member_id bigint NOT NULL, order_settle_id bigint NOT NULL, visit_date date NOT NULL, visit_time timestamp with time zone, member_nickname character varying(100), member_mobile character varying(20), member_birthday date, table_id bigint, table_name character varying(50), area_name character varying(50), area_category character varying(20), table_fee numeric(12,2) DEFAULT 0 NOT NULL, goods_amount numeric(12,2) DEFAULT 0 NOT NULL, assistant_amount numeric(12,2) DEFAULT 0 NOT NULL, total_consume numeric(12,2) DEFAULT 0 NOT NULL, total_discount numeric(12,2) DEFAULT 0 NOT NULL, actual_pay numeric(12,2) DEFAULT 0 NOT NULL, cash_pay numeric(12,2) DEFAULT 0 NOT NULL, balance_pay numeric(12,2) DEFAULT 0 NOT NULL, gift_card_pay numeric(12,2) DEFAULT 0 NOT NULL, groupbuy_pay numeric(12,2) DEFAULT 0 NOT NULL, table_duration_min integer DEFAULT 0 NOT NULL, assistant_duration_min integer DEFAULT 0 NOT NULL, assistant_services jsonb, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, recharge_card_pay numeric(12,2) DEFAULT 0 NOT NULL ); CREATE TABLE dws.dws_member_winback_index ( winback_id bigint DEFAULT nextval('dws.dws_member_winback_index_winback_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, member_id bigint NOT NULL, status character varying(30), segment character varying(10), member_create_time timestamp with time zone, first_visit_time timestamp with time zone, last_visit_time timestamp with time zone, last_recharge_time timestamp with time zone, t_v numeric(6,2), t_r numeric(6,2), t_a numeric(6,2), visits_14d integer DEFAULT 0 NOT NULL, visits_60d integer DEFAULT 0 NOT NULL, visits_total integer DEFAULT 0 NOT NULL, spend_30d numeric(14,2) DEFAULT 0 NOT NULL, spend_180d numeric(14,2) DEFAULT 0 NOT NULL, sv_balance numeric(14,2) DEFAULT 0 NOT NULL, recharge_60d_amt numeric(14,2) DEFAULT 0 NOT NULL, interval_count integer DEFAULT 0 NOT NULL, overdue_old numeric(10,4), drop_old numeric(10,4), recharge_old numeric(10,4), value_old numeric(10,4), raw_score numeric(14,6), display_score numeric(4,2), last_wechat_touch_time timestamp with time zone, calc_time timestamp with time zone DEFAULT now() NOT NULL, calc_version integer DEFAULT 1 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, overdue_cdf_p numeric(10,4), ideal_interval_days numeric(10,2), ideal_next_visit_date date ); CREATE TABLE dws.dws_ml_manual_order_alloc ( alloc_id bigint DEFAULT nextval('dws.dws_ml_manual_order_alloc_alloc_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, biz_date date NOT NULL, external_id character varying(128) NOT NULL, member_id bigint DEFAULT 0 NOT NULL, pay_time timestamp with time zone NOT NULL, order_amount numeric(14,2) DEFAULT 0 NOT NULL, assistant_id bigint NOT NULL, assistant_name character varying(128), share_ratio numeric(14,8) DEFAULT 0 NOT NULL, allocated_amount numeric(14,2) DEFAULT 0 NOT NULL, currency character varying(16) DEFAULT 'CNY'::character varying NOT NULL, import_scope_key character varying(128) NOT NULL, import_batch_no character varying(64) NOT NULL, import_file_name character varying(255) NOT NULL, import_time timestamp with time zone DEFAULT now() NOT NULL, import_user character varying(64), created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_ml_manual_order_source ( source_id bigint DEFAULT nextval('dws.dws_ml_manual_order_source_source_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, biz_date date NOT NULL, external_id character varying(128) NOT NULL, member_id bigint DEFAULT 0 NOT NULL, pay_time timestamp with time zone NOT NULL, order_amount numeric(14,2) DEFAULT 0 NOT NULL, currency character varying(16) DEFAULT 'CNY'::character varying NOT NULL, assistant_id_1 bigint, assistant_name_1 character varying(128), assistant_id_2 bigint, assistant_name_2 character varying(128), assistant_id_3 bigint, assistant_name_3 character varying(128), assistant_id_4 bigint, assistant_name_4 character varying(128), assistant_id_5 bigint, assistant_name_5 character varying(128), import_batch_no character varying(64) NOT NULL, import_file_name character varying(255) NOT NULL, import_scope_key character varying(128) NOT NULL, import_time timestamp with time zone DEFAULT now() NOT NULL, import_user character varying(64), row_no integer NOT NULL, remark text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_order_summary ( site_id bigint NOT NULL, order_settle_id bigint NOT NULL, order_trade_no character varying(64), order_date date NOT NULL, tenant_id bigint NOT NULL, member_id bigint, member_flag boolean DEFAULT false NOT NULL, recharge_order_flag boolean DEFAULT false NOT NULL, item_count integer DEFAULT 0 NOT NULL, total_item_quantity integer DEFAULT 0 NOT NULL, table_fee_amount numeric(14,2) DEFAULT 0 NOT NULL, assistant_service_amount numeric(14,2) DEFAULT 0 NOT NULL, goods_amount numeric(14,2) DEFAULT 0 NOT NULL, group_amount numeric(14,2) DEFAULT 0 NOT NULL, total_coupon_deduction numeric(14,2) DEFAULT 0 NOT NULL, member_discount_amount numeric(14,2) DEFAULT 0 NOT NULL, manual_discount_amount numeric(14,2) DEFAULT 0 NOT NULL, order_original_amount numeric(14,2) DEFAULT 0 NOT NULL, order_final_amount numeric(14,2) DEFAULT 0 NOT NULL, stored_card_deduct numeric(14,2) DEFAULT 0 NOT NULL, external_paid_amount numeric(14,2) DEFAULT 0 NOT NULL, total_paid_amount numeric(14,2) DEFAULT 0 NOT NULL, book_table_flow numeric(14,2) DEFAULT 0 NOT NULL, book_assistant_flow numeric(14,2) DEFAULT 0 NOT NULL, book_goods_flow numeric(14,2) DEFAULT 0 NOT NULL, book_group_flow numeric(14,2) DEFAULT 0 NOT NULL, book_order_flow numeric(14,2) DEFAULT 0 NOT NULL, order_effective_consume_cash numeric(14,2) DEFAULT 0 NOT NULL, order_effective_recharge_cash numeric(14,2) DEFAULT 0 NOT NULL, order_effective_flow numeric(14,2) DEFAULT 0 NOT NULL, refund_amount numeric(14,2) DEFAULT 0 NOT NULL, net_income numeric(14,2) DEFAULT 0 NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); CREATE TABLE dws.dws_platform_settlement ( id bigint DEFAULT nextval('dws.dws_platform_settlement_id_seq'::regclass) NOT NULL, site_id bigint NOT NULL, tenant_id bigint NOT NULL, settlement_date date NOT NULL, platform_type character varying(30) NOT NULL, platform_name character varying(50), platform_order_no character varying(100), order_settle_id bigint, settlement_amount numeric(14,2) DEFAULT 0 NOT NULL, commission_amount numeric(14,2) DEFAULT 0 NOT NULL, service_fee numeric(14,2) DEFAULT 0 NOT NULL, gross_amount numeric(14,2) DEFAULT 0 NOT NULL, import_batch_no character varying(50), import_file_name character varying(200), import_time timestamp with time zone, import_user character varying(50), remark text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); -- 约束（主键 / 唯一 / 外键） ALTER TABLE dws.cfg_area_category ADD CONSTRAINT cfg_area_category_pkey PRIMARY KEY (category_id); ALTER TABLE dws.cfg_assistant_level_price ADD CONSTRAINT cfg_assistant_level_price_pkey PRIMARY KEY (price_id); ALTER TABLE dws.cfg_assistant_level_price ADD CONSTRAINT uk_cfg_assistant_level_price UNIQUE (level_code, effective_from); ALTER TABLE dws.cfg_bonus_rules ADD CONSTRAINT cfg_bonus_rules_pkey PRIMARY KEY (rule_id); ALTER TABLE dws.cfg_bonus_rules ADD CONSTRAINT uk_cfg_bonus_rules UNIQUE (rule_type, rule_code, effective_from); ALTER TABLE dws.cfg_index_parameters ADD CONSTRAINT cfg_index_parameters_pkey PRIMARY KEY (param_id); ALTER TABLE dws.cfg_index_parameters ADD CONSTRAINT uk_cfg_index_parameters UNIQUE (index_type, param_name, effective_from); ALTER TABLE dws.cfg_performance_tier ADD CONSTRAINT cfg_performance_tier_pkey PRIMARY KEY (tier_id); ALTER TABLE dws.cfg_performance_tier ADD CONSTRAINT uk_cfg_performance_tier UNIQUE (tier_code, effective_from); ALTER TABLE dws.cfg_skill_type ADD CONSTRAINT cfg_skill_type_pkey PRIMARY KEY (skill_type_id); ALTER TABLE dws.cfg_skill_type ADD CONSTRAINT uk_cfg_skill_type UNIQUE (skill_id); ALTER TABLE dws.dws_assistant_customer_stats ADD CONSTRAINT dws_assistant_customer_stats_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_assistant_customer_stats ADD CONSTRAINT uk_dws_assistant_customer UNIQUE (site_id, assistant_id, member_id, stat_date); ALTER TABLE dws.dws_assistant_daily_detail ADD CONSTRAINT dws_assistant_daily_detail_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_assistant_daily_detail ADD CONSTRAINT uk_dws_assistant_daily UNIQUE (site_id, assistant_id, stat_date); ALTER TABLE dws.dws_assistant_finance_analysis ADD CONSTRAINT dws_assistant_finance_analysis_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_assistant_finance_analysis ADD CONSTRAINT uk_dws_assistant_finance UNIQUE (site_id, stat_date, assistant_id); ALTER TABLE dws.dws_assistant_monthly_summary ADD CONSTRAINT dws_assistant_monthly_summary_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_assistant_monthly_summary ADD CONSTRAINT uk_dws_assistant_monthly UNIQUE (site_id, assistant_id, stat_month, assistant_level_code); ALTER TABLE dws.dws_assistant_order_contribution ADD CONSTRAINT dws_assistant_order_contribution_pkey PRIMARY KEY (contribution_id); ALTER TABLE dws.dws_assistant_project_tag ADD CONSTRAINT pk_dws_assistant_project_tag PRIMARY KEY (id); ALTER TABLE dws.dws_assistant_project_tag ADD CONSTRAINT uk_dws_assistant_project_tag UNIQUE (site_id, assistant_id, time_window, category_code); ALTER TABLE dws.dws_assistant_recharge_commission ADD CONSTRAINT dws_assistant_recharge_commission_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_assistant_salary_calc ADD CONSTRAINT dws_assistant_salary_calc_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_assistant_salary_calc ADD CONSTRAINT uk_dws_assistant_salary UNIQUE (site_id, assistant_id, salary_month, assistant_level_code); ALTER TABLE dws.dws_finance_daily_summary ADD CONSTRAINT dws_finance_daily_summary_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_finance_daily_summary ADD CONSTRAINT uk_dws_finance_daily UNIQUE (site_id, stat_date); ALTER TABLE dws.dws_finance_discount_detail ADD CONSTRAINT dws_finance_discount_detail_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_finance_discount_detail ADD CONSTRAINT uk_dws_finance_discount_detail UNIQUE (site_id, stat_date, discount_type_code); ALTER TABLE dws.dws_finance_expense_summary ADD CONSTRAINT dws_finance_expense_summary_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_finance_expense_summary ADD CONSTRAINT uk_dws_finance_expense UNIQUE (site_id, expense_month, expense_type_code, import_batch_no); ALTER TABLE dws.dws_finance_income_structure ADD CONSTRAINT dws_finance_income_structure_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_finance_income_structure ADD CONSTRAINT uk_dws_finance_income_structure UNIQUE (site_id, stat_date, structure_type, category_code); ALTER TABLE dws.dws_finance_recharge_summary ADD CONSTRAINT dws_finance_recharge_summary_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_finance_recharge_summary ADD CONSTRAINT uk_dws_finance_recharge UNIQUE (site_id, stat_date); ALTER TABLE dws.dws_goods_stock_daily_summary ADD CONSTRAINT dws_goods_stock_daily_summary_pkey PRIMARY KEY (site_id, stat_date, site_goods_id); ALTER TABLE dws.dws_goods_stock_monthly_summary ADD CONSTRAINT dws_goods_stock_monthly_summary_pkey PRIMARY KEY (site_id, stat_date, site_goods_id); ALTER TABLE dws.dws_goods_stock_weekly_summary ADD CONSTRAINT dws_goods_stock_weekly_summary_pkey PRIMARY KEY (site_id, stat_date, site_goods_id); ALTER TABLE dws.dws_index_percentile_history ADD CONSTRAINT dws_index_percentile_history_pkey PRIMARY KEY (history_id); ALTER TABLE dws.dws_index_percentile_history ADD CONSTRAINT uk_dws_index_percentile_history UNIQUE (site_id, index_type, calc_time); ALTER TABLE dws.dws_member_assistant_intimacy ADD CONSTRAINT dws_member_assistant_intimacy_pkey PRIMARY KEY (intimacy_id); ALTER TABLE dws.dws_member_assistant_intimacy ADD CONSTRAINT uk_dws_member_assistant_intimacy UNIQUE (site_id, member_id, assistant_id); ALTER TABLE dws.dws_member_assistant_relation_index ADD CONSTRAINT dws_member_assistant_relation_index_pkey PRIMARY KEY (relation_id); ALTER TABLE dws.dws_member_assistant_relation_index ADD CONSTRAINT uk_dws_member_assistant_relation_index UNIQUE (site_id, member_id, assistant_id); ALTER TABLE dws.dws_member_consumption_summary ADD CONSTRAINT dws_member_consumption_summary_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_member_consumption_summary ADD CONSTRAINT uk_dws_member_consumption UNIQUE (site_id, member_id, stat_date); ALTER TABLE dws.dws_member_newconv_index ADD CONSTRAINT dws_member_newconv_index_pkey PRIMARY KEY (newconv_id); ALTER TABLE dws.dws_member_newconv_index ADD CONSTRAINT uk_dws_member_newconv UNIQUE (site_id, member_id); ALTER TABLE dws.dws_member_project_tag ADD CONSTRAINT pk_dws_member_project_tag PRIMARY KEY (id); ALTER TABLE dws.dws_member_project_tag ADD CONSTRAINT uk_dws_member_project_tag UNIQUE (site_id, member_id, time_window, category_code); ALTER TABLE dws.dws_member_recall_index ADD CONSTRAINT dws_member_recall_index_pkey PRIMARY KEY (recall_id); ALTER TABLE dws.dws_member_recall_index ADD CONSTRAINT uk_dws_member_recall UNIQUE (site_id, member_id); ALTER TABLE dws.dws_member_spending_power_index ADD CONSTRAINT dws_member_spending_power_index_pkey PRIMARY KEY (spi_id); ALTER TABLE dws.dws_member_visit_detail ADD CONSTRAINT dws_member_visit_detail_pkey PRIMARY KEY (id); ALTER TABLE dws.dws_member_visit_detail ADD CONSTRAINT uk_dws_member_visit UNIQUE (site_id, member_id, order_settle_id); ALTER TABLE dws.dws_member_winback_index ADD CONSTRAINT dws_member_winback_index_pkey PRIMARY KEY (winback_id); ALTER TABLE dws.dws_member_winback_index ADD CONSTRAINT uk_dws_member_winback UNIQUE (site_id, member_id); ALTER TABLE dws.dws_ml_manual_order_alloc ADD CONSTRAINT dws_ml_manual_order_alloc_pkey PRIMARY KEY (alloc_id); ALTER TABLE dws.dws_ml_manual_order_alloc ADD CONSTRAINT uk_dws_ml_manual_order_alloc UNIQUE (site_id, external_id, assistant_id); ALTER TABLE dws.dws_ml_manual_order_source ADD CONSTRAINT dws_ml_manual_order_source_pkey PRIMARY KEY (source_id); ALTER TABLE dws.dws_ml_manual_order_source ADD CONSTRAINT uk_dws_ml_manual_order_source UNIQUE (site_id, external_id, import_scope_key, row_no); ALTER TABLE dws.dws_order_summary ADD CONSTRAINT pk_dws_order_summary PRIMARY KEY (site_id, order_settle_id); ALTER TABLE dws.dws_platform_settlement ADD CONSTRAINT dws_platform_settlement_pkey PRIMARY KEY (id); -- 索引 CREATE INDEX idx_cfg_area_category_code ON dws.cfg_area_category USING btree (category_code); CREATE UNIQUE INDEX uk_cfg_area_category ON dws.cfg_area_category USING btree (source_area_name, COALESCE(source_table_name, ''::character varying)); CREATE INDEX idx_cfg_assistant_level_price_effective ON dws.cfg_assistant_level_price USING btree (effective_from, effective_to); CREATE INDEX idx_cfg_bonus_rules_effective ON dws.cfg_bonus_rules USING btree (effective_from, effective_to); CREATE INDEX idx_cfg_bonus_rules_type ON dws.cfg_bonus_rules USING btree (rule_type); CREATE INDEX idx_cfg_index_params_effective ON dws.cfg_index_parameters USING btree (effective_from, effective_to); CREATE INDEX idx_cfg_index_params_type ON dws.cfg_index_parameters USING btree (index_type); CREATE INDEX idx_cfg_performance_tier_effective ON dws.cfg_performance_tier USING btree (effective_from, effective_to); CREATE INDEX idx_cfg_skill_type_course ON dws.cfg_skill_type USING btree (course_type_code); CREATE INDEX idx_dws_assistant_customer_asst ON dws.dws_assistant_customer_stats USING btree (assistant_id, stat_date); CREATE INDEX idx_dws_assistant_customer_date ON dws.dws_assistant_customer_stats USING btree (stat_date); CREATE INDEX idx_dws_assistant_customer_member ON dws.dws_assistant_customer_stats USING btree (member_id, stat_date); CREATE INDEX idx_dws_assistant_daily_asst_date ON dws.dws_assistant_daily_detail USING btree (assistant_id, stat_date); CREATE INDEX idx_dws_assistant_daily_date ON dws.dws_assistant_daily_detail USING btree (stat_date); CREATE INDEX idx_dws_assistant_daily_site_date ON dws.dws_assistant_daily_detail USING btree (site_id, stat_date); CREATE INDEX idx_dws_assistant_finance_asst ON dws.dws_assistant_finance_analysis USING btree (assistant_id, stat_date); CREATE INDEX idx_dws_assistant_finance_date ON dws.dws_assistant_finance_analysis USING btree (stat_date); CREATE INDEX idx_dws_assistant_monthly_asst ON dws.dws_assistant_monthly_summary USING btree (assistant_id, stat_month); CREATE INDEX idx_dws_assistant_monthly_month ON dws.dws_assistant_monthly_summary USING btree (stat_month); CREATE INDEX idx_dws_assistant_monthly_tier ON dws.dws_assistant_monthly_summary USING btree (tier_code); CREATE UNIQUE INDEX idx_aoc_site_assistant_date ON dws.dws_assistant_order_contribution USING btree (site_id, assistant_id, stat_date); CREATE INDEX idx_aoc_stat_date ON dws.dws_assistant_order_contribution USING btree (site_id, stat_date); CREATE INDEX idx_apt_site_window_tagged ON dws.dws_assistant_project_tag USING btree (site_id, time_window) WHERE (is_tagged = true); CREATE INDEX idx_dws_assistant_commission_asst ON dws.dws_assistant_recharge_commission USING btree (assistant_id, commission_month); CREATE INDEX idx_dws_assistant_commission_batch ON dws.dws_assistant_recharge_commission USING btree (import_batch_no); CREATE INDEX idx_dws_assistant_commission_month ON dws.dws_assistant_recharge_commission USING btree (commission_month); CREATE INDEX idx_dws_assistant_salary_asst ON dws.dws_assistant_salary_calc USING btree (assistant_id, salary_month); CREATE INDEX idx_dws_assistant_salary_month ON dws.dws_assistant_salary_calc USING btree (salary_month); CREATE INDEX idx_dws_finance_daily_date ON dws.dws_finance_daily_summary USING btree (stat_date); CREATE INDEX idx_dws_finance_daily_site ON dws.dws_finance_daily_summary USING btree (site_id, stat_date); CREATE INDEX idx_dws_finance_discount_date ON dws.dws_finance_discount_detail USING btree (stat_date); CREATE INDEX idx_dws_finance_discount_type ON dws.dws_finance_discount_detail USING btree (discount_type_code); CREATE INDEX idx_dws_finance_expense_batch ON dws.dws_finance_expense_summary USING btree (import_batch_no); CREATE INDEX idx_dws_finance_expense_month ON dws.dws_finance_expense_summary USING btree (expense_month); CREATE INDEX idx_dws_finance_expense_type ON dws.dws_finance_expense_summary USING btree (expense_type_code); CREATE INDEX idx_dws_finance_income_date ON dws.dws_finance_income_structure USING btree (stat_date); CREATE INDEX idx_dws_finance_income_type ON dws.dws_finance_income_structure USING btree (structure_type, category_code); CREATE INDEX idx_dws_finance_recharge_date ON dws.dws_finance_recharge_summary USING btree (stat_date); CREATE INDEX idx_dws_goods_stock_daily_date ON dws.dws_goods_stock_daily_summary USING btree (stat_date); CREATE INDEX idx_dws_goods_stock_daily_goods ON dws.dws_goods_stock_daily_summary USING btree (site_goods_id, stat_date); CREATE INDEX idx_dws_goods_stock_daily_site ON dws.dws_goods_stock_daily_summary USING btree (site_id, stat_date); CREATE INDEX idx_dws_goods_stock_monthly_date ON dws.dws_goods_stock_monthly_summary USING btree (stat_date); CREATE INDEX idx_dws_goods_stock_monthly_goods ON dws.dws_goods_stock_monthly_summary USING btree (site_goods_id, stat_date); CREATE INDEX idx_dws_goods_stock_monthly_site ON dws.dws_goods_stock_monthly_summary USING btree (site_id, stat_date); CREATE INDEX idx_dws_goods_stock_weekly_date ON dws.dws_goods_stock_weekly_summary USING btree (stat_date); CREATE INDEX idx_dws_goods_stock_weekly_goods ON dws.dws_goods_stock_weekly_summary USING btree (site_goods_id, stat_date); CREATE INDEX idx_dws_goods_stock_weekly_site ON dws.dws_goods_stock_weekly_summary USING btree (site_id, stat_date); CREATE INDEX idx_dws_percentile_history ON dws.dws_index_percentile_history USING btree (site_id, index_type, calc_time DESC); CREATE INDEX idx_dws_intimacy_assistant ON dws.dws_member_assistant_intimacy USING btree (site_id, assistant_id, display_score DESC); CREATE INDEX idx_dws_intimacy_member ON dws.dws_member_assistant_intimacy USING btree (site_id, member_id, display_score DESC); CREATE INDEX idx_dws_relation_assistant ON dws.dws_member_assistant_relation_index USING btree (site_id, assistant_id, rs_display DESC); CREATE INDEX idx_dws_relation_calc_time ON dws.dws_member_assistant_relation_index USING btree (calc_time); CREATE INDEX idx_dws_relation_member ON dws.dws_member_assistant_relation_index USING btree (site_id, member_id, os_share DESC); CREATE INDEX idx_dws_member_consumption_date ON dws.dws_member_consumption_summary USING btree (stat_date); CREATE INDEX idx_dws_member_consumption_member ON dws.dws_member_consumption_summary USING btree (member_id, stat_date); CREATE INDEX idx_dws_member_consumption_tier ON dws.dws_member_consumption_summary USING btree (customer_tier); CREATE INDEX idx_dws_newconv_display ON dws.dws_member_newconv_index USING btree (site_id, display_score DESC); CREATE INDEX idx_mpt_site_window_tagged ON dws.dws_member_project_tag USING btree (site_id, time_window) WHERE (is_tagged = true); CREATE INDEX idx_dws_recall_display ON dws.dws_member_recall_index USING btree (site_id, display_score DESC); CREATE INDEX idx_spi_display_score ON dws.dws_member_spending_power_index USING btree (site_id, display_score DESC); CREATE UNIQUE INDEX idx_spi_site_member ON dws.dws_member_spending_power_index USING btree (site_id, member_id); CREATE INDEX idx_dws_member_visit_date ON dws.dws_member_visit_detail USING btree (visit_date); CREATE INDEX idx_dws_member_visit_member ON dws.dws_member_visit_detail USING btree (member_id, visit_date); CREATE INDEX idx_dws_member_visit_order ON dws.dws_member_visit_detail USING btree (order_settle_id); CREATE INDEX idx_dws_winback_display ON dws.dws_member_winback_index USING btree (site_id, display_score DESC); CREATE INDEX idx_dws_ml_alloc_member_assistant ON dws.dws_ml_manual_order_alloc USING btree (site_id, member_id, assistant_id); CREATE INDEX idx_dws_ml_alloc_scope ON dws.dws_ml_manual_order_alloc USING btree (site_id, biz_date); CREATE INDEX idx_dws_ml_source_external ON dws.dws_ml_manual_order_source USING btree (site_id, external_id); CREATE INDEX idx_dws_ml_source_scope ON dws.dws_ml_manual_order_source USING btree (site_id, biz_date); CREATE INDEX idx_dws_order_summary_member ON dws.dws_order_summary USING btree (member_id, order_date); CREATE INDEX idx_dws_order_summary_site_date ON dws.dws_order_summary USING btree (site_id, order_date); CREATE INDEX idx_dws_order_summary_trade_no ON dws.dws_order_summary USING btree (order_trade_no); CREATE INDEX idx_dws_platform_settlement_batch ON dws.dws_platform_settlement USING btree (import_batch_no); CREATE INDEX idx_dws_platform_settlement_date ON dws.dws_platform_settlement USING btree (settlement_date); CREATE INDEX idx_dws_platform_settlement_order ON dws.dws_platform_settlement USING btree (order_settle_id); CREATE INDEX idx_dws_platform_settlement_platform ON dws.dws_platform_settlement USING btree (platform_type); CREATE INDEX idx_mv_assistant_daily_l1 ON dws.mv_dws_assistant_daily_detail_l1 USING btree (site_id, stat_date, assistant_id); CREATE INDEX idx_mv_assistant_daily_l2 ON dws.mv_dws_assistant_daily_detail_l2 USING btree (site_id, stat_date, assistant_id); CREATE INDEX idx_mv_assistant_daily_l3 ON dws.mv_dws_assistant_daily_detail_l3 USING btree (site_id, stat_date, assistant_id); CREATE INDEX idx_mv_assistant_daily_l4 ON dws.mv_dws_assistant_daily_detail_l4 USING btree (site_id, stat_date, assistant_id); CREATE INDEX idx_mv_finance_daily_l1 ON dws.mv_dws_finance_daily_summary_l1 USING btree (site_id, stat_date); CREATE INDEX idx_mv_finance_daily_l2 ON dws.mv_dws_finance_daily_summary_l2 USING btree (site_id, stat_date); CREATE INDEX idx_mv_finance_daily_l3 ON dws.mv_dws_finance_daily_summary_l3 USING btree (site_id, stat_date); CREATE INDEX idx_mv_finance_daily_l4 ON dws.mv_dws_finance_daily_summary_l4 USING btree (site_id, stat_date); -- 视图 CREATE OR REPLACE VIEW dws.v_member_recall_priority AS SELECT dws_member_winback_index.site_id, dws_member_winback_index.tenant_id, dws_member_winback_index.member_id, 'WBI'::character varying(10) AS index_type, dws_member_winback_index.status, dws_member_winback_index.segment, dws_member_winback_index.member_create_time, dws_member_winback_index.first_visit_time, dws_member_winback_index.last_visit_time, dws_member_winback_index.last_recharge_time, dws_member_winback_index.t_v, dws_member_winback_index.t_r, dws_member_winback_index.t_a, dws_member_winback_index.visits_14d, dws_member_winback_index.visits_60d, dws_member_winback_index.visits_total, dws_member_winback_index.spend_30d, dws_member_winback_index.spend_180d, dws_member_winback_index.sv_balance, dws_member_winback_index.recharge_60d_amt, NULL::numeric(10,4) AS need_new, NULL::numeric(10,4) AS salvage_new, NULL::numeric(10,4) AS recharge_new, NULL::numeric(10,4) AS value_new, NULL::numeric(10,4) AS welcome_new, NULL::numeric(14,6) AS raw_score_welcome, NULL::numeric(14,6) AS raw_score_convert, dws_member_winback_index.raw_score, NULL::numeric(4,2) AS display_score_welcome, NULL::numeric(4,2) AS display_score_convert, dws_member_winback_index.display_score, dws_member_winback_index.last_wechat_touch_time, dws_member_winback_index.calc_time FROM dws.dws_member_winback_index UNION ALL SELECT dws_member_newconv_index.site_id, dws_member_newconv_index.tenant_id, dws_member_newconv_index.member_id, 'NCI'::character varying(10) AS index_type, dws_member_newconv_index.status, dws_member_newconv_index.segment, dws_member_newconv_index.member_create_time, dws_member_newconv_index.first_visit_time, dws_member_newconv_index.last_visit_time, dws_member_newconv_index.last_recharge_time, dws_member_newconv_index.t_v, dws_member_newconv_index.t_r, dws_member_newconv_index.t_a, dws_member_newconv_index.visits_14d, dws_member_newconv_index.visits_60d, dws_member_newconv_index.visits_total, dws_member_newconv_index.spend_30d, dws_member_newconv_index.spend_180d, dws_member_newconv_index.sv_balance, dws_member_newconv_index.recharge_60d_amt, dws_member_newconv_index.need_new, dws_member_newconv_index.salvage_new, dws_member_newconv_index.recharge_new, dws_member_newconv_index.value_new, dws_member_newconv_index.welcome_new, dws_member_newconv_index.raw_score_welcome, dws_member_newconv_index.raw_score_convert, dws_member_newconv_index.raw_score, dws_member_newconv_index.display_score_welcome, dws_member_newconv_index.display_score_convert, dws_member_newconv_index.display_score, dws_member_newconv_index.last_wechat_touch_time, dws_member_newconv_index.calc_time FROM dws.dws_member_newconv_index; ; -- 物化视图 CREATE MATERIALIZED VIEW dws.mv_dws_assistant_daily_detail_l1 AS SELECT id, site_id, tenant_id, assistant_id, assistant_nickname, stat_date, assistant_level_code, assistant_level_name, total_service_count, base_service_count, bonus_service_count, room_service_count, total_seconds, base_seconds, bonus_seconds, room_seconds, total_hours, base_hours, bonus_hours, room_hours, total_ledger_amount, base_ledger_amount, bonus_ledger_amount, room_ledger_amount, unique_customers, unique_tables, trashed_seconds, trashed_count, created_at, updated_at FROM dws.dws_assistant_daily_detail WHERE (stat_date >= (CURRENT_DATE - '1 day'::interval)); ; CREATE MATERIALIZED VIEW dws.mv_dws_assistant_daily_detail_l2 AS SELECT id, site_id, tenant_id, assistant_id, assistant_nickname, stat_date, assistant_level_code, assistant_level_name, total_service_count, base_service_count, bonus_service_count, room_service_count, total_seconds, base_seconds, bonus_seconds, room_seconds, total_hours, base_hours, bonus_hours, room_hours, total_ledger_amount, base_ledger_amount, bonus_ledger_amount, room_ledger_amount, unique_customers, unique_tables, trashed_seconds, trashed_count, created_at, updated_at FROM dws.dws_assistant_daily_detail WHERE (stat_date >= (CURRENT_DATE - '30 days'::interval)); ; CREATE MATERIALIZED VIEW dws.mv_dws_assistant_daily_detail_l3 AS SELECT id, site_id, tenant_id, assistant_id, assistant_nickname, stat_date, assistant_level_code, assistant_level_name, total_service_count, base_service_count, bonus_service_count, room_service_count, total_seconds, base_seconds, bonus_seconds, room_seconds, total_hours, base_hours, bonus_hours, room_hours, total_ledger_amount, base_ledger_amount, bonus_ledger_amount, room_ledger_amount, unique_customers, unique_tables, trashed_seconds, trashed_count, created_at, updated_at FROM dws.dws_assistant_daily_detail WHERE (stat_date >= (CURRENT_DATE - '90 days'::interval)); ; CREATE MATERIALIZED VIEW dws.mv_dws_assistant_daily_detail_l4 AS SELECT id, site_id, tenant_id, assistant_id, assistant_nickname, stat_date, assistant_level_code, assistant_level_name, total_service_count, base_service_count, bonus_service_count, room_service_count, total_seconds, base_seconds, bonus_seconds, room_seconds, total_hours, base_hours, bonus_hours, room_hours, total_ledger_amount, base_ledger_amount, bonus_ledger_amount, room_ledger_amount, unique_customers, unique_tables, trashed_seconds, trashed_count, created_at, updated_at FROM dws.dws_assistant_daily_detail WHERE ((stat_date >= (date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone) - '6 mons'::interval)) AND (stat_date < date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone))); ; CREATE MATERIALIZED VIEW dws.mv_dws_finance_daily_summary_l1 AS SELECT id, site_id, tenant_id, stat_date, gross_amount, table_fee_amount, goods_amount, assistant_pd_amount, assistant_cx_amount, discount_total, discount_groupbuy, discount_vip, discount_gift_card, discount_manual, discount_rounding, discount_other, confirmed_income, cash_inflow_total, cash_pay_amount, groupbuy_pay_amount, platform_settlement_amount, platform_fee_amount, recharge_cash_inflow, card_consume_total, recharge_card_consume AS cash_card_consume, gift_card_consume, cash_outflow_total, cash_balance_change, recharge_count, recharge_total, recharge_cash, recharge_gift, first_recharge_count, first_recharge_amount, renewal_count, renewal_amount, order_count, member_order_count, guest_order_count, avg_order_amount, created_at, updated_at FROM dws.dws_finance_daily_summary WHERE (stat_date >= (CURRENT_DATE - '1 day'::interval)); ; CREATE MATERIALIZED VIEW dws.mv_dws_finance_daily_summary_l2 AS SELECT id, site_id, tenant_id, stat_date, gross_amount, table_fee_amount, goods_amount, assistant_pd_amount, assistant_cx_amount, discount_total, discount_groupbuy, discount_vip, discount_gift_card, discount_manual, discount_rounding, discount_other, confirmed_income, cash_inflow_total, cash_pay_amount, groupbuy_pay_amount, platform_settlement_amount, platform_fee_amount, recharge_cash_inflow, card_consume_total, recharge_card_consume AS cash_card_consume, gift_card_consume, cash_outflow_total, cash_balance_change, recharge_count, recharge_total, recharge_cash, recharge_gift, first_recharge_count, first_recharge_amount, renewal_count, renewal_amount, order_count, member_order_count, guest_order_count, avg_order_amount, created_at, updated_at FROM dws.dws_finance_daily_summary WHERE (stat_date >= (CURRENT_DATE - '30 days'::interval)); ; CREATE MATERIALIZED VIEW dws.mv_dws_finance_daily_summary_l3 AS SELECT id, site_id, tenant_id, stat_date, gross_amount, table_fee_amount, goods_amount, assistant_pd_amount, assistant_cx_amount, discount_total, discount_groupbuy, discount_vip, discount_gift_card, discount_manual, discount_rounding, discount_other, confirmed_income, cash_inflow_total, cash_pay_amount, groupbuy_pay_amount, platform_settlement_amount, platform_fee_amount, recharge_cash_inflow, card_consume_total, recharge_card_consume AS cash_card_consume, gift_card_consume, cash_outflow_total, cash_balance_change, recharge_count, recharge_total, recharge_cash, recharge_gift, first_recharge_count, first_recharge_amount, renewal_count, renewal_amount, order_count, member_order_count, guest_order_count, avg_order_amount, created_at, updated_at FROM dws.dws_finance_daily_summary WHERE (stat_date >= (CURRENT_DATE - '90 days'::interval)); ; CREATE MATERIALIZED VIEW dws.mv_dws_finance_daily_summary_l4 AS SELECT id, site_id, tenant_id, stat_date, gross_amount, table_fee_amount, goods_amount, assistant_pd_amount, assistant_cx_amount, discount_total, discount_groupbuy, discount_vip, discount_gift_card, discount_manual, discount_rounding, discount_other, confirmed_income, cash_inflow_total, cash_pay_amount, groupbuy_pay_amount, platform_settlement_amount, platform_fee_amount, recharge_cash_inflow, card_consume_total, recharge_card_consume AS cash_card_consume, gift_card_consume, cash_outflow_total, cash_balance_change, recharge_count, recharge_total, recharge_cash, recharge_gift, first_recharge_count, first_recharge_amount, renewal_count, renewal_amount, order_count, member_order_count, guest_order_count, avg_order_amount, created_at, updated_at FROM dws.dws_finance_daily_summary WHERE ((stat_date >= (date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone) - '6 mons'::interval)) AND (stat_date < date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone))); ; -- 物化视图索引 CREATE INDEX idx_mv_assistant_daily_l1 ON dws.mv_dws_assistant_daily_detail_l1 USING btree (site_id, stat_date, assistant_id); CREATE INDEX idx_mv_assistant_daily_l2 ON dws.mv_dws_assistant_daily_detail_l2 USING btree (site_id, stat_date, assistant_id); CREATE INDEX idx_mv_assistant_daily_l3 ON dws.mv_dws_assistant_daily_detail_l3 USING btree (site_id, stat_date, assistant_id); CREATE INDEX idx_mv_assistant_daily_l4 ON dws.mv_dws_assistant_daily_detail_l4 USING btree (site_id, stat_date, assistant_id); CREATE INDEX idx_mv_finance_daily_l1 ON dws.mv_dws_finance_daily_summary_l1 USING btree (site_id, stat_date); CREATE INDEX idx_mv_finance_daily_l2 ON dws.mv_dws_finance_daily_summary_l2 USING btree (site_id, stat_date); CREATE INDEX idx_mv_finance_daily_l3 ON dws.mv_dws_finance_daily_summary_l3 USING btree (site_id, stat_date); CREATE INDEX idx_mv_finance_daily_l4 ON dws.mv_dws_finance_daily_summary_l4 USING btree (site_id, stat_date); -- ============================================================================= -- 种子数据：DWS 配置表初始数据（绩效档位、等级定价、奖金规则、区域分类、技能映射） -- ============================================================================= -- ============================================================================= -- DWS 配置表初始数据 -- 版本: v4.0 -- 创建日期: 2026-02-01 -- 更新日期: 2026-02-21 -- AI_CHANGELOG [2026-02-21] 取消全文注释，数据已写入 test_etl_feiqiu； -- 新增 2025-01-01~2026-02-28 统一提成档位（基础课18元/小时，打赏课40%）； -- 新增 GUARANTEE 保底奖金规则（按等级：初级12000/中级16000/高级18000/星级23000）； -- 历史分档口径截止日期调整为 2024-12-31 -- 描述: 初始化配置表数据，包含绩效档位、等级定价、奖金规则、区域分类、技能映射 -- ============================================================================= -- ============================================================================= -- 1. cfg_performance_tier - 绩效档位配置（含历史口径） -- 数据来源：DWS 数据库处理需求.md -- 三段时间线： -- 2000-01-01 ~ 2024-12-31: 旧方案（6档阶梯抽成） -- 2025-01-01 ~ 2026-02-28: 统一提成（不分档，基础课18元/小时，打赏课40%） -- 2026-03-01 ~ 9999-12-31: 新方案（5档阶梯抽成） -- ============================================================================= TRUNCATE TABLE dws.cfg_performance_tier RESTART IDENTITY CASCADE; INSERT INTO dws.cfg_performance_tier ( tier_code, tier_name, tier_level, min_hours, max_hours, base_deduction, bonus_deduction_ratio, vacation_days, vacation_unlimited, is_new_hire_tier, effective_from, effective_to, description ) VALUES -- 旧方案（至2024-12-31） ('T0', '0档-淘汰压力', 0, 0, 100, 28.00, 0.50, 3, FALSE, FALSE, '2000-01-01', '2024-12-31', '旧方案：H<100，专业课抽成28元/小时，打赏课抽成50%，休假3天'), ('T1', '1档-及格档', 1, 100, 130, 18.00, 0.40, 4, FALSE, FALSE, '2000-01-01', '2024-12-31', '旧方案：100≤H<130，专业课抽成18元/小时，打赏课抽成40%，休假4天'), ('T2', '2档-良好档', 2, 130, 160, 15.00, 0.38, 4, FALSE, FALSE, '2000-01-01', '2024-12-31', '旧方案：130≤H<160，专业课抽成15元/小时，打赏课抽成38%，休假4天'), ('T3', '3档-优秀档', 3, 160, 190, 13.00, 0.35, 5, FALSE, FALSE, '2000-01-01', '2024-12-31', '旧方案：160≤H<190，专业课抽成13元/小时，打赏课抽成35%，休假5天'), ('T4', '4档-卓越加速档', 4, 190, 220, 10.00, 0.33, 6, FALSE, FALSE, '2000-01-01', '2024-12-31', '旧方案：190≤H<220，专业课抽成10元/小时，打赏课抽成33%，休假6天'), ('T5', '5档-冠军加速档', 5, 220, NULL, 8.00, 0.30, 0, TRUE, FALSE, '2000-01-01', '2024-12-31', '旧方案：H≥220，专业课抽成8元/小时，打赏课抽成30%，休假自由'), -- 2025-01-01 ~ 2026-02-28: 统一提成（不分档，所有助教统一规则） -- CHANGE 2026-02-21 | 新增统一提成档位，基础课球房提成18元/小时，打赏课球房提成40% ('T0', '统一档', 0, 0, NULL, 18.00, 0.40, 0, FALSE, FALSE, '2025-01-01', '2026-02-28', '2025-01-01~2026-02-28统一规则：基础课球房提成18元/小时，打赏课球房提成40%，不分档位'), -- 新方案（2026-03-01起） ('T0', '0档-淘汰压力', 0, 0, 120, 28.00, 0.50, 3, FALSE, FALSE, '2026-03-01', '9999-12-31', '新方案：H<120，专业课抽成28元/小时，打赏课抽成50%，休假3天'), ('T1', '1档-及格档', 1, 120, 150, 18.00, 0.40, 4, FALSE, FALSE, '2026-03-01', '9999-12-31', '新方案：120≤H<150，专业课抽成18元/小时，打赏课抽成40%，休假4天'), ('T2', '2档-良好档', 2, 150, 180, 13.00, 0.35, 5, FALSE, FALSE, '2026-03-01', '9999-12-31', '新方案：150≤H<180，专业课抽成13元/小时，打赏课抽成35%，休假5天'), ('T3', '3档-优秀档', 3, 180, 210, 10.00, 0.30, 6, FALSE, FALSE, '2026-03-01', '9999-12-31', '新方案：180≤H<210，专业课抽成10元/小时，打赏课抽成30%，休假6天'), ('T4', '4档-销冠竞争', 4, 210, NULL, 8.00, 0.25, 0, TRUE, FALSE, '2026-03-01', '9999-12-31', '新方案：H≥210，专业课抽成8元/小时，打赏课抽成25%，休假自由'); -- ============================================================================= -- 2. cfg_assistant_level_price - 助教等级定价 -- 说明: -- - level_code 来自 dim_assistant.assistant_level -- - 8=助教管理, 10=初级, 20=中级, 30=高级, 40=星级 -- - 价格为客户支付价格（对外价格），助教收入=客户支付-档位抽成 -- - 包厢课基础课统一138元/小时（不随等级变化） -- ============================================================================= TRUNCATE TABLE dws.cfg_assistant_level_price RESTART IDENTITY CASCADE; INSERT INTO dws.cfg_assistant_level_price ( level_code, level_name, base_course_price, bonus_course_price, effective_from, effective_to, description ) VALUES (10, '初级', 98.00, 190.00, '2000-01-01', '9999-12-31', '初级助教：基础课98元/时，附加课190元/时（客户支付价格）'), (20, '中级', 108.00, 190.00, '2000-01-01', '9999-12-31', '中级助教：基础课108元/时，附加课190元/时（客户支付价格）'), (30, '高级', 118.00, 190.00, '2000-01-01', '9999-12-31', '高级助教：基础课118元/时，附加课190元/时（客户支付价格）'), (40, '星级', 138.00, 190.00, '2000-01-01', '9999-12-31', '星级助教：基础课138元/时，附加课190元/时（客户支付价格）'), (8, '助教管理', 98.00, 190.00, '2000-01-01', '9999-12-31', '助教管理：不参与客户服务计费，默认按初级价格'); -- ============================================================================= -- 3. cfg_bonus_rules - 奖金规则配置 -- 说明: -- - SPRINT: 冲刺奖金（历史口径，至2024-12-31） -- - GUARANTEE: 保底月薪线（2025-01-01~2026-02-28，按等级区分） -- * 保底规则：总课时达标 + 打赏课≥10小时 → 触发保底月薪线 -- * 保底含义：实发 = MAX(课时收入+奖金, 保底金额)，非额外奖金 -- * rule_code 中 LV10/LV20/LV30/LV40 对应 level_code -- - TOP_RANK: Top3排名奖金（2026-03-01起） -- CHANGE 2026-02-21 | 新增 GUARANTEE 保底奖金规则 -- ============================================================================= TRUNCATE TABLE dws.cfg_bonus_rules RESTART IDENTITY CASCADE; INSERT INTO dws.cfg_bonus_rules ( rule_type, rule_code, rule_name, threshold_hours, rank_position, bonus_amount, is_cumulative, priority, effective_from, effective_to, description ) VALUES -- 冲刺奖金（历史口径，至2024-12-31） ('SPRINT', 'SPRINT_190', '冲刺奖金190', 190.00, NULL, 300.00, FALSE, 1, '2000-01-01', '2024-12-31', '历史口径：业绩≥190小时，获得300元冲刺奖金（不累计）'), ('SPRINT', 'SPRINT_220', '冲刺奖金220', 220.00, NULL, 800.00, FALSE, 2, '2000-01-01', '2024-12-31', '历史口径：业绩≥220小时，获得800元冲刺奖金（覆盖190档）'), -- 保底奖金（2025-01-01 ~ 2026-02-28） -- 按助教等级区分，需同时满足总课时和打赏课最低时数（≥10小时） ('GUARANTEE', 'GUAR_LV10', '初级保底奖金', 130.00, NULL, 12000.00, FALSE, 10, '2025-01-01', '2026-02-28', '初级保底：完成130小时课程（含≥10小时打赏课），保底月薪线12000元（实发=MAX(课时收入+奖金, 12000)）'), ('GUARANTEE', 'GUAR_LV20', '中级保底奖金', 150.00, NULL, 16000.00, FALSE, 20, '2025-01-01', '2026-02-28', '中级保底：完成150小时课程（含≥10小时打赏课），保底月薪线16000元（实发=MAX(课时收入+奖金, 16000)）'), ('GUARANTEE', 'GUAR_LV30', '高级保底奖金', 160.00, NULL, 18000.00, FALSE, 30, '2025-01-01', '2026-02-28', '高级保底：完成160小时课程（含≥10小时打赏课），保底月薪线18000元（实发=MAX(课时收入+奖金, 18000)）'), ('GUARANTEE', 'GUAR_LV40', '星级保底奖金', 170.00, NULL, 23000.00, FALSE, 40, '2025-01-01', '2026-02-28', '星级保底：完成170小时课程（含≥10小时打赏课），保底月薪线23000元（实发=MAX(课时收入+奖金, 23000)）'), -- Top排名奖金（2026-03-01起） ('TOP_RANK', 'TOP_1', 'Top1排名奖金', NULL, 1, 1000.00, FALSE, 0, '2026-03-01', '9999-12-31', '月度排名第一，获得1000元（并列都算）'), ('TOP_RANK', 'TOP_2', 'Top2排名奖金', NULL, 2, 600.00, FALSE, 0, '2026-03-01', '9999-12-31', '月度排名第二，获得600元（并列都算）'), ('TOP_RANK', 'TOP_3', 'Top3排名奖金', NULL, 3, 400.00, FALSE, 0, '2026-03-01', '9999-12-31', '月度排名第三，获得400元（并列都算）'); -- ============================================================================= -- 4. cfg_area_category - 台区分类映射（纯台桌级精确映射） -- 说明: -- - 每台桌一行精确映射，source_area_name=区域, source_table_name=台桌名 -- - 不使用 LIKE 模糊匹配，仅 EXACT + DEFAULT 兜底 -- - 数据来源: 用户提供的完整台桌清单（2026-03-09） -- ============================================================================= TRUNCATE TABLE dws.cfg_area_category RESTART IDENTITY CASCADE; INSERT INTO dws.cfg_area_category ( source_area_name, source_table_name, category_code, category_name, display_name, short_name, match_type, match_priority, is_active, description ) VALUES -- ============ BILLIARD 🎱 中式/追分 ============ -- A区（18台） ('A区', 'A1', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A1'), ('A区', 'A2', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A2'), ('A区', 'A3', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A3'), ('A区', 'A4', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A4'), ('A区', 'A5', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A5'), ('A区', 'A6', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A6'), ('A区', 'A7', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A7'), ('A区', 'A8', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A8'), ('A区', 'A9', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A9'), ('A区', 'A10', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A10'), ('A区', 'A11', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A11'), ('A区', 'A12', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A12'), ('A区', 'A13', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A13'), ('A区', 'A14', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A14'), ('A区', 'A15', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A15'), ('A区', 'A16', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A16'), ('A区', 'A17', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A17'), ('A区', 'A18', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'A区-A18'), -- B区（15台） ('B区', 'B1', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B1'), ('B区', 'B2', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B2'), ('B区', 'B3', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B3'), ('B区', 'B4', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B4'), ('B区', 'B5', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B5'), ('B区', 'B6', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B6'), ('B区', 'B7', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B7'), ('B区', 'B8', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B8'), ('B区', 'B9', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B9'), ('B区', 'B10', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B10'), ('B区', 'B11', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B11'), ('B区', 'B12', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B12'), ('B区', 'B13', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B13'), ('B区', 'B14', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B14'), ('B区', 'B15', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'B区-B15'), -- C区（6台） ('C区', 'C1', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'C区-C1'), ('C区', 'C2', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'C区-C2'), ('C区', 'C3', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'C区-C3'), ('C区', 'C4', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'C区-C4'), ('C区', 'C5', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'C区-C5'), ('C区', 'C6', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'C区-C6'), -- VIP包厢 BILLIARD（3台） ('VIP包厢', 'VIP1', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'VIP包厢-VIP1'), ('VIP包厢', 'VIP2', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'VIP包厢-VIP2'), ('VIP包厢', 'VIP3', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'VIP包厢-VIP3'), -- TV台（1台） ('TV台', 'TV', 'BILLIARD', '🎱 中式/追分', '🎱 中式/追分', '🎱', 'EXACT', 10, TRUE, 'TV台-TV'), -- ============ SNOOKER 斯诺克 ============ ('VIP包厢', 'VIP5', 'SNOOKER', '斯诺克', '斯诺克', '斯', 'EXACT', 10, TRUE, 'VIP包厢-VIP5→斯诺克'), ('斯诺克区', 'S1', 'SNOOKER', '斯诺克', '斯诺克', '斯', 'EXACT', 10, TRUE, '斯诺克区-S1'), ('斯诺克区', 'S2', 'SNOOKER', '斯诺克', '斯诺克', '斯', 'EXACT', 10, TRUE, '斯诺克区-S2'), ('斯诺克区', 'S3', 'SNOOKER', '斯诺克', '斯诺克', '斯', 'EXACT', 10, TRUE, '斯诺克区-S3'), ('斯诺克区', 'S4', 'SNOOKER', '斯诺克', '斯诺克', '斯', 'EXACT', 10, TRUE, '斯诺克区-S4'), -- ============ MAHJONG 🀄 麻将/棋牌 ============ ('666', '董事办', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, '666-董事办'), ('666', '666', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, '666-666'), ('麻将房', 'Ｍ1', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, '麻将房-Ｍ1'), ('麻将房', 'M2', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, '麻将房-M2'), ('麻将房', 'M3', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, '麻将房-M3'), ('麻将房', 'M4', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, '麻将房-M4'), ('麻将房', 'M5', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, '麻将房-M5'), ('M7', 'M7', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, 'M7-M7'), ('M7', '大包麻将房', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, 'M7-大包麻将房'), ('M8', 'M8', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, 'M8-M8'), ('发财', '发财', 'MAHJONG', '🀄 麻将/棋牌', '🀄 麻将/棋牌', '🀄', 'EXACT', 10, TRUE, '发财-发财'), -- ============ KTV 🎤 团建/K歌 ============ ('K包', '常乐', 'KTV', '🎤 团建/K歌', '🎤 团建/K歌', '🎤', 'EXACT', 10, TRUE, 'K包-常乐'), ('K包', '幸会(纯k)', 'KTV', '🎤 团建/K歌', '🎤 团建/K歌', '🎤', 'EXACT', 10, TRUE, 'K包-幸会(纯k)'), ('K包', '虚拟188', 'KTV', '🎤 团建/K歌', '🎤 团建/K歌', '🎤', 'EXACT', 10, TRUE, 'K包-虚拟188'), ('K包', '888', 'KTV', '🎤 团建/K歌', '🎤 团建/K歌', '🎤', 'EXACT', 10, TRUE, 'K包-888'), ('k包活动区', '大包', 'KTV', '🎤 团建/K歌', '🎤 团建/K歌', '🎤', 'EXACT', 10, TRUE, 'k包活动区-大包'), ('k包活动区', '小包', 'KTV', '🎤 团建/K歌', '🎤 团建/K歌', '🎤', 'EXACT', 10, TRUE, 'k包活动区-小包'), ('幸会158', '纯k', 'KTV', '🎤 团建/K歌', '🎤 团建/K歌', '🎤', 'EXACT', 10, TRUE, '幸会158-纯k'), -- ============ SPECIAL 补时长/虚拟台 ============ ('补时长', '补时长', 'SPECIAL', '补时长', '补时长', '补', 'EXACT', 10, TRUE, '补时长-补时长'), ('补时长', '补时长2', 'SPECIAL', '补时长', '补时长', '补', 'EXACT', 10, TRUE, '补时长-补时长2'), ('补时长', '补时长3', 'SPECIAL', '补时长', '补时长', '补', 'EXACT', 10, TRUE, '补时长-补时长3'), ('补时长', '补时长4', 'SPECIAL', '补时长', '补时长', '补', 'EXACT', 10, TRUE, '补时长-补时长4'), ('补时长', '补时长5', 'SPECIAL', '补时长', '补时长', '补', 'EXACT', 10, TRUE, '补时长-补时长5'), ('补时长', '补时长6', 'SPECIAL', '补时长', '补时长', '补', 'EXACT', 10, TRUE, '补时长-补时长6'), ('补时长', '补时长7', 'SPECIAL', '补时长', '补时长', '补', 'EXACT', 10, TRUE, '补时长-补时长7'), ('虚拟台', '虚拟台1号', 'SPECIAL', '补时长', '补时长', '补', 'EXACT', 10, TRUE, '虚拟台-虚拟台1号'), -- ============ OTHER 兜底 ============ ('DEFAULT', NULL, 'OTHER', '其他', '其他', '他', 'DEFAULT', 999, TRUE, '兜底规则：无法匹配的归入其他'); -- ============================================================================= -- 5. cfg_skill_type - 技能→课程类型映射 -- 说明: -- - 将 skill_id 映射到课程类型 -- - 基础课/陪打: skill_id = 2791903611396869 -- - 附加课/超休: skill_id = 2807440316432197 -- - 避免依赖 skill_name 文本匹配 -- ============================================================================= TRUNCATE TABLE dws.cfg_skill_type RESTART IDENTITY CASCADE; INSERT INTO dws.cfg_skill_type ( skill_id, skill_name, course_type_code, course_type_name, is_active, description ) VALUES (2791903611396869, '台球基础陪打', 'BASE', '基础课', TRUE, '基础课：陪打服务，按助教等级计价'), (2807440316432197, '台球超休服务', 'BONUS', '附加课', TRUE, '附加课：超休/激励课，固定190元/小时'), (2807440316432198, '包厢服务', 'BASE', '基础课', TRUE, '包厢服务：归入基础课统计，统一按138元/小时计价'); -- ============================================================================= -- 6~8. 优惠类型/支出类型/平台类型 — 作为代码常量使用，不单独建表 -- ============================================================================= -- 优惠类型: GROUPBUY/VIP/GIFT_CARD/MANUAL/ROUNDING/BIG_CUSTOMER/OTHER -- 支出类型: RENT/UTILITY/PROPERTY/SALARY/REIMBURSE/PLATFORM_FEE/OTHER -- 平台类型: MEITUAN/DOUYIN/DIANPING/OTHER -- ============================================================================= -- 验证数据插入 -- ============================================================================= DO $ DECLARE v_tier_count INTEGER; v_price_count INTEGER; v_bonus_count INTEGER; v_area_count INTEGER; v_skill_count INTEGER; BEGIN SELECT COUNT(*) INTO v_tier_count FROM dws.cfg_performance_tier; SELECT COUNT(*) INTO v_price_count FROM dws.cfg_assistant_level_price; SELECT COUNT(*) INTO v_bonus_count FROM dws.cfg_bonus_rules; SELECT COUNT(*) INTO v_area_count FROM dws.cfg_area_category; SELECT COUNT(*) INTO v_skill_count FROM dws.cfg_skill_type; RAISE NOTICE '配置数据初始化完成:'; RAISE NOTICE ' - cfg_performance_tier: % 条', v_tier_count; RAISE NOTICE ' - cfg_assistant_level_price: % 条', v_price_count; RAISE NOTICE ' - cfg_bonus_rules: % 条', v_bonus_count; RAISE NOTICE ' - cfg_area_category: % 条', v_area_count; RAISE NOTICE ' - cfg_skill_type: % 条', v_skill_count; END; $; -- ============================================================================= -- 种子数据：指数算法参数（NCI/WBI/RS/OS/MS/ML/SPI） -- ============================================================================= -- ============================================================================= -- 指数算法参数初始化脚本 -- 版本: v3.0 -- 创建日期: 2026-02-13 -- 描述: 仅保留 RS / OS / MS / ML / NCI / WBI 指数参数（已移除 RECALL / INTIMACY） -- AI_CHANGELOG [2026-02-13] 移除 RECALL/INTIMACY 参数及 ML 废弃参数（source_mode/recharge_attribute_hours） -- ============================================================================= -- 清理旧版指数参数 DELETE FROM dws.cfg_index_parameters WHERE index_type IN ('RECALL', 'INTIMACY'); -- 清理 ML 已废弃参数 DELETE FROM dws.cfg_index_parameters WHERE index_type = 'ML' AND param_name IN ('source_mode', 'recharge_attribute_hours'); INSERT INTO dws.cfg_index_parameters (index_type, param_name, param_value, description, effective_from) VALUES ('NCI', 'active_new_penalty', 0.200000, 'active-new suppression multiplier', DATE '2026-02-06'), ('NCI', 'active_new_recency_days', 7.000000, 'active-new recency window (days)', DATE '2026-02-06'), ('NCI', 'active_new_visit_threshold_14d', 2.000000, 'active-new threshold in 14d visits', DATE '2026-02-06'), ('NCI', 'amount_base_M0', 300.000000, 'spend log base M0', DATE '2026-02-06'), ('NCI', 'balance_base_B0', 500.000000, 'balance log base B0', DATE '2026-02-06'), ('NCI', 'compression_mode', 0.000000, 'compression mode', DATE '2026-02-06'), ('NCI', 'enable_stop_high_balance_exception', 0.000000, 'enable high-balance STOP exception', DATE '2026-02-06'), ('NCI', 'ewma_alpha', 0.200000, 'EWMA alpha', DATE '2026-02-06'), ('NCI', 'h_recharge', 7.000000, 'recharge decay half-life (days)', DATE '2026-02-06'), ('NCI', 'high_balance_threshold', 1000.000000, 'high-balance threshold', DATE '2026-02-06'), ('NCI', 'lookback_days_recency', 60.000000, 'recency lookback window (days)', DATE '2026-02-06'), ('NCI', 'new_days_threshold', 30.000000, 'new member days threshold', DATE '2026-02-06'), ('NCI', 'new_recharge_max_visits', 10.000000, 'max visits for new-recharge grouping', DATE '2026-02-06'), ('NCI', 'new_visit_threshold', 2.000000, 'new member visit threshold', DATE '2026-02-06'), ('NCI', 'no_touch_days_new', 3.000000, 'no-touch threshold (days)', DATE '2026-02-06'), ('NCI', 'percentile_lower', 5.000000, 'lower percentile', DATE '2026-02-06'), ('NCI', 'percentile_upper', 95.000000, 'upper percentile', DATE '2026-02-06'), ('NCI', 'recharge_recent_days', 14.000000, 'recent recharge window (days)', DATE '2026-02-06'), ('NCI', 'salvage_end', 60.000000, 'salvage decay end day', DATE '2026-02-06'), ('NCI', 'salvage_start', 30.000000, 'salvage decay start day', DATE '2026-02-06'), ('NCI', 't2_target_days', 7.000000, 'second-visit target window (days)', DATE '2026-02-06'), ('NCI', 'use_smoothing', 1.000000, 'enable smoothing', DATE '2026-02-06'), ('NCI', 'value_w_bal', 0.800000, 'value weight for balance', DATE '2026-02-06'), ('NCI', 'value_w_spend', 1.000000, 'value weight for spend', DATE '2026-02-06'), ('NCI', 'visit_lookback_days', 180.000000, 'visit history lookback (days)', DATE '2026-02-06'), ('NCI', 'w_need', 1.600000, 'need weight', DATE '2026-02-06'), ('NCI', 'w_re', 0.800000, 'recharge pressure weight', DATE '2026-02-06'), ('NCI', 'w_value', 1.000000, 'value weight', DATE '2026-02-06'), ('NCI', 'w_welcome', 1.000000, 'welcome-stage weight', DATE '2026-02-06'), ('NCI', 'welcome_window_days', 3.000000, 'welcome outreach window for first touch (days)', DATE '2026-02-06'), ('WBI', 'amount_base_M0', 300.000000, 'spend log base M0', DATE '2026-02-06'), ('WBI', 'balance_base_B0', 500.000000, 'balance log base B0', DATE '2026-02-06'), ('WBI', 'compression_mode', 0.000000, 'compression mode', DATE '2026-02-06'), ('WBI', 'enable_stop_high_balance_exception', 0.000000, 'enable high-balance STOP exception', DATE '2026-02-06'), ('WBI', 'ewma_alpha', 0.200000, 'EWMA alpha', DATE '2026-02-06'), ('WBI', 'h_recharge', 7.000000, 'recharge decay half-life (days)', DATE '2026-02-06'), ('WBI', 'high_balance_threshold', 1000.000000, 'high-balance threshold', DATE '2026-02-06'), ('WBI', 'lookback_days_recency', 60.000000, 'recency lookback window (days)', DATE '2026-02-06'), ('WBI', 'new_days_threshold', 30.000000, 'new member days threshold', DATE '2026-02-06'), ('WBI', 'new_recharge_max_visits', 10.000000, 'max visits for new-recharge grouping', DATE '2026-02-06'), ('WBI', 'new_visit_threshold', 2.000000, 'new member visit threshold', DATE '2026-02-06'), ('WBI', 'overdue_alpha', 2.000000, 'overdue fallback alpha', DATE '2026-02-06'), ('WBI', 'overdue_weight_blend_min_samples', 8.000000, 'minimum samples to fully trust weighted overdue CDF', DATE '2026-02-07'), ('WBI', 'overdue_weight_halflife_days', 30.000000, 'overdue weighted-CDF interval half-life (days)', DATE '2026-02-07'), ('WBI', 'percentile_lower', 5.000000, 'lower percentile', DATE '2026-02-06'), ('WBI', 'percentile_upper', 95.000000, 'upper percentile', DATE '2026-02-06'), ('WBI', 'recency_gate_days', 14.000000, 'recency suppression gate center (days)', DATE '2026-02-06'), ('WBI', 'recency_gate_slope_days', 3.000000, 'recency suppression slope (days)', DATE '2026-02-06'), ('WBI', 'recency_hard_floor_days', 14.000000, 'hard floor for winback recency (days)', DATE '2026-02-06'), ('WBI', 'recharge_recent_days', 14.000000, 'recent recharge window (days)', DATE '2026-02-06'), ('WBI', 'use_smoothing', 1.000000, 'enable smoothing', DATE '2026-02-06'), ('WBI', 'value_w_bal', 1.000000, 'value weight for balance', DATE '2026-02-06'), ('WBI', 'value_w_spend', 1.000000, 'value weight for spend', DATE '2026-02-06'), ('WBI', 'visit_lookback_days', 180.000000, 'visit history lookback (days)', DATE '2026-02-06'), ('WBI', 'w_drop', 1.000000, 'drop weight', DATE '2026-02-06'), ('WBI', 'w_over', 2.000000, 'overdue weight', DATE '2026-02-06'), ('WBI', 'w_re', 0.400000, 'recharge pressure weight', DATE '2026-02-06'), ('WBI', 'w_value', 1.200000, 'value weight', DATE '2026-02-06') ON CONFLICT (index_type, param_name, effective_from) DO UPDATE SET param_value = EXCLUDED.param_value, description = EXCLUDED.description, updated_at = NOW(); -- ============================================================================= -- 关系指数（RS/OS/MS/ML）参数 -- 生效时间：北京时间 2026-01-01（按数据库日期管理） -- ============================================================================= INSERT INTO dws.cfg_index_parameters (index_type, param_name, param_value, description, effective_from) VALUES -- RS（关系强度） ('RS', 'lookback_days', 60.000000, '服务行为回溯窗口（天）', DATE '2026-01-01'), ('RS', 'session_merge_hours', 4.000000, '会话合并阈值（小时）', DATE '2026-01-01'), ('RS', 'incentive_weight', 1.500000, '激励课权重', DATE '2026-01-01'), ('RS', 'halflife_session', 14.000000, '会话半衰期（天）', DATE '2026-01-01'), ('RS', 'halflife_last', 10.000000, '最近一次服务半衰期（天）', DATE '2026-01-01'), ('RS', 'weight_f', 1.000000, '频次项权重', DATE '2026-01-01'), ('RS', 'weight_d', 0.700000, '时长项权重', DATE '2026-01-01'), ('RS', 'gate_alpha', 0.600000, '最近服务门控指数', DATE '2026-01-01'), ('RS', 'percentile_lower', 5.000000, '展示分下分位', DATE '2026-01-01'), ('RS', 'percentile_upper', 95.000000, '展示分上分位', DATE '2026-01-01'), ('RS', 'compression_mode', 1.000000, '压缩模式：0=none,1=log1p,2=asinh', DATE '2026-01-01'), ('RS', 'use_smoothing', 1.000000, '是否启用分位平滑', DATE '2026-01-01'), ('RS', 'ewma_alpha', 0.200000, 'EWMA平滑系数', DATE '2026-01-01'), -- OS（归属份额） ('OS', 'min_rs_raw_for_ownership', 0.050000, '参与归属计算的最小RS_raw', DATE '2026-01-01'), ('OS', 'min_total_rs_raw', 0.100000, '形成稳定归属的最小sum_rs', DATE '2026-01-01'), ('OS', 'ownership_main_threshold', 0.600000, '主责阈值', DATE '2026-01-01'), ('OS', 'ownership_comanage_threshold', 0.350000, '共管阈值', DATE '2026-01-01'), ('OS', 'ownership_gap_threshold', 0.150000, '主责与次席份额差阈值', DATE '2026-01-01'), ('OS', 'eps', 0.000001, '数值稳定项', DATE '2026-01-01'), -- MS（升温动量） ('MS', 'lookback_days', 60.000000, '服务行为回溯窗口（天）', DATE '2026-01-01'), ('MS', 'session_merge_hours', 4.000000, '会话合并阈值（小时）', DATE '2026-01-01'), ('MS', 'incentive_weight', 1.500000, '激励课权重', DATE '2026-01-01'), ('MS', 'halflife_short', 7.000000, '短期半衰期（天）', DATE '2026-01-01'), ('MS', 'halflife_long', 30.000000, '长期半衰期（天）', DATE '2026-01-01'), ('MS', 'eps', 0.000001, '数值稳定项', DATE '2026-01-01'), ('MS', 'percentile_lower', 5.000000, '展示分下分位', DATE '2026-01-01'), ('MS', 'percentile_upper', 95.000000, '展示分上分位', DATE '2026-01-01'), ('MS', 'compression_mode', 1.000000, '压缩模式：0=none,1=log1p,2=asinh', DATE '2026-01-01'), ('MS', 'use_smoothing', 1.000000, '是否启用分位平滑', DATE '2026-01-01'), ('MS', 'ewma_alpha', 0.200000, 'EWMA平滑系数', DATE '2026-01-01'), -- ML（付费关联） ('ML', 'lookback_days', 60.000000, '充值行为回溯窗口（天）', DATE '2026-01-01'), ('ML', 'amount_base', 500.000000, '金额压缩基准', DATE '2026-01-01'), ('ML', 'halflife_recharge', 21.000000, '充值半衰期（天）', DATE '2026-01-01'), ('ML', 'percentile_lower', 5.000000, '展示分下分位', DATE '2026-01-01'), ('ML', 'percentile_upper', 95.000000, '展示分上分位', DATE '2026-01-01'), ('ML', 'compression_mode', 1.000000, '压缩模式：0=none,1=log1p,2=asinh', DATE '2026-01-01'), ('ML', 'use_smoothing', 1.000000, '是否启用分位平滑', DATE '2026-01-01'), ('ML', 'ewma_alpha', 0.200000, 'EWMA平滑系数', DATE '2026-01-01') ON CONFLICT (index_type, param_name, effective_from) DO UPDATE SET param_value = EXCLUDED.param_value, description = EXCLUDED.description, updated_at = NOW(); -- ============================================================================= -- SPI（消费力指数）参数 -- 生效时间：北京时间 2026-02-23 -- ============================================================================= INSERT INTO dws.cfg_index_parameters (index_type, param_name, param_value, description, effective_from) VALUES -- 窗口参数 ('SPI', 'spend_window_short_days', 30.000000, '短期消费窗口（天）', DATE '2026-02-23'), ('SPI', 'spend_window_long_days', 90.000000, '长期消费窗口（天）', DATE '2026-02-23'), ('SPI', 'ewma_alpha_daily_spend', 0.300000, '日消费 EWMA 平滑系数', DATE '2026-02-23'), -- 金额压缩基数（基于典型台球门店消费水平的初始默认值） ('SPI', 'amount_base_spend_30', 500.000000, '30天消费额压缩基数', DATE '2026-02-23'), ('SPI', 'amount_base_spend_90', 1500.000000, '90天消费额压缩基数', DATE '2026-02-23'), ('SPI', 'amount_base_ticket_90', 200.000000, '90天客单价压缩基数', DATE '2026-02-23'), ('SPI', 'amount_base_recharge_90', 1000.000000, '90天充值额压缩基数', DATE '2026-02-23'), ('SPI', 'amount_base_speed_abs', 100.000000, '绝对速度压缩基数', DATE '2026-02-23'), ('SPI', 'amount_base_ewma_90', 50.000000, '日消费EWMA压缩基数', DATE '2026-02-23'), -- Level 子分权重 ('SPI', 'w_level_spend_30', 0.300000, 'Level子分：30天消费权重', DATE '2026-02-23'), ('SPI', 'w_level_spend_90', 0.350000, 'Level子分：90天消费权重', DATE '2026-02-23'), ('SPI', 'w_level_ticket_90', 0.200000, 'Level子分：90天客单权重', DATE '2026-02-23'), ('SPI', 'w_level_recharge_90', 0.150000, 'Level子分：90天充值权重', DATE '2026-02-23'), -- Speed 子分权重 ('SPI', 'w_speed_abs', 0.500000, 'Speed子分：绝对速度权重', DATE '2026-02-23'), ('SPI', 'w_speed_rel', 0.300000, 'Speed子分：相对速度权重', DATE '2026-02-23'), ('SPI', 'w_speed_ewma', 0.200000, 'Speed子分：EWMA速度权重', DATE '2026-02-23'), -- 总分权重 ('SPI', 'weight_level', 0.600000, 'SPI总分：Level子分权重', DATE '2026-02-23'), ('SPI', 'weight_speed', 0.300000, 'SPI总分：Speed子分权重', DATE '2026-02-23'), ('SPI', 'weight_stability', 0.100000, 'SPI总分：Stability子分权重', DATE '2026-02-23'), -- 稳定性参数 ('SPI', 'stability_window_days', 90.000000, '稳定性计算窗口（天）', DATE '2026-02-23'), ('SPI', 'use_stability', 1.000000, '是否启用稳定性子分：0=关闭,1=启用', DATE '2026-02-23'), -- 映射与平滑 ('SPI', 'percentile_lower', 5.000000, '展示分下分位', DATE '2026-02-23'), ('SPI', 'percentile_upper', 95.000000, '展示分上分位', DATE '2026-02-23'), ('SPI', 'compression_mode', 1.000000, '压缩模式：0=none,1=log1p,2=asinh', DATE '2026-02-23'), ('SPI', 'use_smoothing', 1.000000, '是否启用分位平滑', DATE '2026-02-23'), ('SPI', 'ewma_alpha', 0.200000, 'EWMA平滑系数', DATE '2026-02-23'), -- 速度计算 ('SPI', 'speed_epsilon', 0.000001, '速度计算防除零小量', DATE '2026-02-23') ON CONFLICT (index_type, param_name, effective_from) DO UPDATE SET param_value = EXCLUDED.param_value, description = EXCLUDED.description, updated_at = NOW(); -- ============================================================================= -- 验证 -- ============================================================================= DO $ DECLARE rs_count INTEGER; os_count INTEGER; ms_count INTEGER; ml_count INTEGER; nci_count INTEGER; wbi_count INTEGER; spi_count INTEGER; BEGIN SELECT COUNT(*) INTO rs_count FROM dws.cfg_index_parameters WHERE index_type = 'RS'; SELECT COUNT(*) INTO os_count FROM dws.cfg_index_parameters WHERE index_type = 'OS'; SELECT COUNT(*) INTO ms_count FROM dws.cfg_index_parameters WHERE index_type = 'MS'; SELECT COUNT(*) INTO ml_count FROM dws.cfg_index_parameters WHERE index_type = 'ML'; SELECT COUNT(*) INTO nci_count FROM dws.cfg_index_parameters WHERE index_type = 'NCI'; SELECT COUNT(*) INTO wbi_count FROM dws.cfg_index_parameters WHERE index_type = 'WBI'; SELECT COUNT(*) INTO spi_count FROM dws.cfg_index_parameters WHERE index_type = 'SPI'; RAISE NOTICE 'RS 参数数量: %', rs_count; RAISE NOTICE 'OS 参数数量: %', os_count; RAISE NOTICE 'MS 参数数量: %', ms_count; RAISE NOTICE 'ML 参数数量: %', ml_count; RAISE NOTICE '新客转化参数数量: %', nci_count; RAISE NOTICE '唤回指数参数数量: %', wbi_count; RAISE NOTICE 'SPI 消费力指数参数数量: %', spi_count; END $; SELECT index_type, param_name, param_value, description, effective_from FROM dws.cfg_index_parameters ORDER BY index_type, param_name, effective_from;