如何简化含重复WHERE子句的SQL查询，生成关闭原因统计报表？

我正在创建一份报表，基于指定WHERE条件统计不同closedReason的出现次数及占比。当前查询可正常运行，但存在三处完全重复的WHERE子句，结构冗余严重，请问如何优化？

注：为保护企业数据，已修改部分表名和结果。

原查询代码 #

select 
    rccr.descr as Description, concat(round(count(*) * 100 / t.t, 2),'%') as Percent,count(*) as `Cases`
from sales_case rc 
    join sales_case_closed_reason rccr on rccr.id = rc.closedReason 
    join customer c on c.customer_id = rc.customerId 
    left outer join affiliate_btb_customer abc on abc.affiliate_btb_id = c.affiliate_id 
cross join (
    select count(*) as t 
    from sales_case rc2 
        join customer c2 on c2.customer_id = rc2.customerId 
        left outer join affiliate_btb_customer abc2 on abc2.affiliate_btb_id = c2.affiliate_id 
    where rc2.closedDTS >= DATE(NOW() - interval 365 day)
        and abc2.is_outlet is false
        and rc2.closedReason is not null
        and rc2.closedReason in (1,2,3,4,5,6,7,8,9,10,11,12,13,16,17,18,19,20,21,22,24,25,26,27,28,30,34,35,37,38,39,40,41,42,43,44)
) as t
where rc.closedDTS >= DATE(NOW() - interval 365 day)
    and abc.is_outlet is false
    and rc.closedReason is not null
    and rc.closedReason in (1,2,3,4,5,6,7,8,9,10,11,12,13,16,17,18,19,20,21,22,24,25,26,27,28,30,34,35,37,38,39,40,41,42,43,44)
group by rc.closedReason 
union 
    select null, 'Total', count(*)
    from sales_case rc3 
        join customer c3 on c3.customer_id = rc3.customerId 
        left outer join affiliate_btb_customer abc3 on abc3.affiliate_btb_id = c3.affiliate_id 
    where rc3.closedDTS >= DATE(NOW() - interval 365 day)
        and abc3.is_outlet is false
        and rc3.closedReason is not null
        and rc3.closedReason in (1,2,3,4,5,6,7,8,9,10,11,12,13,16,17,18,19,20,21,22,24,25,26,27,28,30,34,35,37,38,39,40,41,42,43,44)

示例结果 #

优化方案 #

通过CTE（公共表表达式）提取重复过滤逻辑，结合ROLLUP生成总计行，再用窗口函数替代单独的总数量子查询，可大幅减少冗余并提升效率：

优化后的查询代码

WITH filtered_cases AS (
    -- 一次性筛选符合条件的数据，后续直接复用
    SELECT rc.closedReason
    FROM sales_case rc
    JOIN customer c ON c.customer_id = rc.customerId
    LEFT JOIN affiliate_btb_customer abc ON abc.affiliate_btb_id = c.affiliate_id
    WHERE rc.closedDTS >= DATE(NOW() - INTERVAL 365 DAY)
      AND abc.is_outlet = FALSE
      AND rc.closedReason IS NOT NULL
      AND rc.closedReason IN (1,2,3,4,5,6,7,8,9,10,11,12,13,16,17,18,19,20,21,22,24,25,26,27,28,30,34,35,37,38,39,40,41,42,43,44)
)
SELECT 
    CASE WHEN GROUPING(fc.closedReason) = 1 THEN NULL ELSE rccr.descr END AS Description,
    CASE WHEN GROUPING(fc.closedReason) = 1 THEN 'Total' ELSE CONCAT(ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2), '%') END AS Percent,
    COUNT(*) AS `Cases`
FROM filtered_cases fc
JOIN sales_case_closed_reason rccr ON rccr.id = fc.closedReason
GROUP BY fc.closedReason WITH ROLLUP;

优化说明

1. CTE复用过滤逻辑：filtered_cases只定义一次筛选规则，避免三次重复编写JOIN和WHERE，降低维护成本。
2. ROLLUP生成总计行：用GROUP BY ... WITH ROLLUP替代原UNION，一次分组即可同时得到分组统计和总计，减少全表扫描次数。
3. 窗口函数计算占比：SUM(COUNT(*)) OVER()直接获取总案例数，替代原有的CROSS JOIN子查询，逻辑更简洁。
4. GROUPING函数识别总计行：GROUPING(fc.closedReason) = 1标记ROLLUP生成的总计行，据此设置对应字段值，匹配原结果格式。

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