postgres中记录的交集

zpjtge22  于 2021-07-24  发布在  Java
关注(0)|答案(4)|浏览(396)

假设我有多个与它们相关联的商店的标签,如下所示:

label_id | store_id
--------------------
label_1  | store_1
label_1  | store_2
label_1  | store_3
label_2  | store_2
label_2  | store_3
label_3  | store_1
label_3  | store_2

在sql(或jooq)中,有什么好方法可以获得标签交叉点中的所有存储id吗?这意味着在上面的示例中只返回store_2,因为store_2与label_1、label_2和label_3关联?我想一个一般的方法来处理的情况下,我有n个标签。

bejyjqdl

bejyjqdl1#

然后将@gmb的查询转换成一个sql函数,该函数接受一个数组并返回一个store\ id表。

create or replace 
function stores_with_all_labels( label_list text[] )
 returns table (store_id text) 
 language  sql
as $$
    select store_id
      from label_store
     where label_id = any (label_list)
     group by store_id
    having count(*) = array_length(label_list,1);
$$;

那么所需要的只是一个简单的选择。请参阅此处的完整示例。

dgjrabp2

dgjrabp22#

这是一个关系划分问题,您需要具有所有可能标签的存储。下面是一种使用聚合的方法:

select store_id
from mytable
group by store_id
having count(*) = (select count(distinct label_id) from mytable)

请注意,这假设没有重复 (store_id, label_id) 元组。否则,您需要更改 having 条款至:

having count(distinct label_id) = (select count(distinct label_id) from mytable)
5ktev3wc

5ktev3wc3#

由于您也在寻找一个jooq解决方案,jooq支持一个合成的关系除法运算符,它产生了一种更具学术性的关系除法方法,只使用关系代数运算符:

// Using jOOQ
T t1 = T.as("t1");
T t2 = T.as("t2");

ctx.select()
   .from(t1.divideBy(t2).on(t1.LABEL_ID.eq(t2.LABEL_ID)).returning(t1.STORE_ID).as("t"))
   .fetch();

这将生成类似以下查询的内容:

select t.store_id
from (
  select distinct dividend.store_id
  from t dividend
  where not exists (
    select 1
    from t t2
    where not exists (
      select 1
      from t t1
      where dividend.store_id = t1.store_id
      and t1.label_id = t2.label_id 
    )
  )
) t

用简单的英语:
给我所有商店(股息),其中不存在标签(t2),该商店(股息)没有条目(t1)
或者换句话说
如果有一个标签(t2),而存储(被除数)没有(t1),那么该存储(被除数)就不会有所有可用的标签。
这并不一定比 GROUP BY / HAVING COUNT(*) 基于关系划分的实现(如其他答案所示),实际上 GROUP BY / HAVING 在这里,可能更倾向于基于表的解决方案,特别是因为只涉及一个表。jooq的未来版本可能会使用 GROUP BY / HAVING 取而代之的是:#10450
但是在jooq中,这样写可能非常方便,您要求jooq解决方案:)

zfycwa2u

zfycwa2u4#

如果需要三个特定标签,可以使用:

select store_id
from t
where label in (1, 2, 3)
group by store_id
having count(*) = 3;

如果您只需要这三个标签而不需要其他标签,那么:

select store_id
from t
group by store_id
having count(*) = 3 and
       count(*) filter (where label in (1, 2, 3)) = count(*);

相关问题