使用 Postgres 9.4,我有兴趣拥有一个整数数组,user_ids_who_like
并提供一个用户数组(如user_ids_i_am_following
)来对该交集进行排序。
就像是:
select *
from items
where [there is an intersection between
user_ids_who_like with user_ids_i_am_following]
order by intersection(user_ids_who_like).count
是否可以通过数组交集进行分组和排序?
示例数据:
items
name | user_ids_who_like
'birds' | '{1,3,5,8}'
'planes' | '{2,3,4,11}'
'spaceships' | '{3,4,6}'
对于给定的user_ids_who_i_follow = [3,4,11]
,我可以做类似的事情:
select * from items
where <user_ids_who_like intersects with user_ids_who_i_follow>
order by <count of that intersection>
期望的结果:
name | user_ids_who_like | count
'planes' | '{2,3,4,11}' | 3
'spaceships' | '{3,4,6}' | 2
'birds' | '{1,3,5,8}' | 1
一种可能性似乎是这样的:
select id, user_ids_who_like, (user_ids_who_like & '{514, 515}'::int[]) as jt
from queryables
where user_ids_who_like && '{514, 515}'
order by icount(user_ids_who_like & '{514, 515}'::int[]) desc;
但我不知道这种风格(使用 intarray 扩展而不是原生数组函数和运算符)是否过时;这里有更老练的用户的反馈吗?我不清楚如何使用methods 和 operator进行两个数组的交集。
仅使用基本 Postgres 安装工具,您可能会
unnest()
计算LATERAL
子查询:我们不需要 a
LEFT JOIN
来保留不匹配count()
的行,因为总是返回一行 - 0 表示“不匹配”。intarray
假设没有 NULL 值或重复
integer
的数组,模块的交集运算符会简单得多:&
intarray
我
NULLS LAST
最后添加了对空数组进行排序 - 在您后面的问题提醒之后:intarray
为此,每个数据库安装一次。在子句中使用重叠运算符
&&
WHERE
来排除没有任何重叠的行:为什么?根据文档:
以类似的方式适用于标准数组运算符。细节:
或者,更彻底地,具有单独表而不是数组列的规范化模式
user_ids_who_like
将占用更多磁盘空间,但为这些问题提供简单的 btree 索引的简单解决方案。