如何查询有序哈希表 (PostgreSQL)

发布于 2025-01-12 14:26:12 字数 935 浏览 3 评论 0原文

我有一个哈希表（用于标记项目），其中有一个额外的列，用于确定项目与其标签之间的关系强度：

item_id | tag_name | relationship
1       | linux    | 0.7
1       | computer | 0.9
2       | garden   | 0.5
2       | shovel   | 0.65
...

我想选择所有标记有 'linux' 和 ' 的项目computer'（允许更多标签），按项目与这两个标签之间的关系之和排序。

这是我查询数据库的尝试：

SELECT * FROM items
INNER JOIN (
SELECT items.id FROM items
    INNER JOIN tags ON items.id = tags.item_id
    GROUP BY tags.item_id
    HAVING ARRAY_AGG(tags.tag_id) @> ARRAY['linux','computer']
    ORDER BY SUM(tags.relationship) DESC
) tagged_items ON items.id = tagged_items.id

对于 only 标记有 'linux' 和 'computer' 的项目，这非常有效。但问题是，对于具有附加标签的项目，ORDER BY SUM(tags.relationship) 将对项目所具有的其他标签进行求和...换句话说，具有以下标签的项目大多数标签将首先返回（这不是我想要的）。

如何使 SUM() 仅对与我选择的标签的关系求和？

原文

I have a hash table (for tagging items) with an extra column for the strength of the relationship between items and their tags:

item_id | tag_name | relationship
1       | linux    | 0.7
1       | computer | 0.9
2       | garden   | 0.5
2       | shovel   | 0.65
...

I want to select all items tagged with both 'linux' and 'computer' (more tags are allowed), ordered by the sum of the relationships between items and these two tags.

Here's my attempt at querying the database:

SELECT * FROM items
INNER JOIN (
SELECT items.id FROM items
    INNER JOIN tags ON items.id = tags.item_id
    GROUP BY tags.item_id
    HAVING ARRAY_AGG(tags.tag_id) @> ARRAY['linux','computer']
    ORDER BY SUM(tags.relationship) DESC
) tagged_items ON items.id = tagged_items.id

For items that are only tagged with 'linux' and 'computer', this works perfectly. But the problem is that for items that have additional tags, the ORDER BY SUM(tags.relationship) will sum over the other tags that an item has as well... in other words, items with the most tags will return first (and that's not what I want).

How can I make SUM() only sum over the relationships to the tags that I'm selecting?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝色星空 2025-01-19 14:26:12

您可以简单地使用 in：

select id
from items i
where tags_id in ('linux', 'computer')
group by id
order by sum(relationship) desc;

除非您想要所有列，否则您可以在 cte 和 join 中使用上面的查询：

with tmp as (
    select id
    from items
    where tags_id in ('linux', 'computer')
    group by id
    order by sum(relationship) desc
)
select i.*
from tmp t
inner join items i on t.id = i.id;

You can simply use in:

select id
from items i
where tags_id in ('linux', 'computer')
group by id
order by sum(relationship) desc;

Unless you want all columns, then you can use the query above in cte and join:

with tmp as (
    select id
    from items
    where tags_id in ('linux', 'computer')
    group by id
    order by sum(relationship) desc
)
select i.*
from tmp t
inner join items i on t.id = i.id;

回复收藏 0 原文

~没有更多了~