Amazon Redshift中CASE WHEN条件覆盖致SSA统计缺失的解决方法

原问题的核心是CASE WHEN的互斥逻辑导致SSA区域被AFRICA覆盖：当country_code属于SSA的范围时，会先匹配到AFRICA的条件，后续SSA的分支永远不会执行，因此无法统计到SSA的订单量。要实现两个区域的独立统计，需要让它们的统计逻辑互不干扰，以下是两种可行方案：

方案1：使用UNION ALL独立统计每个区域 #

直接为每个区域单独编写统计语句，再通过UNION ALL合并结果，确保每个区域的筛选逻辑独立执行：

SELECT 'SWE' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('PT','IT','ES','PL','AD')

UNION ALL

SELECT 'SEE' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('MD','BG','BA','SI','HR','ME','RO','RS')

UNION ALL

SELECT 'AFRICA' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('CI','GH','MA','NG','UG','KE','TN')

UNION ALL

SELECT 'SSA' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('CI','GH','NG','UG','KE','TN')

UNION ALL

SELECT 'ECA' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('UA','BY','GE','KZ','KG','AM')

这种方式逻辑简单直观，每个区域的统计互不影响，能准确得到AFRICA（含MA）和SSA（不含MA）的订单量。

方案2：使用CTE定义区域规则，通过关联统计 #

先将区域与对应国家码的映射关系用CTE（公共表表达式）定义，再通过关联原表进行统计，这种方式更便于后续维护（比如新增/修改区域时只需修改CTE）：

WITH region_mappings AS (
    SELECT 'SWE' AS region, ARRAY['PT','IT','ES','PL','AD'] AS country_codes UNION ALL
    SELECT 'SEE' AS region, ARRAY['MD','BG','BA','SI','HR','ME','RO','RS'] AS country_codes UNION ALL
    SELECT 'AFRICA' AS region, ARRAY['CI','GH','MA','NG','UG','KE','TN'] AS country_codes UNION ALL
    SELECT 'SSA' AS region, ARRAY['CI','GH','NG','UG','KE','TN'] AS country_codes UNION ALL
    SELECT 'ECA' AS region, ARRAY['UA','BY','GE','KZ','KG','AM'] AS country_codes
)
SELECT 
    rm.region,
    COUNT(DISTINCT c.orderid) AS amount_of_orders
FROM region_mappings rm
LEFT JOIN country c ON c.country_code = ANY(rm.country_codes)
GROUP BY rm.region
ORDER BY rm.region;

通过LEFT JOIN确保即使某个区域没有订单，也会显示该区域（如果不需要可以改为INNER JOIN），ANY函数用于匹配数组中的任意国家码。

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