SQL中如何按信用分区间分组并统计各区间的出现次数？

你当前编写的SQL中存在重复的WHEN CreditScore BETWEEN 601 AND 700 THEN 'Fair'冗余逻辑，可以直接删除。以下是两种常用场景的调整后代码：

• 场景1：统计全表各信用分等级的总出现次数
  直接将信用分等级判断结果作为分组维度，搭配COUNT函数统计即可：

  SELECT 
    CASE 
      WHEN CreditScore BETWEEN 300 AND 500 THEN 'Very Poor' 
      WHEN CreditScore BETWEEN 501 AND 600 THEN 'Poor'
      WHEN CreditScore BETWEEN 601 AND 700 THEN 'Fair'
      WHEN CreditScore BETWEEN 701 AND 750 THEN 'Good'
      WHEN CreditScore BETWEEN 751 AND 850 THEN 'Great'
      ELSE '未分类' -- 用于处理信用分不在300-850区间的异常值，不需要可直接删除该分支
    END AS SCORETEXT,
    COUNT(*) AS 出现次数
  FROM customerchurn
  GROUP BY SCORETEXT;
  

• 场景2：需要按地区拆分统计各地区的信用分等级数量
  把Geography字段也加入查询和分组逻辑即可：

  SELECT 
    Geography AS 地区,
    CASE 
      WHEN CreditScore BETWEEN 300 AND 500 THEN 'Very Poor' 
      WHEN CreditScore BETWEEN 501 AND 600 THEN 'Poor'
      WHEN CreditScore BETWEEN 601 AND 700 THEN 'Fair'
      WHEN CreditScore BETWEEN 701 AND 750 THEN 'Good'
      WHEN CreditScore BETWEEN 751 AND 850 THEN 'Great'
      ELSE '未分类'
    END AS SCORETEXT,
    COUNT(*) AS 出现次数
  FROM customerchurn
  GROUP BY 地区, SCORETEXT;
  

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