使用大量COUNT CASE WHEN语句致性能低下的优化求助

你当前的写法会让数据库对整张表执行多次条件扫描（每个COUNT(CASE...)都会单独处理全表数据），数据量越大性能损耗越明显。试试下面几种优化思路：

• 先分组聚合再转置
  先对分类字段a做一次分组统计，仅扫描一次表获取各分类的计数，再通过行转列得到目标格式，能把全表扫描次数从N次（N为分类数量）降到1次，性能提升显著。
  通用SQL示例：

  SELECT
    MAX(CASE WHEN a = 'apple' THEN cnt ELSE 0 END) AS '1',
    MAX(CASE WHEN a = 'orange' THEN cnt ELSE 0 END) AS '2',
    MAX(CASE WHEN a = 'mango' THEN cnt ELSE 0 END) AS '3',
    MAX(CASE WHEN a = 'melon' THEN cnt ELSE 0 END) AS '4',
    MAX(CASE WHEN a = 'grape' THEN cnt ELSE 0 END) AS '5',
    MAX(CASE WHEN a = 'lemon' THEN cnt ELSE 0 END) AS '6',
    MAX(CASE WHEN a = 'watermelon' THEN cnt ELSE 0 END) AS '7'
  FROM (
    SELECT a, COUNT(*) AS cnt
    FROM your_table
    GROUP BY a
  ) AS grouped_data;
  

  若你的数据库支持PIVOT语法（如SQL Server、Oracle），可以用更简洁的写法：

  SELECT [1], [2], [3], [4], [5], [6], [7]
  FROM (
    SELECT 
      CASE a 
        WHEN 'apple' THEN '1'
        WHEN 'orange' THEN '2'
        WHEN 'mango' THEN '3'
        WHEN 'melon' THEN '4'
        WHEN 'grape' THEN '5'
        WHEN 'lemon' THEN '6'
        WHEN 'watermelon' THEN '7'
      END AS col_num,
      COUNT(*) AS cnt
    FROM your_table
    GROUP BY a
  ) AS src
  PIVOT (
    MAX(cnt) FOR col_num IN ([1], [2], [3], [4], [5], [6], [7])
  ) AS pvt;
  

• 给分类字段添加索引
  在字段a上创建普通索引，让数据库可以通过索引扫描直接获取分组数据，避免全表扫描：

  CREATE INDEX idx_your_table_a ON your_table(a);
  

• 替换COUNT为SUM（可选）
  逻辑上COUNT(CASE WHEN ... THEN 1 END)与SUM(CASE WHEN ... THEN 1 ELSE 0 END)等价，但部分数据库优化器对SUM的处理更高效，可尝试替换：

  SUM(CASE WHEN a = 'apple' THEN 1 ELSE 0 END) AS '1',
  SUM(CASE WHEN a = 'orange' THEN 1 ELSE 0 END) AS '2'
  -- 其余字段同理
  

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