-----------------------------------------------------------------------------
-- 6.2.1 EXPLAIN PLAN
-----------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT t.month, t.year, p.product_id, 
       SUM (purchase_price) as sum_of_sales, 
       COUNT (purchase_price) as total_sales, 
       COUNT(*) as cstar
FROM time t, product p, purchases f
WHERE t.time_key = f.time_key AND
      f.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id;

-----------------------------------------------------------------------------
-- 6.2.3 Star Transformation
-----------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT c.city, t.quarter, p.product_name, 
       SUM(f.purchase_price) sales
FROM purchases f, time t, customer c, product p
WHERE f.time_key = t.time_key and
      f.customer_id = c.customer_id and
      f.product_id = p.product_id and
      t.month = 200301 and c.state = 'MA' and 
      p.category = 'HDRW'
GROUP BY c.city, t.quarter, p.product_name;

SELECT *
FROM purchases
WHERE product_id IN (SELECT product_id 
                     FROM product 
                     WHERE category = 'HDRW');

-----------------------------------------------------------------------------
-- 6.2.4 Partition Pruning 
-----------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT t.time_key, SUM(f.purchase_price) as sales
FROM purchases f, time t
WHERE f.time_key = t.time_key
  AND t.time_key BETWEEN TO_DATE('1-Nov-2003', 'DD-Mon-YYYY')
  AND                    TO_DATE('31-Dec-2003', 'DD-Mon-YYYY')
GROUP BY t.time_key;

-----------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT store_number, dept_number, SUM(sales_amount) as q1_sales
FROM regional_sales
WHERE state in ('NH', 'MA', 'CT', 'CA', 'AZ')
GROUP BY store_number, dept_number;

-----------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT store_number, dept_number, SUM(sales_amount) as q1_sales
FROM sales
WHERE sale_date between TO_DATE('15-Feb-2003', 'DD-Mon-YYYY') 
  AND TO_DATE('15-Mar-2003', 'DD-Mon-YYYY') 
  AND state in ('NH', 'MA', 'CT')
GROUP BY store_number, dept_number;

-----------------------------------------------------------------------------
-- 6.3.1 Parallel Processing
-----------------------------------------------------------------------------

-- set DOP to 4 for a table
ALTER TABLE purchases PARALLEL 4;

--set DOP to 6 for a session for parallel DML or query
ALTER SESSION FORCE PARALLEL DML PARALLEL 6;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 6;

-- set DOP to 2 for just this SQL statement
SELECT /*+ PARALLEL (2)*/ * FROM purchases;

-----------------------------------------------------------------------------
-- 6.4.1.1 Cube
-----------------------------------------------------------------------------

SELECT p.category, t.year, SUM(purchase_price) total_sales
FROM product p, purchases f, time t
WHERE p.product_id = f.product_id AND
      t.time_key = f.time_key
GROUP BY CUBE (p.category, t.year);

-----------------------------------------------------------------------------
-- 6.4.1.2 Rollup
-----------------------------------------------------------------------------

SELECT p.category, t.year, SUM(purchase_price)
FROM product p, purchases f, time t
WHERE p.product_id = f.product_id AND
      t.time_key = f.time_key
GROUP BY ROLLUP (p.category, t.year);

-----------------------------------------------------------------------------
-- 6.4.1.3 Grouping Sets
-----------------------------------------------------------------------------

SELECT p.category as cat, t.year, c.region, c.state as st,
       SUM(f.purchase_price) sales
FROM product p, purchases f,  time t, customer c
WHERE p.product_id = f.product_id AND
      t.time_key = f.time_key AND
      c.customer_id = f.customer_id AND
      c.country = 'USA' and c.region in ('AmerWest', 'AmerSouth')
GROUP BY GROUPING SETS ((p.category, c.state), 
                        (t.year, c.region), 
                        (p.category, t.year),());
-----------------------------------------------------------------------------

