使用group by在Athena中计算Mode()

Question

我有一个带有模式的表"a">

subject, marks

假设它有以下条目

(maths,90),(maths,90),(maths,80),(english,30),(english,30),(english,10)

我希望在"marks"列上计算Mode((，以便

(maths,90),(english,30) is the output

在互联网上找不到与AWS Athena相同的方法。感谢您的帮助！！

Answer 1

使用内置直方图函数有一种更简洁的方法：

SELECT subject, 
map_keys(histogram(marks))[
array_position(
map_values(histogram(marks)),
array_max(map_values(histogram(marks)))
)] as mode
FROM (
VALUES
('maths',90),
('maths',80),
('maths',80),
('english',30),
('english',30),
('english',10)
) AS t(subject, marks)
GROUP BY subject;

解释：

• 直方图(标记(返回地图，如"english"的{10=1, 30=2}
• map_values返回值[1,2]的数组
• array_max返回数组2的最大值
• array_position返回该最大值的位置(2，第二个元素(
• map_keys返回键的数组[10,30]
• 最后[]返回上述位置的元素(30，第二个元素(

Answer 2

这个查询应该会给你想要的结果：

WITH ModeRankCTE AS 
( 
SELECT 
subject, 
marks,
COUNT(*) AS marksFrequency,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY COUNT(*) DESC) as ranking
FROM A 
GROUP BY subject, marks 
) 
SELECT 
subject, 
MIN(marks) AS highestMark, 
marksFrequency, 
ranking
FROM ModeRankCTE
WHERE ranking = 1
GROUP BY subject, marksFrequency, ranking