> 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
