Snowflake SQL：如何通过单次扫描对去重值求和？

我在处理调研响应数据，部分用户会多次参与调研。每个用户有整数类型的user_weight，需要按城市对这个权重求和，但得避免重复计算多次参与的用户——也就是同一城市里同一个用户只算一次他的user_weight。

示例数据里每个用户都参与了两次调研，我现在用两步法能得到正确结果：先通过CTE把(city, user_id, user_weight)这组值去重，再按城市求和，得到芝加哥150、丹佛55的城市权重。想问问有没有办法只扫描一次表就完成这个计算？

示例数据SQL #

create or replace temp table temp as 
select 1 as response_id, 1 as user_id, 50 as user_weight, 'chicago' as city
union select 2, 1, 50, 'chicago'
union select 3, 2, 100, 'chicago'
union select 4, 2, 100, 'chicago'
union select 5, 3, 30, 'denver'
union select 6, 3, 30, 'denver'
union select 7, 4, 25, 'denver'
union select 8, 4, 25, 'denver'
;

原两步法SQL #

with base as (
select
 distinct
    city,
    user_id,
    user_weight
 from
    temp
)
select
    city,
    sum(user_weight) as city_weight
from
    base
 group by city
;

单次扫描解决方案 #

可以用窗口函数加条件求和实现，不用先做去重的CTE，只需要扫描一次源表：

select
    city,
    sum(case when row_num = 1 then user_weight else 0 end) as city_weight
from (
    select
        *,
        -- 按城市和用户分组，给每个用户的第一条记录标1
        row_number() over (partition by city, user_id order by response_id) as row_num
    from temp
) t
group by city;

原理说明

• row_number() over (partition by city, user_id order by response_id)：把同一城市、同一用户的调研记录归为一组，按响应ID排序后给每条记录编号，第一条记录的编号是1，后面的重复记录编号依次增大。
• 外层用case判断，只累加编号为1的记录的user_weight，这样就保证了同一个用户在同一个城市里只会被计算一次。

另外，如果能确保同一用户在同一城市的user_weight永远不变，也可以用一种取巧的distinct写法，但这种方法依赖数据的一致性，通用性不强，还可能有数值溢出风险，优先推荐上面的窗口函数方案：

select
    city,
    sum(distinct user_weight * 100000 + user_id) / 100000 as city_weight
from temp
group by city;

注：这里用user_weight * 100000 + user_id生成唯一标识（假设user_id不超过99999），去重后再拆分回权重求和，仅适合数据规则固定的场景。

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