BigQuery窗口函数遇并列最大值时如何仅返回单个结果

解决方案 #

你当前返回重复记录的核心原因是窗口函数仅为每一行追加计算结果，不会过滤行数，同一个customer_key分组下有多少条记录就会返回多少条。要实现每个客户仅返回1条门店记录，有两种常用的简洁实现方式：

方案1：使用QUALIFY + ROW_NUMBER() （推荐，BigQuery原生支持，性能最优）

直接在排序后取每个客户的第一条记录即可，访问次数相同时新增一个排序维度即可保证结果唯一：

SELECT customer_key, store_location, store_key AS fav_store
FROM VISIT_DB
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_key
    -- 先按访问次数倒序，次数相同时按门店键/门店名排序，随便取第一个即可
    ORDER BY visits DESC, store_key
) = 1
ORDER BY customer_key ASC

上面的第二个排序字段store_key可以替换为任意你需要的字段（比如store_location），只要能在访问次数相同时确定出先后顺序即可。

方案2：保留窗口函数逻辑搭配去重

如果你需要保留窗口函数的计算逻辑，可以搭配DISTINCT去重，实现每个客户仅返回1条：

SELECT DISTINCT 
  customer_key,
  FIRST_VALUE(store_location) OVER (
    PARTITION BY customer_key
    ORDER BY visits DESC, store_key
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS store_location,
  FIRST_VALUE(store_key) OVER (
    PARTITION BY customer_key
    ORDER BY visits DESC, store_key
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS fav_store
FROM VISIT_DB
ORDER BY customer_key ASC

原方案失效的原因 #

FIRST_VALUE、LAST_VALUE、MAX这类窗口函数的作用是为每一行生成分组计算结果，不会对行数做过滤，因此当同一个客户有多条访问次数相同的门店记录时，自然会返回多条结果，必须搭配行号过滤、去重这类逻辑才能实现每个客户单条返回的需求。

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