SQL> -- +version+

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

PL/SQL Release 10.1.0.2.0 - Production

CORE 10.1.0.2.0 Production

TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

NLSRTL Version 10.1.0.2.0 - Production

SQL> --- set up environment

SQL> -- +setup+

SQL> -- product table

SQL> drop table prod;

Table dropped.

SQL> create table prod as

2 select product_id, month_id,

3 -- an unusual way to define DATE column containing all NULLs

4 case when 1=0 then sysdate else to_date(null) end month,

5 0 qty, 0 price, ' '||to_char(NULL) product_desc

6 from

7 (select rownum product_id from dba_objects where rownum < 5) pr,

8 (select rownum month_id from dba_objects where rownum < 13) pr

9 where --- product 4 has some holes

10 NOT (product_id = 4 and month_id in (1,4,5,8,10));

Table created.

SQL> ---

SQL> -- define product description

SQL> update prod set product_desc = 'linear' where product_id = 1;

12 rows updated.

SQL> update prod set product_desc = 'geometric' where product_id = 2;

12 rows updated.

SQL> update prod set product_desc = 'very stable' where product_id = 3;

12 rows updated.

SQL> update prod set product_desc = 'lineal sparce' where product_id = 4;

7 rows updated.

SQL> ---

SQL> --- set qty

SQL> --

SQL> update prod set qty = 1 + (2*(month_id-1)),

2 price = 10 - (0.2*(month_id-1))

3 where product_id = 1;

12 rows updated.

SQL> --

SQL> update prod set qty = 1 + power(2,(month_id-1)),

2 price = round(1000 / power(2,(month_id-1)),2)

3 where product_id = 2;

12 rows updated.

SQL> --

SQL> update prod set qty = 10 + trunc(dbms_random.value(0,3))-1,

2 price = 10 - trunc(dbms_random.value(0,3))-1

3 where product_id = 3;

12 rows updated.

SQL> ---

SQL> update prod set qty = month_id,

2 price = 10 + month_id

3 where product_id = 4;

7 rows updated.

SQL> -- and time dimension lookup

SQL> drop table month;

Table dropped.

SQL> create table month

2 (month_id number,

3 month_first_date date,

4 month_desc varchar2(30),

5 year number);

Table created.

SQL> --- insert full year 2003

SQL> insert into month

2 select rownum month_id,

3 to_date('01-'||to_char(rownum,'09')||'-2003','dd-mm-yyyy'),

4 to_char(to_date('01-'||to_char(rownum,'09')||'-2003','dd-mm-yyyy'),'MONTH','NLS_DATE_LANGUAGE = english') month_desc,

5 2003 year

6 from prod where rownum < 13;

12 rows created.

SQL> -- and start of 2004

SQL> insert into month

2 select 12+rownum month_id,

3 to_date('01-'||to_char(rownum,'09')||'-2004','dd-mm-yyyy'),

4 to_char(to_date('01-'||to_char(rownum,'09')||'-2003','dd-mm-yyyy'),'MONTH','NLS_DATE_LANGUAGE = english') month_desc,

5 2004 year

6 from prod where rownum < 3;

2 rows created.

SQL> ---

SQL> --- add MONTH column to product table

SQL> alter table month

2 ADD CONSTRAINT month_pk1 PRIMARY KEY (month_id) -- required for UJV

3 ;

Table altered.

SQL> -- init month in product

SQL> UPDATE

2 (SELECT p.month month, m.month_first_date from prod p, month m where p.month_id = m.month_id)

3 set month = month_first_date;

43 rows updated.

SQL> commit;

Commit complete.

SQL> -- +prods+

SQL> select * from prod order by product_id, month_id;

PRODUCT_ID MONTH_ID MONTH QTY PRICE PRODUCT_DESC

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

1 1 01-JAN-03 1 10 linear

1 2 01-FEB-03 3 9.8 linear

1 3 01-MAR-03 5 9.6 linear

1 4 01-APR-03 7 9.4 linear

1 5 01-MAY-03 9 9.2 linear

1 6 01-JUN-03 11 9 linear

1 7 01-JUL-03 13 8.8 linear

1 8 01-AUG-03 15 8.6 linear

1 9 01-SEP-03 17 8.4 linear

1 10 01-OCT-03 19 8.2 linear

1 11 01-NOV-03 21 8 linear

1 12 01-DEC-03 23 7.8 linear

2 1 01-JAN-03 2 1000 geometric

2 2 01-FEB-03 3 500 geometric

2 3 01-MAR-03 5 250 geometric

2 4 01-APR-03 9 125 geometric

2 5 01-MAY-03 17 62.5 geometric

2 6 01-JUN-03 33 31.25 geometric

2 7 01-JUL-03 65 15.63 geometric

2 8 01-AUG-03 129 7.81 geometric

2 9 01-SEP-03 257 3.91 geometric

2 10 01-OCT-03 513 1.95 geometric

2 11 01-NOV-03 1025 .98 geometric

2 12 01-DEC-03 2049 .49 geometric

3 1 01-JAN-03 10 8 very stable

3 2 01-FEB-03 11 8 very stable

3 3 01-MAR-03 9 8 very stable

3 4 01-APR-03 10 9 very stable

3 5 01-MAY-03 9 8 very stable

3 6 01-JUN-03 11 8 very stable

3 7 01-JUL-03 9 8 very stable

3 8 01-AUG-03 10 7 very stable

3 9 01-SEP-03 9 9 very stable

3 10 01-OCT-03 10 9 very stable

3 11 01-NOV-03 11 7 very stable

3 12 01-DEC-03 10 8 very stable

4 2 01-FEB-03 2 12 lineal sparce

4 3 01-MAR-03 3 13 lineal sparce

4 6 01-JUN-03 6 16 lineal sparce

4 7 01-JUL-03 7 17 lineal sparce

4 9 01-SEP-03 9 19 lineal sparce

4 11 01-NOV-03 11 21 lineal sparce

4 12 01-DEC-03 12 22 lineal sparce

43 rows selected.

SQL> -- +months+

SQL> select * from month order by month_id;

MONTH_ID MONTH_FIR MONTH_DESC YEAR

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

1 01-JAN-03 JANUARY 2003

2 01-FEB-03 FEBRUARY 2003

3 01-MAR-03 MARCH 2003

4 01-APR-03 APRIL 2003

5 01-MAY-03 MAY 2003

