Hi Shawn
Thanks for your response. In your experience do you think I should
still retain the data used to generate the computed totals ? Or just
compute the totals and disregard the data used ?
Regards
Neil
On 8 Oct 2010, at 19:46, "Shawn Green (MySQL)"
<shawn.l.green@stripped> wrote:
> Hi Neil,
>
> On 10/5/2010 5:07 AM, Tompkins Neil wrote:
>> Hi
>>
>> I have a number of tables of which I use to compute totals. For
>> example I
>> have
>>
>> table : players_master
>> rec_id
>> players_name
>> teams_id
>> rating
>>
>> I can easily compute totals for the field rating. However, at the
>> end of a
>> set period within my application, the values in the rating field are
>> changed. As a result my computed totals would then be incorrect.
>>
>> Is the best way to overcome this problem to either compute the
>> total and
>> store as a total value (which wouldn't change in the future), or to
>> store
>> the rating values in a different table altogether and compute when
>> required.
>> If you need table information please let me know and I can send
>> this.
>>
>
> Many databases designed for rapid, time-based reporting do exactly
> as you propose: build a table just to hold the aggregate of a time-
> interval of values.
>
> Here's a rough example.
>
> Let's say that you run a web site and you want to track your traffic
> levels. Every second you may have thousands of hits, every hour
> hundreds of thousands of hits, and by the end of the week you may
> have hundreds of millions of individual data points to report on. To
> compute monthly stats, you are looking at a huge volume (billions)
> of data points unless you start aggregating.
>
> Lets say you build tables like: stats_hour, stats_day, stats_week,
> and stats_month.
>
> Every hour, you would take the last hour's worth of traffic and
> condense those values into the stats_hour table. At the end of the
> day, you take the previous 24 entries from stats_hour and compute a
> stats_day entry. Each level up aggregates the data from the level
> below.
>
> Does that give you an idea about how other people may have solved a
> similar problem?
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN
