CASE表达式能否跨行引用？GA阶段日期判断优化咨询

我有一张product表，结构及数据如下：

id  product_id phase_type phase_start phase_end
  1    1         Obsolete   2025-01-01  null
  2    1         GA         2022-01-01  null

需要判断产品是否处于指定日期区间：当GA阶段的phase_end为null时，用同产品Obsolete阶段的phase_start作为GA的phase_end，进而判断当前日期是否处于该phase_end前6个月的区间内。

我尝试过自连接实现，但担心CASE表达式内嵌套查询的性能问题，想寻求更优方案。期望输出结果：

id product_id phase_type phase_start phase_end current_phase
   1  1          Obsolete   2025-01-01  null      false
   2  1          GA         2022-01-01  null      true

可以使用窗口函数替代自连接，避免嵌套查询带来的性能损耗，具体SQL如下：

SELECT
    id,
    product_id,
    phase_type,
    phase_start,
    phase_end,
    CASE
        WHEN phase_type = 'GA'
             AND phase_end IS NULL
             AND CURRENT_DATE BETWEEN (obsolete_start - INTERVAL '6 month')::DATE AND obsolete_start
        THEN TRUE
        ELSE FALSE
    END AS current_phase
FROM (
    SELECT
        *,
        MAX(CASE WHEN phase_type = 'Obsolete' THEN phase_start END) OVER (PARTITION BY product_id) AS obsolete_start
    FROM product
) AS sub

1. 子查询通过MAX(CASE WHEN phase_type = 'Obsolete' THEN phase_start END) OVER (PARTITION BY product_id)，按product_id分组提取每个产品对应的Obsolete阶段phase_start，命名为obsolete_start。基于数据逻辑（每个产品仅一个Obsolete阶段），MAX函数可精准获取目标值。
2. 外层查询直接使用obsolete_start完成日期区间判断，逻辑与原伪代码一致，但窗口函数的计算效率远高于自连接，尤其在数据量较大时优势明显。

运行上述SQL后，将得到预期输出：

id product_id phase_type phase_start phase_end current_phase
   1  1          Obsolete   2025-01-01  null      false
   2  1          GA         2022-01-01  null      true

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