在BigQuery中按ID分组统计每组Top100值的总和

首先纠正原代码里的字段名不一致问题：子查询中GROUP BY app_id但SELECT ID，需要统一为同一个字段名（以下示例统一使用ID）。

针对你的需求，用窗口函数实现会比ARRAY_AGG的写法更高效，具体方案如下：

高效查询写法 #

SELECT
  ID,
  SUM(Count_total) AS top_100_sum
FROM (
  SELECT
    ID,
    Count_total,
    -- 按ID分组，给Count_total降序排名
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Count_total DESC) AS rank_num
  FROM topcount
)
-- 筛选每组排名前100的记录
WHERE rank_num <= 100
GROUP BY ID;

效率优势 #

• 省去了ARRAY_AGG将数据打包成数组再展开求和的额外步骤，大数据量下能减少内存占用和计算开销。
• 窗口函数的排序筛选逻辑更直接，数据库执行计划通常会优先处理排序过滤，避免全量数据聚合。

特殊场景适配 #

如果需要对相同Count_total的记录保留并列排名，可以把ROW_NUMBER()替换为RANK()或DENSE_RANK()：

• RANK()：相同值共享排名，后续排名会跳号（例如1,1,3）
• DENSE_RANK()：相同值共享排名，后续排名不跳号（例如1,1,2）

示例（使用RANK）：

SELECT
  ID,
  SUM(Count_total) AS top_100_sum
FROM (
  SELECT
    ID,
    Count_total,
    RANK() OVER (PARTITION BY ID ORDER BY Count_total DESC) AS rank_num
  FROM topcount
)
WHERE rank_num <= 100
GROUP BY ID;

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