245 lines
15 KiB
SQL
245 lines
15 KiB
SQL
-- =============================================================================
|
||
-- 指数算法参数初始化脚本
|
||
-- 版本: 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;
|