Re: SQL Table Summary - Best Method?

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 Sat Dec 16 2000 - 23:48:42 CST