如何移除SQL CASE WHEN的Else结果，解决Chartio图表多余分类问题

我来帮你搞定这个问题！你当前的CASE WHEN语句里，Else "autre"会把所有不满足前三个WHEN条件的行都归类为"autre"，要只保留RunningCosts、StorageCosts和OtherCosts这三个分类，推荐两种解决方案，其中第一种更高效：

方案一：提前过滤符合条件的行（推荐） #

把环境(env="prod")和业务线(family="gaming")的筛选条件提到WHERE子句中，这样只有符合要求的行才会进入后续的CASE判断，既避免了CASE里重复写相同的条件，也直接排除了会产生"autre"的无关数据。

修改后的SQL代码如下：

SELECT 
  {DATE_BUCKET.BUCKET('TIMESTAMP_ADD(CAST(`usage_start_time` AS TIMESTAMP), INTERVAL {UTC_OFFSET.RAW} HOUR)', UTC_OFFSET)} AS `Date_BucketOfCreated_At`, 
  CASE 
    WHEN sku.description LIKE "%Licensing Fee%" OR sku.description LIKE "%running%" THEN "RunningCosts"
    WHEN sku.description LIKE "%storage%" OR sku.description LIKE "%SSD%" THEN "StorageCosts"
    ELSE "OtherCosts" 
  END AS `Origin`, 
  ROUND(SUM(cost), 2) AS charges 
FROM gcp.gcp_billing_export_v1_00A342_468C35_E1E69C 
WHERE 
  (SELECT value FROM UNNEST(labels) WHERE KEY = "env")="prod" 
  AND (SELECT value FROM UNNEST(labels) WHERE KEY="family")="gaming"
GROUP BY Date_BucketOfCreated_At, Origin 
ORDER BY Date_BucketOfCreated_At

改动说明： #

• 将原本分散在每个WHEN里的env="prod"和family="gaming"条件统一移到WHERE中，提前过滤掉不符合要求的行
• CASE语句简化：因为剩下的行都是符合prod+gaming的，只需根据sku描述判断分类即可，最后用Else直接归类为OtherCosts，不会出现"autre"

方案二：过滤掉Else产生的"autre"分类 #

如果不想提前过滤数据，也可以保留原CASE结构，但修改Else分支为NULL，然后通过WHERE或HAVING过滤掉无效的分类：

SELECT 
  {DATE_BUCKET.BUCKET('TIMESTAMP_ADD(CAST(`usage_start_time` AS TIMESTAMP), INTERVAL {UTC_OFFSET.RAW} HOUR)', UTC_OFFSET)} AS `Date_BucketOfCreated_At`, 
  CASE 
    WHEN (sku.description LIKE "%Licensing Fee%" OR sku.description LIKE "%running%") 
         AND (SELECT value FROM UNNEST(labels) WHERE KEY = "env")="prod" 
         AND (SELECT value FROM UNNEST(labels) WHERE KEY="family")="gaming" THEN "RunningCosts"
    WHEN (sku.description LIKE "%storage%" OR sku.description LIKE "%SSD%") 
         AND (SELECT value FROM UNNEST(labels) WHERE KEY = "env")="prod" 
         AND (SELECT value FROM UNNEST(labels) WHERE KEY="family")="gaming" THEN "StorageCosts"
    WHEN (sku.description NOT LIKE "%storage%" AND sku.description NOT LIKE "%SSD%" AND sku.description NOT LIKE "%Licensing Fee%" AND sku.description NOT LIKE "%running%") 
         AND (SELECT value FROM UNNEST(labels) WHERE KEY = "env")="prod" 
         AND (SELECT value FROM UNNEST(labels) WHERE KEY="family")="gaming" THEN "OtherCosts"
    ELSE NULL 
  END AS `Origin`, 
  ROUND(SUM(cost), 2) AS charges 
FROM gcp.gcp_billing_export_v1_00A342_468C35_E1E69C 
WHERE Origin IS NOT NULL
GROUP BY Date_BucketOfCreated_At, Origin 
ORDER BY Date_BucketOfCreated_At

或者在分组后用HAVING过滤：

-- ... 其他部分和原SQL一致 ...
GROUP BY Date_BucketOfCreated_At, Origin 
HAVING Origin != 'autre'
ORDER BY Date_BucketOfCreated_At

不过这种方法不如方案一高效，因为它会先处理所有数据，再过滤掉不需要的分类。

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