SELECT p.category as cat, t.quarter as quart, t.year,
       c.state as st, c.region, SUM(f.purchase_price) sales
FROM  purchases f, time t, customer c, product p
WHERE p.product_id = f.product_id AND
      t.time_key = f.time_key AND
      c.customer_id = f.customer_id AND 
      c.country = 'USA' and c.region in ('AmerWest', 'AmerSouth')
GROUP BY p.category,
         GROUPING SETS (c.state, c.region),
         GROUPING SETS (t.quarter, t.year);

-----------------------------------------------------------------------------
-- 6.4.1.4 GROUPING & GROUPING_ID functions
-----------------------------------------------------------------------------

SELECT t.year, p.category as cat, 
       SUM(f.purchase_price) sales, 
       GROUPING(t.year) grp_y, GROUPING(p.category) grp_c
FROM product p, purchases f, time t 
WHERE p.product_id = f.product_id AND
      t.time_key = f.time_key 
GROUP BY ROLLUP (t.year, p.category);

-----------------------------------------------------------------------------

SELECT t.year, p.category as cat, 
       SUM(f.purchase_price) sales, 
       GROUPING_ID(p.category,t.year) gid
FROM product p, purchases f, time t
WHERE p.product_id = f.product_id AND
    t.time_key = f.time_key
GROUP BY ROLLUP (t.year, p.category);

-----------------------------------------------------------------------------
-- 6.4.1.5 User-defined Aggregates
-----------------------------------------------------------------------------

-- creating a type for the aggregate
CREATE OR REPLACE TYPE SalesForecastFunction as OBJECT (
  data number,
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT SalesForecastFunction)
      RETURN number,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT SalesForecastFunction, 
      value IN number) return number,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN OUT SalesForecastFunction,
      returnValue OUT number, flags IN number) return number,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT SalesForecastFunction, 
    ctx2 IN OUT SalesForecastFunction) return number
);
/

-- sample implementation pf SalesForecastFunction

CREATE OR REPLACE TYPE BODY SalesForecastFunction is
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT SalesForecastFunction)
      RETURN number is
  begin 
    ctx := SalesForecastFunction(0);
    return ODCIConst.Success;
  end;
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT SalesForecastFunction, 
      value IN number) return number is
  begin
    self.data := self.data + 1.1 * value;
    return ODCIConst.Success;
  end;
  MEMBER FUNCTION ODCIAggregateTerminate(self IN OUT SalesForecastFunction,
      returnValue OUT number, flags IN number) return number is
  begin
    returnValue := self.data;
    return ODCIConst.Success;
  end;
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT SalesForecastFunction, 
    ctx2 IN OUT SalesForecastFunction) return number is
  begin
    self.data := self.data + ctx2.data;
    return ODCIConst.Success;
  end;
end;
/

-- declaration of SalesForecast Function using SalesForecastFunction type

CREATE or REPLACE FUNCTION SalesForecast(x number) RETURN number
PARALLEL_ENABLE AGGREGATE USING SalesForecastFunction;
/


-- using the user-defined aggregate in a query

SELECT p.category, SUM(f.purchase_price) sales,
       SalesForecast(f.purchase_price) as salesforecast
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.category;

-----------------------------------------------------------------------------
-- 6.4.2.1 Ranking Functions
-----------------------------------------------------------------------------

SELECT *
FROM
(SELECT p.product_id p_id, SUM(f.purchase_price) as sales,
        RANK() OVER (ORDER BY SUM(f.purchase_price)) as rank
 FROM purchases f, product p
 WHERE f.product_id = p.product_id
 GROUP BY p.product_id)
WHERE rank <= 10;

-----------------------------------------------------------------------------

SELECT p.product_id p_id, SUM(f.purchase_price) as sales,
       RANK() OVER (ORDER BY SUM(f.purchase_price)
                    DESC NULLS LAST) as rank
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.product_id;

-----------------------------------------------------------------------------

