Spreadsheet like calcing (Using MODEL clause)

Oracle 10g introduced MODEL clause to simulate spreadsheet like calculations in SQL . It is complex but yet powerful . There us a chapter on this clause in the dataware housing guide .

I  really wish this ( along with analytics)  should be moved to Application Developers Guide

Here is an example ... In the following example , we create a new column called new_sal , and give all the employees 50% rise ( .. in this economy , this will be a jackpot... )

SQL>  SELECT empno,ename , deptno, sal , new_sal
  2   FROM scott.emp
  3   MODEL
  4   PARTITION BY (empno , ename , sal)
  5   DIMENSION BY (deptno)
  6   MEASURES (sal new_sal) IGNORE NAV
  7   RULES ( new_sal[ANY] = CV(sal) * 1.5)
  8   ORDER BY deptno;

     EMPNO ENAME          DEPTNO        SAL    NEW_SAL
---------- ---------- ---------- ---------- ----------
      7782 CLARK              10       2450       3675
      7934 MILLER             10       1300       1950
      7839 KING               10       5000       7500
      7902 FORD               20       3000       4500
      7566 JONES              20       2975     4462.5
      7788 SCOTT              20       3000       4500
      7876 ADAMS              20       1100       1650
      7369 SMITH              20        800       1200
      7844 TURNER             30       1500       2250
      7900 JAMES              30        950       1425
      7698 BLAKE              30       2850       4275
      7521 WARD               30       1250       1875
      7499 ALLEN              30       1600       2400
      7654 MARTIN             30       1250       1875


14 rows selected.

Now , I can create additional records ( to display... it is not saved into database yet ) by the following SQL . Here , I am assigning all the employees to dept 10 ( in addition to their departments).  

For example , you can see the employee highlighted in both depts ( 20 and 10) 

  1   SELECT empno,ename , deptno, sal , new_sal
  2   FROM scott.emp
  3   MODEL
  4   PARTITION BY (empno , ename , sal)
  5   DIMENSION BY (deptno)
  6   MEASURES (sal new_sal) IGNORE NAV
  7   RULES ( new_sal[10] = CV(sal) * 1.5)
  8*  ORDER BY deptno
SQL> /

     EMPNO ENAME          DEPTNO        SAL    NEW_SAL
---------- ---------- ---------- ---------- ----------
      7934 MILLER             10       1300       1950
      7902 FORD               10       3000       4500
      7844 TURNER             10       1500       2250
      7788 SCOTT              10       3000       4500
      7900 JAMES              10        950       1425
      7698 BLAKE              10       2850       4275
      7369 SMITH              10        800       1200
      7654 MARTIN             10       1250       1875
      7876 ADAMS              10       1100       1650
      7782 CLARK              10       2450       3675
      7521 WARD               10       1250       1875
      7839 KING               10       5000       7500
      7499 ALLEN              10       1600       2400
      7566 JONES              10       2975     4462.5
      7902 FORD               20       3000       3000
      7369 SMITH              20        800        800
      7788 SCOTT              20       3000       3000
      7566 JONES              20       2975       2975
      7876 ADAMS              20       1100       1100
      7521 WARD               30       1250       1250
      7499 ALLEN              30       1600       1600
      7900 JAMES              30        950        950
      7844 TURNER             30       1500       1500
      7654 MARTIN             30       1250       1250
      7698 BLAKE              30       2850       2850

25 rows selected.

If I am interested only the records that were impacted by this rule , I can run the following SQL.

  1   SELECT empno,ename , deptno, sal , new_sal
  2   FROM scott.emp
  3   MODEL return updated rows
  4   PARTITION BY (empno , ename , sal)
  5   DIMENSION BY (deptno)
  6   MEASURES (sal new_sal) IGNORE NAV
  7   RULES ( new_sal[10] = CV(sal) * 1.5)
  8*  ORDER BY deptno
SQL> /

     EMPNO ENAME          DEPTNO        SAL    NEW_SAL
---------- ---------- ---------- ---------- ----------
      7934 MILLER             10       1300       1950
      7499 ALLEN              10       1600       2400
      7566 JONES              10       2975     4462.5
      7521 WARD               10       1250       1875
      7782 CLARK              10       2450       3675
      7876 ADAMS              10       1100       1650
      7839 KING               10       5000       7500
      7369 SMITH              10        800       1200
      7698 BLAKE              10       2850       4275
      7900 JAMES              10        950       1425
      7788 SCOTT              10       3000       4500
      7844 TURNER             10       1500       2250
      7902 FORD               10       3000       4500
      7654 MARTIN             10       1250       1875

14 rows selected.

You can go for iterations using MODEL clause. 

If I have a initial value of 500 and if reduce that value by 50% in each iteration , then at the 9th iteration , the values goes below 1.

SQL> SELECT value_below_breakeven , num_iterations, breakeven_value  FROM dual
  2   MODEL
  3   DIMENSION BY (1 breakeven_value)
  4   MEASURES (500 value_below_breakeven, 0 num_iterations)
  5   RULES ITERATE (20) UNTIL (value_below_breakeven[1]<=1)
  6  (value_below_breakeven[1] = value_below_breakeven[1]/2,
  7   num_iterations[1] = num_iterations[1] + 1);

VALUE_BELOW_BREAKEVEN NUM_ITERATIONS BREAKEVEN_VALUE
--------------------- -------------- ---------------
             .9765625              9               1
 
Here are the iteration and its value.

Iteration	Value
0	500
1	250
2	125
3	62.5
4	31.25
5	15.625
6	7.8125
7	3.90625
8	1.953125
9	0.976563

Good luck with the MODEL clause.