PostgreSQL：SELECT语句中计算变量的使用（2/2）

我编写了如下无法正常运行的SELECT语句：

SELECT foo,
    extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm,
    CASE 
       WHEN age_norm >= 0 AND age_norm < 1 THEN '00'
       WHEN age_norm >= 1 AND age_norm < 5 THEN '01-4'
       --etc
    END as age_group
FROM bar

请问是否有方法可以在该查询中直接引用定义的计算变量age_norm？

说明 #

我曾提出过类似问题，但此前的问题场景中不包含本次查询涉及的foo字段。

补充尝试 #

我曾尝试如下写法：

SELECT foo,
    (
    SELECT t.age_norm,
    CASE 
       WHEN t.age_norm >= 0 AND t.age_norm < 1 THEN '00'
       WHEN t.age_norm >= 1 AND t.age_norm < 5 THEN '01-4'
       --etc
    END 
    FROM (SELECT extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25 as age_norm FROM bar) t
    )
   as age_group
FROM bar

但执行后返回如下报错：

ERROR:  subquery must return only one column
LINE 10:     (  SELECT t.age_norm,
             ^
SQL state: 42601
Character: 212

最终结论 #

我尝试了部分推荐的解决方案，但要么查询执行效率过低（相较于在每个WHEN子句中硬编码extract计算逻辑的写法），要么查询结构过于复杂（实际业务场景中的查询还包含GROUP BY等其他逻辑，远比例示内容复杂）。

因此最终我选择在每个WHEN分支中直接重复编写年龄计算逻辑，写法如下：

WHEN (extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25) >= 0 AND (extract(day from CAST (date as TIMESTAMP) - CAST (birth_date as TIMESTAMP)) / 365.25) < 1 THEN '00' 
etc.

感谢大家的解答！

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