How Convert Rows to Map in Presto?
Consider Sql Statement: Select Key, Value from ( Select Key, Value, Row_Number() over (Partition by Key Order by Length(Value) Desc) as Rn from My_Table )...
Consider SQL statement:
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;
This produces:
key value
A 1
A 2
B 10
How to make this like:
key values
A [1;2;3;4;5]
B [10;20;30;40;50]
Sql engine is presto. Any Ideas?
1 Answer
map_agg is the solution:
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;
Many thanks, @Shantanu Kher for the quickest reply!
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
