-- Chapter 3 Data Warehousing Features:
---------------------------------------

-- 3.2.1: Bitmap Indexes

-- Page 71:
-- query using a bitmap index

SELECT customer_name, town
FROM customer
WHERE sex = 'F' AND occupation = 'Self-Employed';

-- Page 72:
-- creating a bitmap index

CREATE BITMAP INDEX easydw.customer_gender_index
ON customer(sex)
pctfree 5 tablespace indx
storage (initial 64k next 64k pctincrease 0);

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

-- 3.2.2: Bitmap Join Indexes

-- Page 74: 
-- query using bitmap join index

SELECT sum(p.purchase_price)
FROM purchases p, customer c
WHERE p.customer_id = c.customer_id
  AND c.sex = 'F';


-- Page 75:
-- creating a bitmap join index

CREATE BITMAP INDEX easydw.purchase_cust_index
ON
purchases (customer.sex)
FROM purchases, customer
WHERE purchases.customer_id = customer.customer_id;

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

-- 3.3.2 : Partitioning

-- Page 78:
-- Range partitioned Table

CREATE TABLE easydw.purchases
(product_id varchar2(8),
 time_key date,
 customer_id varchar2(10),
 purchase_date date,
 purchase_time number(4,0),
 purchase_price number(6,2),
 shipping_charge number(5,2),
 today_special_offer varchar2(1))
PARTITION by RANGE (time_key)
 (partition purchases_jan2002
    values less than (TO_DATE('01-02-2002', 'DD-MM-YYYY'))
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_jan2002,
  partition purchases_feb2002
    values less than (TO_DATE('01-03-2002', 'DD-MM-YYYY'))
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_feb2002,
  partition purchases_mar2002
    values less than (TO_DATE('01-04-2002', 'DD-MM-YYYY'))
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_mar2002,
  partition purchase_catchall
    values less than (MAXVALUE)
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_maxvalue);

-- Page 80:
-- Hash Partitioned Table

CREATE TABLE easydw.purchases
(product_id varchar2(8),
 time_key date,
 customer_id varchar2(10),
 purchase_date date,
 purchase_time number(4,0),
 purchase_price number(6,2),
 shipping_charge number(5,2),
 today_special_offer varchar2(1))
PARTITION BY HASH(product_id)
PARTITIONS 3;

-- Page 81:
-- List Partitioned Table

CREATE TABLE easydw.regional_sales
(state varchar2(2),
 store_number number,
 dept_number number,
 dept_name varchar2(10),
 sales_amount number (6,2)
)
PARTITION BY LIST(state)
(
PARTITION northeast VALUES ('NH', 'VT', 'MA', 'RI', 'CT'),
PARTITION southeast VALUES ('NC', 'GA', 'FL'),
PARTITION northwest VALUES ('WA', 'OR'),
PARTITION midwest VALUES ('IL', 'WI', 'OH'),
PARTITION west VALUES ('CA', 'NV', 'AZ'),
PARTITION otherstates VALUES (DEFAULT));


-- Page 83:
-- Range-Hash Composite Partitioned Table

CREATE TABLE easydw.purchases
(product_id varchar2(8),
 time_key date,
 customer_id varchar2(10),
 purchase_date date,
 purchase_time number(4,0),
 purchase_price number(6,2),
 shipping_charge number(5,2),
 today_special_offer varchar2(1))
PARTITION by RANGE (time_key)
  SUBPARTITION BY HASH(product_id)
  SUBPARTITIONS 3
 (partition purchases_jan2002
   values less than (TO_DATE('01-02-2002', 'DD-MM-YYYY'))
   pctfree 0 pctused 99 
   storage (initial 64k next 16k pctincrease 0)
   STORE IN (purchases_jan2002, 
             purchases_jan2002_2, 
             purchases_jan2002_3),
  partition purchases_feb2002
   values less than (TO_DATE('01-03-2002', 'DD-MM-YYYY'))
   pctfree 0 pctused 99 
   storage (initial 64k next 16k pctincrease 0)
   STORE IN (purchases_feb2002, 
             purchases_feb2002_2, 
             purchases_feb2002_3),
  partition purchases_mar2002
   values less than (TO_DATE('01-04-2002', 'DD-MM-YYYY'))
   pctfree 0 pctused 99 
   storage (initial 64k next 16k pctincrease 0)
   STORE IN (purchases_mar2002, 
             purchases_mar2002_2, 
             purchases_mar2002_3));


-- Page 84:
-- Range-List Composite Partitioned Table

