可以用CASE表达式引用另一行

Question

我有一个产品表，它的结构是:

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

我想检查产品是否在日期之间，但在本例中phase_end为空，因此我想获得Obsolete的phase_start，并将其用作GA的阶段结束，以检查该产品是否在阶段结束前6个月的时间间隔内:

select 
id,
product_id,
phase_type,
phase_start,
phase_end,
(
case
when phase_type = 'GA' 
and phase_end is null 
and current_date between (phase_start_of_obsolete - interval '6 month')::date and phase_start_of_obsolete then true
else false
end
) as current_phase
from table_product

上面的值phase_start_of_obsolete是伪代码，并表示我希望在该阶段过时时具有相同产品的阶段开始日期。我尝试了一些与自己表的自连接，但我相信在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

Answer 1

一种方法是使用EXISTS子句进行自连接:

SELECT t1.*,
EXISTS (SELECT FROM table_product AS t2
WHERE t1.phase_type = 'GA'
AND t1.phase_end IS NULL
AND t2.product_id = t1.product_id
AND t2.phase_type = 'Obsolete'
AND t2.phase_start - current_timestamp
BETWEEN INTERVAL '0-0' AND INTERVAL '0-6'
) AS phase_end
FROM table_product AS t1;
id │ product_id │ phase_type │ phase_start │ phase_end │ phase_end 
════╪════════════╪════════════╪═════════════╪═══════════╪═══════════
1 │          1 │ Obsolete   │ 2025-01-01  │           │ f
2 │          1 │ GA         │ 2022-01-01  │           │ f
(2 rows)

phase_end目前都是FALSE，因为它仍然是2023。

Answer 2

下面的查询返回所请求的结果，不使用CASE语句或自连接:

SELECT
id,
product_id,
phase_type,
phase_start,
phase_end,
phase_type = 'GA'
AND phase_end IS NULL
AND daterange(CURRENT_DATE, (CURRENT_DATE + '6 months'::interval)::date) @> min(phase_start) FILTER (WHERE phase_type = 'Obsolete') OVER (PARTITION BY product_id) AS current_phase
FROM
table_product;

使用BETWEEN代替multirange的另一个形式是:

SELECT
id,
product_id,
phase_type,
phase_start,
phase_end,
phase_type = 'GA'
AND phase_end IS NULL
AND min(phase_start) FILTER (WHERE phase_type = 'Obsolete') OVER (PARTITION BY product_id) BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '6' MONTH AS current_phase
FROM
table_product;

与其检查6个月的范围，不如简单地确定当前日期晚于"Obsolete"开始之前的6个月。阶段。

不需要CASE表达式，因为逻辑值可以直接求值。通常，无论何时需要逻辑值，都要避免使用CASE并显式地设置该值:这是不必要的冗长，并且会产生引入代码缺陷的额外机会。