6 01-JUN-03 JUNE 2003

7 01-JUL-03 JULY 2003

8 01-AUG-03 AUGUST 2003

9 01-SEP-03 SEPTEMBER 2003

10 01-OCT-03 OCTOBER 2003

11 01-NOV-03 NOVEMBER 2003

12 01-DEC-03 DECEMBER 2003

13 01-JAN-04 JANUARY 2004

14 01-FEB-04 FEBRUARY 2004

14 rows selected.

SQL> --- +rollup+

SQL> --- ROLLUP qty and amount per quarter and product

SQL> select

2 decode(GROUPING(to_char(month,'YYYY-Q')),1,'All Quarters',

3 to_char(month,'YYYY-Q')) as month,

4 decode(GROUPING(product_desc),1,'All Products',product_desc) as product,

5 sum(qty) qty, sum(qty * price) amount

6 from prod

7 group by ROLLUP (to_char(month,'YYYY-Q'), product_desc);

MONTH PRODUCT QTY AMOUNT

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

2003-1 linear 9 87.4

2003-1 geometric 10 4750

2003-1 very stable 30 240

2003-1 lineal sparce 5 63

2003-1 All Products 54 5140.4

2003-2 linear 27 247.6

2003-2 geometric 59 3218.75

2003-2 very stable 30 250

2003-2 lineal sparce 6 96

2003-2 All Products 122 3812.35

2003-3 linear 45 386.2

2003-3 geometric 451 3028.31

2003-3 very stable 28 223

2003-3 lineal sparce 16 290

2003-3 All Products 540 3927.51

2003-4 linear 63 503.2

2003-4 geometric 3587 3008.86

2003-4 very stable 31 247

2003-4 lineal sparce 23 495

2003-4 All Products 3704 4254.06

All Quarters All Products 4420 17134.32

21 rows selected.

SQL> -- +cube+

SQL> --- CUBE qty and amount per quarter and product

SQL> select

2 decode(GROUPING(to_char(month,'YYYY-Q')),1,'All Quarters',

3 to_char(month,'YYYY-Q')) as month,

4 decode(GROUPING(product_desc),1,'All Products',product_desc) as product,

5 sum(qty) qty, sum(qty * price) amount

6 from prod

7 group by CUBE (to_char(month,'YYYY-Q'), product_desc);

MONTH PRODUCT QTY AMOUNT

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

All Quarters All Products 4420 17134.32

All Quarters linear 144 1224.4

All Quarters geometric 4107 14005.92

All Quarters very stable 119 960

All Quarters lineal sparce 50 944

2003-1 All Products 54 5140.4

2003-1 linear 9 87.4

2003-1 geometric 10 4750

2003-1 very stable 30 240

2003-1 lineal sparce 5 63

2003-2 All Products 122 3812.35

2003-2 linear 27 247.6

2003-2 geometric 59 3218.75

2003-2 very stable 30 250

2003-2 lineal sparce 6 96

2003-3 All Products 540 3927.51

2003-3 linear 45 386.2

2003-3 geometric 451 3028.31

2003-3 very stable 28 223

2003-3 lineal sparce 16 290

2003-4 All Products 3704 4254.06

2003-4 linear 63 503.2

2003-4 geometric 3587 3008.86

2003-4 very stable 31 247

2003-4 lineal sparce 23 495

25 rows selected.

SQL> -- +cube2+

SQL> --- CUBE qty and amount per quarter and product - subtotals only

SQL> select

2 decode(GROUPING(to_char(month,'YYYY-Q')),1,'All Quarters',

3 to_char(month,'YYYY-Q')) as month,

4 decode(GROUPING(product_desc),1,'All Products',product_desc) as product,

5 sum(qty) qty, sum(qty * price) amount

6 from prod

7 group by CUBE (to_char(month,'YYYY-Q'), product_desc)

8 having GROUPING(to_char(month,'YYYY-Q')) + GROUPING(product_desc) >= 1;

MONTH PRODUCT QTY AMOUNT

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

All Quarters All Products 4420 17134.32

All Quarters linear 144 1224.4

All Quarters geometric 4107 14005.92

All Quarters very stable 119 960

All Quarters lineal sparce 50 944

2003-1 All Products 54 5140.4

2003-2 All Products 122 3812.35

2003-3 All Products 540 3927.51

2003-4 All Products 3704 4254.06

9 rows selected.

SQL> -- +runsum+

SQL> ---- runing sums (balance)

SQL> select

2 month, product_desc,

3 qty,

4 sum(qty) over(partition by month) total_month, -- total qty in month

5 sum(qty) over(order by month) running_total, -- total qty from the beginning

6 sum(qty) over (partition by product_desc order by month) running_total_product

7 -- total per product from beginning

8 from prod

9 order by product_desc,month;

MONTH PRODUCT_DESC QTY TOTAL_MONTH RUNNING_TOTAL RUNNING_TOTAL_PRODUCT

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

01-JAN-03 geometric 2 13 13 2

01-FEB-03 geometric 3 19 32 5

01-MAR-03 geometric 5 22 54 10

01-APR-03 geometric 9 26 80 19

01-MAY-03 geometric 17 35 115 36

01-JUN-03 geometric 33 61 176 69

01-JUL-03 geometric 65 94 270 134

01-AUG-03 geometric 129 154 424 263

01-SEP-03 geometric 257 292 716 520

01-OCT-03 geometric 513 542 1258 1033

01-NOV-03 geometric 1025 1068 2326 2058

01-DEC-03 geometric 2049 2094 4420 4107

01-FEB-03 lineal sparce 2 19 32 2

01-MAR-03 lineal sparce 3 22 54 5

01-JUN-03 lineal sparce 6 61 176 11

01-JUL-03 lineal sparce 7 94 270 18

01-SEP-03 lineal sparce 9 292 716 27

01-NOV-03 lineal sparce 11 1068 2326 38

01-DEC-03 lineal sparce 12 2094 4420 50

01-JAN-03 linear 1 13 13 1

01-FEB-03 linear 3 19 32 4

01-MAR-03 linear 5 22 54 9

01-APR-03 linear 7 26 80 16

01-MAY-03 linear 9 35 115 25

01-JUN-03 linear 11 61 176 36

01-JUL-03 linear 13 94 270 49

01-AUG-03 linear 15 154 424 64

01-SEP-03 linear 17 292 716 81

