BigQuery中CASE WHEN查询优化：图书与库分类匹配校验

优化思路 #

• 合并冗余CTE，将图书编码、库编码、书架信息的计算合并到一个查询中，减少执行步骤
• 统一大小写处理，避免因大小写差异导致的匹配错误
• 优化路径提取逻辑，用正则表达式更精准地提取Project后的书架名称，避免原SPLIT+REPLACE的逻辑错误
• 简化CASE语句的写法，提升可读性
• 移除不必要的DISTINCT（若原始数据无重复或无需去重），降低查询开销

优化后的查询代码 #

SELECT
  Book_names,
  library,
  -- 提取图书编码：统一转小写后匹配，确保大小写无关
  CASE
    WHEN LOWER(Book_names) LIKE '%fantasy%' OR LOWER(Book_names) LIKE '%fan%' THEN 'fan'
    WHEN LOWER(Book_names) LIKE '%fiction%' OR LOWER(Book_names) LIKE '%fic%' THEN 'fic'
    WHEN LOWER(Book_names) LIKE '%biography%' OR LOWER(Book_names) LIKE '%bio%' THEN 'bio'
  END AS BOOK_code,
  -- 用正则精准提取Project后的书架目录，处理多斜杠情况
  REGEXP_EXTRACT(library, r'//AA/Project/([^/]+)') AS Shelf,
  -- 提取库编码：统一转小写后匹配
  CASE
    WHEN LOWER(library) LIKE '%fantasy%' OR LOWER(library) LIKE '%fan%' THEN 'fan'
    WHEN LOWER(library) LIKE '%fiction%' OR LOWER(library) LIKE '%fic%' THEN 'fic'
    WHEN LOWER(library) LIKE '%biography%' OR LOWER(library) LIKE '%bio%' THEN 'bio'
  END AS LIBRARY_CODE
FROM `table_name`
-- 筛选不匹配或编码为空的记录
WHERE BOOK_code != LIBRARY_CODE 
   OR BOOK_code IS NULL 
   OR LIBRARY_CODE IS NULL

关键优化点说明 #

1. 统一大小写匹配：所有匹配逻辑都通过LOWER()转换为小写，避免因路径或书名中的大小写差异（如Fantasy和fantasy）导致的错误匹配。
2. 精准路径提取：使用REGEXP_EXTRACT(library, r'//AA/Project/([^/]+)')直接提取//AA/Project/后的第一个目录，无需处理多余斜杠，比原SPLIT(REPLACE(...))逻辑更可靠——原逻辑中REPLACE(library, '/', '')会把所有斜杠去掉，导致SPLIT后的结果完全错误，正则方法能正确处理多斜杠的情况。
3. 合并CTE：将原两个CTE合并为单查询，减少BigQuery的执行阶段，降低数据扫描次数，提升查询效率。
4. 增强匹配逻辑：在CASE语句中同时匹配完整分类名（如fantasy）和编码（如fan），覆盖更多场景，避免仅匹配编码导致的遗漏（比如书名中是fantasy而非fan的情况）。
5. 移除不必要的DISTINCT：原查询中的DISTINCT会增加计算开销，若原始数据无重复记录或无需去重，建议移除；若需去重，可将DISTINCT放在最终查询的SELECT后，而非中间步骤，减少中间数据量。

验证结果 #

执行上述查询后，将准确返回你期望的不匹配记录，同时解决了原查询中可能存在的大小写误判、路径提取错误等问题。

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