英文:
Count and join with column type array in Postgres
问题
我有两张表在我的Postgres数据库（v14）中，分别是tags和locations。
示例标签表：
id | tag_name
----+------------
1 | football
2 | tennis
3 | athletics
4 | concert
示例位置表（其中tag_ids是整数数组）：
id | name | tag_ids
----+--------------+------------
1 | Wimbledon | {2}
2 | Wembley | {1,4}
3 | Letzigrund | {3,4}
如何找到标签的名称以及它们被使用的次数？查询应该返回类似以下结果：
tag_name | count
------------+-------
football | 1
tennis | 1
athletics | 1
concert | 2
英文:
I have two tables in my Postgres DB (v14), tags and locations.
Example of tags:
id | tag_name
----+------------
1 | football
2 | tennis
3 | athletics
4 | concert
Example of locations (where tag_ids is array of ints):
id | name | tag_ids
----+--------------+------------
1 | Wimbledon | {2}
2 | Wembley | {1,4}
3 | Letzigrund | {3,4}
How can I find the name of the tags and how many times they are used? The query should result in something like this:
tag_name | count
------------+-------
football | 1
tennis | 1
athletics | 1
concert | 2
答案1
得分: 0
首先将locations中的locations展开为t CTE，然后与tags进行连接。
with t as
(
select id, name, unnest(tag_ids) as tag_id from locations
-- 不需要所有列，仅供示例
)
select tag_name, count(*) as count
from tags join t on t.tag_id = tags.id
group by tag_name;
查看演示。
英文:
First flatten locations as t CTE and then join with tags.
with t as
(
select id, name, unnest(tag_ids) as tag_id from locations
-- not all columns are needed, for illustration only
)
select tag_name, count(*) as count
from tags join t on t.tag_id = tags.id
group by tag_name;
See demo.
答案2
得分: 0
你可以使用联接和GROUP BY 来实现这个：
select t.tag_name, count(*)
from tags t
join locations l on t.id = any(l.tag_ids)
group by t.tag_name
order by t.tag_name;
英文:
You can do this using a join and a GROUP BY:
select t.tag_name, count(*)
from tags t
join locations l on t.id = any(l.tag_ids)
group by t.tag_name
order by t.tag_name;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论