Presto查询:在地图中查找具有最大值的键

Question

我有一个表

Name  pets
--------------
Andy  {dog:2, cat:1, bird:4}
John  {tiger:3, elephant:1, fish:2}
Mary  {dog:2, pig:2}

我想为每个人找到最大数量的宠物类型。如果出现平局，请为每只宠物复制一行。结果应该是这样的：

Name  max_pet
------------------
Andy  bird
John  tiger
Mary  dog
Mary  pig

目前，我导出了该表，并在python中进行了导出。但我想知道我可以使用Presto/SQL查询实现这一点吗？谢谢

Answer 1

有几种方法可以做到这一点。一种方法是使用UNNEST将映射转换为行，每个映射条目一行。然后，您可以使用rank()窗口功能为每个名称的宠物分配排名，然后只选择排名靠前的项目。

WITH people (name, pets) AS (
VALUES
('Andy', map_from_entries(array[('dog', 2), ('cat', 1), ('bird', 4)])),
('John', map_from_entries(array[('tiger', 3), ('elephant', 1), ('fish', 2)])),
('Mary', map_from_entries(array[('dog', 2), ('pig', 2)]))
)
SELECT name, pet AS max_pet
FROM (
SELECT name, pet, count,
rank() OVER (PARTITION BY name ORDER BY count DESC) rnk
FROM people
CROSS JOIN UNNEST(pets) AS t (pet, count)
)
WHERE rnk = 1;

name | max_pet 
------+---------
Andy | bird    
John | tiger   
Mary | dog     
Mary | pig     
(4 rows)

使用UNNEST很容易理解，但如果需要将其与其他操作结合使用，或者名称重复，则不能很好地工作。

另一种方法是使用map_entries()将映射转换为数组，使用filter()选择计数等于最大计数的宠物，然后使用transform()仅返回宠物名称。在这一点上，您有一个最大宠物数组。然后，您可以将它UNNEST为多行，或者将其保留为一个数组以供进一步处理。CCD_ 8和CCD_ 9使用lambda表达式。

WITH people (name, pets) AS (
VALUES
('Andy', map_from_entries(array[('dog', 2), ('cat', 1), ('bird', 4)])),
('John', map_from_entries(array[('tiger', 3), ('elephant', 1), ('fish', 2)])),
('Mary', map_from_entries(array[('dog', 2), ('pig', 2)]))
)
SELECT
name,
transform(
filter(
map_entries(pets),
e -> e[2] = array_max(map_values(pets))),
e -> e[1]) AS max_pets
FROM people;

name |  max_pets  
------+------------
Andy | [bird]     
John | [tiger]    
Mary | [dog, pig] 
(3 rows)

Answer 2

对于Athena用户，如果无法通过索引访问行字段(至少对于第二个版本的引擎是正确的，没有检查第三个(，则可以使用map_keys重写答案中的第二种方法，并通过索引进行映射访问：

-- sample data
WITH people (name, pets) AS (
VALUES
('Andy', map_from_entries(array[('dog', 2), ('cat', 1), ('bird', 4)])),
('John', map_from_entries(array[('tiger', 3), ('elephant', 1), ('fish', 2)])),
('Mary', map_from_entries(array[('dog', 2), ('pig', 2)]))
)
-- query
SELECT
name,
transform(
filter(
map_keys(pets),
e -> pets[e] = array_max(map_values(pets))), -- we now that e is present in pets
e -> e) AS max_pets
FROM people;

输出：

约翰玛丽

名称	max_pets
Andy	[bird]

使用map_top_n_keys函数：

SELECT map_top_n_keys(map(ARRAY[‘a’, ‘b’, ‘c’], ARRAY[1, 2, 3]), 2) — [‘c’, ‘b’]