PostgreSQL引用查询内计算列报错 求正确子查询写法

PostgreSQL的查询逻辑执行顺序中，同层级SELECT子句里定义的别名无法被同层级的其他计算引用，你可以通过以下两种方式修正语法：

方法1：嵌套子查询 #

把hourly_rate的计算逻辑放到内层子查询中，外层查询直接引用该别名做后续计算：

SELECT 
  well_id, 
  reported_date, 
  oil,
  hourly_rate,
  (oil/hourly_rate)::double precision as six
FROM (
  SELECT 
    well_id, 
    reported_date, 
    oil,
    (EXTRACT(EPOCH FROM age(reported_date, LAG(reported_date) OVER w))/3600)::int as hourly_rate
  FROM public.production
  WINDOW w AS (PARTITION BY well_id ORDER BY well_id, reported_date 
               ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
) AS t

方法2：使用CTE（公共表表达式） #

结构更清晰，适合多层计算的复杂场景：

WITH base_calc AS (
  SELECT 
    well_id, 
    reported_date, 
    oil,
    (EXTRACT(EPOCH FROM age(reported_date, LAG(reported_date) OVER w))/3600)::int as hourly_rate
  FROM public.production
  WINDOW w AS (PARTITION BY well_id ORDER BY well_id, reported_date 
               ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
)
SELECT 
  well_id, 
  reported_date, 
  oil,
  hourly_rate,
  (oil/hourly_rate)::double precision as six
FROM base_calc

注意：虽然你也可以直接把hourly_rate的完整计算表达式重复写到six的计算逻辑里，但会导致代码冗余、维护成本升高，不推荐使用。

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