Vertica SQL 中的 Concat GROUP BY [英] Concat GROUP BY in Vertica SQL

问题描述

我需要获取一个逗号分隔的 id 列表作为一个字段，用于杂乱的第三方 api :s 这是我想要实现的简化版本.

I need to get a comma separated list of ids as a field for a messy third party api :s This is a simplified version of what I am trying to achieve.

| 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 语句来解决这个问题?

I know MySQL has the CONCAT_GROUP function and I was hoping to solve this problem without installing more functions because of the environment. Maybe I can solve this problem using an OVER statement?

推荐答案

从长远来看，最简单的方法是使用官方 Vertica UDF 之一，该 UDF 位于 github 上 https://github.com/vertica/Vertica-Extension-Packages/tree/master/strings_package 提供了一个 group_concat功能.安装过程在README中可以找到，还提供了示例.

The easiest on the long term is to use one of the official Vertica UDFs to be found on github at https://github.com/vertica/Vertica-Extension-Packages/tree/master/strings_package which provides a group_concat function. The installation procedure is to found in the README, and examples are even provided.

这篇关于Vertica SQL 中的 Concat GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！