Re: SQL counting to see trend

[Quoted] On 2 Oct 2001 06:22:16 -0700, Thomas Kyte <tkyte_at_us.oracle.com> wrote:

>In article <3bb99812.8998852_at_news.easynet.co.uk>,
>library.treasures_at_NOSPAMsaqnet.co.uk says...
>>
>>There in an event LOG table, EVENTS are listed beside a TIME column.
>>Each event corresponds to the point of time, accurately to the second,
>>in which it took place.
>>The table contains precisely three weeks of continuous log.
>>
>>I would like to learn how to query with the SQL count(*) statement for
>>the following needs:
>>
>>(1) In order to see a coarse event density trend for an average day, I
>>would like to count the events for each of the distinct twentyfour
>>hours of an average day.
>>
>
>select count(*), trunc(TIME)
> from t
> <where .... if you want to>
> group by trunc(TIME);
>
>>(2) Desirably, to sharpen the resolution and so to refine my analysis,
>>I would like to reduce the hourly intervals to a few minutes, say,
>>sufficiently enough, 5 minutes.
>>
>
>select count(*),
> trunc( time, 'hh' ) + trunc(to_char(created,'mi')/5)*5*(1/24/60)
> from t
>/
>
>(change 5 in the above to 10 to get 10 minute intervals and so on -- change BOTH
>5's
>
>>(3) My next desired trend analysis would be to do the same for each
>>distinct day of the week, i.e. from Monday to Sunday.
>>
>
>how is that different from #1?

What I meant was doing the count specifically for Mondays, Tuesdays, and so on to Sundays, in seven separate counts. Thus, I would see how an average Wednesday is different from an average Sunday, for example.

So, the distinct results for these would expectedly be different from that of #1.

In #1, the mid-week and weekend patterns could not be juxtaposed, for they are averaged out in a single result for an average day.
>
>>(4) Finally, I would like to create another coarse, general daily
>>count, for the average week. This would obviously return just seven
>>rows, one for each day of the week, from Monday to Sunday.
>>
>
>add where trunc(time) > trunc(sysdate-7) to #1 to get just that last week.

Thank you very much indeed, Tom.

I am going to try your suggested queries, though I am not exactly sure yet what do you mean by "t"? Is it some special table, or rather simply the abbreviation for the applicable table? The table I use is called "LOG".

[Quoted] I am new to SQL but I like its promising statistical capabilities.

I have looked at your helpful pages and I would like to visit them regularly in the future.

Regards,
Thomas
>
>>Any kind help would be greatly appreciated.
>>
>>Thomas
>>
>>
>>
>>
>
>--
>Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/
>Expert one on one Oracle, programming techniques and solutions for Oracle.
>http://www.amazon.com/exec/obidos/ASIN/1861004826/
>Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Tue Oct 02 2001 - 21:00:08 CEST