01-OCT-03 linear 19 542 1258 100

01-NOV-03 linear 21 1068 2326 121

01-DEC-03 linear 23 2094 4420 144

01-JAN-03 very stable 10 13 13 10

01-FEB-03 very stable 11 19 32 21

01-MAR-03 very stable 9 22 54 30

01-APR-03 very stable 10 26 80 40

01-MAY-03 very stable 9 35 115 49

01-JUN-03 very stable 11 61 176 60

01-JUL-03 very stable 9 94 270 69

01-AUG-03 very stable 10 154 424 79

01-SEP-03 very stable 9 292 716 88

01-OCT-03 very stable 10 542 1258 98

01-NOV-03 very stable 11 1068 2326 109

01-DEC-03 very stable 10 2094 4420 119

43 rows selected.

SQL> -- +perc+

SQL> --- percentage and additive percetage

SQL> select

2 month, product_desc, qty,

3 round(rtr*100,2) as perc_total,round(rtr_sum*100,2) as perc_r_total,

4 round(rtr_prod*100,2) as perc_prod,round(rtr_prod_sum*100,2) as perc_prod_r_total

5 from (

6 select

7 month, product_desc,

8 qty, rtr,

9 ---

10 -- additive percentage from the beginning

11 sum(rtr) over(order by product_desc, month) rtr_sum,

12 rtr_prod,

13 sum(rtr_prod) over(partition by product_desc order by month) rtr_prod_sum

14 from (

15 select

16 month, product_desc,

17 qty,

18 -- percentage of qty in total qty of the two products

19 RATIO_TO_REPORT(qty) over() rtr,

20 -- percentage of qty in total qty per product

21 RATIO_TO_REPORT(qty) over(partition by product_desc) rtr_prod

22 from prod

23 where product_desc IN ('geometric','linear')

24 ))

25 order by product_desc, month;

MONTH PRODUCT_DESC QTY PERC_TOTAL PERC_R_TOTAL PERC_PROD PERC_PROD_R_TOTAL

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

01-JAN-03 geometric 2 .05 .05 .05 .05

01-FEB-03 geometric 3 .07 .12 .07 .12

01-MAR-03 geometric 5 .12 .24 .12 .24

01-APR-03 geometric 9 .21 .45 .22 .46

01-MAY-03 geometric 17 .4 .85 .41 .88

01-JUN-03 geometric 33 .78 1.62 .8 1.68

01-JUL-03 geometric 65 1.53 3.15 1.58 3.26

01-AUG-03 geometric 129 3.03 6.19 3.14 6.4

01-SEP-03 geometric 257 6.05 12.23 6.26 12.66

01-OCT-03 geometric 513 12.07 24.3 12.49 25.15

01-NOV-03 geometric 1025 24.11 48.41 24.96 50.11

01-DEC-03 geometric 2049 48.2 96.61 49.89 100

01-JAN-03 linear 1 .02 96.64 .69 .69

01-FEB-03 linear 3 .07 96.71 2.08 2.78

01-MAR-03 linear 5 .12 96.82 3.47 6.25

01-APR-03 linear 7 .16 96.99 4.86 11.11

01-MAY-03 linear 9 .21 97.2 6.25 17.36

01-JUN-03 linear 11 .26 97.46 7.64 25

01-JUL-03 linear 13 .31 97.77 9.03 34.03

01-AUG-03 linear 15 .35 98.12 10.42 44.44

01-SEP-03 linear 17 .4 98.52 11.81 56.25

01-OCT-03 linear 19 .45 98.96 13.19 69.44

01-NOV-03 linear 21 .49 99.46 14.58 84.03

01-DEC-03 linear 23 .54 100 15.97 100

24 rows selected.

SQL> -- interpretation of the colums

SQL> -- QTY - quantity o fteh prduct sold in the month

SQL> -- perc_total - QTY / total sale of this product in percent

SQL> -- perc_r_total - sum of perc_total form the beginning

SQL> -- perc_prod - QTY / total sale of both selected products in percent

SQL> -- perc_prod_r_total - sum of perc_prod form the beginning

SQL> --

SQL> -- +perctriv+

SQL> -- percentage trivial computation

SQL> select

2 product_desc, month, qty,

3 round(100 * qty / sum(qty) over(partition by product_desc),3) as prod_proc

4 from prod

5 order by product_desc, qty;

PRODUCT_DESC MONTH QTY PROD_PROC

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

geometric 01-JAN-03 2 .049

geometric 01-FEB-03 3 .073

geometric 01-MAR-03 5 .122

geometric 01-APR-03 9 .219

geometric 01-MAY-03 17 .414

geometric 01-JUN-03 33 .804

geometric 01-JUL-03 65 1.583

geometric 01-AUG-03 129 3.141

geometric 01-SEP-03 257 6.258

geometric 01-OCT-03 513 12.491

geometric 01-NOV-03 1025 24.957

geometric 01-DEC-03 2049 49.89

lineal sparce 01-FEB-03 2 4

lineal sparce 01-MAR-03 3 6

lineal sparce 01-JUN-03 6 12

lineal sparce 01-JUL-03 7 14

lineal sparce 01-SEP-03 9 18

lineal sparce 01-NOV-03 11 22

lineal sparce 01-DEC-03 12 24

linear 01-JAN-03 1 .694

linear 01-FEB-03 3 2.083

linear 01-MAR-03 5 3.472

linear 01-APR-03 7 4.861

linear 01-MAY-03 9 6.25

linear 01-JUN-03 11 7.639

linear 01-JUL-03 13 9.028

linear 01-AUG-03 15 10.417

linear 01-SEP-03 17 11.806

linear 01-OCT-03 19 13.194

linear 01-NOV-03 21 14.583

linear 01-DEC-03 23 15.972

very stable 01-MAR-03 9 7.563

very stable 01-SEP-03 9 7.563

very stable 01-MAY-03 9 7.563

very stable 01-JUL-03 9 7.563

very stable 01-JAN-03 10 8.403

very stable 01-OCT-03 10 8.403

very stable 01-DEC-03 10 8.403

very stable 01-AUG-03 10 8.403

very stable 01-APR-03 10 8.403

very stable 01-FEB-03 11 9.244

very stable 01-NOV-03 11 9.244

very stable 01-JUN-03 11 9.244

43 rows selected.

SQL> -- +rank+

SQL> --- ranking

SQL> --- top 5 months per product

