PostgreSQL查询优化：排除对应多program_id的org_scope_metric_id

你可以通过两种方式实现需求：排除所有对应多个不同program_id的org_scope_metric_id，只保留仅关联唯一program_id的行。

方法1：使用窗口函数（简洁直观） #

在原查询中添加窗口函数统计每个org_scope_metric_id对应的不同program_id数量，再筛选出数量为1的记录：

WITH base_query AS (
    SELECT
        posm.id AS org_scope_metric_id,
        cc.code AS control_code,
        ccp.code AS part_code,
        pop.id AS program_id,
        -- 统计当前org_scope_metric_id关联的不同program_id总数
        COUNT(DISTINCT pop.id) OVER (PARTITION BY posm.id) AS program_count
    FROM public.org_scope_metric posm
    JOIN public.org_metric pom ON pom.id = posm.metric_id
    JOIN compliance.metric cm ON cm.id = pom.metric_id
    JOIN compliance.control_part_metric ccpm ON ccpm.metric_id = cm.id
    JOIN compliance.control_part ccp ON ccp.id = ccpm.part_id
    JOIN compliance.control cc ON cc.id = ccp.control_id
    JOIN compliance.program_framework cpf ON cpf.framework_id = cc.framework_id
    JOIN public.org_program pop ON pop.program_id = cpf.program_id
    WHERE posm.scope_id ='e6d11bfe-e5fc-11ee-8cda-238cdb93d281'
    GROUP BY posm.id, cc.code, ccp.code, pop.id
)
SELECT
    org_scope_metric_id,
    control_code,
    part_code,
    program_id
FROM base_query
WHERE program_count = 1;

方法2：使用子查询预筛选合格ID（适合大数据量场景） #

先通过子查询找出仅关联唯一program_id的org_scope_metric_id，再与原查询结果关联过滤：

-- 先筛选出符合条件的org_scope_metric_id
WITH valid_org_scope_ids AS (
    SELECT
        posm.id AS org_scope_metric_id
    FROM public.org_scope_metric posm
    JOIN public.org_metric pom ON pom.id = posm.metric_id
    JOIN compliance.metric cm ON cm.id = pom.metric_id
    JOIN compliance.control_part_metric ccpm ON ccpm.metric_id = cm.id
    JOIN compliance.control_part ccp ON ccp.id = ccpm.part_id
    JOIN compliance.control cc ON cc.id = ccp.control_id
    JOIN compliance.program_framework cpf ON cpf.framework_id = cc.framework_id
    JOIN public.org_program pop ON pop.program_id = cpf.program_id
    WHERE posm.scope_id ='e6d11bfe-e5fc-11ee-8cda-238cdb93d281'
    GROUP BY posm.id
    HAVING COUNT(DISTINCT pop.id) = 1
)
-- 关联原查询，只保留合格ID对应的行
SELECT
    posm.id AS org_scope_metric_id,
    cc.code AS control_code,
    ccp.code AS part_code,
    pop.id AS program_id
FROM public.org_scope_metric posm
JOIN public.org_metric pom ON pom.id = posm.metric_id
JOIN compliance.metric cm ON cm.id = pom.metric_id
JOIN compliance.control_part_metric ccpm ON ccpm.metric_id = cm.id
JOIN compliance.control_part ccp ON ccp.id = ccpm.part_id
JOIN compliance.control cc ON cc.id = ccp.control_id
JOIN compliance.program_framework cpf ON cpf.framework_id = cc.framework_id
JOIN public.org_program pop ON pop.program_id = cpf.program_id
JOIN valid_org_scope_ids vos ON vos.org_scope_metric_id = posm.id
WHERE posm.scope_id ='e6d11bfe-e5fc-11ee-8cda-238cdb93d281'
GROUP BY posm.id, cc.code, ccp.code, pop.id;

说明 #

两种方法核心逻辑一致：

1. 统计每个org_scope_metric_id关联的不同program_id数量
2. 仅保留数量为1的org_scope_metric_id对应的所有行

小数据集用方法1更简洁；大数据量场景下方法2性能更优，因为它先缩小了后续查询的数据集范围。

内容的提问来源于stack exchange，提问作者Vyacheslav Fedorin