Date interval Blank count [message #681957] |
Wed, 16 September 2020 13:57 |
|
Marinus1979
Messages: 2 Registered: September 2020
|
Junior Member |
|
|
There are occasions when no data is taken for while. So if there are no data for a particular interval then I would like this to show up as zero so that it shows up on the plot. So if i'm taking averages every 10 minutes and
between 12:30 and 13:00 there is no data stored in the
table how can I get results like
Time Ave
12:00 1.2
12:10 3.0
12:20 4.0
12:30 0
12:40 0
12:50 0
13:00 1.2
[Updated on: Thu, 17 September 2020 01:57] by Moderator Report message to a moderator
|
|
|
|
Re: Date interval Blank count [message #681960 is a reply to message #681957] |
Wed, 16 September 2020 14:22 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
One option is to create a "calendar" of all times you're working with and outer join it with your own data. See comments.
SQL> with test (datum, val) as
2 -- your data
3 (select to_date('10.09.2020 12:03', 'dd.mm.yyyy hh24:mi'), 5 from dual union all
4 select to_date('10.09.2020 12:06', 'dd.mm.yyyy hh24:mi'), 2 from dual union all
5 select to_date('10.09.2020 12:14', 'dd.mm.yyyy hh24:mi'), 6 from dual union all
6 select to_date('10.09.2020 12:22', 'dd.mm.yyyy hh24:mi'), 1 from dual union all
7 select to_date('10.09.2020 13:02', 'dd.mm.yyyy hh24:mi'), 5 from dual
8 ),
9 -- min and max dates involved - needed for a calendar
10 minimax as
11 (select trunc(min(datum), 'hh24') mindat,
12 trunc(max(datum), 'hh24') maxdat
13 from test
14 ),
15 -- "calendar"
16 times as
17 (select mindat + 10/(24*60) * (level - 1) tfrom
18 from minimax
19 connect by level <= (maxdat - mindat) * 24 * 60 / 6
20 ),
21 -- periods of 10 minutes
22 tab_times as
23 (select t.tfrom time_from,
24 lead(t.tfrom) over (order by t.tfrom) time_to
25 from times t
26 )
27 -- finally, outer join created "calendar" with your own data
28 select to_char(tt.time_from, 'hh24:mi') time_From,
29 to_char(tt.time_to, 'hh24:mi') time_to,
30 nvl(avg(s.val), 0) avg_val
31 from tab_times tt left join test s on s.datum >= tt.time_from
32 and s.datum < tt.time_to
33 group by tt.time_from, tt.time_to
34 order by tt.time_from;
TIME_ TIME_ AVG_VAL
----- ----- ----------
12:00 12:10 3,5
12:10 12:20 6
12:20 12:30 1
12:30 12:40 0
12:40 12:50 0
12:50 13:00 0
13:00 13:10 5
13:10 13:20 0
13:20 13:30 0
13:30 0
10 rows selected.
SQL>
|
|
|
|
|
|