arrays - 计算 array_agg 中的值

Question

考虑以下数据：

|model| qty | color                              |
--------------------------------------------------
|  y  |  6  | {yellow,red,red}                   |
--------------------------------------------------
|  z  |  4  | {red,blue,yellow}                  |
--------------------------------------------------
|  x  |  9  | {yellow,yellow,yellow,blue,yellow} |
--------------------------------------------------

有没有一种方法可以在不使用子查询的情况下计算下面数组中分组的每个“颜色”？（例如，对于“y”，我会使用“yellow:1”、“red:2”）

SELECT model, SUM(qty), ARRAY_AGG(color) FROM test GROUP BY model;

就像是：

SELECT model, SUM(qty), ARRAY_AGG(ARRAY[color, COUNT(*)::TEXT]) FROM test GROUP BY model;

示例代码：

CREATE TABLE test (model TEXT, qty DOUBLE PRECISION, color TEXT);

INSERT INTO test (model, qty, color) VALUES
('x', 1, 'yellow'),
('y', 1, 'yellow'),
('z', 2, 'red'),
('x', 3, 'yellow'),
('y', 1, 'red'),
('z', 1, 'blue'),
('x', 2, 'yellow'),
('x', 1, 'blue'),
('y', 4, 'red'),
('z', 1, 'yellow'),
('x', 2, 'yellow');

标签： arrayspostgresqlgroup-bycountarray-agg

Answer 1

您可以使用两个级别的聚合来做到这一点：

select model, sum(qty) qty, array_agg(array[color, qty::text]) colors
from (select model, color, sum(qty) qty from test group by model, color) t
group by model

我不认为没有子查询有一种简单的方法可以做到这一点，而且我不明白为什么您首先要避免使用子查询。

DB Fiddle 上的演示：

型号 | 数量 | 颜色                       
:---- | :-- | :----------------------------
z | 4 | {{黄色,1},{蓝色,1},{红色,2}}
是 | 6 | {{黄色,1},{红色,5}}         
x | 9 | {{黄色,8},{蓝色,1}}