SELECT p.product_id p_id, SUM(f.purchase_price) as sales,
       RANK() OVER (ORDER BY SUM(f.purchase_price)) as rank,
       DENSE_RANK() OVER (ORDER BY SUM(f.purchase_price)) as drank
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.product_id;

-----------------------------------------------------------------------------

-- Partition By Clause
SELECT p.category, p.product_id, SUM(f.purchase_price) as sales,
       RANK() OVER (PARTITION BY p.category
                    ORDER BY SUM(f.purchase_price)) as rank
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.category, p.product_id;

-----------------------------------------------------------------------------

-- Relative Ranking Functions
SELECT p.category, p.product_id, SUM(f.purchase_price) as sales,
       NTILE(4) OVER (PARTITION BY p.category
                      ORDER BY SUM(f.purchase_price)) as quartile
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.category, p.product_id
HAVING SUM(f.purchase_price) < 7500;

-----------------------------------------------------------------------------

SELECT p.category, p.product_id, SUM(f.purchase_price) as sales,
       CUME_DIST() OVER (PARTITION BY p.category
                         ORDER BY SUM(f.purchase_price)) as cume_dist,
       PERCENT_RANK() OVER (PARTITION BY p.category
                         ORDER BY SUM(f.purchase_price)) as pct_rank
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.category, p.product_id
HAVING SUM(f.purchase_price) < 7500 ;

-----------------------------------------------------------------------------
-- 6.4.2.2 Bucketing Functions
-----------------------------------------------------------------------------

SELECT width_bucket, min(sales) , max(sales), COUNT(*)
FROM
(SELECT  p.product_id, SUM(f.purchase_price) as sales,
        WIDTH_BUCKET(SUM(f.purchase_price), 0, 100000, 4)
                     as width_bucket
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.product_id)
GROUP BY width_bucket;

-----------------------------------------------------------------------------
-- 6.4.2.3 Period-over-period Comparison Functions – LAG and LEAD
-----------------------------------------------------------------------------

SELECT t.month, SUM(f.purchase_price) as monthly_sales,
       LAG(SUM(f.purchase_price),6)
           OVER (ORDER BY t.month) as sales_6_months_ago
FROM purchases f, time t
WHERE f.time_key = t.time_key
GROUP BY t.year, t.month; 

-----------------------------------------------------------------------------
-- 6.4.2.4 Window Aggregate Functions
-----------------------------------------------------------------------------

SELECT t.month, SUM(f.purchase_price) as sales,
       SUM(SUM(f.purchase_price))
           OVER (ORDER BY t.month ROWS UNBOUNDED PRECEDING)
             as cumulative_sales
FROM purchases f, time t
WHERE f.time_key = t.time_key and t.year = 2003
GROUP BY t.month;

SELECT t.month, SUM(f.purchase_price) as sales,
       AVG(SUM(f.purchase_price))
           OVER (ORDER BY t.month ROWS 2 PRECEDING) as mov_avg
FROM purchases f, time t
WHERE f.time_key = t.time_key and t.year = 2003
GROUP BY t.month;

-- Specifying a Logical Window

SELECT t.time_key, SUM(f.purchase_price) as sales,
       SUM(SUM(f.purchase_price)) OVER (ORDER BY t.time_key
       RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND
                     INTERVAL '2' DAY FOLLOWING) as sales_5_day
FROM purchases f, time t
WHERE f.time_key = t.time_key and t.year = 2003
GROUP BY t.time_key
HAVING SUM(f.purchase_price) < 25000;

-----------------------------------------------------------------------------
-- 6.4.2.5 Converting Sparse Data into Dense Form 
-----------------------------------------------------------------------------

SELECT t.month, p.product_name, SUM(f.purchase_price) as sales
FROM purchases f, time t, product p
WHERE f.time_key = t.time_key
  AND f.product_id = p.product_id
GROUP BY p.product_name, t.month;
-----------------------------------------------------------------------------

