I have a table with energy usage data in fifteen minute intervals:

I need to calculate energy usage per hour, where, for example, usage during hour 23 of 12/04/2022 is the sum of the intervals from 12/04/2022 23:15 to 13/04/2022 00:00 (notice the change in date), and usage during hour 0 of 12/04/2022 is the sum of the intervals from 13/04/2022 00:15 to 13/04/2022 01:00.

I'll appreciate suggestions on how to code this concisely in Oracle SQL.

CodePudding user response：

You can subtract 15 minutes from the times and then truncate to the start of the hour and then aggregate by that and the meter:

SELECT meter,
       TRUNC(timestamp - INTERVAL '15' MINUTE, 'HH') AS hour,
       SUM(kwh) AS total_kwh
FROM   table_name
GROUP BY
       meter,
       TRUNC(timestamp - INTERVAL '15' MINUTE, 'HH');

Which, for the sample data:

CREATE TABLE table_name (METER, TIMESTAMP, KWH) AS
SELECT 2500396, DATE '2022-04-12'   INTERVAL '23:15' HOUR TO MINUTE, 131.01 FROM DUAL UNION ALL
SELECT 2500396, DATE '2022-04-12'   INTERVAL '23:30' HOUR TO MINUTE, 132.11 FROM DUAL UNION ALL
SELECT 2500396, DATE '2022-04-12'   INTERVAL '23:45' HOUR TO MINUTE, 125.84 FROM DUAL UNION ALL
SELECT 2500396, DATE '2022-04-13'   INTERVAL '00:00' HOUR TO MINUTE, 127.27 FROM DUAL UNION ALL
SELECT 2500396, DATE '2022-04-13'   INTERVAL '00:15' HOUR TO MINUTE, 123.86 FROM DUAL UNION ALL
SELECT 2500396, DATE '2022-04-13'   INTERVAL '00:30' HOUR TO MINUTE, 114.51 FROM DUAL UNION ALL
SELECT 2500396, DATE '2022-04-13'   INTERVAL '00:45' HOUR TO MINUTE, 117.70 FROM DUAL UNION ALL
SELECT 2500396, DATE '2022-04-13'   INTERVAL '01:00' HOUR TO MINUTE, 120.01 FROM DUAL;

Outputs:

METER	HOUR	TOTAL_KWH
2500396	2022-04-12 23:00:00	516.23
2500396	2022-04-13 00:00:00	476.08

db<>fiddle here