Databricks中按ID筛选最高消费分类（OTHER时取次高）的SQL实现

解决方案 #

在Databricks中，可以通过嵌套窗口函数结合QUALIFY子句实现需求，全程无需使用WHERE CATEGORY <> 'OTHER'的子查询，避免内存溢出问题。核心思路是先判断每个ID的最高金额分类是否为OTHER，再根据结果动态调整窗口排序规则，筛选出符合要求的记录。

SELECT id, CATEGORY, AMOUNT
FROM your_table
QUALIFY 
  CASE 
    -- 检查当前ID的最高金额分类是否为OTHER
    WHEN FIRST_VALUE(CATEGORY) OVER (PARTITION BY id ORDER BY AMOUNT DESC) != 'OTHER' THEN
      -- 最高分类非OTHER，直接取金额最高的记录（行号1）
      ROW_NUMBER() OVER (PARTITION BY id ORDER BY AMOUNT DESC) = 1
    ELSE
      -- 最高分类是OTHER，优先取非OTHER分类中金额最高的记录；若无其他分类则保留OTHER
      ROW_NUMBER() OVER (PARTITION BY id ORDER BY CASE WHEN CATEGORY != 'OTHER' THEN 0 ELSE 1 END, AMOUNT DESC) = 1
  END;

逻辑说明 #

1. FIRST_VALUE(CATEGORY) OVER (PARTITION BY id ORDER BY AMOUNT DESC)：获取每个ID金额最高的分类，用于判断是否需要切换到第二高分类。
2. 当最高分类不是OTHER时，用常规的ROW_NUMBER()按金额降序取行号1的记录，直接得到目标结果。
3. 当最高分类是OTHER时，重新定义排序规则：
   • 通过CASE WHEN CATEGORY != 'OTHER' THEN 0 ELSE 1 END让非OTHER分类排在最前面
   • 再按AMOUNT DESC排序，取行号1的记录，即非OTHER分类中金额最高的那条；如果ID只有OTHER分类，这条规则也会保留唯一的OTHER记录。

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