-- Outer Join
SELECT p.product_name, SUM(f.purchase_price) as sales
FROM purchases f RIGHT OUTER JOIN product p 
  ON (f.product_id = p.product_id)
GROUP BY p.product_name;
-----------------------------------------------------------------------------

-- Partition Outer Join
SELECT v2.month, v1.product_name, nvl(v1.sales,0)
FROM
(SELECT t.month, p.product_name, SUM(f.purchase_price) as sales
 FROM purchases f, time t, product p
 WHERE f.time_key = t.time_key
   AND f.product_id = p.product_id
 GROUP BY p.product_name, t.month) v1 PARTITION BY (product_name)
 RIGHT OUTER JOIN
(SELECT DISTINCT t.month
 FROM time t) v2
ON v1.month = v2.month;

-----------------------------------------------------------------------------
-- 6.4.2.6 Reporting Aggregates
-----------------------------------------------------------------------------

SELECT category, SUM(f.purchase_price) as sales,
       SUM(SUM(f.purchase_price)) OVER () as sales_total,
       SUM(f.purchase_price)/SUM(SUM(f.purchase_price)) OVER() 
          as ratio_sales
FROM product p, purchases f
WHERE f.product_id = p.product_id
GROUP BY p.category;
-----------------------------------------------------------------------------

SELECT category, RATIO_TO_REPORT(SUM(f.purchase_price)) 
                                 OVER () as ratio_sales
FROM product p, purchases f
WHERE f.product_id = p.product_id
GROUP BY p.category;
-----------------------------------------------------------------------------

SELECT *
FROM (SELECT p.category, p.product_id prod_id, 
             SUM(f.purchase_price) prod_sales, 
             AVG(SUM(f.purchase_price)) 
                 OVER (PARTITION BY p.category) category_avg
      FROM product p, purchases f
      WHERE f.product_id = p.product_id
      GROUP BY p.category, p.product_id)
WHERE prod_sales < category_avg;

-----------------------------------------------------------------------------
-- 6.4.2.7 First and Last Functions
-----------------------------------------------------------------------------

SELECT p.category cat, SUM(f.purchase_price) total_sales,
       MIN(p.sell_price) cheap_prod,
       COUNT(*) KEEP (DENSE_RANK FIRST
                      ORDER BY p.sell_price) cheap_sales,
       MAX(p.sell_price) costly_prod,
       COUNT(*) KEEP (DENSE_RANK LAST
                      ORDER BY p.sell_price) costly_sales
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.category;

-----------------------------------------------------------------------------
-- 6.4.2.8 Inverse Percentile
-----------------------------------------------------------------------------

SELECT p.category, p.product_id, SUM(f.purchase_price) as sales,
       CUME_DIST() over (PARTITION BY p.category
                         ORDER BY SUM(f.purchase_price)) 
                   as cume_dist
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.category, p.product_id
HAVING SUM(f.purchase_price) < 7500 ;

-----------------------------------------------------------------------------

SELECT p.category, p.product_id, SUM(f.purchase_price) as sales,
       PERCENTILE_DISC(0.5) WITHIN GROUP 
            (ORDER BY SUM(f.purchase_price))
            OVER (PARTITION BY p.category) as pct_disc,
       PERCENTILE_CONT(0.5) WITHIN GROUP 
            (ORDER BY SUM(f.purchase_price))
            OVER (PARTITION BY p.category) as pct_cont
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.category, p.product_id
HAVING SUM(f.purchase_price) < 7500;

-----------------------------------------------------------------------------
-- 6.4.2.9 Hypothetical RANK and Distribution functions
-----------------------------------------------------------------------------

SELECT p.product_id, SUM(f.purchase_price) sales,
       RANK() OVER (ORDER BY SUM(f.purchase_price)) as  rank
FROM purchases f, product p
WHERE f.product_id = p.product_id and p.category = 'HDRW'
GROUP BY p.product_id;

-----------------------------------------------------------------------------

