加入别名列 SQL [英] Join Alias Columns SQL
问题描述
我正在努力加入别名命名的列.总的来说,我想要一个包含日期、小时以及实际和预测(最近的前一天上午 10 点之前)风速的输出.
I am trying struggling with joining alias named columns. Overall, I want an output with the with date, hour, and the actual and forecasted (most recent before 10am on the previous day) windspeeds.
使用以下代码我得到:
错误:日期"列不存在
第 xx 行:...ast_prep.lat AND meso.lon = forecast_prep.lon AND Date ...
ERROR: column "date" does not exist
LINE xx: ...ast_prep.lat AND meso.lon = forecast_prep.lon AND Date ...
我不知道如何让 SQL 加入这些命名列.
谢谢.是的,我是 SQL 新手.
I cannot figure out how to get SQL to join these named columns.
Thanks. And yes, I am a SQL newbie.
with forecast_prep as (
SELECT
date_trunc('day', foretime)::date AS Foredate,
extract(hour from foretime)+1 AS foreHE,
lat,
lon,
windspeed,
max(as_of) AS as_of
FROM weather.forecast
WHERE date_trunc('day', foretime)::date-as_of>= interval '16 hours'
GROUP BY Foredate, foreHE, lat, lon, windspeed)
SELECT
meso.station,
date_trunc('day', meso.timestmp)::date AS Date,
extract(hour from meso.timestmp)+1 AS HE,
CAST(AVG(meso.windspd) as numeric(19,2)) As Actual,
forecast_prep.windspeed,
forecast_prep.as_of
FROM weather.meso
INNER JOIN forecast_prep ON (
meso.lat = forecast_prep.lat AND
meso.lon = forecast_prep.lon AND
Date = Foredate AND ----<<<< Error here
HE = foreHE)
WHERE
(meso.timestmp Between '2016-02-01' And '2016-02-02') AND
(meso.station='KSBN')
GROUP BY meso.station, Date, HE, forecast_prep.windspeed, forecast_prep.as_of
ORDER BY Date, HE ASC
表结构如下:
-- Table: weather.forecast
-- DROP TABLE weather.forecast;
CREATE TABLE weather.forecast
(
foretime timestamp without time zone NOT NULL,
as_of timestamp without time zone NOT NULL, -- in UTC
summary text,
precipintensity numeric(8,4),
precipprob numeric(2,2),
temperature numeric(5,2),
apptemp numeric(5,2),
dewpoint numeric(5,2),
humidity numeric(2,2),
windspeed numeric(5,2),
windbearing numeric(4,1),
visibility numeric(5,2),
cloudcover numeric(4,2),
pressure numeric(6,2),
ozone numeric(5,2),
preciptype text,
lat numeric(8,6) NOT NULL,
lon numeric(9,6) NOT NULL,
CONSTRAINT forecast_pkey PRIMARY KEY (foretime, as_of, lat, lon)
-- Table: weather.meso
-- DROP TABLE weather.meso;
CREATE TABLE weather.meso
(
timestmp timestamp without time zone NOT NULL,
station text NOT NULL,
lat numeric NOT NULL,
lon numeric NOT NULL,
tmp numeric,
hum numeric,
windspd numeric,
winddir integer,
dew numeric,
CONSTRAINT meso_pkey PRIMARY KEY (timestmp, station, lat, lon)
推荐答案
从那里看不到日期"别名.
'Date' alias can't be seen from there.
在 WITH 之后您可以使用几个表,因此我建议您将第二个选择移到那里.
You can use few tables after WITH, so I'll advice you to move second select there.
我不完全确定 weather.meso 表结构,但根据您的查询进行猜测,这应该可行:
I'm not completly sure about weather.meso table structure but by guesing based on your query, this should work:
WITH
forecast_prep AS (
SELECT
date_trunc('day', foretime) :: DATE AS Foredate,
extract(HOUR FROM foretime) + 1 AS foreHE,
lat,
lon,
max(windspeed) as windspeed,
max(as_of) AS as_of
FROM weather.forecast
WHERE date_trunc('day', foretime) :: DATE - as_of >= INTERVAL '16 hours'
GROUP BY Foredate, foreHE, lat, lon
),
tmp AS (
SELECT
meso.station,
meso.lat,
meso.lon,
meso.timestmp,
date_trunc('day', meso.timestmp) :: DATE AS Date,
extract(HOUR FROM meso.timestmp) + 1 AS HE,
CAST(AVG(meso.windspd) AS NUMERIC(19, 2)) AS Actual
FROM weather.meso
GROUP BY station, lat, lon, timestmp, Date, HE
)
SELECT
tmp.station, tmp.Date, tmp.HE, tmp.Actual, forecast_prep.windspeed, forecast_prep.as_of
FROM tmp
INNER JOIN forecast_prep ON (
tmp.lat = forecast_prep.lat
AND tmp.lon = forecast_prep.lon
AND tmp.Date = forecast_prep.Foredate
AND tmp.HE = forecast_prep.foreHE
)
WHERE
(tmp.timestmp BETWEEN '2016-02-01' AND '2016-02-02')
AND (tmp.station = 'KSBN')
GROUP BY
tmp.station, tmp.Date, tmp.HE, forecast_prep.windspeed, forecast_prep.as_of, tmp.Actual
ORDER BY tmp.Date, tmp.HE ASC;
就像这里的第一个例子 https://www.postgresql.org/docs/8.4/static/queries-with.html
Like in first example right here https://www.postgresql.org/docs/8.4/static/queries-with.html
这篇关于加入别名列 SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!