如何在红移中分组和连接字段 [英] How to GROUP BY and CONCATENATE fields in redshift

问题描述

如何在 redshift 中分组和连接字段，例如如果我有桌子

How to GROUP BY and CONCATENATE fields in redshift e.g If i have table

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

我怎样才能得到这样的结果

How can i get result like this

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

那里一些解决方案 PostgreSQL，但答案中提到的功能都没有在 Redshift 中可用.

There some solutions PostgreSQL, but none of functions mentioned in answers are available in Redshift righnow.

推荐答案

好吧，我有点晚了但是 关于此功能的公告发生在 2015 年 8 月 3 日.Redshift 引入了 LISTAGG 窗口函数，现在可以这样做.这是您问题的快速解决方案 - 可能有用也可能没有用，但请将其放在这里以便人们知道！

Well, I am a little late but the announcement about this feature happened on 3rd Aug 2015. Redshift has introduced LISTAGG window function that makes it possible to do so now. Here is a quick solution to your problem - may or may not be useful but putting it here so that people will know!

SELECT COMPANY_ID,
       LISTAGG(EMPLOYEE,', ')
WITHIN GROUP (ORDER BY EMPLOYEE)
OVER (PARTITION BY COMPANY_ID) AS EMPLOYEE
FROM YOUR_TABLE
ORDER BY COMPANY_ID

我很高兴看到此功能，而且我们的许多生产脚本都可以升级，以包含 Redshift 不断添加的所有新功能.

I was happy to see this feature, and many of our production scripts are up for upgrade with all the new features Redshift keeps adding.

这里是关于函数的文档

这篇关于如何在红移中分组和连接字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！