SQL> select * from (

2 select

3 month, product_desc,qty * price as amount,

4 RANK() OVER (partition by product_desc order by (qty * price) desc) as prod_rank

5 from prod

6 )

7 where prod_rank<=5

8 order by product_desc, prod_rank;

MONTH PRODUCT_DESC AMOUNT PROD_RANK

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

01-JAN-03 geometric 2000 1

01-FEB-03 geometric 1500 2

01-MAR-03 geometric 1250 3

01-APR-03 geometric 1125 4

01-MAY-03 geometric 1062.5 5

01-DEC-03 lineal sparce 264 1

01-NOV-03 lineal sparce 231 2

01-SEP-03 lineal sparce 171 3

01-JUL-03 lineal sparce 119 4

01-JUN-03 lineal sparce 96 5

01-DEC-03 linear 179.4 1

01-NOV-03 linear 168 2

01-OCT-03 linear 155.8 3

01-SEP-03 linear 142.8 4

01-AUG-03 linear 129 5

01-APR-03 very stable 90 1

01-OCT-03 very stable 90 1

01-FEB-03 very stable 88 3

01-JUN-03 very stable 88 3

01-SEP-03 very stable 81 5

20 rows selected.

SQL> -- +morerank+

SQL> ----- get more with dense_rank

SQL> --- consider the tie break rules on very stable product

SQL> select * from (

2 select

3 month, product_desc,qty * price as amount,

4 DENSE_RANK() OVER (partition by product_desc order by (qty * price) desc) as prod_rank

5 from prod

6 )

7 where prod_rank<=5

8 order by product_desc, prod_rank;

MONTH PRODUCT_DESC AMOUNT PROD_RANK

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

01-JAN-03 geometric 2000 1

01-FEB-03 geometric 1500 2

01-MAR-03 geometric 1250 3

01-APR-03 geometric 1125 4

01-MAY-03 geometric 1062.5 5

01-DEC-03 lineal sparce 264 1

01-NOV-03 lineal sparce 231 2

01-SEP-03 lineal sparce 171 3

01-JUL-03 lineal sparce 119 4

01-JUN-03 lineal sparce 96 5

01-DEC-03 linear 179.4 1

01-NOV-03 linear 168 2

01-OCT-03 linear 155.8 3

01-SEP-03 linear 142.8 4

01-AUG-03 linear 129 5

01-APR-03 very stable 90 1

01-OCT-03 very stable 90 1

01-FEB-03 very stable 88 2

01-JUN-03 very stable 88 2

01-SEP-03 very stable 81 3

01-JAN-03 very stable 80 4

01-DEC-03 very stable 80 4

01-NOV-03 very stable 77 5

23 rows selected.

SQL> --- +surerank+

SQL> ---- use row_number if you need ranking 1 to 5 exactly

SQL> select * from (

2 select

3 month, product_desc,qty * price as amount,

4 ROW_NUMBER() OVER (partition by product_desc order by (qty * price) desc, month) as prod_rank

5 from prod

6 )

7 where prod_rank<=5

8 order by product_desc, prod_rank;

MONTH PRODUCT_DESC AMOUNT PROD_RANK

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

01-JAN-03 geometric 2000 1

01-FEB-03 geometric 1500 2

01-MAR-03 geometric 1250 3

01-APR-03 geometric 1125 4

01-MAY-03 geometric 1062.5 5

01-DEC-03 lineal sparce 264 1

01-NOV-03 lineal sparce 231 2

01-SEP-03 lineal sparce 171 3

01-JUL-03 lineal sparce 119 4

01-JUN-03 lineal sparce 96 5

01-DEC-03 linear 179.4 1

01-NOV-03 linear 168 2

01-OCT-03 linear 155.8 3

01-SEP-03 linear 142.8 4

01-AUG-03 linear 129 5

01-APR-03 very stable 90 1

01-OCT-03 very stable 90 2

01-FEB-03 very stable 88 3

01-JUN-03 very stable 88 4

01-SEP-03 very stable 81 5

20 rows selected.

SQL> --- +max+

SQL> ---- max - find the best month per product

SQL> ---

SQL> select

2 month, product_desc,amount

3 from (

4 select

5 month, product_desc,qty*price as amount,

6 MAX(qty*price) OVER (partition by product_desc) as max_amount

7 from prod

8 ) where amount = max_amount;

MONTH PRODUCT_DESC AMOUNT

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

01-JAN-03 geometric 2000

01-DEC-03 lineal sparce 264

01-DEC-03 linear 179.4

01-APR-03 very stable 90

01-OCT-03 very stable 90

SQL> ---- be carefull, if there are dups you can receive more records!

SQL> select

2 month, product_desc,amount

3 from (

4 select

5 month, product_desc,qty*price as amount,

6 MIN(qty*price) OVER (partition by product_desc) as min_amount

7 from prod

8 ) where amount = min_amount;

MONTH PRODUCT_DESC AMOUNT

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

01-OCT-03 geometric 1000.35

01-FEB-03 lineal sparce 24

01-JAN-03 linear 10

01-AUG-03 very stable 70

SQL> -- +suremax+

SQL> --- use ROW_NUMBER in that case

SQL> select

2 month, product_desc,amount

3 from (

4 select

5 month, product_desc,qty*price as amount,

6 ROW_NUMBER() OVER (partition by product_desc order by qty*price desc,month) as rn

7 from prod

8 ) where rn = 1;

MONTH PRODUCT_DESC AMOUNT

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

01-JAN-03 geometric 2000

01-DEC-03 lineal sparce 264

01-DEC-03 linear 179.4

01-APR-03 very stable 90

SQL> -- +lastnext+

SQL> ---- last and next value

SQL> select

2 month, product_desc,qty,

3 qty_lag, qty_lead,

4 qty-qty_lag as diff_last_month,

5 qty_lead - qty as diff_next_month

6 from (

7 select

8 month, product_desc, qty,

9 LAG(qty,1,0) OVER (partition by product_desc order by month) as qty_lag,

10 LEAD(qty,1,0) OVER (partition by product_desc order by month) as qty_lead

11 from prod

12 )

13 order by product_desc, month;

MONTH PRODUCT_DESC QTY QTY_LAG QTY_LEAD DIFF_LAST_MONTH DIFF_NEXT_MONTH

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

01-JAN-03 geometric 2 0 3 2 1

01-FEB-03 geometric 3 2 5 1 2

