如何高效统计不同决策组合下各部门多状态的案例数量

我有一张名为tbl_decisions的表，数据结构及内容如下：

该表数据量极大，存在各类Decision1与Decision2的组合。其中：

• Open：案例发起时所在部门
• Close：案例结案时所在部门
• Current：案例当前所在部门

我的需求是：针对每一组Decision1与Decision2的组合，统计各部门在Open、Close、Current三个状态下的案例数量，每个部门对应3个统计列（以Marketing和IT为例，期望输出如下）：

我当前使用的SQL语句如下：

SELECT
    DECISION1,
    DECISION2,
    (SELECT COUNT (*) FROM TBL_DECISIONS D2 WHERE D2.OPEN = 'Marketing' AND D2.DECISION1 = D1.DECISION1 AND D2.DECISION2 = D1.DECISION2) AS 'Marketing Open',
    (SELECT COUNT (*) FROM TBL_DECISIONS D2 WHERE D2.CLOSE = 'Marketing' AND D2.DECISION1 = D1.DECISION1 AND D2.DECISION2 = D1.DECISION2) AS 'Marketing Close',
    (SELECT COUNT (*) FROM TBL_DECISIONS D2 WHERE D2.CURRENT = 'Marketing' AND D2.DECISION1 = D1.DECISION1 AND D2.DECISION2 = D1.DECISION2) AS 'Marketing Current',
    (SELECT COUNT (*) FROM TBL_DECISIONS D2 WHERE D2.OPEN = 'IT' AND D2.DECISION1 = D1.DECISION1 AND D2.DECISION2 = D1.DECISION2) AS 'IT Open',
    (SELECT COUNT (*) FROM TBL_DECISIONS D2 WHERE D2.CLOSE = 'IT' AND D2.DECISION1 = D1.DECISION1 AND D2.DECISION2 = D1.DECISION2) AS 'IT Close',
    (SELECT COUNT (*) FROM TBL_DECISIONS D2 WHERE D2.CURRENT = 'IT' AND D2.DECISION1 = D1.DECISION1 AND D2.DECISION2 = D1.DECISION2) AS 'IT Current'
FROM
    TBL_DECISIONS D1
GROUP BY
    DECISION1,
    DECISION2

这个方法效率极低，会消耗大量算力，想请教更高效的实现思路。

你当前的写法是关联子查询，每一行分组都会执行6次子查询，数据量大时会重复扫描全表，性能极差。推荐用单表扫描+条件聚合的方式，只需要扫描一次表就能完成所有统计，效率提升明显。

优化后的SQL语句 #

SELECT
    Decision1,
    Decision2,
    -- Marketing相关统计
    SUM(CASE WHEN Open = 'Marketing' THEN 1 ELSE 0 END) AS Marketing_Open,
    SUM(CASE WHEN Close = 'Marketing' THEN 1 ELSE 0 END) AS Marketing_Close,
    SUM(CASE WHEN Current = 'Marketing' THEN 1 ELSE 0 END) AS Marketing_Cur,
    -- IT相关统计
    SUM(CASE WHEN Open = 'IT' THEN 1 ELSE 0 END) AS IT_Open,
    SUM(CASE WHEN Close = 'IT' THEN 1 ELSE 0 END) AS IT_Close,
    SUM(CASE WHEN Current = 'IT' THEN 1 ELSE 0 END) AS IT_Cur
FROM tbl_decisions
GROUP BY Decision1, Decision2
-- 如果需要包含所有可能的Decision1+Decision2组合（包括无数据的），可以关联全量组合表
-- 比如先获取所有组合：SELECT DISTINCT Decision1, Decision2 FROM tbl_decisions AS combos
-- 然后LEFT JOIN 上面的聚合结果 ON combos.Decision1 = agg.Decision1 AND combos.Decision2 = agg.Decision2

优化思路说明 #

1. 单次表扫描：整个查询只对tbl_decisions扫描一次，避免了子查询的重复扫描。
2. 条件聚合：利用CASE表达式判断每行数据是否符合统计条件，符合则记1，否则记0，再用SUM函数对分组内的结果求和，得到对应统计值。
3. 扩展性强：如果需要增加其他部门的统计，只需要复制对应的SUM(CASE...)块修改部门名称即可，不会额外增加查询复杂度。

额外性能建议 #

• 给(Decision1, Decision2)建立复合索引，分组时可以直接利用索引排序，避免临时表排序开销。
• 如果部门数量较多，且需要动态生成所有部门的统计列，可以考虑用动态SQL生成查询语句，但静态条件聚合在已知部门列表时性能最优。

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