这种重复写复杂CASE表达式的烦恼我太懂了！之前也遇到过类似的场景，给你几个既简洁又不牺牲性能的方案，完全可以替代你当前的LATERAL关联：

1. 封装为标量SQL函数（最简洁，适合多查询复用） #

把你的日期选择逻辑封装成一个简单的SQL函数，PostgreSQL会自动内联这类简单函数，性能和直接写CASE表达式几乎没有差异，同时能彻底避免重复代码。

首先创建函数：

CREATE OR REPLACE FUNCTION get_promotion_date(promotion_date date, offboard_date date)
RETURNS date
STABLE -- 因为用到current_date，事务内值不变，所以用STABLE而不是IMMUTABLE
LANGUAGE sql
AS $$
SELECT CASE
    WHEN promotion_date IS NULL AND offboard_date IS NULL THEN current_date::date
    WHEN promotion_date IS NULL THEN offboard_date
    ELSE promotion_date
END;
$$;

之后在查询中直接调用函数即可：

SELECT
    get_promotion_date(promotion_date, offboard_date) AS selected_date,
    -- 你的各种求和、计算操作，直接用selected_date或者重复调用函数都可以
    (get_promotion_date(promotion_date, offboard_date) - hire_date) AS tenure,
    SUM(amount) FILTER (WHERE get_promotion_date(promotion_date, offboard_date) > '2023-01-01') AS total_recent
FROM promotions
-- 其他JOIN、WHERE、GROUP BY逻辑
GROUP BY selected_date, tenure;

优点：代码极度简洁，逻辑集中管理（后续修改只需改函数），性能无损耗。
注意：一定要标记函数为STABLE，因为current_date在单个事务内是固定的，这样优化器能更好地处理它。

2. 用子查询/CTE提前计算日期（兼容性好，性能最优） #

如果不想创建函数，可以把日期计算逻辑放在内层子查询或者CTE中，外层直接引用别名。PostgreSQL的优化器会自动将这个逻辑合并到主查询中，性能和直接写CASE完全一致，不会有额外开销。

子查询版本（所有PostgreSQL版本都适用） #

SELECT
    p.selected_date,
    (p.selected_date - p.hire_date) AS tenure,
    SUM(p.amount) FILTER (WHERE p.selected_date > '2023-01-01') AS total_recent
    -- 其他计算操作
FROM (
    SELECT
        *,
        CASE
            WHEN promotion_date IS NULL AND offboard_date IS NULL THEN current_date::date
            WHEN promotion_date IS NULL THEN offboard_date
            ELSE promotion_date
        END AS selected_date
    FROM promotions
) AS p
-- 后续的JOIN、WHERE、GROUP BY逻辑
GROUP BY p.selected_date, p.tenure;

CTE版本（PostgreSQL 12+推荐，可读性更好） #

PostgreSQL 12及以后版本默认不再将CTE视为“优化屏障”，优化器会把CTE的逻辑直接合并到主查询中，性能和子查询一致：

WITH promotions_with_date AS (
    SELECT
        *,
        CASE
            WHEN promotion_date IS NULL AND offboard_date IS NULL THEN current_date::date
            WHEN promotion_date IS NULL THEN offboard_date
            ELSE promotion_date
        END AS selected_date
    FROM promotions
)
SELECT
    selected_date,
    (selected_date - hire_date) AS tenure,
    SUM(amount) FILTER (WHERE selected_date > '2023-01-01') AS total_recent
FROM promotions_with_date
-- 其他逻辑
GROUP BY selected_date, tenure;

优点：无需创建对象，代码结构清晰，性能和原生CASE完全相同。
注意：如果使用PostgreSQL 11及更早版本，CTE是优化屏障，可能会导致性能下降，这时候优先选择子查询版本。

为什么这些方案比LATERAL更好？ #

你之前用的LEFT JOIN LATERAL其实是“大材小用”了——LATERAL主要用于处理需要关联多行结果（比如调用返回多行的函数）或者依赖主查询列的多行子查询场景。而你的需求只是每行计算一个单一值，用子查询/CTE/函数的方式，优化器能更高效地处理，不会有LATERAL带来的逐行关联开销。

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