CREATE TABLE sales
(state varchar2(2),
 store_number number,
 dept_number number,
 dept_name varchar2(10),
 sales_amount number (6,2),
 sale_date date,
 item_number number (10)
)
PARTITION BY RANGE (sale_date)
  SUBPARTITION BY LIST(state)
  SUBPARTITION TEMPLATE
  (SUBPARTITION "NorthEast" VALUES ('NH','VT','MA','RI','CT')
                              TABLESPACE sales_ne,
   SUBPARTITION "SouthEast" VALUES ('NC','GA','FL') 
                              TABLESPACE sales_se,
   SUBPARTITION "NorthWest" VALUES ('WA','OR') 
                              TABLESPACE sales_nw,
   SUBPARTITION "MidWest"   VALUES ('IL','WI','OH') 
                              TABLESPACE sales_mw,
   SUBPARTITION "West"      VALUES ('CA','NV','AZ') 
                              TABLESPACE sales_w)
(
  PARTITION sales_jan_2002
   VALUES LESS THAN (TO_DATE('01-Feb-2002', 'DD-Mon-YYYY')),
  PARTITION sales_feb_2002
   VALUES LESS THAN (TO_DATE('01-Mar-2002', 'DD-Mon-YYYY')),
  PARTITION sales_mar_2002
   VALUES LESS THAN (TO_DATE('01-Apr-2002', 'DD-Mon-YYYY'))
);


-- Page 85:
-- Example illustrating Partition Pruning

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


-- Page 86-87:
-- Multi-column Partition Keys

CREATE TABLE easydw.purchases
(product_id varchar2(8),
 time_key date,
 customer_id varchar2(10),
 purchase_date date,
 purchase_time number(4,0),
 purchase_price number(6,2),
 shipping_charge number(5,2),
 today_special_offer varchar2(1))
PARTITION by RANGE (time_key, product_id)
 (partition purchases_jan2002_100
    values less than (TO_DATE('31-01-2002','DD-MM-YYYY'), 100)
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_jan2002_100,
  partition purchases_jan2002_200
    values less than (TO_DATE('31-01-2002','DD-MM-YYYY'), 200)
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_jan2002_200 ,
  partition purchases_feb2002_all
    values less than (TO_DATE('28-02-2002','DD-MM-YYYY'), 100)
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_feb2002,
  partition purchases_mar2002_all
    values less than (TO_DATE('31-03-2002','DD-MM-YYYY'), 100)
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_mar2002
);

-- insert some values into the table

insert into purchases (product_id, time_key)
    values (1, TO_DATE('15-01-2002', 'DD-MM-YYYY'));
insert into purchases (product_id, time_key)
    values (150, TO_DATE('15-01-2002', 'DD-MM-YYYY'));
insert into purchases (product_id, time_key)
    values (101, TO_DATE('31-01-2002', 'DD-MM-YYYY'));
insert into purchases (product_id, time_key)
    values (170, TO_DATE('31-01-2002', 'DD-MM-YYYY'));
insert into purchases (product_id, time_key)
    values (200, TO_DATE('31-01-2002', 'DD-MM-YYYY'));
insert into purchases (product_id, time_key)
    values (1, TO_DATE('28-02-2002', 'DD-MM-YYYY'));

-- now see which partitions they go into

SELECT product_id, time_key 
FROM purchases partition(purchases_jan2002_100);

SELECT product_id, time_key 
FROM purchases partition(purchases_jan2002_200);

SELECT product_id, time_key 
FROM purchases partition(purchases_feb2002_all);

--------------------------------------------------------------------------------
-- 3.3.3 Partitioned Indexes


-- Page 89
-- Global Index 

CREATE INDEX easydw.global_product_index on purchases
(product_id)
 global
 partition by range (product_id)
 (partition sp1000 values less than ('SP1000') ,
  partition sp2000 values less than ('SP2000') ,
  partition other values less than (maxvalue) );

-- Page 91
-- Local Index (Prefixed)
CREATE INDEX easydw.purchase_time_index
ON purchases (time_key, customer_id)
local
(partition indexJan2002 tablespace purchases_jan2002_idx,
 partition indexFeb2002 tablespace purchases_feb2002_idx,
 partition indexMar2002 tablespace purchases_mar2002_idx );

-- Page 91
-- Local Index (Non-prefixed)
CREATE BITMAP INDEX easydw.purchase_product_index
ON purchases (product_id)
local
(partition indexJan2002 tablespace purchases_jan2002_idx,
 partition indexFeb2002 tablespace purchases_feb2002_idx,
 partition indexMar2002 tablespace purchases_mar2002_idx);


