Re: query: "count" each minute between start/end
- From: "Michel Cadot" <micadot{at}altern{dot}org>
- Date: Sat, 28 Jul 2007 17:13:58 +0200
"Nikolas Tautenhahn" <virtual@xxxxxx> a écrit dans le message de news: f8fhlj$4il$03$1@xxxxxxxxxxxxxxxxxxxx
| Hi,
|
| Michel Cadot wrote:
| >
| > 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
| > 7 where t.c_date (+) < cal.tim and (t.c_date(+)+duration(+)/86400) >= cal.tim
| > 8 group by cal.tim
| > 9 order by cal.tim
| > 10 /
|
| That was what I had in mind... it is quite slow on real data, but the
| trick with "connect by level" was a great thing to learn - thanks a lot
| for that!
|
| Now if I could just make it a bit more efficient...
| Maybe it would be better to cycle over the data and "create" the minutes
| which are between start and end instead of creating the minutes first
| and searching for matching data afterwards (maybe grouping after the
| minutes)...
|
| Ah well it is not easy, but at least there is one working example :)
|
| Regards,
| N.
It depends if you want each and every minute or only the minutes when
there is someone.
If the former case, you have to first generate the calendar and outer join.
You said it is slow, post your execution plan.
Regards
Michel Cadot
.
- Follow-Ups:
- Re: query: "count" each minute between start/end
- From: Nikolas Tautenhahn
- Re: query: "count" each minute between start/end
- References:
- query: "count" each minute between start/end
- From: Nikolas Tautenhahn
- Re: query: "count" each minute between start/end
- From: Michel Cadot
- Re: query: "count" each minute between start/end
- From: Nikolas Tautenhahn
- query: "count" each minute between start/end
- Prev by Date: Re: query: "count" each minute between start/end
- Next by Date: Re: query: "count" each minute between start/end
- Previous by thread: Re: query: "count" each minute between start/end
- Next by thread: Re: query: "count" each minute between start/end
- Index(es):
Relevant Pages
|
