Re: Week numbers and calculating weekly statistics/diagrams

On 16 February 2010 12:14, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
> Hello,
>
> I have multiplayer card game in Flash/Perl/C and would
> like to add weekly tournaments/player ratings to it.
>
> This means I have to add a table which holds:
> player id, weekly score (which I update after each round)
> and the week number.
>
> Does anybody has an advice how to save the week number?
>
> If I save it as a timestamp then calculating realtime statistics
> (on a player profile click) will probably be CPU-intensive,
> because I have to calculate the week numbers each time.
>
> If I save it as string "2010/52" then it's difficult to show
> statistics for a period of time (like for the last 12 months)
> if there is a new year inbetween.
>
> Maybe there is a better way?
>
> I'm using postgresql-server-8.3.6 and OpenBSD 4.5
>
> Regards
> Alex
>

If you're worried about CPU overhead, couldn't you just index using an
expression?

Such as:

CREATE INDEX this_index ON results (extract(week from game_date));

Or even a multicolumn index like:

CREATE INDEX this_index ON results (extract(week from game_date), player_id);

Then:

SELECT extract(week from game_date), player_id, sum(score)
FROM results
GROUP BY extract(week from game_date), player_id
ORDER BY extract(week from game_date), player_id

Thom