SELECT RANK(7600.00)
       WITHIN GROUP (ORDER BY SUM(f.purchase_price)) as hrank
FROM purchases f, product p
WHERE f.product_id = p.product_id and p.category = 'HDRW'
GROUP BY p.product_id;

-----------------------------------------------------------------------------
-- 6.4.2.10 Statistical Analysis Functions
-----------------------------------------------------------------------------

SELECT manufacturer, 
       REGR_SLOPE(sell_price, total_purchases) slope,
       REGR_INTERCEPT(sell_price, total_purchases) intercept,
       REGR_R2(sell_price, total_purchases) coeff_determination
FROM
(
SELECT p.manufacturer , p.product_id, 
       f.purchase_price sell_price,
       count(f.purchase_price) as total_purchases
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.manufacturer, p.product_id, f.purchase_price
)
GROUP BY manufacturer;

-----------------------------------------------------------------------------

-- DBMS_STATS_FUNC Package

set serveroutput on;
DECLARE 
  srec dbms_stat_funcs.summaryType;
BEGIN   
  dbms_stat_funcs.summary(p_ownername=>'EASYDW', 
                          p_tablename=>'PURCHASES', 
                          p_columnname=>'PURCHASE_PRICE',
                          s=>srec);
  dbms_output.put_line('Quantile 5   => ' || srec.quantile_5);
  dbms_output.put_line('Quantile 25  => ' || srec.quantile_25);
  dbms_output.put_line('Median       => ' || srec.median);
  dbms_output.put_line('Quantile 75  => ' || srec.quantile_75);
  dbms_output.put_line('Quantile 95  => ' || srec.quantile_95);
END;
/

-----------------------------------------------------------------------------
-- 6.4.2.11 CASE Expression
-----------------------------------------------------------------------------

SELECT f.product_id,  SUM(f.purchase_price) as sales,
       CASE WHEN SUM(f.purchase_price) > 150000 THEN 'High'
            WHEN SUM(f.purchase_price) 
                 BETWEEN 100000 and 150000 THEN 'Medium'
            WHEN SUM(f.purchase_price) 
                 BETWEEN 50000 and 100000 THEN 'Low'
            ELSE 'Other'  END  as sales_value
FROM purchases f
GROUP BY f.product_id;

-----------------------------------------------------------------------------

SELECT AVG(f.shipping_charge) as current_avg_shipcosts,
       AVG(CASE WHEN c.state = 'CA' and f.purchase_price > 100 THEN 0
                WHEN c.country = 'UK' and f.purchase_price > 250 
                     THEN  0.9 * f.shipping_charge
                WHEN f.purchase_price > 50 THEN 0
                ELSE f.shipping_charge END) projected_shipping_costs
FROM purchases f, customer c
WHERE f.customer_id = c.customer_id;

-----------------------------------------------------------------------------
-- 6.4.2.12 WITH Clause
-----------------------------------------------------------------------------

SELECT s.category, s.month, s.monthly_prod_sales
FROM (SELECT p.category, t.month, 
             SUM(f.purchase_price) as monthly_prod_sales
      FROM product p, purchases f, time t
      WHERE f.product_id = p.product_id 
        AND f.time_key = t.time_key
      GROUP BY p.category, t.month) s 
WHERE s.monthly_prod_sales 
  IN (SELECT MAX(v.monthly_sales)
      FROM (SELECT p.category, t.month, 
                  SUM(f.purchase_price) as monthly_sales       
            FROM product p, purchases f, time t
            WHERE f.product_id = p.product_id 
              AND f.time_key = t.time_key
            GROUP BY p.category, t.month) v
      GROUP BY v.month);

-----------------------------------------------------------------------------

