SQL同数据集子集计算最高效方法：分组占比统计优化

欢迎提供更优的标题建议。

问题背景 #

现有业务基础表包含part（零件类型）、color（颜色）两个字段，以门（door）类型为例，样例共9条记录：红色4条、灰色3条、黑色2条，需要实现三类占比统计：

• 红色门占全部门的百分比：4 / 9 = 44.4%
• 深色（定义为灰色或黑色）门占全部门的百分比：5 / 9 = 55.6%
• 灰色门占所有深色门的百分比：3 / 5 = 60%

下方给出的SQL可以实现上述需求，但当数据规模达到总零件记录1000万条、门类型记录100万条、共50种不同颜色时，需要找到性能更优的实现方式——这类基于数据集子集的占比计算是日常高频操作。

原有实现代码 #

-- 构造样例测试数据
insert into #temp values ('door', 'red')
insert into #temp values ('door', 'red')
insert into #temp values ('door', 'red')
insert into #temp values ('door', 'red')
insert into #temp values ('door', 'gray')
insert into #temp values ('door', 'gray')
insert into #temp values ('door', 'gray')
insert into #temp values ('door', 'black')
insert into #temp values ('door', 'black')

-- 统计逻辑
select part,
'percent red' = cast(1.0 * sum(case when color = 'red' then 1 end) / count(color) as decimal(5,3)),
'percent dark' = cast(1.0 * sum(case when color in ('gray','black') then 1 end) / count(color) as decimal(5,3)),
'percent gray' = cast(1.0 * sum(case when color in ('gray') then 1 end) / sum(case when color in ('gray','black') then 1 end) as decimal(5,3))
from #temp
group by part

drop table #temp

可落地的性能优化方向 #

1. 前置过滤，减少无效数据扫描 #

原有逻辑默认扫描全表所有零件数据，如果你不需要统计非door类型的零件，直接在聚合前增加过滤条件where part = 'door'，可以直接过滤掉90%的非目标数据，扫描量从1000万降至100万，是收益最高的优化手段。
如果需要同时统计多个part类型的指标，优先给part字段建立索引，避免全表遍历。

2. 两层聚合减少重复计算 #

原有逻辑在明细行层面多次执行重复的条件判断：比如深色判断color in ('gray','black')就执行了2次，每行数据都要重复匹配条件。可以先做第一层细粒度聚合，先按part + color维度统计各颜色的基础数量，再基于聚合后的小结果集计算占比：

with color_base_cnt as (
    select 
        part,
        color,
        count(*) as cnt
    from #temp
    -- 按需增加part过滤条件
    where part = 'door'
    group by part, color
)
select
    part,
    cast(1.0 * sum(case when color = 'red' then cnt else 0 end) / sum(cnt) as decimal(5,3)) as [percent red],
    cast(1.0 * sum(case when color in ('gray','black') then cnt else 0 end) / sum(cnt) as decimal(5,3)) as [percent dark],
    -- 增加nullif避免无深色数据时除零报错
    cast(1.0 * sum(case when color = 'gray' then cnt else 0 end) / nullif(sum(case when color in ('gray','black') then cnt else 0 end), 0) as decimal(5,3)) as [percent gray]
from color_base_cnt
group by part

这种写法的优势是：当总颜色数为50种时，第一层聚合后每个part最多输出50条记录，后续占比计算仅需处理几十行数据，不需要在百万/千万级明细行上重复执行case when判断，数据量越大性能优势越明显。

3. 建立覆盖索引消除回表 #

如果这类统计是固定高频需求，可以建立(part, color)的联合覆盖索引，索引本身已经按part、color完成排序，数据库不需要访问原表数据、不需要额外排序，直接扫描索引就能完成第一层聚合，性能相比扫描原表可提升数倍。

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