Re: Generating custom statistics rows puzzler...left join

> Method 2 (overkill) - Create a table called 'time' with a row for each
> hour on the clock.  When creating the rows, do a left join on this table
> so that hours that don't have any plays would still be represented. I'm
> leery about doing left joins like this, as they are tricky, and can be
> killers on the database.
>
> Is there a slick use of 'between', 'interval' or some other SQL-ism (or
> PostgreSQL-ism) that will give me in effect a left join on all the hours
> from start::timestamp to end::timestamp?

Hrm...okay, I have what looks like a potential solution using a table
containing every hourly timestamp from 2001 through now into 2003 left
joined to the initial hourly query above...but after this, I'm know I'm
gonna have nightmares tonight.

Can anyone make this more elegant?

(NOTE: the comments were entered by hand in this email...they might not
work in the database...)

select *
from
  (--
   -- Get all hours and the promotion id
   -- from the beginning of the promotion to now.
   select timeval, promotion_id as leftpromo
   from timetab, promotions
   where
     timetab.timeval >= promotions.startdate
     and timetab.timeval >= '2002-04-01'::timestamp
     and timetab.timeval <= promotions.enddate
     and timetab.timeval <= CURRENT_TIMESTAMP
     and promotions.startdate <= CURRENT_TIMESTAMP
     and promotions.enddate >= CURRENT_TIMESTAMP
  ) as times
left outer join
  (--
   -- Join those with the hourly counts
   select u.promotion_id,date_trunc('hour',u.wheninserted) as hour
,count(date_trunc('hour',u.wheninserted))
   from player u, promotions p
   where u.promotion_id=p.promotion_id
     and p.startdate <= CURRENT_TIMESTAMP
     and p.enddate >= CURRENT_TIMESTAMP
   group by u.promotion_id,date_trunc('hour',u.wheninserted)) as result
on (times.timeval = result.hour and times.leftpromo=result.promotion_id)
order by leftpromo, timeval;

incidentally, this returns ~40,000 rows...*way* too many to store for
IRO 15 promotions.

I guess I'm going to have to try either the smarts in wrong place or
brute force method to conserve on resource usage.

Ah well.

-Ken

--
Ken Corey  CTO  http://www.atomic-interactive.com  07720 440 731