01-MAR-03 geometric 5 3 9 2 4

01-APR-03 geometric 9 5 17 4 8

01-MAY-03 geometric 17 9 33 8 16

01-JUN-03 geometric 33 17 65 16 32

01-JUL-03 geometric 65 33 129 32 64

01-AUG-03 geometric 129 65 257 64 128

01-SEP-03 geometric 257 129 513 128 256

01-OCT-03 geometric 513 257 1025 256 512

01-NOV-03 geometric 1025 513 2049 512 1024

01-DEC-03 geometric 2049 1025 0 1024 -2049

01-FEB-03 lineal sparce 2 0 3 2 1

01-MAR-03 lineal sparce 3 2 6 1 3

01-JUN-03 lineal sparce 6 3 7 3 1

01-JUL-03 lineal sparce 7 6 9 1 2

01-SEP-03 lineal sparce 9 7 11 2 2

01-NOV-03 lineal sparce 11 9 12 2 1

01-DEC-03 lineal sparce 12 11 0 1 -12

01-JAN-03 linear 1 0 3 1 2

01-FEB-03 linear 3 1 5 2 2

01-MAR-03 linear 5 3 7 2 2

01-APR-03 linear 7 5 9 2 2

01-MAY-03 linear 9 7 11 2 2

01-JUN-03 linear 11 9 13 2 2

01-JUL-03 linear 13 11 15 2 2

01-AUG-03 linear 15 13 17 2 2

01-SEP-03 linear 17 15 19 2 2

01-OCT-03 linear 19 17 21 2 2

01-NOV-03 linear 21 19 23 2 2

01-DEC-03 linear 23 21 0 2 -23

01-JAN-03 very stable 10 0 11 10 1

01-FEB-03 very stable 11 10 9 1 -2

01-MAR-03 very stable 9 11 10 -2 1

01-APR-03 very stable 10 9 9 1 -1

01-MAY-03 very stable 9 10 11 -1 2

01-JUN-03 very stable 11 9 9 2 -2

01-JUL-03 very stable 9 11 10 -2 1

01-AUG-03 very stable 10 9 9 1 -1

01-SEP-03 very stable 9 10 10 -1 1

01-OCT-03 very stable 10 9 11 1 1

01-NOV-03 very stable 11 10 10 1 -1

01-DEC-03 very stable 10 11 0 -1 -10

43 rows selected.

SQL> -- +surenext+

SQL> --- caution - not valid if missing data

SQL> -- the same example using RANGE (i.e. logical offset instead of row offset)

SQL> select

2 month, product_desc,qty,

3 qty-qty_last_month as diff_last_month,

4 qty_next_month - qty as diff_next_month

5 from (

6 select

7 month, product_desc, qty,

8 FIRST_VALUE(qty)

9 over (partition by product_desc order by month range interval '1' month preceding) as qty_last_month,

10 LAST_VALUE(qty)

11 over (partition by product_desc order by month range between current row and interval '1' month following) as qty_next_month

12 from prod

13 )

14 order by product_desc, month;

MONTH PRODUCT_DESC QTY DIFF_LAST_MONTH DIFF_NEXT_MONTH

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

01-JAN-03 geometric 2 0 1

01-FEB-03 geometric 3 1 2

01-MAR-03 geometric 5 2 4

01-APR-03 geometric 9 4 8

01-MAY-03 geometric 17 8 16

01-JUN-03 geometric 33 16 32

01-JUL-03 geometric 65 32 64

01-AUG-03 geometric 129 64 128

01-SEP-03 geometric 257 128 256

01-OCT-03 geometric 513 256 512

01-NOV-03 geometric 1025 512 1024

01-DEC-03 geometric 2049 1024 0

01-FEB-03 lineal sparce 2 0 1

01-MAR-03 lineal sparce 3 1 0

01-JUN-03 lineal sparce 6 0 1

01-JUL-03 lineal sparce 7 1 0

01-SEP-03 lineal sparce 9 0 0

01-NOV-03 lineal sparce 11 0 1

01-DEC-03 lineal sparce 12 1 0

01-JAN-03 linear 1 0 2

01-FEB-03 linear 3 2 2

01-MAR-03 linear 5 2 2

01-APR-03 linear 7 2 2

01-MAY-03 linear 9 2 2

01-JUN-03 linear 11 2 2

01-JUL-03 linear 13 2 2

01-AUG-03 linear 15 2 2

01-SEP-03 linear 17 2 2

01-OCT-03 linear 19 2 2

01-NOV-03 linear 21 2 2

01-DEC-03 linear 23 2 0

01-JAN-03 very stable 10 0 1

01-FEB-03 very stable 11 1 -2

01-MAR-03 very stable 9 -2 1

01-APR-03 very stable 10 1 -1

01-MAY-03 very stable 9 -1 2

01-JUN-03 very stable 11 2 -2

01-JUL-03 very stable 9 -2 1

01-AUG-03 very stable 10 1 -1

01-SEP-03 very stable 9 -1 1

01-OCT-03 very stable 10 1 1

01-NOV-03 very stable 11 1 -1

01-DEC-03 very stable 10 -1 0

43 rows selected.

SQL> -- +nullfirst+

SQL> ---- NULLS first/last

SQL> select

2 month.month_first_date month, product_desc,

3 qty,

4 ROW_NUMBER() OVER (order by qty NULLS LAST) as qty_order,

5 price,

6 ROW_NUMBER() OVER (order by price NULLS FIRST) as price_order

7 from (select * from prod where product_desc = 'lineal sparce') p

8 RIGHT OUTER JOIN month

9 ON (month.month_id = p.month_id)

10 order by 4;

MONTH PRODUCT_DESC QTY QTY_ORDER PRICE PRICE_ORDER

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

01-FEB-03 lineal sparce 2 1 12 8

01-MAR-03 lineal sparce 3 2 13 9

01-JUN-03 lineal sparce 6 3 16 10

01-JUL-03 lineal sparce 7 4 17 11

01-SEP-03 lineal sparce 9 5 19 12

01-NOV-03 lineal sparce 11 6 21 13

01-DEC-03 lineal sparce 12 7 22 14

01-JAN-03 8 1

01-APR-03 9 2

01-AUG-03 10 3

01-OCT-03 11 4

01-FEB-04 12 5

01-JAN-04 13 6

01-MAY-03 14 7

14 rows selected.

