如何在presto中将行转换为映射?

Question

考虑SQL语句：

SELECT key, value
FROM (
SELECT key, value, row_number() OVER (PARTITION BY key ORDER BY length(value) DESC) AS rn
FROM my_table
)
WHERE rn <= 5;

这会产生：

key value
A   1
A   2
B   10

如何使它像：

key values
A   [1;2;3;4;5]
B   [10;20;30;40;50]

Sql 引擎是 presto。有什么想法吗？

Answer 1

map_agg

是解决方案：

SELECT key, map_agg(key, value)
FROM (
SELECT key, value, row_number() OVER (PARTITION BY key ORDER BY length(value) DESC) AS rn
FROM my_table
)
WHERE rn <= 5 group by key;

非常感谢，@Shantanu Kher 的最快回复！