Summarizing data over time - by time interval
Peter Jones, June 12, 2002 - 10:52 am UTC
Thanks again this is very helpful. One question though.
There are occasions when no data is taken for while. So if ther 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
Thanks again for you help
June 12, 2002 - 6:27 pm UTC
just need to outer join to a set of times you want to see. Use of inline view makes this pretty easy:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select trunc(the_dt,'hh24') + (trunc(to_char(the_dt,'mi')/:n)*:n)/24/60, avg(value)
2 from (select (trunc(dt,'hh24') + (trunc(to_char(dt,'mi')/:n)*:n)/24/60) dt, value from t ) t,
3 ( select to_date(:start_dt,'hh24:mi')+(rownum-1)*:n/24/60 the_dt
4 from all_objects
5 where rownum <= (to_date(:end_dt,'hh24:mi')-to_date(:start_dt,'hh24:mi'))*24*60/:n + 2)t2
6 where t.dt (+) = t2.the_dt
7 group by trunc(the_dt,'hh24') + (trunc(to_char(the_dt,'mi')/:n)*:n)/24/60;
TRUNC AVG(VALUE)
----- ----------
12:00 1.66666667
12:05 1.8
12:10 1.55
12:15
12:20
12:25
12:30
7 rows selected.
Further Question
A reader, June 12, 2002 - 3:02 pm UTC
You need a driving set of data which contains all the time intervals you require, you can then outer join it to your result set. If I have done anything like this in the past it has been implemented via a calendar object containing all the required time intervals... but Tom will probably have a cuter answer !!
Can we use INTERVAL?
Andy Tsang, June 13, 2002 - 5:49 am UTC
I see in 9iR2, there is an INTERVAL expression. Can we use it for this purpose?
June 13, 2002 - 8:37 am UTC
It is there in 9iR1 actually and yes you could use the interval type for some of these calculations in 9i
Summarizing data over time - by time interval
Rich, June 13, 2002 - 5:17 pm UTC
Tom,
Thanks for this tip, it came in handy. I was able to modify it a little and get the pivot table type report I needed using your code, decode and the rollup grouping functions.
TIME Accepts Declined Canceled Totals
----------------------- ---------- ---------- ---------- ----------
06/12/2002 02:00:00 am 1 0 0 1
06/12/2002 03:00:00 am 1 0 0 1
06/12/2002 08:00:00 am 3 0 0 3
06/12/2002 09:00:00 am 3 0 0 3
06/12/2002 10:00:00 am 6 0 0 6
06/12/2002 11:00:00 am 6 1 3 10
06/12/2002 12:00:00 pm 2 1 0 3
06/12/2002 01:00:00 pm 2 2 0 4
06/12/2002 02:00:00 pm 2 0 0 2
06/12/2002 03:00:00 pm 4 0 1 5
06/12/2002 04:00:00 pm 6 0 1 7
All Times 36 4 5 45
12 rows selected.
select decode(grouping(trunc(o.offer_status_date,'hh24') + (trunc(to_char(o.offer_status_date,'mi')/60)*60)/24/60), 1, 'All Times', trunc(o.offer_status_date,'hh24') + (trunc(to_char(o.offer_status_date,'mi')/60)*60)/24/60) TIME,
sum(decode(o.offer_status_code, 'A', 1, 0)) "Accepts",
sum(decode(o.offer_status_code, 'D', 1, 0)) "Declined",
sum(decode(o.offer_status_code, 'C', 1, 0)) "Canceled",
count(o.offer_status_code) "Totals"
from offer o
where o.offer_status_date between to_date( '12-jun-2002 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )
and to_date( '13-jun-2002 23:59:59', 'dd-mon-yyyy hh24:mi:ss')
and o.offer_status_code in ('A', 'D', 'C')
group by rollup(trunc(o.offer_status_date,'hh24') + (trunc(to_char(o.offer_status_date,'mi')/60)*60)/24/60)
Summarizing data over time - by time interval
Warwick, July 02, 2004 - 3:20 am UTC
Tom,
I am trying to implement this solution into my own query which will show how many records have been created on each day per half hour interval. Each day being the day the user is viewing the data ie. sysdate
What I require is something very similiar. For example,
TimeStamp Records
--------- ------
06:00 3
06:30 1
07:00 0
07:30 5
And so on til 8pm.
I have used this query:
select trunc(the_dt,'hh24') +
(trunc(to_char(the_dt,'mi')/30)*30)/24/60, count(R_ID), t.dt, t2.the_dt
from
(
select (trunc(CREATEDATE,'hh24') + (trunc(to_char(CREATEDATE,'mi')/30)*30)/24/60)
dt, RD_ID from RECORD_TABLE
where
to_char(createdate, 'dd/mm/yyyy') = to_char(sysdate, 'dd/mm/yyyy')
) t,
(
select to_date('06:00','HH24:mi')+(rownum-1)*30/24/60 the_dt
from all_objects
where rownum <=
(to_date('19:59','HH24:mi')-to_date('06:00','HH24:mi'))*24*60/30 + 2
)t2
where t.dt (+) = t2.the_dt
group by trunc(the_dt,'hh24') +
(trunc(to_char(the_dt,'mi')/30)*30)/24/60,
t.dt, t2.the_dt;
Each time I run the query it displays the following:
1/07/2004 6:00:00 AM,0,,1/07/2004 6:00:00 AM
1/07/2004 6:30:00 AM,0,,1/07/2004 6:30:00 AM
1/07/2004 7:00:00 AM,0,,1/07/2004 7:00:00 AM
1/07/2004 7:30:00 AM,0,,1/07/2004 7:30:00 AM
1/07/2004 8:00:00 AM,0,,1/07/2004 8:00:00 AM
1/07/2004 8:30:00 AM,0,,1/07/2004 8:30:00 AM
1/07/2004 9:00:00 AM,0,,1/07/2004 9:00:00 AM
1/07/2004 9:30:00 AM,0,,1/07/2004 9:30:00 AM
1/07/2004 10:00:00 AM,0,,1/07/2004 10:00:00 AM
1/07/2004 10:30:00 AM,0,,1/07/2004 10:30:00 AM
1/07/2004 11:00:00 AM,0,,1/07/2004 11:00:00 AM
1/07/2004 11:30:00 AM,0,,1/07/2004 11:30:00 AM
1/07/2004 12:00:00 PM,0,,1/07/2004 12:00:00 PM
1/07/2004 12:30:00 PM,0,,1/07/2004 12:30:00 PM
1/07/2004 1:00:00 PM,0,,1/07/2004 1:00:00 PM
1/07/2004 1:30:00 PM,0,,1/07/2004 1:30:00 PM
1/07/2004 2:00:00 PM,0,,1/07/2004 2:00:00 PM
1/07/2004 2:30:00 PM,0,,1/07/2004 2:30:00 PM
1/07/2004 3:00:00 PM,0,,1/07/2004 3:00:00 PM
1/07/2004 3:30:00 PM,0,,1/07/2004 3:30:00 PM
1/07/2004 4:00:00 PM,0,,1/07/2004 4:00:00 PM
1/07/2004 4:30:00 PM,0,,1/07/2004 4:30:00 PM
1/07/2004 5:00:00 PM,0,,1/07/2004 5:00:00 PM
1/07/2004 5:30:00 PM,0,,1/07/2004 5:30:00 PM
1/07/2004 6:00:00 PM,0,,1/07/2004 6:00:00 PM
1/07/2004 6:30:00 PM,0,,1/07/2004 6:30:00 PM
1/07/2004 7:00:00 PM,0,,1/07/2004 7:00:00 PM
1/07/2004 7:30:00 PM,0,,1/07/2004 7:30:00 PM
1/07/2004 8:00:00 PM,0,,1/07/2004 8:00:00 PM
No results appear (although there are records for 02/07/2004) because I believe that the dates are being matched. For example 01/07/2004 - 02/07/2004. But on the other hand, the dates are formatted to hh24 and shouldn't matter?
Could you please help as I am getting very confused as to what is going on.
Much appreciated
July 02, 2004 - 9:49 am UTC
You need to put your t2 and t1 dates into half hour formats.
here t1 is the set of times you want.
t2 represents YOUR data:
ops$tkyte@ORA10G> create table t2( dt date );
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> insert into t2
2 select trunc(sysdate)+dbms_random.value(0,1) from all_users;
25 rows created.
ops$tkyte@ORA10G> commit;
Commit complete.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select count(*) from t2
2 where to_char(dt,'hh24') between 6 and 20;
COUNT(*)
----------
14
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select t1.dt, t1.dt+1/24/60*30, count(t2.dt)
2 from ( select trunc(sysdate)+1/24*6+(rownum-1)*1/24/60*30 dt
3 from all_objects
4 where rownum <= 30 ) t1,
5 (select trunc(dt,'hh') + (1/24/60) * 30 * trunc(((to_char(dt,'mi'))+0.1)/30) dt
6 from t2 ) t2
7 where t1.dt = t2.dt(+)
8 group by rollup( t1.dt )
9 /
DT T1.DT+1/24/60*30 COUNT(T2.DT)
-------------------- -------------------- ------------
02-jul-2004 06:00:00 02-jul-2004 06:30:00 1
02-jul-2004 06:30:00 02-jul-2004 07:00:00 1
02-jul-2004 07:00:00 02-jul-2004 07:30:00 0
02-jul-2004 07:30:00 02-jul-2004 08:00:00 0
02-jul-2004 08:00:00 02-jul-2004 08:30:00 0
02-jul-2004 08:30:00 02-jul-2004 09:00:00 0
02-jul-2004 09:00:00 02-jul-2004 09:30:00 0
02-jul-2004 09:30:00 02-jul-2004 10:00:00 0
02-jul-2004 10:00:00 02-jul-2004 10:30:00 1
02-jul-2004 10:30:00 02-jul-2004 11:00:00 2
02-jul-2004 11:00:00 02-jul-2004 11:30:00 0
02-jul-2004 11:30:00 02-jul-2004 12:00:00 3
02-jul-2004 12:00:00 02-jul-2004 12:30:00 0
02-jul-2004 12:30:00 02-jul-2004 13:00:00 0
02-jul-2004 13:00:00 02-jul-2004 13:30:00 2
02-jul-2004 13:30:00 02-jul-2004 14:00:00 0
02-jul-2004 14:00:00 02-jul-2004 14:30:00 1
02-jul-2004 14:30:00 02-jul-2004 15:00:00 0
02-jul-2004 15:00:00 02-jul-2004 15:30:00 2
02-jul-2004 15:30:00 02-jul-2004 16:00:00 0
02-jul-2004 16:00:00 02-jul-2004 16:30:00 0
02-jul-2004 16:30:00 02-jul-2004 17:00:00 0
02-jul-2004 17:00:00 02-jul-2004 17:30:00 0
02-jul-2004 17:30:00 02-jul-2004 18:00:00 0
02-jul-2004 18:00:00 02-jul-2004 18:30:00 0
02-jul-2004 18:30:00 02-jul-2004 19:00:00 0
02-jul-2004 19:00:00 02-jul-2004 19:30:00 0
02-jul-2004 19:30:00 02-jul-2004 20:00:00 1
02-jul-2004 20:00:00 02-jul-2004 20:30:00 0
02-jul-2004 20:30:00 02-jul-2004 21:00:00 0
14
31 rows selected.
your query does not yield the same result
A reader, December 20, 2004 - 12:30 pm UTC
ops$tkyte@ORA815.US.ORACLE.COM> select trunc(dt,'hh24') +
(trunc(to_char(dt,'mi')/:n)*:n)/24/60, avg(value)
2 from t
3 group by trunc(dt,'hh24') + (trunc(to_char(dt,'mi')/:n)*:n)/24/60;
TRUNC AVG(VALUE)
----- ----------
12:00 1.66666667
12:05 1.8
12:10 1.55
it produces the date part also,
TRUNC AVG(VALUE)
----- ----------
2/27/04 12:00 1.66666667
2/27/04 12:05 1.8
2/27/04 12:10 1.55
...
3/01/04 12:00 1.8
3/02/04 12:05 1.8
3/03/04 12:10 1.55
---
I just need
TRUNC AVG(VALUE)
------ ----------
12:00 1.72
12:05 1.80
12:10 1.55
...
December 20, 2004 - 2:02 pm UTC
where did you make up 1.72 from
not sure who you are OR what data you are working with or whatever.
ok it is an average
A reader, December 20, 2004 - 6:02 pm UTC
the 10.2 is an average of feb and march avg.
I do not want to consider the date part of the date
I just want to consider the Time part of the date
December 20, 2004 - 6:44 pm UTC
ok, after I normalize the date down to the interval, you just add:
to_char( ...., 'hh24:mi' )
around it -- removing the date, leaving the time -- and there you go.
it gave me invalid number
A reader, December 21, 2004 - 10:58 am UTC
when I try to do that to_number(to_char(dt,'hh:mi')/10)*10/..
it gives me invalid number error
December 21, 2004 - 1:10 pm UTC
yes, of course -- you cannot divide a date, but why are you trying to divide a date???
A reader, December 21, 2004 - 2:29 pm UTC
create tbale tmln(tml number);
insert into tmln values(0);
insert into tmln values(10);
insert into tmln values(20);
insert into tmln values(30);
insert into tmln values(50);
SELECT TO_CHAR (dt, 'dd-MM-yyyy hh24') day_hour,
(TRUNC (TO_CHAR (dt, 'mi') / 10) * 10) as mins10,
COUNT (DISTINCT id) cntr
FROM history t,tmln b
WHERE 1 = 1
and (TRUNC (TO_CHAR (dt, 'mi') / 10) * 10) (+) = b.TML
GROUP BY TO_CHAR (dt, 'dd-MM-yyyy hh24'),
TRUNC (TO_CHAR (dt, 'mi') / 10) * 10)
how can I outer join this ?
December 21, 2004 - 3:10 pm UTC
select day_hour, mins10, count(distinct id) cntr
from (query against history),
tmln b
where b.tml = mins10(+)
I did that but
A reader, December 21, 2004 - 3:35 pm UTC
thank, tom.
I did that, but it does not return all 6 rows, from tmln table for every hour as there is no data in history table for every 10 mins. that's why I used outer join (the way you suggested), but it doesnot return all 6 fows for every hour.
December 21, 2004 - 3:38 pm UTC
you need a table with every hour and every 10 minute interval then.
Thanks
A reader, December 21, 2004 - 3:40 pm UTC
ahh..,
I was tring with interval only, as I have atleast one row/hour, can't garantee 1 row/10mins.
so my tmln should have hour and mins. scale.
thanks,
is there any other way
A reader, December 21, 2004 - 4:19 pm UTC
is there any other way may be by using analytic functions ? to do this ?
December 21, 2004 - 7:14 pm UTC
the original question could not benefit from analytics and the more recent talk requires an outer join (to fill in the gaps)
Exactly what I was looking for!
Martijn, April 26, 2006 - 9:18 am UTC
Number of periods, not duration of periods
Michal, May 29, 2006 - 4:40 am UTC
How about the other way round: instead of duration of a single period, we get the number of periods. A user specifies start, end and number of periods and we have to divide the (end-start)/n and return n rows with average for the given period. For exemple:
create table mydata (dt date, val number);
insert into mydata values (to_date('2006-05-29 10:10:00', 'YYYY-MM-DD HH:MI:SS'), 1);
insert into mydata values (to_date('2006-05-29 10:10:10', 'YYYY-MM-DD HH:MI:SS'), 2);
insert into mydata values (to_date('2006-05-29 10:10:20', 'YYYY-MM-DD HH:MI:SS'), 3);
insert into mydata values (to_date('2006-05-29 10:10:30', 'YYYY-MM-DD HH:MI:SS'), 4);
insert into mydata values (to_date('2006-05-29 10:15:00', 'YYYY-MM-DD HH:MI:SS'), 5);
insert into mydata values (to_date('2006-05-29 10:20:00', 'YYYY-MM-DD HH:MI:SS'), 6);
insert into mydata values (to_date('2006-05-29 10:20:10', 'YYYY-MM-DD HH:MI:SS'), 7);
commit;
When start='2006-05-29 10:10:00', end='2006-05-29 10:20:10', number of periods=2, we would get:
period | average
'2006-05-29 10:10:00' - '2006-05-29 10:15:05' | 3
'2006-05-29 10:15:05' - '2006-05-29 10:20:10' | 6.5
(we can just name periods as 1,2,3,..)
But for the same start and end with number of periods=3 we would get:
period | average
'2006-05-29 10:10:00' - '2006-05-29 10:13:23' | 2.5
'2006-05-29 10:13:23' - '2006-05-29 10:16:46' | 5
'2006-05-29 10:16:46' - '2006-05-29 10:20:10' | 6.5
One can think of it as a base for a chart - we get both ends and number of bars between that we have to draw. The problem is that the duration of periods can be mesured in seconds, minutes, days or whatever - we have to calculate it based on just the start/end/number_of_periods.
Thanks for help,
Reader, June 30, 2009 - 3:34 pm UTC
create table dt_tst
(dt date, val number);
--9:30:00
insert into dt_tst values (to_date('5/29/2009 9:30:00','mm/dd/yyyy hh24:mi:ss'),100);
insert into dt_tst values (to_date('5/29/2009 9:30:00','mm/dd/yyyy hh24:mi:ss'),200);
insert into dt_tst values (to_date('5/29/2009 9:30:00','mm/dd/yyyy hh24:mi:ss'),300);
insert into dt_tst values (to_date('5/29/2009 9:30:00','mm/dd/yyyy hh24:mi:ss'),400);
--no data for 9:30:01 and 9:30:02
--9:30:03
insert into dt_tst values (to_date('5/29/2009 9:30:03','mm/dd/yyyy hh24:mi:ss'),500);
insert into dt_tst values (to_date('5/29/2009 9:30:03','mm/dd/yyyy hh24:mi:ss'),200);
insert into dt_tst values (to_date('5/29/2009 9:30:03','mm/dd/yyyy hh24:mi:ss'),300);
insert into dt_tst values (to_date('5/29/2009 9:30:03','mm/dd/yyyy hh24:mi:ss'),400);
select * from dt_tst
data exists for 9:30:00; no data for 9:30:01 and 9:30:02;data exists for 9:30:03
I need to group the data by assigning group numbers as shown below.
DT VAL group_no
------------------- ---------- --------
05/29/2009 09:30:00 100 1
05/29/2009 09:30:00 200 1
05/29/2009 09:30:00 300 1
05/29/2009 09:30:00 400 1
05/29/2009 09:30:00 500 1
05/29/2009 09:30:03 500 2
05/29/2009 09:30:03 200 2
05/29/2009 09:30:03 300 2
05/29/2009 09:30:03 400 2
Basically for every second I need to use a group_no starting with 0. Please tell how to get this result.
July 06, 2009 - 6:53 pm UTC
ops$tkyte%ORA10GR2> select dt, val, dense_rank() over (order by dt) rnk
2 from dt_tst;
DT VAL RNK
--------- ---------- ----------
29-MAY-09 100 1
29-MAY-09 200 1
29-MAY-09 300 1
29-MAY-09 400 1
29-MAY-09 500 2
29-MAY-09 200 2
29-MAY-09 300 2
29-MAY-09 400 2
8 rows selected.
usefull
A reader, January 03, 2011 - 12:30 am UTC
hi i have used the same logic , i got expected output,
thanks
Very helpful! What about hours?
Susan, November 28, 2012 - 2:13 pm UTC
I have been looking for query to average data over standard periods, such as 5 seconds, 15 seconds, ... 5 minutes, 10 minutes, 30 minutes; and this is doing the trick very nicely, thank you!!
The data looks something like this:
sensorid | sampletime | correctedvalue
---------------------------------------------------
2426 | 20120101 000001.049 | 1.234
2426 | 20120101 000010.039 | 2.340
2426 | 20120101 000021.058 | 2.143
286 | 20120101 000030.049 | 2.569
There are many billions of rows.
The sampling frequency is anywhere from 4Hz to .1 Hz, sometimes slower.
For 5 second averaging, I just changed it to the following:
select
trunc(sampletime,'hh24') +
(trunc(to_char(sampletime,'ss')/5)*5)/24/60/60 as tspan,
avg(correctedvalue)
from scalarsample
where sensorid = 2426
group by trunc(sampletime,'hh24') +
(trunc(to_char(sampletime,'ss')/5)*5)/24/60/60
order by tspanI also need to average the data over 6-hour and 12-hour periods; at first I was stumped and when I started this review I was going to ask the question as to how to do that, but I stopped and thought about it a bit more, and I think I got it:
select
trunc(sampletime) +
(trunc(to_char(sampletime,'hh24')/6)*6)/24 as tspan,
avg(correctedvalue)
from scalarsample
where sensorid = 2426
group by trunc(sampletime,'hh24') +
(trunc(to_char(sampletime,'hh24')/6)*6)/24
order by tspanThis appears to be doing what I need.
Thanks again!
November 30, 2012 - 3:19 am UTC
but I stopped and thought about it a bit more
I love that :)
perfect..
thanks!
on the other hand
Susan, November 28, 2012 - 3:53 pm UTC
On further inspection, it turns out, the code I tweaked above didn't work as expected. It would skip data:
tspan | average
---------------------------------------
30-MAY-12 19:00:50 | 106.538
30-MAY-12 19:00:55 | 106.541
30-MAY-12 20:00:00 | 106.324
30-MAY-12 20:00:05 | 106.322
It jumped the minutes.
For averaging spans shorter than 60s, I added a line:
select
trunc(sampletime,'hh24') +
(trunc(to_char(sampletime,'mi')))/24/60 +
(trunc(to_char(sampletime,'ss')/5)*5)/24/60/60 as tspan,
avg(correctedvalue)
from scalarsample
group by sensorid,
trunc(sampletime,'hh24') +
(trunc(to_char(sampletime,'mi')))/24/60 +
(trunc(to_char(sampletime,'ss')/5)*5)/24/60/60
order by tspan
trying get same output
A reader, April 11, 2013 - 1:05 am UTC
I am trying to get same output but I am not, did mis anything. Here is what I am trying to
created a table t as create table t(dt date,value number) ;
insert into t select end_time,value from v$sysmetric_history
where metric_name='CPU Usage Per Sec'
and group_id=2;
commit;
1 select trunc(dt,'hh24') +
2 (trunc(to_char(dt,'mi')/:n)*:n)/24/60, avg(value)
3 from t
4* group by trunc(dt,'hh24') + (trunc(to_char(dt,'mi')/:n)*:n)/24/60
SQL> /
TRUNC(DT, AVG(VALUE)
--------- ----------
10-APR-13 1.60539927
10-APR-13 .648244207
10-APR-13 1.39715399
10-APR-13 .758010165
10-APR-13 96.1036004
10-APR-13 100.199214
10-APR-13 100.859189
I don't get same output as yours for date. like hh24:mi, did I mis anything or do I need set session date format
April 22, 2013 - 2:47 pm UTC
yes, use to_char( ..., 'format mask' ) on the select - or set your nls date format
required results are different
R. Desai, April 07, 2015 - 9:43 pm UTC
I have a view with the start_time and end_time of tasks (of different types)of which I want to select only two kinds of tasks and for these two taks, I want to count number of people doing each task by 15 minute interval. After reading this article, I wrote a query as below but I don't think it is giving me correct counts:
select t1.dt, t1.dt+(1/24/60)*15, count(t2.dt)
from ( select trunc(TO_DATE('03/01/2015','MM/DD/YYYY'))+(rownum-1)*1/24/60*15 dt
from all_objects
where rownum <= to_number(To_date('04/01/2015','MM/DD/YYYY') - to_date('03/01/2015','MM/DD/YYYY'))*96) t1,
(select area_id, trunc(start_time,'hh') + (1/24/60) * 15 * trunc(((to_char(start_time,'mi'))+0.1)/15) dt
from TASK_HISTORY o
where
o.start_time between to_date( '01-mar-2015 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )
and to_date( '01-apr-2015 00:00:00', 'dd-mon-yyyy hh24:mi:ss')
and o.task_type in ('Available')
) t2
where t1.dt = t2.dt(+)
group by rollup( t1.dt)
order by t1.dt---
I think it is counting only tasks which starts within 15 min window but it does not count a person already doing the task the entire 15 min (i.e. if the person is on a specific task before 15 min window starts and remains on task through the end of 15 min, it is not counted.) Appreciate direction.
Tried the previous solutions, get error message
Emily, July 11, 2017 - 4:31 pm UTC
I created a table with timestamp columns and used sql loader to populate it.
CREATE TABLE TRAFFICDATA (
id int,
speed float,
traveltime int,
status int,
dateValidFrom timestamp,
linkId int,
owner varchar(25),
transcomID int,
borough varchar(25),
linkName varchar(25),
dateValidTo timestamp);
I ran
SELECT TRUNC(dateValidFrom,'HH24') + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60 AS DVF, AVG(speed) AS maxspeed FROM trafficdata
GROUP BY TRUNC(dateValidFrom,'HH24') + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60;
It returned
03-DEC-15| 41.02
03-DEC-15| 42.72
03-DEC-15| 42.35
wanted time only. I ran
SELECT TRUNC(to_char(dateValidFrom,'HH24')) + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60 AS DVF,
AVG(speed) AS maxspeed
FROM trafficdata
GROUP BY TRUNC(dateValidFrom,'HH24') + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60;
it returned
ERROR at line 1:
ORA-00979: not a GROUP BY expression
July 12, 2017 - 1:29 pm UTC
The expressions in the group by and select need to match. You have
SELECT TRUNC(to_char(dateValidFrom,'HH24')) ...
GROUP BY TRUNC(dateValidFrom,'HH24') ...
change one to match the other...
Got rid of error message but...
Emily, July 12, 2017 - 8:46 pm UTC
Thanks for your previous help.
When I run this:
SELECT TRUNC(to_char(dateValidFrom,'HH24')) + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60 AS DVF,
AVG(speed) AS maxspeed
FROM trafficdata
GROUP BY TRUNC(to_char(dateValidFrom,'HH24')) + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60;
I get:
.02| 44.00
1.00| 46.36
1.01| 46.18
2.03| 48.15
7.02| 32.85
10.01| 38.43
16.01| 33.68
This:
SELECT TRUNC(dateValidFrom,'HH24') + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60 AS DVF,
AVG(speed) AS maxspeed
FROM trafficdata
GROUP BY TRUNC(dateValidFrom,'HH24') + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60;
returns:
03-DEC-15| 23.90
03-DEC-05| 45.82
03-DEC-15| 26.32
03-DEC-15| 41.63
03-DEC-05| 50.15
03-DEC-15| 37.60
03-DEC-15| 36.46
I need:
2015-12-03 22.57.00.0 | 43.37 |
2015-12-03 23.07.00.0 | 42.81 |
2015-12-03 23.17.00.0 | 41.13 |
2015-12-03 23.27.00.0 | 40.56 |
2015-12-03 23.37.00.0 | 42.77 |
2015-12-03 23.47.00.0 | 42.62 |
2015-12-03 23.57.00.0 | 43.31 |
July 13, 2017 - 10:01 am UTC
Remove to_char from inside the trunc at:
TRUNC(dateValidFrom,'HH24') + (trunc(to_char(dateValidFrom,'mi')/10)*10)/24/60
Then to_char the whole expression, i.e.
to_char(TRUNC(dateValidFrom,'HH24') + ((trunc(dateValidFrom,'mi')/10)*10)/24/60)
Forgot info about my data...
Emily, July 12, 2017 - 8:51 pm UTC
Here is the control file I used to load the data with sql loader:
LOAD DATA
infile 'traffData.csv'
badfile 'nyBad.txt'
discardfile 'nyDiscard.txt'
INSERT
INTO TABLE TRAFFICDATA
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
id integer,
speed DECIMAL EXTERNAL,
traveltime integer,
status integer,
dateValidFrom timestamp "YYYY-MM-DD HH24.MI.SS.FF3",
linkId integer,
owner char(25),
transcomID integer,
borough char(25),
linkName char(25)
)
Here's a sample of my CSV:
2,21.75,67,0,2015-12-3 00.02.07.000,4616325,NYC_DOT_LIC,4616325,Manhattan,11th ave s ganservoort - west st @ spring st
3,19.26,327,0,2015-12-3 00.02.07.000,4616324,NYC_DOT_LIC,4616324,Manhattan,12th ave @ 45th - 11 ave ganservoort st
4,13.05,250,0,2015-12-3 00.02.07.000,4616338,NYC_DOT_LIC,4616338,Manhattan,12th Ave N 40th - 57th St
106,19.26,119,0,2015-12-3 00.02.07.000,4616323,NYC_DOT_LIC,4616323,Manhattan,12th Ave S 57th St - 45th St
107,49.71,217,0,2015-12-3 12.26.11.000,4616279,NYC_DOT_LIC,4616279,Staten Island,278 E BRUNSWICK AVENUE - SIE E SOUTH AVENUE
119,34.18,191,0,2015-12-3 00.01.28.000,4456502,MTA Bridges & Tunnels,4456502,Manhattan,BBT E Manhattan Portal - Toll Plaza
122,18.02,294,0,2015-12-3 00.02.07.000,4616344,NYC_DOT_LIC,4616344,Manhattan,BBT Manhattan Portal inbound - West St N Watts St
123,26.72,54,0,2015-12-2 12.47.07.000,4616345,NYC_DOT_LIC,4616345,Manhattan,BBT Manhattan Portal inbound - West St S Battery Place
124,37.28,175,0,2015-12-3 00.01.28.000,4456501,MTA Bridges & Tunnels,4456501,Manhattan,BBT W Toll Plaza - Manhattan Portal
129,52.20,84,0,2015-12-3 00.02.06.000,4616246,NYC_DOT_LIC,4616246,Bronx,BE N STRATFORD AVENUE - CASTLE HILL AVE
137,57.17,75,0,2015-12-3 00.02.07.000,4616260,NYC_DOT_LIC,4616260,Bronx,BE S CASTLE HILL AVENUE - STRATFORD AVENUE
140,32.93,92,0,2015-12-3 00.01.27.000,4456479,MTA Bridges & Tunnels,4456479,Queens,BE S TBB EXIT RAMP - MANHATTAN LIFT SPAN
141,44.12,163,0,2015-12-3 00.01.27.000,4456478,MTA Bridges & Tunnels,4456478,Queens,BE S TBB EXIT RAMP - QUEENS ANCHORAGE
145,31.69,80,0,2015-12-3 00.02.07.000,4616342,NYC_DOT_LIC,4616342,Manhattan,BKN Bridge Manhattan Side - FDR N Catherine Slip
Now I get another error
Emily, July 13, 2017 - 4:59 pm UTC
This query:
SELECT to_char(TRUNC(dateValidFrom,'HH24') + ((trunc(dateValidFrom,'mi')/10)*10)/24/60) AS DVF,
AVG(speed) AS avgspeed FROM trafficdata
GROUP BY to_char(TRUNC(dateValidFrom,'HH24') + ((trunc(dateValidFrom,'mi')/10)*10)/24/60);
results in error:
"ERROR at line 1:
ORA-00907: missing right parenthesis"
I quadruple checked the matching of my parenthesis; ever left has a corresponding right.
I've tried multiple groupings with parenthesis but always get the above error.