freedakgmail
20 lines
1.5 KiB
SQL
20 lines
1.5 KiB
SQL
-- Step 1: 填充 dim_supplier 从配送流水去重
|
|
INSERT INTO analytics.dim_supplier (supplier_code, supplier_name, supplier_type, contact_person, contact_phone, region, status, created_at, updated_at)
|
|
SELECT d.supplier_code, d.supplier_name, d.supplier_category, d.supplier_contact, d.supplier_phone,
|
|
CASE WHEN d.supplier_address ~ '北京' THEN '北京' WHEN d.supplier_address ~ '福建' THEN '福建'
|
|
WHEN d.supplier_address ~ '四川' THEN '四川' WHEN d.supplier_address ~ '山东' THEN '山东'
|
|
WHEN d.supplier_address ~ '新疆' THEN '新疆' WHEN d.supplier_address ~ '河北' THEN '河北'
|
|
WHEN d.supplier_address ~ '天津' THEN '天津' WHEN d.supplier_address ~ '内蒙古' THEN '内蒙古'
|
|
WHEN d.supplier_address ~ '甘肃' THEN '甘肃' WHEN d.supplier_address ~ '宁夏' THEN '宁夏'
|
|
ELSE '其他' END,
|
|
'启用', now(), now()
|
|
FROM (
|
|
SELECT DISTINCT ON (supplier_code) supplier_code, supplier_name, supplier_category, supplier_contact, supplier_phone, supplier_address
|
|
FROM distribution_detail_records WHERE supplier_code IS NOT NULL
|
|
ORDER BY supplier_code, (supplier_phone IS NOT NULL AND supplier_phone != '') DESC, (supplier_address IS NOT NULL AND supplier_address != '') DESC
|
|
) d
|
|
ON CONFLICT (supplier_code) DO UPDATE SET
|
|
supplier_name = EXCLUDED.supplier_name, supplier_type = EXCLUDED.supplier_type,
|
|
contact_person = EXCLUDED.contact_person, contact_phone = EXCLUDED.contact_phone,
|
|
region = EXCLUDED.region, updated_at = now();
|