PostgreSQL：如果发现多行具有相似列值，则跳过 Select 行

发布于03月23日

我有以下 Select :

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
WHERE posm.scope_id ='e6d11bfe-e5fc-11ee-8cda-238cdb93d281'
GROUP BY posm.id, cc.code, ccp.code, pop.id

that returns such result:

如果有org_scope_metric_id个不同的program_id，我需要从 Select 中跳过它们.

请帮助解决！

推荐答案

统计每组中相等的org_scope_metric_id次出现的次数和不同的program_id-S的次数，然后过滤结果行中大于1的both个数字.

with t as ( 
  select *, 
    count(*) over w cnt,
    array_agg(program_id) over w program_id_list
  from (<your query here>) t 
  window w as (partition by org_scope_metric_id)
)
select * from t 
where cnt > 1 and array_distinct_count(program_id_list) > 1;

你需要一个辅助函数来实现—

create or replace function array_distinct_count(arr anyarray)
returns integer language sql immutable as $$
 select count(distinct el) from unnest(arr) el;
$$;