计算Postgres数组中重叠元素的数量 [英] Count the number of overlapping elements in Postgres array

问题描述

我有一个整数数组作为列.

I have an array of integer as a column.

有没有办法查询重叠整数的数量?

Is there a way to query the number of overlapping integers?

例如以下3条记录:

COLUMN1
{1,3,7}
{3,4,5}
{1,2}

如何使用ARRAY [3,4,8]获得重叠元素的数量?

How can I get the number of overlapping elements with ARRAY[3,4,8] ?

我的示例的结果应为:

1 (element 3)
2 (element 3 and 4)
0 (none)

我找到了

SELECT COLUMN1 && ARRAY[44,45,46] FROM table1

但这会返回true或false.

But that returns a true or false.

推荐答案

如果您 intarray 扩展名该扩展名中的交集"运算符:

If you install the intarray extension you can use the "intersection" operator from that extension:

select column1, column1 & ARRAY[3,4,8] as elements
from table1

返回:

column1 | elements
--------+---------
{1,3,8} | {3,8}   
{3,4,5} | {3,4}   
{1,2}   | {}      

要获取结果数组中元素的数量，请使用 cardinality(column1& ARRAY [3,4,8])

To get the number of the elements in the resulting array, use cardinality(column1 & ARRAY[3,4,8])

这篇关于计算Postgres数组中重叠元素的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！