SQL> -- +3months+

SQL> --- 3 months sum

SQL> -- see '2' month: current month + 2 preceding give 3 months!

SQL> select

2 month, product_desc, qty,

3 SUM(qty)

4 over (partition by product_desc order by month range interval '2' month preceding) as qty_3_month

5 from prod

6 order by product_desc, month;

MONTH PRODUCT_DESC QTY QTY_3_MONTH

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

01-JAN-03 geometric 2 2

01-FEB-03 geometric 3 5

01-MAR-03 geometric 5 10

01-APR-03 geometric 9 17

01-MAY-03 geometric 17 31

01-JUN-03 geometric 33 59

01-JUL-03 geometric 65 115

01-AUG-03 geometric 129 227

01-SEP-03 geometric 257 451

01-OCT-03 geometric 513 899

01-NOV-03 geometric 1025 1795

01-DEC-03 geometric 2049 3587

01-FEB-03 lineal sparce 2 2

01-MAR-03 lineal sparce 3 5

01-JUN-03 lineal sparce 6 6

01-JUL-03 lineal sparce 7 13

01-SEP-03 lineal sparce 9 16

01-NOV-03 lineal sparce 11 20

01-DEC-03 lineal sparce 12 23

01-JAN-03 linear 1 1

01-FEB-03 linear 3 4

01-MAR-03 linear 5 9

01-APR-03 linear 7 15

01-MAY-03 linear 9 21

01-JUN-03 linear 11 27

01-JUL-03 linear 13 33

01-AUG-03 linear 15 39

01-SEP-03 linear 17 45

01-OCT-03 linear 19 51

01-NOV-03 linear 21 57

01-DEC-03 linear 23 63

01-JAN-03 very stable 10 10

01-FEB-03 very stable 11 21

01-MAR-03 very stable 9 30

01-APR-03 very stable 10 30

01-MAY-03 very stable 9 28

01-JUN-03 very stable 11 30

01-JUL-03 very stable 9 29

01-AUG-03 very stable 10 30

01-SEP-03 very stable 9 28

01-OCT-03 very stable 10 29

01-NOV-03 very stable 11 30

01-DEC-03 very stable 10 31

43 rows selected.

SQL> -- +runavg+

SQL> -- running AVG 3 months

SQL> select

2 month, product_desc, qty,

3 round(AVG(qty)

4 over (partition by product_desc

5 order by month range interval '2' month preceding),1) as avg_qty_3_month

6 from prod

7 order by product_desc, month;

MONTH PRODUCT_DESC QTY AVG_QTY_3_MONTH

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

01-JAN-03 geometric 2 2

01-FEB-03 geometric 3 2.5

01-MAR-03 geometric 5 3.3

01-APR-03 geometric 9 5.7

01-MAY-03 geometric 17 10.3

01-JUN-03 geometric 33 19.7

01-JUL-03 geometric 65 38.3

01-AUG-03 geometric 129 75.7

01-SEP-03 geometric 257 150.3

01-OCT-03 geometric 513 299.7

01-NOV-03 geometric 1025 598.3

01-DEC-03 geometric 2049 1195.7

01-FEB-03 lineal sparce 2 2

01-MAR-03 lineal sparce 3 2.5

01-JUN-03 lineal sparce 6 6

01-JUL-03 lineal sparce 7 6.5

01-SEP-03 lineal sparce 9 8

01-NOV-03 lineal sparce 11 10

01-DEC-03 lineal sparce 12 11.5

01-JAN-03 linear 1 1

01-FEB-03 linear 3 2

01-MAR-03 linear 5 3

01-APR-03 linear 7 5

01-MAY-03 linear 9 7

01-JUN-03 linear 11 9

01-JUL-03 linear 13 11

01-AUG-03 linear 15 13

01-SEP-03 linear 17 15

01-OCT-03 linear 19 17

01-NOV-03 linear 21 19

01-DEC-03 linear 23 21

01-JAN-03 very stable 10 10

01-FEB-03 very stable 11 10.5

01-MAR-03 very stable 9 10

01-APR-03 very stable 10 10

01-MAY-03 very stable 9 9.3

01-JUN-03 very stable 11 10

01-JUL-03 very stable 9 9.7

01-AUG-03 very stable 10 10

01-SEP-03 very stable 9 9.3

01-OCT-03 very stable 10 9.7

01-NOV-03 very stable 11 10

01-DEC-03 very stable 10 10.3

43 rows selected.

SQL> -- +dsinterval+

SQL> --- interval definition

SQL> -- 4 days 5 hours + 12 minutes + 10.222 seconds

SQL> select to_timestamp('01-01-2003','dd-mm-yyyy hh24:mi:ss.ff') +

2 INTERVAL '4 5:12:10.222' DAY TO SECOND(3) as tstamp

3 from dual;

TSTAMP

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

05-JAN-03 05.12.10.222000000 AM

SQL> -- 4 days 5 hours + 12 minutes

SQL> select to_date('01-01-2003','dd-mm-yyyy') +

2 INTERVAL '4 5:12' DAY TO MINUTE as date_d

3 from dual;

DATE_D

---------

05-JAN-03

SQL> -- 400 days + 5 hours

SQL> -- if day-precision > 2, the precision must be defined explicitely

SQL> select to_date('01-01-2003','dd-mm-yyyy') +

2 INTERVAL '400 5' DAY(3) TO HOUR as date_d

3 from dual;

DATE_D

---------

05-FEB-04

SQL> -- 400 days

SQL> select to_date('01-01-2003','dd-mm-yyyy') + INTERVAL '400' DAY(3) as date_d

2 from dual;

DATE_D

---------

05-FEB-04

SQL> -- +yminterval+

SQL> --- interval definition

SQL> -- 4 years + 1 month

SQL> select to_date('01-01-2003','dd-mm-yyyy') +

2 INTERVAL '4-1' YEAR TO MONTH as date_d

3 from dual;

DATE_D

---------

01-FEB-07

SQL> -- 4 years

SQL> select to_date('01-01-2003','dd-mm-yyyy') + INTERVAL '4' YEAR as date_d

2 from dual;

DATE_D

---------

01-JAN-07

SQL> -- +intkonv+

SQL> --- interval conversion

SQL> select to_date('01-01-2003','dd-mm-yyyy') + NUMTOYMINTERVAL(4,'year') as date_d

2 from dual;

DATE_D

---------

