考虑以下表格结构:
CREATE TABLE residences (id int, price int, categories jsonb);
INSERT INTO residences VALUES
(1, 3, '["monkeys", "hamsters", "foxes"]'),
(2, 5, '["monkeys", "hamsters", "foxes", "foxes"]'),
(3, 7, '[]'),
(4, 11, '["turtles"]');
SELECT * FROM residences;
id | price | categories
----+-------+-------------------------------------------
1 | 3 | ["monkeys", "hamsters", "foxes"]
2 | 5 | ["monkeys", "hamsters", "foxes", "foxes"]
3 | 7 | []
4 | 11 | ["turtles"]
现在我想知道每个类别有多少住宅,以及它们的价格总和。我发现的唯一方法是使用子查询:
SELECT category, SUM(price), COUNT(*) AS residences_no
FROM
residences a,
(
SELECT DISTINCT(jsonb_array_elements(categories)) AS category
FROM residences
) b
WHERE a.categories @> category
GROUP BY category
ORDER BY category;
category | sum | residences_no
------------+-----+---------------
"foxes" | 8 | 2
"hamsters" | 8 | 2
"monkeys" | 8 | 2
"turtles" | 11 | 1
使用不带子查询的jsonb_array_elements将返回狐狸的三个住所,因为第二行中有重复的条目。住宅的价格也会上涨5.
有没有什么方法可以不使用子查询来完成这个任务,或者有没有更好的方法来完成这个任务?
编辑
起初,我没有提到价格。
1条答案
按热度按时间k4emjkb11#
您必须使用派生表来聚合另一列(所有价格均为10):