Re: query: "count" each minute between start/end

"Nikolas Tautenhahn" <virtual_at_gmx.de> a écrit dans le message de news: f8fhlj$4il$03$1_at_news.t-online.com...
| 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 Received on Sat Jul 28 2007 - 10:13:58 CDT