postgresql - 加入别名列 SQL

Question

我正在尝试加入别名命名的列。总的来说，我想要一个包含日期、小时以及实际和预测(最近在前一天上午 10 点之前)风速的输出。

通过下面的代码我得到:

ERROR: column "date" does not exist
LINE xx: ...ast_prep.lat AND meso.lon = forecast_prep.lon AND Date ...

我不知道如何让 SQL 连接这些命名列。
谢谢。是的，我是 SQL 新手。

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)

Answer 1

从那里看不到“日期”别名。

您可以在 WITH 之后使用几个表，所以我建议您将第二个选择移到那里。

我不完全确定 weather.meso 表结构，但根据您的查询进行猜测，这应该可行:

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