BigQuery如何对排除当前分组的列值求和并写入对应被排除窗口？

你编写的SQL中核心问题出在PARTITION BY id<>id这个分区条件：id <> id是恒假的布尔表达式，所有行的计算结果完全一致，以此作为分区键会将全表所有行划入同一个分区，最终返回的就是全表value的总和。你后续尝试的几种写法也都存在逻辑偏差：

• SUM(value) OVER (PARTITION BY id, id<>id) 等价于按id分区，仅会计算当前id分组内的value总和
• 其余带ORDER BY id<>id的写法，排序规则无实际业务意义，也无法实现排除当前分组的聚合逻辑

你的需求是计算排除当前id分组之外所有行的value总和，可通过「全表总value减去当前id的总value」实现，性能最优：

保留原始明细行的写法 #

WITH t AS (
    SELECT 'a1' id, 10 value 
    UNION ALL 
    SELECT 'a1' id, 20 value 
    UNION ALL 
    SELECT 'a2' id, 20 value 
    UNION ALL 
    SELECT 'a2' id, 40 value
    UNION ALL 
    SELECT 'a2' id, 12 value
    UNION ALL 
    SELECT 'a3' id, 44 value
    UNION ALL 
    SELECT 'a3' id, 34 value    
)
SELECT 
    id,
    value,
    SUM(value) OVER() - SUM(value) OVER(PARTITION BY id) AS sum_all_except_current_id
FROM t;

按id分组去重的写法 #

如果只需要每个id对应一条结果，可以先分组聚合再计算：

WITH t AS (
    SELECT 'a1' id, 10 value 
    UNION ALL 
    SELECT 'a1' id, 20 value 
    UNION ALL 
    SELECT 'a2' id, 20 value 
    UNION ALL 
    SELECT 'a2' id, 40 value
    UNION ALL 
    SELECT 'a2' id, 12 value
    UNION ALL 
    SELECT 'a3' id, 44 value
    UNION ALL 
    SELECT 'a3' id, 34 value    
),
id_sum AS (
    SELECT id, SUM(value) AS id_total
    FROM t 
    GROUP BY id
)
SELECT 
    id,
    SUM(id_total) OVER() - id_total AS sum_all_except_current_id
FROM id_sum;

两种写法的计算逻辑一致：全表value总和为180，a1分组总和30、对应排除后总和150；a2分组总和72、对应排除后总和108；a3分组总和78、对应排除后总和102，完全符合需求。

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