sql - athena 上的复杂 SQL 查询聚合和分组

Question

我有一张这样的桌子：

|     db      |      chat_id      |   Admin    |     user      |
+-------------+-------------------+------------+---------------+
|    db_1     |      chat_id1     |    max     |     greg      |
|    db_1     |      chat_id2     |    max     |     bob       |
|    db_1     |      chat_id3     |    max     |     greg      |
|    db_1     |      chat_id2     |    helen   |     greg      |
|    db_2     |      chat_id1     |    alan    |     greg      |

我想检索用户为每个数据库（db）执行的聊天次数以及我失败的最后一部分，还检索用户的所有导师列表。

例如，最终输出应该是这样的（请注意，管理列中的 greg 只有一次最大值）

|     db      |      user     |  nb_of_chat  |     admins    |
+-------------+---------------+--------------+---------------+
|    db_1     |      greg     |      3       |   max, helen  |
|    db_1     |      bob      |      1       |      max      |
|    db_2     |      greg     |      1       |      alan     |

我编写了以下查询，但它没有聚合管理员，并且我已经分离了 nb_of 聊天/导师。

SELECT db, user, COUNT(chat_id), admins
FROM "chat_db"."chats" 
GROUP BY db, user, admins;

正如预期的那样，我得到了以下结果（但我只希望 db/user 在同一列中将其与分组管理员放在一行中）：

|     db      |      user     |  nb_of_chat  |     admins    |
+-------------+---------------+--------------+---------------+
|    db_1     |      greg     |      2       |       max     |
|    db_1     |      greg     |      1       |      helen    |
|    ...      |      ...      |     ...      |      ...      |

你知道如何执行它吗？

感谢您的时间 ！

问候。

标签： sqlstringcountprestoamazon-athena

Answer 1

尝试使用array_agg()：

select db, user, count(chat_id), array_agg(admins)
from  "chat_db"."chats" 
group by db, user;

如果你想要每行db：

select db, count(*) as num_chats, count(distinct user) as num_users, array_agg(admins)
from  "chat_db"."chats" 
group by db;