Snowflake/SQL中能否简化嵌套ILIKE类型的CASE语句？

原SQL语句（保留原文结构，存在语法错误） #

SELECT
  CASE coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+)(/|$)', 1, 1, 'e', 1)), random_sample_column)
     when 'green' then 'GO'
     when 'red' then 'STOP'
     when 'yellow' then 'SLOW'
     else
         case when coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+) ilike '%green' then 'GO?'
         case when coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+) ilike '%yellow' then 'SLOW?'
         case when coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+) ilike '%red' then 'STOP?'     
   END as sample_derived_column
FROM SAMPLE_TABLE_NAME;

样本数据 #

random_sample_column
--------------
yellow30-1123$%schmidt
jingle43123heimer
red
isthelightgreen
beluw
beow
blue

改写后的简洁版SQL #

可以把嵌套CASE合并到同一个CASE结构中，用WHEN ... THEN ...依次处理等值匹配、模糊匹配，同时修正原语句的语法错误：

SELECT
  CASE 
    -- 优先处理精确等值匹配
    WHEN coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+)(/|$)', 1, 1, 'e', 1)), random_sample_column) = 'green' THEN 'GO'
    WHEN coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+)(/|$)', 1, 1, 'e', 1)), random_sample_column) = 'red' THEN 'STOP'
    WHEN coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+)(/|$)', 1, 1, 'e', 1)), random_sample_column) = 'yellow' THEN 'SLOW'
    -- 再处理模糊匹配（ILIKE）
    WHEN coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+)(/|$)', 1, 1, 'e', 1)), random_sample_column) ILIKE '%green%' THEN 'GO?'
    WHEN coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+)(/|$)', 1, 1, 'e', 1)), random_sample_column) ILIKE '%yellow%' THEN 'SLOW?'
    WHEN coalesce(lower(regexp_substr(random_sample_column, '/key_word/\d+/([\w-]+)(/|$)', 1, 1, 'e', 1)), random_sample_column) ILIKE '%red%' THEN 'STOP?'
    -- 所有匹配不命中时的默认输出
    ELSE NULL
  END as sample_derived_column
FROM SAMPLE_TABLE_NAME;

改写说明 #

1. 合并嵌套CASE为单一结构，逻辑层级更清晰，和等值匹配写法保持统一，避免冗余嵌套。
2. 补全原语句中未闭合的括号，修正ILIKE的引号语法错误。
3. 保留原逻辑的优先级：先精确匹配，再模糊匹配。
4. 明确添加ELSE NULL分支，确保所有场景都有明确输出。

样本数据对应输出 #

执行改写后的SQL，会得到如下结果：

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