如何用SQL高效获取列中Top1000值及对应数据集占比？

需求说明 #

获取任意表任意列的Top1000值，以及这些值在近365天内的出现频率，输出格式要求：

<column_name>, PercentOfDataSet
Value1, X.XX
Value2, X.XX
Value3, X.XX

现有实现 #

当前通过两步动态SQL实现：先查询符合时间条件的总行数，再基于该数值计算各值的占比，代码如下：

sql_row_count := 'select count(*) from '|| table_name ||' where '|| date_column ||' > DATEADD(day, -365, getdate());';
total_row_count := (execute immediate :sql_row_count);
sql_query := 'select '|| column_name ||', iff('|| total_row_count ||' = 0, 0.00, cast(count(*) as numeric(18,2))/'|| total_row_count ||'*100) PercentOfDataSet' ||
                 ' from '|| table_name ||' where '|| date_column ||'> DATEADD(day, -365, getdate())' ||
                 ' group by 1 order by 2 desc limit 1000;';

优化方案 #

可以利用Snowflake的窗口函数特性，在单查询中完成总行数计算与占比统计，避免两次扫描目标表，提升效率的同时简化逻辑。推荐的优化写法如下：

sql_query := 'select '|| column_name ||', 
                 cast(count(*) as numeric(18,2))/sum(count(*)) over()*100 as PercentOfDataSet
              from '|| table_name ||' 
              where '|| date_column ||' > DATEADD(day, -365, getdate())
              group by 1
              order by 2 desc 
              limit 1000;';

如果需要兼容总行数为0的场景（避免除以0报错），可以添加IFF判断：

sql_query := 'select '|| column_name ||', 
                 iff(total = 0, 0.00, cast(count(*) as numeric(18,2))/total*100) as PercentOfDataSet
              from '|| table_name ||' 
              cross join (select count(*) as total from '|| table_name ||' where '|| date_column ||' > DATEADD(day, -365, getdate())) t
              where '|| date_column ||' > DATEADD(day, -365, getdate())
              group by 1, total
              order by 2 desc 
              limit 1000;';

优化点说明 #

• 减少表扫描次数：原方案需要两次查询目标表，优化后仅需一次扫描，大表场景下性能提升明显。
• 避免竞态问题：原方案两次查询的时间差内若数据发生变化，会导致总行数与分组统计的总和不一致，单查询可彻底规避该问题。
• 简化代码逻辑：无需额外变量存储总行数，动态SQL的结构更简洁，维护成本更低。

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