如何简化统计列中含指定后缀的URI数量的SQL查询？

需求背景 #

我需要统计表default.www-live-lon-cf中，2023年2月1日至8月31日期间，包含以下后缀的URI数量：

ukpga
ukla
asp
asc
anaw
mwa
ukcm
nia
aosp
aep
aip
apgb
nisi
mnia
apni

当前使用的查询通过多次UNION实现，功能正常但代码冗长，希望简化写法。现有查询示例：

SELECT 'uksi' AS legtype,
 COUNT (uri)  
FROM "default"."www-live-lon-cf" 
WHERE date >= date('2023-02-01')
  AND date <= date('2023-08-31')
  AND uri LIKE '%.uksi'
UNION
SELECT 'ukpga' AS legtype,
 COUNT (uri)  
FROM "default"."www-live-lon-cf" 
WHERE date >= date('2023-02-01')
  AND date <= date('2023-08-31')
  AND uri LIKE '%.ukpga'

简化方案 #

方案1：使用CASE表达式分组统计

只需要扫描一次表，通过CASE匹配后缀后分组计数，代码更紧凑：

SELECT
  CASE
    WHEN uri LIKE '%.ukpga' THEN 'ukpga'
    WHEN uri LIKE '%.ukla' THEN 'ukla'
    WHEN uri LIKE '%.asp' THEN 'asp'
    WHEN uri LIKE '%.asc' THEN 'asc'
    WHEN uri LIKE '%.anaw' THEN 'anaw'
    WHEN uri LIKE '%.mwa' THEN 'mwa'
    WHEN uri LIKE '%.ukcm' THEN 'ukcm'
    WHEN uri LIKE '%.nia' THEN 'nia'
    WHEN uri LIKE '%.aosp' THEN 'aosp'
    WHEN uri LIKE '%.aep' THEN 'aep'
    WHEN uri LIKE '%.aip' THEN 'aip'
    WHEN uri LIKE '%.apgb' THEN 'apgb'
    WHEN uri LIKE '%.nisi' THEN 'nisi'
    WHEN uri LIKE '%.mnia' THEN 'mnia'
    WHEN uri LIKE '%.apni' THEN 'apni'
    WHEN uri LIKE '%.uksi' THEN 'uksi'
  END AS legtype,
  COUNT(uri) AS count
FROM "default"."www-live-lon-cf"
WHERE date >= date('2023-02-01')
  AND date <= date('2023-08-31')
  AND (
    uri LIKE '%.ukpga' OR
    uri LIKE '%.ukla' OR
    uri LIKE '%.asp' OR
    uri LIKE '%.asc' OR
    uri LIKE '%.anaw' OR
    uri LIKE '%.mwa' OR
    uri LIKE '%.ukcm' OR
    uri LIKE '%.nia' OR
    uri LIKE '%.aosp' OR
    uri LIKE '%.aep' OR
    uri LIKE '%.aip' OR
    uri LIKE '%.apgb' OR
    uri LIKE '%.nisi' OR
    uri LIKE '%.mnia' OR
    uri LIKE '%.apni' OR
    uri LIKE '%.uksi'
  )
GROUP BY legtype
ORDER BY legtype;

方案2：使用CTE生成后缀列表再关联查询

这种方式更易维护，新增/删除后缀只需修改CTE部分，还能显示无匹配的后缀（计数为0）：

WITH suffixes AS (
  SELECT 'ukpga' AS legtype UNION ALL
  SELECT 'ukla' UNION ALL
  SELECT 'asp' UNION ALL
  SELECT 'asc' UNION ALL
  SELECT 'anaw' UNION ALL
  SELECT 'mwa' UNION ALL
  SELECT 'ukcm' UNION ALL
  SELECT 'nia' UNION ALL
  SELECT 'aosp' UNION ALL
  SELECT 'aep' UNION ALL
  SELECT 'aip' UNION ALL
  SELECT 'apgb' UNION ALL
  SELECT 'nisi' UNION ALL
  SELECT 'mnia' UNION ALL
  SELECT 'apni' UNION ALL
  SELECT 'uksi'
)
SELECT
  s.legtype,
  COUNT(t.uri) AS count
FROM suffixes s
LEFT JOIN "default"."www-live-lon-cf" t
  ON t.uri LIKE CONCAT('%.', s.legtype)
  AND t.date >= date('2023-02-01')
  AND t.date <= date('2023-08-31')
GROUP BY s.legtype
ORDER BY s.legtype;

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