146 lines
7.0 KiB
PL/PgSQL
146 lines
7.0 KiB
PL/PgSQL
-- 测试/目标环境企业去重与外部导入默认状态修复。
|
||
--
|
||
-- 规则：仅合并名称在忽略大小写和所有空白后完全一致的有效企业；不会做简称、
|
||
-- 有限公司后缀等近似匹配。保留企业优先级为：外部有效岗位数、有效岗位数、
|
||
-- 已审核通过、创建时间、企业 ID。无实时关联的冗余企业才会物理删除；保留
|
||
-- job_bak202607071415 历史快照中的原始 company_id，不将其作为实时引用。
|
||
--
|
||
-- 可重复执行：已删除企业不再进入映射；状态已符合默认值的岗位/企业不会重复更新。
|
||
|
||
BEGIN;
|
||
|
||
SELECT pg_advisory_xact_lock(hashtext('shz.cleanup_duplicate_companies_and_external_defaults'));
|
||
|
||
CREATE TABLE IF NOT EXISTS "shz"."company_duplicate_cleanup_audit" (
|
||
"duplicate_company_id" BIGINT PRIMARY KEY,
|
||
"canonical_company_id" BIGINT NOT NULL,
|
||
"normalized_name" VARCHAR(500) NOT NULL,
|
||
"duplicate_company_name" VARCHAR(500) NOT NULL,
|
||
"cleaned_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"cleaned_by" VARCHAR(64) NOT NULL DEFAULT 'external-import-cleanup'
|
||
);
|
||
|
||
CREATE TEMP TABLE company_duplicate_merge_map ON COMMIT DROP AS
|
||
WITH current_company AS (
|
||
SELECT c.company_id,
|
||
c.name,
|
||
c.status,
|
||
c.create_time,
|
||
LOWER(REGEXP_REPLACE(BTRIM(c.name), '[[:space:]]+', '', 'g')) AS normalized_name,
|
||
COUNT(j.job_id) FILTER (WHERE j.del_flag = '0') AS active_job_count,
|
||
COUNT(j.job_id) FILTER (WHERE j.del_flag = '0' AND j.external_import_flag = '1') AS active_external_job_count
|
||
FROM shz.company c
|
||
LEFT JOIN shz.job j ON j.company_id = c.company_id
|
||
WHERE c.del_flag = '0'
|
||
AND NULLIF(BTRIM(c.name), '') IS NOT NULL
|
||
GROUP BY c.company_id, c.name, c.status, c.create_time
|
||
), ranked AS (
|
||
SELECT current_company.*,
|
||
FIRST_VALUE(company_id) OVER (
|
||
PARTITION BY normalized_name
|
||
ORDER BY active_external_job_count DESC,
|
||
active_job_count DESC,
|
||
CASE WHEN status = 1 THEN 0 ELSE 1 END,
|
||
create_time NULLS LAST,
|
||
company_id
|
||
) AS canonical_company_id,
|
||
COUNT(*) OVER (PARTITION BY normalized_name) AS duplicate_group_size
|
||
FROM current_company
|
||
)
|
||
SELECT company_id AS duplicate_company_id,
|
||
canonical_company_id,
|
||
normalized_name
|
||
FROM ranked
|
||
WHERE duplicate_group_size > 1
|
||
AND company_id <> canonical_company_id;
|
||
|
||
-- 若将来的数据在删除前已经关联了冗余企业，先无损迁移岗位归属。
|
||
UPDATE shz.job j
|
||
SET company_id = map.canonical_company_id,
|
||
update_by = 'external-import-cleanup',
|
||
update_time = CURRENT_TIMESTAMP
|
||
FROM company_duplicate_merge_map map
|
||
WHERE j.company_id = map.duplicate_company_id;
|
||
|
||
-- 仅删除无实时关联记录的企业。所有业务关联表均纳入保护条件；若将来出现新的
|
||
-- 关联数据，脚本会保留该企业而不是产生孤儿数据。
|
||
CREATE TEMP TABLE deletable_duplicate_company ON COMMIT DROP AS
|
||
SELECT map.duplicate_company_id,
|
||
map.canonical_company_id,
|
||
map.normalized_name
|
||
FROM company_duplicate_merge_map map
|
||
WHERE NOT EXISTS (SELECT 1 FROM shz.app_user_block_company t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.cms_outdoor_fair_booth_booking t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.cms_outdoor_fair_device t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.cms_outdoor_fair_job t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.cms_project t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.company_collection t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.company_contact t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.company_job_live t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.company_label t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.employee_confirm t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.fair_company t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.hr_company_talent_collect t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.interview_invitation t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.public_job_fair_company t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.public_job_fair_job t WHERE t.company_id = map.duplicate_company_id)
|
||
AND NOT EXISTS (SELECT 1 FROM shz.resume_access_log t WHERE t.company_id = map.duplicate_company_id);
|
||
|
||
INSERT INTO shz.company_duplicate_cleanup_audit (
|
||
duplicate_company_id, canonical_company_id, normalized_name, duplicate_company_name, cleaned_at, cleaned_by
|
||
)
|
||
SELECT target.duplicate_company_id,
|
||
target.canonical_company_id,
|
||
target.normalized_name,
|
||
c.name,
|
||
CURRENT_TIMESTAMP,
|
||
'external-import-cleanup'
|
||
FROM deletable_duplicate_company target
|
||
JOIN shz.company c ON c.company_id = target.duplicate_company_id
|
||
ON CONFLICT (duplicate_company_id) DO NOTHING;
|
||
|
||
DELETE FROM shz.company c
|
||
USING deletable_duplicate_company target
|
||
WHERE c.company_id = target.duplicate_company_id;
|
||
|
||
-- 外部岗位对用户可见；关联企业视为审核通过且上架。
|
||
UPDATE shz.job
|
||
SET is_publish = 1,
|
||
job_status = '0',
|
||
update_by = 'external-import-cleanup',
|
||
update_time = CURRENT_TIMESTAMP
|
||
WHERE external_import_flag = '1'
|
||
AND del_flag = '0'
|
||
AND (is_publish IS DISTINCT FROM 1 OR job_status IS DISTINCT FROM '0');
|
||
|
||
UPDATE shz.company c
|
||
SET status = 1,
|
||
company_status = '0',
|
||
not_pass_reason = NULL,
|
||
update_by = 'external-import-cleanup',
|
||
update_time = CURRENT_TIMESTAMP
|
||
WHERE c.del_flag = '0'
|
||
AND EXISTS (
|
||
SELECT 1
|
||
FROM shz.job j
|
||
WHERE j.company_id = c.company_id
|
||
AND j.external_import_flag = '1'
|
||
AND j.del_flag = '0'
|
||
)
|
||
AND (c.status IS DISTINCT FROM 1 OR c.company_status IS DISTINCT FROM '0' OR c.not_pass_reason IS NOT NULL);
|
||
|
||
SELECT 'duplicate_companies_detected' AS metric, COUNT(*)::TEXT AS value FROM company_duplicate_merge_map
|
||
UNION ALL
|
||
SELECT 'duplicate_companies_deleted', COUNT(*)::TEXT FROM deletable_duplicate_company
|
||
UNION ALL
|
||
SELECT 'duplicate_companies_retained_for_references',
|
||
(SELECT COUNT(*) - COUNT(*) FILTER (WHERE duplicate_company_id IN (SELECT duplicate_company_id FROM deletable_duplicate_company)) FROM company_duplicate_merge_map)::TEXT
|
||
UNION ALL
|
||
SELECT 'active_external_jobs', COUNT(*)::TEXT FROM shz.job WHERE external_import_flag = '1' AND del_flag = '0'
|
||
UNION ALL
|
||
SELECT 'approved_active_external_companies', COUNT(*)::TEXT FROM shz.company c
|
||
WHERE c.del_flag = '0' AND c.status = 1 AND c.company_status = '0'
|
||
AND EXISTS (SELECT 1 FROM shz.job j WHERE j.company_id = c.company_id AND j.external_import_flag = '1' AND j.del_flag = '0');
|
||
|
||
COMMIT;
|