logo标签列表

在Vertica SQL中使用Concat GROUP BY

sqlvertica
5
我需要将一组id作为字段以逗号分隔的形式传递给一个混乱的第三方API :s 这是我想要实现的简化版本。
| id | name |
|====|======|
| 01 | greg |
| 02 | paul |
| 03 | greg |
| 04 | greg |
| 05 | paul |

SELECT name, {some concentration function} AS ids
FROM table
GROUP BY name

返回

| name | ids        |
|======|============|
| greg | 01, 03, 04 |
| paul | 02, 05     |

我知道MySQL有CONCAT_GROUP函数，但由于环境原因，我希望不安装更多函数来解决这个问题。也许我可以使用OVER语句来解决这个问题吗？

1
如果我没记错的话，Vertica是Postgres的一个分支。因此，也许string_agg或array_agg在那里是可用的。 - user330315
2
@a_horse_with_no_name 很不幸，两者都不是。 - Kermit
4个回答
9

您需要使用OVER()与NVL()（对于每个名称，您需要扩展串联超过10个实例）：

CREATE TABLE t1 (
  id int,
  name varchar(10)
);

INSERT INTO t1
SELECT 1 AS id, 'greg' AS name
UNION ALL
SELECT 2, 'paul'
UNION ALL
SELECT 3, 'greg'
UNION ALL
SELECT 4, 'greg'
UNION ALL
SELECT 5, 'paul';

COMMIT;

SELECT name,
    MAX(DECODE(row_number, 1, a.id)) ||
    NVL(MAX(DECODE(row_number, 2, ',' || a.id)), '') ||
    NVL(MAX(DECODE(row_number, 3, ',' || a.id)), '') ||
    NVL(MAX(DECODE(row_number, 4, ',' || a.id)), '') ||
    NVL(MAX(DECODE(row_number, 5, ',' || a.id)), '') ||
    NVL(MAX(DECODE(row_number, 6, ',' || a.id)), '') ||
    NVL(MAX(DECODE(row_number, 7, ',' || a.id)), '') ||
    NVL(MAX(DECODE(row_number, 8, ',' || a.id)), '') ||
    NVL(MAX(DECODE(row_number, 9, ',' || a.id)), '') ||
    NVL(MAX(DECODE(row_number, 10, ',' || a.id)), '') id
FROM
    (SELECT name, id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) row_number FROM t1) a
GROUP BY a.name
ORDER BY a.name;

结果

 姓名 |  编号
------+-------
 格雷格 | 1,3,4
 保罗 | 2,5
这似乎是一个不错的现实解决方案。@Gregology，你需要它成为任意长度的连接吗？ - kimbo305
4

请查看Vertica安装包自带的Concatenate UDAF示例，这是与MySQL等效的。您可以直接安装它。

more /opt/vertica/sdk/examples/AggregateFunctions/Concatenate.cpp

-- Shell comppile
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

-- Create LIBRARY
CREATE LIBRARY AggregateFunctionsConcatenate AS '/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
CREATE AGGREGATE FUNCTION agg_group_concat AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;


in the Concatenate.cpp
replace : input_len*10
with : 65000

在代码中，有两个地方需要替换此值。

varchar 的最大长度为65000，由于 Vertica 对小于65000个字符的值并不使用完整的65000，因此您可以放心使用。

3
如链接https://community.dev.hp.com/t5/Vertica-Forum/Does-Vertica-have-an-equivalent-to-MySQL-s-GROUP-CONCAT-function/td-p/208697和Guillaume的答复所述，Concatenate无法使用分隔符。相反，您必须使用https://github.com/vertica/Vertica-Extension-Packages/tree/master/strings_package。这是一个第三方UDF，能够完成此操作。请注意不要改变原来的意思，并使内容更加通俗易懂。 - user1084563
1
@user1084563 这个方法很好用，只需要使用 rtrim(agg_group_concat(field || ', '),', ')。rtrim函数会移除末尾的分隔符，而且这种方法不需要使用分析函数。 - woot
@woot 有趣的解决方法，我绝对没有意识到你可以在函数调用中添加附加内容。你可能还想将其添加到我发布的 Vertica 社区线程中，因为那里还没有提出这个建议。 - user1084563
@user1084563 好的 - woot
2
1
一个很老的问题，但是作为更新，你可以使用listagg()函数。
id name
01 格雷格
02 保罗
03 格雷格
04 格雷格
05 保罗

SELECT name, listagg(id) AS ids FROM table GROUP BY name

这将返回所需的输出：

名称 编号
格雷格 01, 03, 04
保罗 02, 05
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接