01-JAN-07

SQL> --

SQL> select to_date('01-01-2003','dd-mm-yyyy') +

2 NUMTODSINTERVAL(100, 'day') as date_d

3 from dual;

DATE_D

---------

11-APR-03

SQL> --

SQL> select to_date('01-01-2003','dd-mm-yyyy') +

2 NUMTODSINTERVAL(100, 'second') as date_d

3 from dual;

DATE_D

---------

01-JAN-03

SQL> -- +top25+

SQL> -- top 25% products

SQL> --- cume dist (percentile)

SQL> -- top 25 percent of the products (that is, products whose sales amount is

SQL> -- greater or equal than the amounts for 75 percent

SQL> -- of the other products).

SQL> select * from (

2 select

3 product_desc, sum(qty*price) amount,

4 round(100 * CUME_DIST() over(order by sum(qty*price) asc)) as prod_percentile

5 from prod

6 group by product_desc

7 ) where prod_percentile >= 75;

PRODUCT_DESC AMOUNT PROD_PERCENTILE

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

linear 1224.4 75

geometric 14005.92 100

SQL> -- the worst product (of four) has percentile 25% (i.e. 1/4)

SQL> -- +top25/2+

SQL> -- percentile of the month per product

SQL> -- top 25% of months per product sales

SQL> select * from (

2 select

3 product_desc, month, qty,

4 round(100 * CUME_DIST() over(partition by product_desc order by qty asc))

5 as prod_percentile

6 from prod

7 ) where prod_percentile >= 75

8 order by product_desc, 4;

PRODUCT_DESC MONTH QTY PROD_PERCENTILE

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

geometric 01-SEP-03 257 75

geometric 01-OCT-03 513 83

geometric 01-NOV-03 1025 92

geometric 01-DEC-03 2049 100

lineal sparce 01-NOV-03 11 86

lineal sparce 01-DEC-03 12 100

linear 01-SEP-03 17 75

linear 01-OCT-03 19 83

linear 01-NOV-03 21 92

linear 01-DEC-03 23 100

very stable 01-JAN-03 10 75

very stable 01-OCT-03 10 75

very stable 01-DEC-03 10 75

very stable 01-AUG-03 10 75

very stable 01-APR-03 10 75

very stable 01-FEB-03 11 100

very stable 01-NOV-03 11 100

very stable 01-JUN-03 11 100

18 rows selected.

SQL> -- +ntile+

SQL> --- ntile

SQL> -- divides the month per product in 5 groups based on qty

SQL> select

2 product_desc, month, qty,

3 ntile(5) over(partition by product_desc order by qty) as prod_ntile

4 from prod

5 order by product_desc, qty;

PRODUCT_DESC MONTH QTY PROD_NTILE

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

geometric 01-JAN-03 2 1

geometric 01-FEB-03 3 1

geometric 01-MAR-03 5 1

geometric 01-APR-03 9 2

geometric 01-MAY-03 17 2

geometric 01-JUN-03 33 2

geometric 01-JUL-03 65 3

geometric 01-AUG-03 129 3

geometric 01-SEP-03 257 4

geometric 01-OCT-03 513 4

geometric 01-NOV-03 1025 5

geometric 01-DEC-03 2049 5

lineal sparce 01-FEB-03 2 1

lineal sparce 01-MAR-03 3 1

lineal sparce 01-JUN-03 6 2

lineal sparce 01-JUL-03 7 2

lineal sparce 01-SEP-03 9 3

lineal sparce 01-NOV-03 11 4

lineal sparce 01-DEC-03 12 5

linear 01-JAN-03 1 1

linear 01-FEB-03 3 1

linear 01-MAR-03 5 1

linear 01-APR-03 7 2

linear 01-MAY-03 9 2

linear 01-JUN-03 11 2

linear 01-JUL-03 13 3

linear 01-AUG-03 15 3

linear 01-SEP-03 17 4

linear 01-OCT-03 19 4

linear 01-NOV-03 21 5

linear 01-DEC-03 23 5

very stable 01-MAR-03 9 1

very stable 01-SEP-03 9 1

very stable 01-MAY-03 9 1

very stable 01-JUL-03 9 2

very stable 01-JAN-03 10 2

very stable 01-OCT-03 10 2

very stable 01-DEC-03 10 3

very stable 01-AUG-03 10 3

very stable 01-APR-03 10 4

very stable 01-FEB-03 11 4

very stable 01-NOV-03 11 5

very stable 01-JUN-03 11 5

43 rows selected.

SQL> -- +hyprank+

SQL> --- hypothetical rank

SQL> -- which rank would yield the sell of 8 products

SQL> select

2 product_desc,

3 RANK(8) within group (order by qty) as hypo_rank

4 from prod

5 group by product_desc

6 order by product_desc;

PRODUCT_DESC HYPO_RANK

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

geometric 4

lineal sparce 5

linear 5

very stable 1

SQL> -- i.e. qty=8 would rank as 4th month

SQL> -- +getsnap+

SQL> --- get snapshot

SQL> -- i.e. take the nearest preceeding record to selected snapshot timestamp

SQL> select month, product_desc,qty,price

2 from (

3 select month, product_desc,qty,price,

4 row_number() over (partition by product_desc order by month desc) rn

5 from prod

6 where month <= to_date('01.04.2003','dd.mm.yyyy') --<<< your snapshot time here

7 ) where rn = 1

8 ;

MONTH PRODUCT_DESC QTY PRICE

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

01-APR-03 geometric 9 125

01-MAR-03 lineal sparce 3 13

01-APR-03 linear 7 9.4

01-APR-03 very stable 10 9

SQL> -- verify that you must get all products, event if tehre is no sell on selected

SQL> -- snapshot time

SQL> -- +ojoin+

SQL> -- left outer join

SQL> -- months without sales are returned only once, without relation to product

SQL> select product_desc,

2 month_first_date month,

3 nvl(qty,0) qty

4 from month left outer join

5 prod

6 on ( month.month_first_date =

7 prod.month )

8 order by product_desc, month;

PRODUCT_DESC MONTH QTY

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

geometric 01-JAN-03 2

geometric 01-FEB-03 3

geometric 01-MAR-03 5

geometric 01-APR-03 9

geometric 01-MAY-03 17

geometric 01-JUN-03 33

geometric 01-JUL-03 65

geometric 01-AUG-03 129

geometric 01-SEP-03 257

