PostgreSQL 10单SELECT查询如何简化SELECT CASE中的重复算术表达式

常见无效写法的问题根源 #

你之前尝试的两种写法无法生效，核心原因是SQL语法规则的限制：

• 第一种写法CASE 表达式 WHEN <100：简单CASE语法要求WHEN后直接跟待匹配的固定值，不能写带比较符的条件；带比较逻辑的场景必须使用搜索型CASE（即CASE后不跟初始表达式，每个WHEN后写完整判断条件）。
• 第二种写法CASE 表达式 AS x WHEN x <100：SQL的解析顺序决定了同一SELECT层级内定义的列别名，会在整个SELECT子句解析完成后才生成，无法被同层级的其他表达式（包括当前CASE分支）直接引用。

可落地的无性能损耗实现方案 #

以下方案完全适配PostgreSQL 10环境，为单条原生SQL、无自定义函数、无存储过程，执行计划与原查询完全一致，无任何性能损失，同时完全消除CASE分支中重复的算术表达式：

方案1：最小改动实现（CROSS JOIN LATERAL 提取公共计算） #

仅在原有查询的FROM子句中新增一行横向连接，把重复的算术表达式单独定义为可引用列，CASE分支直接引用该列即可：

SELECT
  CASE
    WHEN calc.val < 100 THEN 1
    WHEN calc.val < 200 THEN 2
    WHEN calc.val < 250 THEN 3
    WHEN calc.val < 300 THEN 4
    WHEN calc.val < 800 THEN 5
    -- 后续所有分桶分支直接引用 calc.val，无需重复写长算术表达式
  END bucket_range,
  COUNT(table1.id) as stats
FROM
  table1 INNER JOIN table2
    ON table1.column_x = table2.column_y
  -- 仅需新增这一行，单独提取公共算术表达式
  CROSS JOIN LATERAL (VALUES ((table1.col_a + table2.col_b) / 2)) AS calc(val)
WHERE
  <filter conditions>
GROUP BY 1
ORDER BY bucket_range

性能说明：PostgreSQL优化器会自动对这种无外部关联的LATERAL计算做表达式内联，最终执行计划和你原本重复写算术表达式的查询没有任何区别，不会产生额外的计算开销。不管分桶边界如何调整，公共算术表达式只需要维护一次。

方案2：嵌套派生表提取公共表达式 #

如果不使用LATERAL语法，也可以在原有查询外层嵌套一层派生表，把公共计算逻辑放到内层：

SELECT
  CASE
    WHEN val < 100 THEN 1
    WHEN val < 200 THEN 2
    WHEN val < 250 THEN 3
    WHEN val < 300 THEN 4
    WHEN val < 800 THEN 5
    -- 后续分桶分支直接引用 val 即可
  END bucket_range,
  COUNT(id) as stats
FROM (
  SELECT
    table1.id,
    (table1.col_a + table2.col_b) / 2 AS val
  FROM
    table1 INNER JOIN table2
      ON table1.column_x = table2.column_y
  WHERE
    <filter conditions>
) AS base
GROUP BY 1
ORDER BY bucket_range

性能说明：PostgreSQL 10的优化器会自动对这类简单派生表做谓词下推、表达式上提，执行效率与原查询无差异，不会因为嵌套产生性能损耗。

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