Re: SQL Table Summary - Best Method?

I would want to summarize the data for a given hour. So I would like to report for example the total number of events from 8-9, 9-10, 10-11 and so on..... Sounds like what you're saying the best thing to do is go ahead and create the summary table and populate the table on an hourly basis.

"Business" <ihatespam_at_nomail.com> wrote in message news:91hjik$1c6o$1_at_msunews.cl.msu.edu...
> If your data is a time series sum, creating a running sum table is best.
> You should never stored derived data that can change. However, as long
 as
> your data between two points in time is static, it is better to store the
> sums than to recalculate.
>
> Your particular query poses a problem. Do you want to summarize the
 events
> that occur within an hour? Events that are running during a given hour?
 or
> Events that end within a given hour? Each of these scenarios has its own
> problems.
>
> If you want the events that end within a particular hour the query is
> easiest to build using a group by on a trunc function.
>
> SELECT count(EVENT), event, trunc(end_time/3600) as hour_marker
> FROM event_data
> group by event, trunc(end_time/3600);
>
> You can greatly increase the performance of this query by creating a
> function based index (only available in 8i) such as the following:
>
> create index event_data_f on event_data(trunc(end_time/3600));
>
> Chris Weiss
> Chief Scientist for Database Engineering
> PureCarbon, Inc.
>
>
>
>
> "Woody" <woodburn_at_san.rr.com> wrote in message
> news:MyS_5.17322$c32.3083076_at_typhoon.san.rr.com...
> > I would like to retreive a summary result from the data stored in a
 table,
> > but I'm unsure what is the best method. The data looks like the
 following:
> >
> > dummy numbers are used in the start and end time just to show the data.
> >
> > Event Start_Time(epoch_seconds) End_Time(epoch_seconds)
> > 1 99978977 99879849
> > 1 99978977 99879849
> > 1 99978977 99879849
> > 1 99978977 99879849
> > 1 99978977 99879849
> >
> > I want to sum the # of Events hourly shown over a period of several
 days.
> > What would be the best way about doing this given that the data will be
> > eventually dumped to a web page. Should I use a stored procedure and if
 so
> > does anyone have an example? Generate a summary table, however, I've
 always
> > been told don't store calculated data or should I just generate multiple
> > queries dynamically to fullfil the request of the user.
> >
> >
>
>
Received on Mon Dec 18 2000 - 10:05:54 CST