WITH  product_sales_by_month                 
AS
(
 SELECT p.category, t.month, 
        SUM(f.purchase_price) as monthly_prod_sales
 FROM  product p, purchases f, time t
 WHERE f.product_id = p.product_id 
   AND f.time_key = t.time_key
 GROUP BY p.category, t.month
)
SELECT s.category, s.month, s.monthly_prod_sales
FROM   product_sales_by_month s      
WHERE  s.monthly_prod_sales 
   IN (SELECT MAX(v.monthly_prod_sales)
       FROM product_sales_by_month v       
       GROUP BY v.month);

-----------------------------------------------------------------------------
-- 6.4.3 The SQL Model Clause
-----------------------------------------------------------------------------

-- 6.4.3.1 A Simple Calculation
SELECT month_name, sales
FROM (SELECT t.month, t.month_name, SUM(f.purchase_price) sales
      FROM purchases f, time t
      WHERE f.time_key = t.time_key AND t.year = 2003
      GROUP BY t.month, t.month_name)       
MODEL               
MAIN holiday_sales_model         
DIMENSION BY (month_name)
MEASURES (sales)
RULES 
(sales['Holidays'] = sales['November'] + sales['December']);

-----------------------------------------------------------------------------

SELECT region, month_name, sales
FROM (SELECT t.month_name, c.region, SUM(f.purchase_price) sales
      FROM purchases f, time t, customer c
      WHERE f.time_key = t.time_key
        AND c.customer_id = f.customer_id
      GROUP BY c.region, t.month_name)
MODEL
RETURN UPDATED ROWS          
MAIN holiday_sales_model       
PARTITION BY (region)
DIMENSION BY (month_name)
MEASURES (sales)
RULES
(sales['Holiday'] = sales['November'] + sales['December'])
ORDER BY region;

-----------------------------------------------------------------------------
-- 6.4.3.2 More about RULES
-----------------------------------------------------------------------------

CREATE VIEW sales_region_month
AS
SELECT t.month, t.month_name, c.region, t.year, 
       SUM(f.purchase_price) sales
FROM purchases f, time t, customer c
WHERE f.time_key = t.time_key
  AND c.customer_id = f.customer_id
GROUP BY t.month, t.month_name, c.region, t.year;

-- Cell Referencing 
SELECT region, year, sales
FROM (SELECT region, year, SUM(sales) as sales
      FROM sales_region_month
      GROUP BY region, year)
MODEL
RETURN UPDATED ROWS
MAIN sales_growth_2004
DIMENSION BY (region, year)
MEASURES (sales)
RULES
(sales[region IS ANY, 2004]
= (sales[CV(region), 2004] - sales[CV(region), 2003])*100
  /sales[CV(region), 2003])
ORDER BY region;

-----------------------------------------------------------------------------

-- Update Semantics
SELECT region, sales
FROM (SELECT region, SUM(sales) as sales
      FROM sales_region_month
      GROUP BY region)
MODEL
RETURN UPDATED ROWS
MAIN holiday_sales_model
DIMENSION BY (region)
MEASURES (sales)
RULES UPDATE    
(sales['All Regions'] = SUM(sales) [region is ANY])
ORDER BY region;

-----------------------------------------------------------------------------

-- Upsert Semantics
SELECT region, sales
FROM (SELECT region, SUM(sales) as sales
      FROM sales_region_month
      GROUP BY region)
MODEL
RETURN UPDATED ROWS
MAIN holiday_sales_model
DIMENSION BY (region)
MEASURES (sales)
RULES UPSERT                                 <-  upsert semantics	 
(sales['All Regions'] = SUM(sales) [region is ANY])
ORDER BY region;

-----------------------------------------------------------------------------

-- Using the FOR keyword
SELECT region, month_name,sales
FROM (SELECT region, month_name, sales
      FROM sales_region_month
      WHERE year = 2003)
MODEL
RETURN UPDATED ROWS
MAIN holiday_sales_model
DIMENSION BY (region, month_name)
MEASURES (sales)
RULES UPSERT
(sales[FOR region IN ('AmerNorthEast', 'AmerMidWest'), 'Winter']
= SUM(sales) [CV(region),
              month_name IN ('November', 'December',
                             'January', 'February', 'March')],
 sales[FOR region IN ('AmerWest', 'AmerSouth'), 'Winter']
= SUM(sales) [CV(region),
              month_name IN ('January', 'February')])
