如何在Snowflake中高效计算各Filter的使用相对占比

原始数据表 #

需求 #

计算每个Filter被不同SearchID使用的相对占比，预期结果如下：

现有低效方案 #

当前可以实现需求的SQL如下，但执行效率不高：

select count(*) / max(total)
from mytable
join (select count(distinct SearchID) total from mytable) on 1=1
group by Filter

希望通过窗口函数优化该查询，当前使用Snowflake数据库，也可切换至其他数据库。

优化后的SQL方案 #

方案一：利用窗口函数传递总唯一SearchID数 #

select 
    Filter,
    count(distinct SearchID) / max(total_search_ids) as Ratio
from (
    select 
        SearchID,
        Filter,
        count(distinct SearchID) over () as total_search_ids
    from mytable
    group by SearchID, Filter
) grouped_data
group by Filter

方案二：标量子查询简化写法 #

select
    Filter,
    count(distinct SearchID) / (select count(distinct SearchID) from mytable) as Ratio
from mytable
group by Filter

优化逻辑说明 #

• 原方案中的join会产生大量重复的总SearchID数据，额外增加计算开销，优化后的方案避免了这种不必要的数据关联。
• 方案一中，窗口函数count(distinct SearchID) over ()会一次性计算出所有唯一的SearchID总数，并传递给每一行，之后分组统计每个Filter对应的唯一SearchID数，再计算占比。
• 方案二更简洁，标量子查询(select count(distinct SearchID) from mytable)只会执行一次，获取总唯一SearchID数后直接用于占比计算，在Snowflake中这种写法的执行效率通常优于原join方案。

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