使用窗口函数结合GROUP BY SUM获取分组首行数据及聚合指标

可以通过窗口函数结合QUALIFY子句实现需求，避免使用JOIN。核心思路是用窗口聚合函数计算分组总和，再通过行号过滤出每组的目标行：

SELECT
    fruit,
    store AS top_purchase_store,
    state AS top_purchase_state,
    SUM(cost) OVER (PARTITION BY fruit) AS total_cost
FROM TABLE_ID
QUALIFY ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY cost DESC) = 1
ORDER BY fruit;

结果验证 #

执行后会得到和原JOIN方案完全一致的输出：

fruit   top_purchase_store  top_purchase_state  total_cost
apple   Whole Foods         CA                  41
banana  Walmart             NY                  144.5

为什么之前用FIRST_VALUE没达到预期？ #

FIRST_VALUE是用于在窗口内取指定列的首个值，但如果直接和聚合SUM()混用，若未正确处理分组逻辑，会导致聚合结果重复出现在每一行，无法精准过滤到单一行。上面的方案先通过窗口SUM()计算每个分组的总成本（每行都会携带该分组的总和），再用QUALIFY只保留每组排序后的第一行，自然就得到了目标结果。

内容的提问来源于stack exchange，提问作者Alex