Page 356
and s.channel_id = c.channel_id
and s.time_id = t.time_id
and c.channel_desc = 'Direct Sales'
group by c.channel_desc, p.prod_category, t.calendar_year
order by c.channel_desc, p.prod_category, t.calendar_year;
CHANNEL_DESC PROD_CATEGORY YEAR QUANTITY_SOLD
---------------- ------------------------------ ------ -------------
Direct Sales Electronics 1998 7758
Direct Sales Electronics 1999 15007
...
Direct Sales Hardware 2000 1970
Direct Sales Hardware 2001 2399
Direct Sales Peripherals and Accessories 1998 44258
...
Direct Sales Software/Other 2000 64483
Direct Sales Software/Other 2001 49146
In the results, we see the historical aggregate quantity_sold for each year by product category for the 'Direct Sales' channel. We can use the model clause to project the quantity_sold. In the following listing, we will project values for 2002 for the product category Hardware in the channel. The quantity_sold will be based on the previous year's value (2001), plus 10 percent. Table 9-18 explains the syntax of the listing.
1 select channel_desc, prod_category, year, quantity_sold
2 from
3 (select c.channel_desc, p.prod_category, t.calendar_year year,
4 sum(s.quantity_sold) quantity_sold
5 from sales s, products p, channels c, times t
6 where s.prod_id = p.prod_id
7 and s.channel_id = c.channel_id
8 and s.time_id = t.time_id
9 group by c.channel_desc, p.prod_category, t.calendar_year) sales
10 where channel_desc = 'Direct Sales'
11 model
12 partition by (channel_desc)
13 dimension by (prod_category, year)
14 measures (quantity_sold)
15 rules (quantity_sold['Hardware', 2002]
16 = quantity_sold['Hardware', 2001] * 1.10)
17 order by channel_desc, prod_category, year;
