I have some data where there is simply a gap in the data and I need those gaps to be value '0'. Given the data below, thing 'a', 'b' and 'c' would ideally have +/- 12 hours of data around a given period. Where no data exists, that should be a 0
select *
from (
select 'a' as thing, timestamp '2024-12-19 11:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 12:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 13:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 14:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 15:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 16:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 17:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 18:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 19:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 20:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 21:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 22:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 23:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 15:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 16:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 17:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 18:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 19:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 20:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'c' as thing, timestamp '2024-12-19 12:00:00' as t, DBMS_RANDOM.value() as v from dual
) where t between timestamp '2024-12-19 20:00:00' - interval '12' hour
and timestamp '2024-12-19 11:00:00' + interval '12' hour
I know this is possible, but I am not quite sure how.
This can be solved by generating all of the combinations of things and hours that you are interested in and then LEFT OUTER JOINING that generated data to your actual table.
In your example it would look like:
WITH input_data AS
(
select *
from (
select 'a' as thing, timestamp '2024-12-19 11:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 12:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 13:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 14:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 15:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 16:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 17:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 18:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 19:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 20:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 21:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 22:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'a' as thing, timestamp '2024-12-19 23:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 15:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 16:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 17:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 18:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 19:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'b' as thing, timestamp '2024-12-19 20:00:00' as t, DBMS_RANDOM.value() as v from dual union all
select 'c' as thing, timestamp '2024-12-19 12:00:00' as t, DBMS_RANDOM.value() as v from dual
)
)
,hours AS
(
select timestamp '2024-12-19 11:00:00' + numtodsinterval(rownum,'HOUR') as hr
from dual
connect by level <= 12
)
,things AS
(
SELECT DISTINCT thing FROM input_data
)
/*cross join to generate all the data you are wanting*/
, hourthings AS
(
SELECT hr, thing
FROM hours
CROSS JOIN things
)
/*now join it all together*/
SELECT
hourthings.thing,
hourthings.hr,
COALESCE(input_data.v, 0) as v
FROM hourthings
LEFT OUTER JOIN input_data
ON hourthings.thing = input_data.thing
AND hourthings.hr = input_data.t
ORDER BY thing, hr, v;
+-------+------------------------------+-----------------------------------------+
| THING | HR | V |
+-------+------------------------------+-----------------------------------------+
| a | 19-DEC-24 12.00.00.000000000 | .69774073052016968823672302782349609322 |
| a | 19-DEC-24 13.00.00.000000000 | .74681895242556208805921962750792112372 |
| a | 19-DEC-24 14.00.00.000000000 | .13895201336452094187697669624078005226 |
| a | 19-DEC-24 15.00.00.000000000 | .45337500667932949332016060056416492918 |
| a | 19-DEC-24 16.00.00.000000000 | .18899747383037346346775198378542334477 |
| a | 19-DEC-24 17.00.00.000000000 | .67102219547677447329531648462053776372 |
| a | 19-DEC-24 18.00.00.000000000 | .66857093410415223366767066184514813993 |
| a | 19-DEC-24 19.00.00.000000000 | .40391951442247044543080670749491385357 |
| a | 19-DEC-24 20.00.00.000000000 | .66678394623467745293923962502423558974 |
| a | 19-DEC-24 21.00.00.000000000 | .08192799627659883827769961887128183705 |
| a | 19-DEC-24 22.00.00.000000000 | .82864536740652777939535875967936388967 |
| a | 19-DEC-24 23.00.00.000000000 | .06074762180454381335128190562038752096 |
| b | 19-DEC-24 12.00.00.000000000 | 0 |
| b | 19-DEC-24 13.00.00.000000000 | 0 |
| b | 19-DEC-24 14.00.00.000000000 | 0 |
| b | 19-DEC-24 15.00.00.000000000 | .49068229240906850444551902753208786223 |
| b | 19-DEC-24 16.00.00.000000000 | .49309406570981735526120776844366484152 |
| b | 19-DEC-24 17.00.00.000000000 | .81504764682984037766335094773881648938 |
| b | 19-DEC-24 18.00.00.000000000 | .56003821415269263216558677201167824597 |
| b | 19-DEC-24 19.00.00.000000000 | .47232889342064636947176597827340419611 |
| b | 19-DEC-24 20.00.00.000000000 | .26243408497186222403849841769849597216 |
| b | 19-DEC-24 21.00.00.000000000 | 0 |
| b | 19-DEC-24 22.00.00.000000000 | 0 |
| b | 19-DEC-24 23.00.00.000000000 | 0 |
| c | 19-DEC-24 12.00.00.000000000 | .82675839474283690631176461696378570193 |
| c | 19-DEC-24 13.00.00.000000000 | 0 |
| c | 19-DEC-24 14.00.00.000000000 | 0 |
| c | 19-DEC-24 15.00.00.000000000 | 0 |
| c | 19-DEC-24 16.00.00.000000000 | 0 |
| c | 19-DEC-24 17.00.00.000000000 | 0 |
| c | 19-DEC-24 18.00.00.000000000 | 0 |
| c | 19-DEC-24 19.00.00.000000000 | 0 |
| c | 19-DEC-24 20.00.00.000000000 | 0 |
| c | 19-DEC-24 21.00.00.000000000 | 0 |
| c | 19-DEC-24 22.00.00.000000000 | 0 |
| c | 19-DEC-24 23.00.00.000000000 | 0 |
+-------+------------------------------+-----------------------------------------+
A working example of this can be found at this dbfiddle
That SQL is more verbose than necessary as the three last CTEs could be collapsed into a single statement, but this more verbose write-up will help for understanding each logical step.