Re: Many joins: monthly summaries S-L--O--W

Micheal,

> where product_name is products(name), jan03_qty is the quantity of
> sales in January, 2003, half1_qty is quantity of sales from January
> through June, 2003, inv_qty is the latest inventory data we have,
> inv_date is the date of that inventory data, est_inv_qty is the
> estimated current inventory based on inv_qty and sales since inv_date,
> and months_remaining is an estimate of how many months the estimated
> inventory will last at average sales rates (calculated using the
> average monthly sales for the previous months).

Well, I wouldn't recommend your left outer join approach. You're making the
query do far too much work. There are a number of different ways to solve
the "crosstab" problem, and the outer join method is only really good for
small data sets.

I'd suggest instead that you use the "aggregate grid" method:

Construct a table like this, called month_xtab:

month_no jan_ct feb_ct mar_ct .... half_ct half2_ct
1 1 0 0 1 0
2 0 1 0 1 0
3 0 0 1 1 0
...
12 0 0 0 0 1

Then you can do monthly crosstabs like:

SELECT item, SUM(no_sold * jan_ct) as jan_qty, SUM(no_sold * feb_ct) as
feb_qty .... SUM (no_sold * half_ct) as half_qty, SUM(no_sold) as tot_qty
FROM sales, month_xtab
WHERE (extract(month from sales_date) = month_no and extract(year from
sales_date) = 2003)

This is much, much faster than the outer join method for large numbers of
columns. For better performance, make an index on extract(month from
sales_date).

This doesn't solve your whole query problem, but it's a good start.

This solution, and other tips, can be found in Joe Celko's "SQL for Smarties"

--
Josh Berkus
Aglio Database Solutions
San Francisco