I have a query in an Oracle database that I am hoping to simplify. I have done something similar with a PIVOT before, but not something this complex. I have two tables, WEATHER AND TELEMETRY. The query is for an ancient report that ties several values from several weather readings to the telemetry data. The weather data looks like this:

The telemetry table looks like this:

The telemetry values are only tied to the date and hour, not to the station ids. The desired result is a query that lays out the temperature, wind, humidity, and conditions for each hour as shown below (the temp, wind, humidity and conditions repeat for up to 10 stations, but I cut it off after two here):

The report has a working query, which is shown below. Would it be possible to refactor this somehow using a pivot or some other method so that I don't have to individually select every piece of data for every station?

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

CodePudding user response：

PIVOT the stations from rows to columns:

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)

Which, for the sample data:

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;

Outputs:

fiddle