使用计算列的Postgresql子查询

Question

我是这个平台的新手，需要使用我已经计算过的列获得一个值。我知道我需要一个子查询，但是我被正确的语法所迷惑。

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

我得到的错误是错误:column "hourly_rate"不存在第4行:(oil/hourly_rate):双精度为6^提示:也许您想引用"production.hour_rate"列。SQL状态:42703性格:171我能理解……我尝试了括号，命名子查询和不同的策略。我知道这是语法问题有人能帮我一下吗?谢谢你

Answer 1

我对你的符号有点困惑，但看起来有括号问题:你的from语句没有链接到选择。

在我看来，管理子查询的最好方法是这样写:

WITH query1 AS (
select col1, col2
from table1
),
query2 as (
select col1, col2
from query1
(additional clauses)
),
select (what you want)
from query2
(additional statements)
Then you can manipulate your data progressively until you have the right organisation of your data for the final select, including aggregations

Answer 2

不能在选择列表中使用别名。您需要在列中包含原始计算。因此，您更新的查询将看起来像-

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