Spark SQL:按键查找最大值和总和

Question

我有一个Spark DataFrame与以下模式。

counts
 |-- index: string
 |-- name: string
 |-- count: long

我想对每个index的count列求和，并找到最大的count及其对应的name。因此，每行将包含一个唯一的index, SUM(count), MAX(count)和相应的name。

我按照index对表项进行分组，并通过执行以下查询选择count最高的行。

SELECT * FROM counts 
WHERE (index, count) 
IN (
    SELECT index, MAX(count) 
    FROM counts
    GROUP BY index
)
ORDER BY count DESC

这工作得很好，给我一个独特的行每个index包含行最高的count。

但是，我想添加一个列，该列包含基于原始表的index分组的count的总和。

我可以用这个查询将视图计数和index分组:

SELECT index, SUM(count)
FROM counts
GROUP BY index

但是我希望有一个包含两个查询结果的统一表(以有效的方式查询)

Answer 1

使用窗口函数:

SELECT c.*
FROM (SELECT c.*, SUM(count) OVER (PARTITION BY index) as cnt,
             ROW_NUMBER() OVER (PARTITION BY index ORDER BY count DESC) as seqnum
      FROM counts c
     ) c
WHERE seqnum = 1;

假设您还有其他想要保留的列。如果你只想要每个index的最大值和总和:

SELECT index, MAX(count), SUM(count)
FROM counts
GROUP BY index;

和index是一个列的坏名字，因为它是一个SQL关键字(想想"创建索引")。