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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 28 Jul 2007 19:15:11 +0200
Message-ID: <46ab799e$0$1916$426a34cc@news.free.fr>

"Nikolas Tautenhahn" <virtual_at_gmx.de> a écrit dans le message de news: f8fpu1$mjf$02$1_at_news.t-online.com...
| Hi,
|
| Michel Cadot wrote:
| > "Nikolas Tautenhahn" <virtual_at_gmx.de> a écrit dans le message de news: f8fhlj$4il$03$1_at_news.t-online.com...
| > It depends if you want each and every minute or only the minutes when
| > there is someone.
|
| It would be sufficient to get only the minutes when there is someone. I
| got this by removing an outer join:
|
| with cal as
| ( select sysdate - (1/12) +(rownum-1)/1440 tim
| from dual
| connect by level <= 120 )
| select cal.tim, sum(decode(testlog.testlog_c_date,null,0,1)) "nbIn"
| from cal, testlog
| where testlog.testlog_c_date >= sysdate - (1/12) and
| testlog.testlog_c_date < cal.tim and
| (testlog.testlog_c_date(+)+testlog.testlog_x_duration(+)/86400) >= cal.tim
| group by cal.tim
| order by cal.tim
|
| this was done with real data and was fast for the example above (going
| 2hrs into history took 1.6 seconds, 6hrs took 20 seconds and going back
| 18hrs took 180s... well and this calculation would be needed for the
| last 21days with approx. 5000-8000 datasets per day.
|
| Unfortunately I don't have a sqlplus at the moment but only the
| sqldeveloper. Its output of explain plan was exported to html and the
| source code uploaded to:
|
| http://np.cryosphere.de/p:f02423f7c5f0ce859a727395673715c0/plain
|
| All other exports were less readable, sorry for the mess...
|
| Regards,
| N.

If you only want minutes where there is someone, the following one is faster:

SQL> with cal as
  2 ( select --+ materialize

  3             to_date('&start','DD/MM/YYYY HH24:MI')+(rownum-1)/1440 tim
  4      from dual
  5      connect by level <= &duration )
  6  select --+ cardinality(cal &duration)
  7         cal.tim, count(t.c_date) nb

  8 from cal cal, t
  9 where t.c_date < cal.tim and t.c_date+duration/86400 >= cal.tim  10 group by cal.tim
 11 order by cal.tim
 12 /

TIM                 NB
---------------- -----
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

5 rows selected.

Regards
Michel Cadot Received on Sat Jul 28 2007 - 12:15:11 CDT