SQL: counting and dates
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
Thomas Parsli wrote:
> ...
> 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?;)
You could use the CASE statement to get your information in a single
pass, as follows
SELECT link_id,
CASE
WHEN (CURRENT - date) < INTERVAL (1) DAY TO DAY
THEN "1"
WHEN (CURRENT - date) < INTERVAL (7) DAY TO DAY
THEN "2-7"
ELSE "8-30"
END,
COUNT(*)
FROM jump
WHERE (CURRENT - date) < INTERVAL (30) DAY TO DAY
GROUP BY link_id, 2;
However, note that the CASE statement allows a row to fall into only
one slot, even if it could evaluate to true in multiple WHEN clauses -
Hence the titles "1", "2-7" and "8-30".
Rudy
Rudy Fernandes <rferdy@americasm01.nt.com> writes:
> Thomas Parsli wrote:
>
> > ...
> > 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?;)
>
> You could use the CASE statement to get your information in a single
> pass, as follows
>
> SELECT link_id,
> CASE
> WHEN (CURRENT - date) < INTERVAL (1) DAY TO DAY
> THEN "1"
> WHEN (CURRENT - date) < INTERVAL (7) DAY TO DAY
> THEN "2-7"
> ELSE "8-30"
> END,
> COUNT(*)
> FROM jump
> WHERE (CURRENT - date) < INTERVAL (30) DAY TO DAY
> GROUP BY link_id, 2;>
> However, note that the CASE statement allows a row to fall into only
> one slot, even if it could evaluate to true in multiple WHEN clauses -
> Hence the titles "1", "2-7" and "8-30".
And I'll have to calculate jumps/days with another run.
I know how to do this with three passes, but hoped there was some "better"
way to do it...
I've also noticed that WHERE date > DATE("2000-04-01 00:00")
is way faster (than WHERE (CURRENT - date) < INTERVAL (10) DAY TO DAY)
so I'll probably write some ESQL or Perl to do this in three passes.
Thomas