RE: SQL: counting and dates
I would probably build the weekly and monthly aggregates off
of the daily aggregate. I think this is a good idea, but I dont know
how sparse your data ends up being for your 1 day aggregates..
Beyond that I can't think of any other suggestions.
Will
>===== Original Message From Thomas Parsli <thomas.parsli@startsiden.no> =====
>On our web-pages we count users clicks on links (we're a portal;)
>
>create table link
> (
> link_id serial not null ,
> ...
> )>
>create table jump
> (
> link_id integer not null ,
> date datetime year to minute
> default current year to minute not null
> )>
>alter table jump add constraint (foreign key (link_id)
> references link );>
>Now I'd like to count the clicks/jumps for each link the last
>day, 7 days and 30 days. We get ~150 000 jumps/day so I'll insert
>those aggreagtes in a table for dynamic use.
>
>Counting these separatly is pretty easy
>
>SELECT link_id, COUNT(link_id) jumps_day
>FROM jump
>WHERE (CURRENT - date) < INTERVAL (1) DAY TO DAY
>GROUP BY link_id>
>-but how do I count for 1 day, 7 days and 30 days in the same
>statement without going through the same table (jump) three times?
>(and should I?;)
>
>Thomas
------------------------------------------------------------
This e-mail has been sent to you courtesy of OperaMail, as a free service from
Opera Software, makers of the award-winning Web Browser, Opera. Visit us at
http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail
account is waiting at: http://www.operamail.com/
------------------------------------------------------------