Refactor Oracle SQL Query that has many subqueries

Question

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:

READING_DATE	HOUR_VALUE	STATION_ID	TEMPERATURE	HUMIDITY	WIND	CONDITIONS
08/01/2022	1	station1	77	50	1	clear
08/01/2022	1	station2	75	50	0	clear
08/01/2022	1	station3	74	60	3	overcast
08/01/2022	2	station1	76	50	3	clear
08/01/2022	2	station2	74	50	0	clear
08/01/2022	2	station3	70	65	2	overcast
08/01/2022	3	station1	73	60	5	cloudy
08/01/2022	3	station2	71	70	2	cloudy
08/01/2022	3	station3	69	100	3	rain

The telemetry table looks like this:

READING_DATE	HOUR_VALUE	TELEMETRY_VALUE_1	TELEMETRY_VALUE_2
08/01/2022	1	430	10
08/01/2022	2	405	9
08/01/2022	3	390	8

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):

READING_DATE	HOUR_VALUE	TELEMETRY_VALUE_1	C1_TEMP	C1_WIND	C1_HUMIDITY	C1_CONDITIONS	C2_TEMP	C2_WIND	C2_HUMIDITY	C2_CONDITIONS
08/01/2022	1	430	77	1	50	clear	75	0	50	clear
08/01/2022	2	405	76	3	50	clear	74	0	50	clear
08/01/2022	3	390	73	5	60	overcast	71	2	70	cloudy

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

MT0 · Accepted Answer · 2022-09-08 23:15:58Z

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:

READING_DATE	HOUR_VALUE	TELEMETRY_VALUE_1	TELEMETRY_VALUE_2	C1_TEMP	C1_HUM	C1_WIND	C1_COND	C2_TEMP	C2_HUM	C2_WIND	C2_COND	C3_TEMP	C3_HUM	C3_WIND	C3_COND
08-JAN-22	1	430	10	77	50	1	clear	75	50	0	clear	74	60	3	overcast
08-JAN-22	2	405	9	76	50	3	clear	74	50	0	clear	70	65	2	overcast
08-JAN-22	3	390	8	73	60	5	cloudy	71	70	2	cloudy	69	100	3	rain

fiddle

D'oh! There are actually some additional columns in the weather table that I needed to specifically ignore. I took this same approach as your query initially, but it was selecting each set of values for each station on individual lines. SQL isn't my best language, so I thought I was just doing the pivot wrong. However, once I filtered out these columns it worked. I had ultimately had to put this on the pivot line: SELECT * From ( SELECT reading_date, hour_value, station_id, temperature, humidity, wind, conditions from weather) PIVOT...

Collectives™ on Stack Overflow

Refactor Oracle SQL Query that has many subqueries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related