重構具有許多子查詢的OracleSQL查詢

我在希望簡化的 Oracle 資料庫中有一個查詢。我以前用 PIVOT 做過類似的事情，但不是這么復雜的事情。我有兩張桌子，天氣和遙測。該查詢是針對一份古老的報告，該報告將來自多個天氣讀數的多個值與遙測資料聯系起來。天氣資料如下所示：

遙測表如下所示：

遙測值僅與日期和小時相關聯，與站 ID 無關。所需的結果是一個查詢，其中列出了每小時的溫度、風、濕度和條件，如下所示（溫度、風、濕度和條件最多重復 10 個站點，但我在這里兩個站點后將其切斷）：

該報告有一個作業查詢，如下所示。是否可以使用樞軸或其他方法以某種方式重構它，以便我不必為每個站點單獨選擇每條資料？

select a.reading_date,
    a.hour_value,
    a.telemetry_value_1,

(select AVG(temperature) from WEATHER_ACTUALS b where station_id = 'station1' and reading_date = a.reading_date and hour_value = a.hour_value) as c1_temp,
(select AVG(wind) from WEATHER_ACTUALS b where station_id = 'station1' and reading_date = a.reading_date and hour_value = a.hour_value) as c1_wind,
(select AVG(humidity) from WEATHER_ACTUALS b where station_id = 'station1' and reading_date = a.reading_date and hour_value = a.hour_value) as c1_humidity,
(select conditions from WEATHER_ACTUALS b where rownum = 1 and station_id = 'station1' and reading_date = a.reading_date and hour_value = a.hour_value) as c1_conditions,

(select AVG(temperature) from WEATHER_ACTUALS b where station_id = 'station2' and reading_date = a.reading_date and hour_value = a.hour_value) as c2_temp,
(select AVG(wind) from WEATHER_ACTUALS b where station_id = 'station2' and reading_date = a.reading_date and hour_value = a.hour_value) as c2_wind,
(select AVG(humidity) from WEATHER_ACTUALS b where station_id = 'station2' and reading_date = a.reading_date and hour_value = a.hour_value) as c2_humidity,
(select conditions from WEATHER_ACTUALS b where rownum = 1 and station_id = 'station2' and reading_date = a.reading_date and hour_value = a.hour_value) as c2_conditions,

...

(select AVG(temperature) from WEATHER_ACTUALS b where station_id = 'station10' and reading_date = a.reading_date and hour_value = a.hour_value) as c10_temp,
(select AVG(wind) from WEATHER_ACTUALS b where station_id = 'station10' and reading_date = a.reading_date and hour_value = a.hour_value) as c10_wind,
(select AVG(humidity) from WEATHER_ACTUALS b where station_id = 'station10' and reading_date = a.reading_date and hour_value = a.hour_value) as c10_humidity,
(select conditions from WEATHER_ACTUALS b where rownum = 1 and station_id = 'station10' and reading_date = a.reading_date and hour_value = a.hour_value) as c10_conditions

  from telemetry_data a
 where a.reading_date between :beginDate and :endDate

uj5u.com熱心網友回復：

PIVOT從行到列的站點：

SELECT t.*,
       w.c1_temp,
       w.c1_hum,
       w.c1_wind,
       w.c1_cond,
       w.c2_temp,
       w.c2_hum,
       w.c2_wind,
       w.c2_cond,
       w.c3_temp,
       w.c3_hum,
       w.c3_wind,
       w.c3_cond
FROM   telemetry t
       INNER JOIN (
         SELECT *
         FROM   weather
         PIVOT (
           AVG(temperature) AS temp,
           AVG(humidity) AS hum,
           AVG(wind) AS wind,
           MAX(conditions) KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS cond
           FOR station_id IN (
             'station1' AS c1,
             'station2' AS c2,
             'station3' AS c3
           )
         )
       ) w
       ON (   t.reading_date = w.reading_date
          AND t.hour_value   = w.hour_value)

其中，對于樣本資料：

CREATE TABLE weather (READING_DATE, HOUR_VALUE, STATION_ID, TEMPERATURE, HUMIDITY, WIND, CONDITIONS) AS
SELECT DATE '2022-01-08', 1, 'station1', 77,  50, 1, 'clear'    FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 1, 'station2', 75,  50, 0, 'clear'    FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 1, 'station3', 74,  60, 3, 'overcast' FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 2, 'station1', 76,  50, 3, 'clear'    FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 2, 'station2', 74,  50, 0, 'clear'    FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 2, 'station3', 70,  65, 2, 'overcast' FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 3, 'station1', 73,  60, 5, 'cloudy'   FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 3, 'station2', 71,  70, 2, 'cloudy'   FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 3, 'station3', 69, 100, 3, 'rain'     FROM DUAL;

CREATE TABLE telemetry (READING_DATE, HOUR_VALUE, TELEMETRY_VALUE_1, TELEMETRY_VALUE_2) AS
SELECT DATE '2022-01-08', 1, 430, 10 FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 2, 405,  9 FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 3, 390,  8 FROM DUAL;

輸出：

小提琴

標籤：sql 甲骨文

上一篇：OracleSQL中Java運算子“%”的等效函式

下一篇：我的作業有問題-加入顯示沒有資料