在分组/聚合期间合并/合并数组值 [英] Concatenate/merge array values during grouping/aggregation
问题描述
我有一个表,该表的数组列类型为:
I have a table with the an array column type:
title tags
"ridealong";"{comedy,other}"
"ridealong";"{comedy,tragedy}"
"freddyjason";"{horror,silliness}"
我想编写一个查询,为每个标题生成单个数组(理想情况下,它将是一个设置/去重复的数组)
I would like to write a query that produces a single array per title(in an ideal world it would be a set/deduplicated array)
eg
select array_cat(tags),title from my_test group by title
上面的查询当然行不通,但是我想产生两行:
The above query doesn't work of course, but I would like to produce 2 rows:
"ridealong";"{comedy,other,tragedy}"
"freddyjason";"{horror,silliness}"
任何帮助或指针都将非常感激
(我使用的是Postgres 9.1)
Any help or pointers would be very much appreciated (I am using Postgres 9.1)
基于Craig的帮助,我得到了以下内容(语法略有更改,因为9.1完全按照他的显示抱怨该查询)
Based on Craig's help I ended up with the following (slightly altered syntax since 9.1 complains about the query exactly as he shows it)
SELECT t1.title, array_agg(DISTINCT tag.tag)
FROM my_test t1, (select unnest(tags) as tag,title from my_test) as tag
where tag.title=t1.title
GROUP BY t1.title;
推荐答案
自定义汇总
方法1:定义自定义汇总。 这是我之前写过的书。
CREATE TABLE my_test(title text, tags text[]);
INSERT INTO my_test(title, tags) VALUES
('ridealong', '{comedy,other}'),
('ridealong', '{comedy,tragedy}'),
('freddyjason', '{horror,silliness}');
CREATE AGGREGATE array_cat_agg(anyarray) (
SFUNC=array_cat,
STYPE=anyarray
);
select title, array_cat_agg(tags) from my_test group by title;
横向查询
...或由于您不想保留订单并希望删除重复数据,因此可以使用 LATERAL
查询,例如:
SELECT title, array_agg(DISTINCT tag ORDER BY tag)
FROM my_test, unnest(tags) tag
GROUP BY title;
在这种情况下,您不需要自定义汇总。由于重复数据删除,对于大数据集而言,这可能会稍微慢一些。不过,如果不需要,可以删除 ORDER BY
。
in which case you don't need the custom aggregate. This one is probably a fair bit slower for big data sets due to the deduplication. Removing the ORDER BY
if not required may help, though.
这篇关于在分组/聚合期间合并/合并数组值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!