-- Page 93
-- Data Segment Compression

CREATE TABLE easydw.purchases
(product_id varchar2(8),
 time_key date,
 customer_id varchar2(10),
 purchase_date date,
 purchase_time number(4,0),
 purchase_price number(6,2),
 shipping_charge number(5,2),
 today_special_offer varchar2(1))
PARTITION by RANGE (time_key)
 (partition purchases_jan2002
    values less than (TO_DATE('01-02-2002', 'DD-MM-YYYY'))
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_jan2002 COMPRESS,
  partition purchases_feb2002
    values less than (TO_DATE('01-03-2002', 'DD-MM-YYYY'))
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_feb2002 COMPRESS,
  partition purchases_mar2002
    values less than (TO_DATE('01-04-2002', 'DD-MM-YYYY'))
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_mar2002 COMPRESS,
  partition purchase_catchall
    values less than (MAXVALUE)
    pctfree 0 pctused 99 
    storage (initial 64k next 64k pctincrease 0)
    tablespace purchases_maxvalue NOCOMPRESS);

--------------------------------------------------------------------------------
-- 3.5.1 EXPLAIN PLAN 

-- Page 96
-- EXPLAIN PLAN Usage
-- use utlxplan.sql in the rdbms/admin directory to create the plan table
-- use utlxpls.sql in the rdbms/admin directory to view the 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;

--------------------------------------------------------------------------------
-- 3.5.2 Join optimizations

-- Page 99-100:
-- Star Transformation

SELECT c.town, 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 = 1 and c.county = 'Hants' and 
      p.category = 'HDRW'
GROUP BY c.town, t.quarter, p.product_name;

--------------------------------------------------------------------------------
-- 3.6 Automatic Memory Management

-- Page 101,102:

-- Seeing the estimated memory size
SELECT sql_text, operation_type, 
       estimated_optimal_size estsize, last_memory_used
FROM v$sql_workarea w, v$sql s
WHERE w.address = s.address
AND parsing_schema_id = USERENV('SCHEMAID') ;

-- shows the current workarea usage during an execution
SELECT operation_type, work_area_size, expected_size, 
       actual_mem_used
FROM v$sql_workarea_active;

--------------------------------------------------------------------------------
-- NOTE NOTE NOTE NOTE
-- 
-- Some of the following examples uses a table geography and a slightly
-- different version of the purchases table (called purchases2 here). 
-- You can create these tables if required, as follows.

create table geography 
(state_id   varchar2(2),
 state_name varchar2(20),
 region     varchar2(10),
 tax_rate   number);

create table purchases2 as select * from purchases;
alter table purchases2 add (state_id varchar2(2));

insert into geography values ('MA', 'Massachusetts', 'NorthEast', 5.6);
insert into geography values ('NH', 'New Hampshire', 'NorthEast', 0);
insert into geography values ('CA', 'California', 'West', 8.5);
insert into geography values ('WA', 'Washington', 'NorthWest', 4);
insert into geography values ('TX', 'Texas', 'South', 0);
insert into geography values ('OH', 'Ohio', 'MidWest', 3);
insert into geography values ('WI', 'Wisconsin', 'MidWest', 4.5);
commit;

update purchases2 set state_id = 'MA'where rownum < 3000 ;
update purchases2 set state_id = 'NH' where state_id is null and rownum < 3000 ;
update purchases2 set state_id = 'CA' where state_id is null and rownum < 6000 ;
update purchases2 set state_id = 'WI' where state_id is null and rownum < 3000 ;
update purchases2 set state_id = 'OH' where state_id is null and rownum < 100 ;
update purchases2 set state_id = 'TX' where state_id is null and rownum < 200 ;
update purchases2 set state_id = 'WA' where state_id is null;
commit;

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

-- 3.7.2 Functions for Aggregation

-- Page 103:
-- CUBE Function