ORDER BY region, month_name;

-----------------------------------------------------------------------------

-- Rule Ordering 
SELECT region, sales
FROM (SELECT region, SUM(sales) sales
      FROM sales_region_month
      WHERE year = 2003
      GROUP BY region)
MODEL
RETURN UPDATED ROWS
MAIN holiday_sales_model
DIMENSION BY (region)
MEASURES (sales)
RULES AUTOMATIC ORDER
(sales['Total'] 
      = sales['TotalAmericas'] 
      + sales['TotalEurope'],                 
 sales['TotalAmericas'] 
      = SUM(sales)[region like 'Amer%'],
 sales['TotalEurope'] 
      = SUM(sales)[region like 'Eur%'])
ORDER BY region;

-----------------------------------------------------------------------------

-- Iteration
SELECT year, sales
FROM (SELECT year, SUM(sales) sales
      FROM sales_region_month
      WHERE year = 2003
      GROUP BY year)
MODEL
RETURN ALL ROWS    
MAIN forecast_over_10_years
DIMENSION BY (year)
MEASURES (sales)
RULES ITERATE (10)       
(sales[2003+ITERATION_NUMBER+1]
 = sales[2003+ITERATION_NUMBER] * 0.15
 + AVG(sales)[year in (2003+ITERATION_NUMBER,
                       2003+ITERATION_NUMBER-1)]);

-----------------------------------------------------------------------------
-- 6.4.3.3 Some Examples of SQL Model 
-----------------------------------------------------------------------------

SELECT region, Q1sales, Q2sales, Q3sales, Q4sales
FROM
(SELECT region,month_name, Q1sales, Q2sales, Q3sales, Q4sales
 FROM (SELECT region, month_name, SUM(sales) sales
       FROM sales_region_month WHERE year = 2003
       GROUP BY region, month_name)
 MODEL
 RETURN ALL ROWS
 MAIN holiday_sales_model
 DIMENSION BY (region, month_name)
 MEASURES (sales,
           0 as Q1sales, 0 as Q2sales, 0 as Q3sales, 0 as q4sales)
 RULES AUTOMATIC ORDER
 (
 Q1sales[region is ANY, 'January'] 
         = SUM(sales)[CV(region), month_name  
                      in  ('January', 'February', 'March')],
 Q2sales[region is ANY, 'January'] 
         = SUM(sales)[CV(region), month_name
                      in ('April', 'May', 'June')],
 Q3sales[region is ANY, 'January'] 
         = SUM(sales)[CV(region), month_name
                      in ('July', 'August', 'September')],
 Q4sales[region is ANY, 'January'] 
         = SUM(sales)[CV(region), month_name
                      in ('October', 'November','December')]
 )
) WHERE month_name = 'January'
ORDER BY region;
-----------------------------------------------------------------------------

SELECT year, state, sales, net_sales
FROM (SELECT t.year, c.state, SUM(f.purchase_price) sales
      FROM purchases f, time t, customer c
      WHERE f.time_key = t.time_key
        AND c.customer_id = f.customer_id
        AND c.country = 'USA'
      GROUP BY t.year, c.state)
MODEL
REFERENCE state_tax_model
       ON (SELECT distinct state, tax_rate FROM customer)
       DIMENSION BY (state) MEASURES (tax_rate) IGNORE NAV
MAIN
DIMENSION BY (year, state)
MEASURES (sales, 0 as net_sales) IGNORE NAV
RULES SEQUENTIAL ORDER
(net_sales[ANY, ANY] =  sales[CV(year), CV(state)] *
                (1 - state_tax_model.tax_rate[CV(state)]/100));

-----------------------------------------------------------------------------