geometric 01-OCT-03 513

geometric 01-NOV-03 1025

geometric 01-DEC-03 2049

lineal sparce 01-FEB-03 2

lineal sparce 01-MAR-03 3

lineal sparce 01-JUN-03 6

lineal sparce 01-JUL-03 7

lineal sparce 01-SEP-03 9

lineal sparce 01-NOV-03 11

lineal sparce 01-DEC-03 12

linear 01-JAN-03 1

linear 01-FEB-03 3

linear 01-MAR-03 5

linear 01-APR-03 7

linear 01-MAY-03 9

linear 01-JUN-03 11

linear 01-JUL-03 13

linear 01-AUG-03 15

linear 01-SEP-03 17

linear 01-OCT-03 19

linear 01-NOV-03 21

linear 01-DEC-03 23

very stable 01-JAN-03 10

very stable 01-FEB-03 11

very stable 01-MAR-03 9

very stable 01-APR-03 10

very stable 01-MAY-03 9

very stable 01-JUN-03 11

very stable 01-JUL-03 9

very stable 01-AUG-03 10

very stable 01-SEP-03 9

very stable 01-OCT-03 10

very stable 01-NOV-03 11

very stable 01-DEC-03 10

01-JAN-04 0

01-FEB-04 0

45 rows selected.

SQL> -- +pojoin+

SQL> --- partitioned join by product

SQL> --- for aech month all products are returned

SQL> select product_desc,

2 month_first_date month,

3 nvl(qty,0) qty

4 from month left outer join

5 prod partition by (product_desc)

6 on ( month.month_first_date =

7 prod.month )

8 order by product_desc, month_first_date;

PRODUCT_DESC MONTH QTY

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

geometric 01-JAN-03 2

geometric 01-FEB-03 3

geometric 01-MAR-03 5

geometric 01-APR-03 9

geometric 01-MAY-03 17

geometric 01-JUN-03 33

geometric 01-JUL-03 65

geometric 01-AUG-03 129

geometric 01-SEP-03 257

geometric 01-OCT-03 513

geometric 01-NOV-03 1025

geometric 01-DEC-03 2049

geometric 01-JAN-04 0

geometric 01-FEB-04 0

lineal sparce 01-JAN-03 0

lineal sparce 01-FEB-03 2

lineal sparce 01-MAR-03 3

lineal sparce 01-APR-03 0

lineal sparce 01-MAY-03 0

lineal sparce 01-JUN-03 6

lineal sparce 01-JUL-03 7

lineal sparce 01-AUG-03 0

lineal sparce 01-SEP-03 9

lineal sparce 01-OCT-03 0

lineal sparce 01-NOV-03 11

lineal sparce 01-DEC-03 12

lineal sparce 01-JAN-04 0

lineal sparce 01-FEB-04 0

linear 01-JAN-03 1

linear 01-FEB-03 3

linear 01-MAR-03 5

linear 01-APR-03 7

linear 01-MAY-03 9

linear 01-JUN-03 11

linear 01-JUL-03 13

linear 01-AUG-03 15

linear 01-SEP-03 17

linear 01-OCT-03 19

linear 01-NOV-03 21

linear 01-DEC-03 23

linear 01-JAN-04 0

linear 01-FEB-04 0

very stable 01-JAN-03 10

very stable 01-FEB-03 11

very stable 01-MAR-03 9

very stable 01-APR-03 10

very stable 01-MAY-03 9

very stable 01-JUN-03 11

very stable 01-JUL-03 9

very stable 01-AUG-03 10

very stable 01-SEP-03 9

very stable 01-OCT-03 10

very stable 01-NOV-03 11

very stable 01-DEC-03 10

very stable 01-JAN-04 0

very stable 01-FEB-04 0

56 rows selected.

SQL> --

SQL> -- +initprice+

SQL> -- initial and final price per product

SQL> select product_desc,

2 to_char(min(month),'mm-yyyy') first_month,

3 min(price) -- as month is unique within product_id, the function plays no role,

4 -- you may use max, sum, avg with the same result

5 KEEP (dense_rank FIRST order by month) init_price,

6 avg(price) -- verify it usign avg, getting same result ...

7 KEEP (dense_rank FIRST order by month) init_price_same_result,

8 to_char(max(month),'mm-yyyy') last_month,

9 min(price) KEEP (dense_rank LAST order by month) final_price

10 from prod a

11 group by product_desc;

PRODUCT_DESC FIRST_M INIT_PRICE INIT_PRICE_SAME_RESULT LAST_MO FINAL_PRICE

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

geometric 01-2003 1000 1000 12-2003 .49

lineal sparce 02-2003 12 12 12-2003 22

linear 01-2003 10 10 12-2003 7.8

very stable 01-2003 8 8 12-2003 8

SQL> -- +sample+

SQL> ---- sample columns in group by queries

SQL> ---- sample product with price per month

SQL> ---- important bith product and price are select from one record!

SQL> ---- i.e. NOT min(price), min(product_desc)

SQL> select month,

2 min(product_desc) KEEP (dense_rank FIRST

3 order by month, -- group by columns and ..

4 price, product_desc -- sample columns

5 ) as sample_prod,

6 min(price)

7 KEEP (dense_rank FIRST order by month,price, product_desc) as sample_price,

8 --- another sample

9 min(product_desc)

10 KEEP (dense_rank LAST order by month,price, product_desc) as sample_prod2,

11 min(price) KEEP (dense_rank LAST

12 order by month, price, product_desc) as sample_price2

13 from prod

14 group by month;

MONTH SAMPLE_PROD SAMPLE_PRICE SAMPLE_PROD2 SAMPLE_PRICE2

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

01-JAN-03 very stable 8 geometric 1000

01-FEB-03 very stable 8 geometric 500

01-MAR-03 very stable 8 geometric 250

01-APR-03 very stable 9 geometric 125

01-MAY-03 very stable 8 geometric 62.5

01-JUN-03 very stable 8 geometric 31.25

01-JUL-03 very stable 8 lineal sparce 17

01-AUG-03 very stable 7 linear 8.6

01-SEP-03 geometric 3.91 lineal sparce 19

01-OCT-03 geometric 1.95 very stable 9

01-NOV-03 geometric .98 lineal sparce 21

01-DEC-03 geometric .49 lineal sparce 22

12 rows selected.

SQL> -- and quit we are ready..

SQL> quit;