Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: query: "count" each minute between start/end
"Nikolas Tautenhahn" <virtual_at_gmx.de> a écrit dans le message de news: f8dm6f$qpk$01$1_at_news.t-online.com...
| Hi there...
|
| I have a table with access statistics with columns representing the
| [date] when a user first connected and the duration[seconds] they stayed.
|
| For a graphical report I need to create a view which shows how many
| users were online each minute of the day, i.e. say we have this raw data:
|
| user1: entered today, 9:30:17, stayed for 200 seconds (so he needs to be
| counted for 9:31, 9:32, 9:33 as he left 9:30:37).
| user2: entered today, 9:28:10, stayed for 180 seconds (so he needs to be
| counted for 9:29, 9:30, 9:31) and what I would like to get from this raw
| data is something like:
|
| 9:29, 1
| 9:30, 1
| 9:31, 2
| 9:32, 1
| 9:33, 1
| Let's name the connect date when a user entered "c_date" and the
| duration simply "duration"
| how could I do something? I can calculate the [date] when a user left by
| c_date + (1/86400)*duration and I guess I would need something like
| between, but I can't put my finger on how I could increment the counter
| for all whole minutes between c_date and the end-date...
|
| thanks for all help,
| N.
If I understand you want the number of sessions that: - starts before the beginning of the current minute and - ends during the current minute or after
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
Session altered.
SQL> select * from t;
C_DATE DURATION
------------------- -------- 28/07/2007 09:30:17 200 28/07/2007 09:28:10 180
2 rows selected.
SQL> def start='28/07/2007 09:26' SQL> def duration=10 SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI';
Session altered.
SQL> with cal as
2 ( select to_date('&start','DD/MM/YYYY HH24:MI')+(rownum-1)/1440 tim
3 from dual 4 connect by level <= &duration )5 select cal.tim, sum(decode(t.c_date,null,0,1)) "nbIn" 6 from cal, t
TIM nbIn
---------------- ----- 28/07/2007 09:26 0 28/07/2007 09:27 0 28/07/2007 09:28 0 28/07/2007 09:29 1 28/07/2007 09:30 1 28/07/2007 09:31 2 28/07/2007 09:32 1 28/07/2007 09:33 1 28/07/2007 09:34 0 28/07/2007 09:35 0
10 rows selected.
Regards
Michel Cadot
Received on Sat Jul 28 2007 - 03:38:25 CDT