SELECT p.category, t.time_key, SUM(f.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 CUBE (p.category, t.time_key);

-- Page 104:
-- ROLLUP Function

SELECT p.category, t.time_key, SUM(f.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.time_key);

-- Page 105:
-- Grouping Sets

SELECT p.category as cat,  t.time_key, g.region, g.state_id as st,
       SUM(f.purchase_price) sales
FROM product p, purchases2 f,  time t, geography g 
WHERE p.product_id = f.product_id AND 
      t.time_key = f.time_key AND
      g.state_id = f.state_id 
GROUP BY GROUPING SETS ((p.category, t.time_key),  
         (p.category, g.state_id), (t.time_key, g.region), ());

-- Page 106:
-- Concatenated Grouping Sets

SELECT p.category as cat, t.quarter as quart, t.year, 
       g.state_id as st, g.region, SUM(f.purchase_price) sales
FROM  purchases2 f, time t, geography g, product p
WHERE p.product_id = f.product_id AND 
      t.time_key = f.time_key AND
      g.state_id = f.state_id 
GROUP BY p.category,
         GROUPING SETS (g.state_id, g.region),
         GROUPING SETS (t.quarter, t.year); 

-- Page 107:
-- GROUPING() function

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

-- Page 108:
-- GROUPING_ID() function

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

--------------------------------------------------------------------------------
-- 3.7.2 SQL Functions for Analysis

-- Page 110:
-- RANK Function

SELECT *
FROM
(SELECT p.product_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;

-- Page 111:
-- RANK in descending order

SELECT p.product_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;

-- Page 111:
-- 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;


-- Page 113:
-- CUME_DIST Function

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) < 100000 ;

-- Page 114:
-- PERCENT_RANK Function

SELECT p.category, p.product_id, SUM(f.purchase_price) as sales,
       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) < 100000;

-- Page 114:
-- NTILE Function

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;

-- Page 115:
-- WIDTH_BUCKET Function

SELECT p.product_id, SUM(f.purchase_price) as sales,
       WIDTH_BUCKET(SUM(f.purchase_price), 80000, 120000, 4) as width_bucket
FROM purchases f, product p
WHERE f.product_id = p.product_id and p.category = 'HDRW'
GROUP BY p.product_id
ORDER BY sales;

-- Page 116:
-- Window aggregation function (Cumulative sum, Moving Average)

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
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 moving_average
FROM purchases f, time t
WHERE f.time_key = t.time_key
GROUP BY t.month;

-- Page 117:
-- Cumulative 5-day sales using 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
GROUP BY t.time_key;

-- Page 118:
-- Reporting aggregates

SELECT p.category, g.region, 
       SUM(f.purchase_price) as regional_prod_sales,
       MAX(SUM(f.purchase_price)) 
           OVER (PARTITION BY g.region) as max_sales
FROM product p, geography g, purchases2 f
WHERE f.product_id = p.product_id
AND f.state_id = g.state_id
GROUP BY p.category, g.region;

SELECT category, region, regional_prod_sales
FROM
(
SELECT p.category, g.region,
        SUM(f.purchase_price) as regional_prod_sales,
        MAX(SUM(f.purchase_price))
        OVER (PARTITION BY g.region) as max_sales
FROM product p, geography g, purchases f
WHERE f.product_id = p.product_id
AND f.state_id = g.state_id
GROUP BY p.category, g.region
)
WHERE regional_prod_sales = max_sales;

-- Page 120
-- ratio-to-report
SELECT category, SUM(f.purchase_price) reg_sales,
       SUM(SUM(f.purchase_price)) OVER () as reg_sales_total,
       RATIO_TO_REPORT(SUM(f.purchase_price)) OVER () as ratio_to_report
FROM product p, purchases f
WHERE f.product_id = p.product_id
GROUP BY p.category;

-- Page 120:
-- LAG/LEAD Functions
SELECT t.month, SUM(f.purchase_price) as sales,
       LAG(SUM(f.purchase_price),1)
           OVER (ORDER BY t.month) as sales_last_month,
       LEAD(SUM(f.purchase_price),1)
           OVER (ORDER BY t.month) as sales_next_month
FROM purchases f, time t
WHERE f.time_key = t.time_key AND t.year = 2002
GROUP BY t.month;

-- Page 121: 
-- FIRST/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;

-- Page 122:
-- Statistical Analysis Functions

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

-- Page 124:
-- Inverse Percentile Function
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;

-- Page 125
-- Hypothetical RANK Function
SELECT RANK(98000.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;


-- Page 126-127: 
-- User-defined aggregate 

-- 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 of SalesForecastFunction (not in the book)

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;


-- Page 128:
-- CASE Function

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;

-- Page 128:
-- CASE Function inside an Aggregate Function

SELECT AVG(f.purchase_price) as current_avg_sales,
       AVG (CASE WHEN f.purchase_price < 10 THEN 0
                 WHEN f.purchase_price > 500 
                      THEN 1.1 * f.purchase_price 
                 WHEN f.state_id = 'MA' THEN 0
                 ELSE f.purchase_price END) projected_avg_sales
FROM purchases2 f;

--------------------------------------------------------------------------------
-- 3.7.5 WITH Clause

-- Page 129:
-- Complex Query used to illustrate 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);

-- Page 130
-- same query written using WITH clause
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);