我有一个Vertica数据库中的表,名称为test,结构如下:
ID | name
1 | AA
2 | AB
2 | AC
3 | AD
3 | AE
3 | AF
我该如何使用聚合函数或编写查询以获取类似于此的数据(Vertica语法)?
ID | ag
1 | AA
2 | AB, AC
3 | AD, AE, AF
agg_concatenate
的 udx。-- Shell commands
cd /opt/vertica/sdk/examples/AggregateFunctions/
g++ -D HAVE_LONG_INT_64 -I /opt/vertica/sdk/include -Wall -shared -Wno-unused-value -fPIC -o Concatenate.so Concatenate.cpp /opt/vertica/sdk/include/Vertica.cpp
-- vsql commands
CREATE LIBRARY AggregateFunctionsConcatenate AS '/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
CREATE AGGREGATE FUNCTION agg_concatenate AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;
select id, rtrim(agg_concatenate(name || ', '),', ') ag
from mytable
group by 1
order by 1
使用rtrim来去掉最后的', '。
如果您需要按特定方式对聚合进行排序,则可能需要在内联视图或使用first中进行选择/排序。
SELECT id,
MAX(DECODE(row_number, 1, a.name)) ||
NVL(MAX(DECODE(row_number, 2, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 3, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 4, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 5, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 6, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 7, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 8, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 9, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 10, ',' || a.name)), '')||
NVL(MAX(DECODE(row_number, 11, ',' || a.name)), '') ||
NVL(MAX(DECODE(row_number, 12, ',' || a.name)), '') ag
FROM
(SELECT id, name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) row_number FROM test) a
GROUP BY a.id
ORDER BY a.id;
GROUP_CONCAT
。select id, group_concat(name) over (partition by id order by name) ag
from mytable
LISTAGG()
函数。
LISTAGG
函数将一组行中的非空值转换为由可配置分隔符分隔的值列表,可用于将行非规范化为逗号分隔值或其他格式的字符串。
/opt/vertica/sdk/examples
目录,你会发现许多其他函数。还有一个通用的makefile和几个SQL文件,展示了如何安装和测试它们。 - wootagg_concatenate
可以用于这个问题这里吗?在哪里可以下载它? - hhh