16. Juni 2008

Rechnen wie in Excel - in einer SQL-Abfrage

English title: Excel formulas in a SQL command: SQL Model clause

Wusstet Ihr schon, dass Ihr in einem SQL-Kommando auch Formeln wie in Excel verwenden könnt? Das gehtseit Oracle10g und zwar mit der SQL Model Clause. Hilfreich ist das vor allem, wenn Ihr sog. Interrow-Berechnungen durchführen - also in einer Zeile quasi auf eine andere zugreifen wollt. Das geht zwar auch mit analytischen Funktionen - die SQL Model Clause geht hier allerdings noch wesentlich weiter.
Did you know that the Oracle database can evaluate Excel formulas within a SQL query? Beginning with Oracle10g the SQL Model clause was introduced. The SQL Model clause is very helpful for interrow calculations - although this is also possible with analytic functions. But the SQL Model Clause allows much more ...
Beginnen wir mit einem einfachen Beispiel:
Let's start with a simple example:
select empno, ename, hiredate, dname, sal, comm 
from emp join dept using (deptno)
model
dimension by (empno)
measures (ename, hiredate, dname, sal, comm)
rules upsert (
  ename[7844] = 'Czarski',
  sal[7839] = sal[7844] + sal[7654],
  sal[9999] = avg(sal)[any]
)
Um das Ergebnis einer SQL-Abfrage wie ein 'Spreadsheet' zu behandeln, muss zunächst festgelegt werden, wie einzelne 'Zellen' angesprochen werden (Dimension) und welche Spalten die 'Zellen' sind (Measures). Das Beispiel legt fest, dass die Spalten EMPNO, ENAME, HIREDATE, DNAME, SAL und COMM als 'Zellen' fungieren sollen und die Zeilen über die EMPNO angesprochen werden.
The result set of a SQL query is now treated like a spreadsheet. For that one must first specify how the 'cells' of the spreadsheet are accessed (dimension) and which columns make up the 'cells'. In this example the columns EMPNO, ENAME, HIREDATE, DNAME, SAL and COMM are 'cells' and accessed via EMPNO.
Anschließend folgen die 'Formeln' als Rules - das Schlüsselwort UPSERT bedeutet, dass durch die Regeln sowohl "bestehende" Zeilen der SQL-Ergebnismenge verändert als auch neue Zeilen hinzugefügt werden können. Das Ergebnis obiger Abfrage sieht dann in etwa so aus:
After that the formulas are specified as rules - the keyword UPSERT determines that the Rules can modify existing rows as well as create new ones. The above SQL query returns the following result:
     EMPNO ENAME      HIREDATE DNAME                 SAL       COMM
---------- ---------- -------- -------------- ---------- ----------
      7782 CLARK      09.06.81 ACCOUNTING           2450
      7934 MILLER     23.01.82 ACCOUNTING           1300
      7566 JONES      02.04.81 RESEARCH             2975
      7902 FORD       03.12.81 RESEARCH             4000
      7876 ADAMS      23.05.87 RESEARCH             1100
      7369 SMITH      17.12.80 RESEARCH              800
      7788 SCOTT      19.04.87 RESEARCH             3000
      7521 WARD       22.02.81 SALES                1250        500
      7499 ALLEN      20.02.81 SALES                1600        300
      7900 JAMES      03.12.81 SALES                 950
      7698 BLAKE      01.05.81 SALES                2850
      7839 KING       17.11.81 ACCOUNTING           2750
      7844 Czarski    08.09.81 SALES                1500          0
      7654 MARTIN     28.09.81 SALES                1250       1400
      9999                                    1983,92857
Was die Formeln bewirken, ist hier rot auf gelb dargestellt. Die ersten beiden dürften selbsterklärend sein - die letzte Formel erzeugt quasi eine neue TZeile mit einer "EMPNO" von 9999 und dem Durchschnitt über alle Gehälter als SAL.
The result of the individual rules are marked red on yellow. The first two rules are self-explaining ; the third rule "creates" a new row and populates it with EMPNO=9999 and the average SAL (over all rows) as SAL.
Man kann mit der SQL Model Clause nun ganz nette Dinge tun ... wir berechnen nun einmal einen Tilgungsplan für ein Darlehen, nicht mit einer Table Function, sondern mit "Excel-Formeln": in SQL!
So the SQL Model Clause allows pretty nice calculations - let's do another example - calculating a loan: Not with a table function but with "Excel formulas" - in a SQL query!
select zeile, a, b, c, d, e, f
from dual
model
dimension by (rownum zeile)
measures (0 a, 0 b, 0 c, 0 d, 0 e, 0 f)
rules upsert iterate (300) (
 a[0]=200000,
 b[ITERATION_NUMBER]=a[cv(zeile)]*0.06/12,
 d[ITERATION_NUMBER]=1500,
 c[ITERATION_NUMBER]=d[cv(zeile)]-b[cv(zeile)],
 a[ITERATION_NUMBER+1]=a[cv(zeile)-1] - c[cv(zeile)-1]
)
order by zeile
Diese Formeln werden nun durch das ITERATE-Schlüsselwort 300 Mal durchgearbeitet. Wichtig wird nun die Funktion cv(). cv() kann nur auf der rechten Seite der Gleichung verwendet werden; sie gibt einfach nur die aktuelle Zeilennummer der Zelle zurück, die auf der linken Seite angesprochen wurde. cv() verwendet man also dann, wenn man die gleiche Berechnung in vielen Zeilen immer wieder durchführen möchte. Damit erklären sich die Formeln wie folgt:
  • Die anfängliche Darlehenssumme (A) beträgt 200.000
  • Der Zinssatz ist 6 Prozent; die Zinsen werden für einen Monat berechnet. Die Zinsen (B) in der jeweiligen Zeile seien gleich dem Darlehensbetrag (A) in der gleichen Zeile multipliziert mit dem 0.06 geteilt durch 12.
  • Die Rate (D) sei in jeder Zeile gleich 1.500
  • Die Tilgung (C) sei in jeder Zeile gleich der Rate (D) weniger die Zinsen (B) (in der gleichen Zeile)
  • Der nächste Darlehens-Restbetrag (A) sei gleich dem Darlehensbetrag (A) weniger der Tilgung (C).
These Rules get calculated 300 times (by the keyword ITERATE). Now the function cv() gets important - it returns the dimension of the current row accessed on the left side of the formula. cv() can only be used on the right side of the formula. So the forumlas explain themselves as follows:
  • The initial loan amount is (A) is 200.000
  • The interest rate is 6 percent; interest is calculated for a month (30 days). The interest amount (B) equals therefore as the remaining loan amount multiplied by 0.06 divided by 12.
  • The amount of annuity (D) is 1.500
  • The payoff amount (C) equals the ammount of annuity (C) less the interest for this month (B)
  • And the next remaining loan amount (A) equals the previous loan amount (A) less the payoff amount (C).
Aber diese Abfrage rechnet 300 Mal durch - immer! Besser wäre es, aufzuhören, wenn das Darlehen abgezahlt ist ...
But this query calculates 300 times - always! It would be better if it stopped when the loan was paid off completely. This little improvement is easy to achieve ...
select zeile, a, b, c, d, e, f
from dual
model
dimension by (rownum zeile)
measures (0 a, 0 b, 0 c, 0 d, 0 e, 0 f)
rules upsert iterate (300) until (a[ITERATION_NUMBER]<=0) (
 a[0]=200000,
 b[ITERATION_NUMBER]=a[cv(zeile)]*0.06/12,
 d[ITERATION_NUMBER]=least(1500, a[cv(zeile)]+b[cv(zeile)]),
 c[ITERATION_NUMBER]=d[cv(zeile)]-b[cv(zeile)],
 a[ITERATION_NUMBER+1]=a[cv(zeile)-1] - c[cv(zeile)-1]
)
order by zeile
Nun rechnet er die Formeln maximal 300 Mal durch, jedoch nur solange der Darlehensbetrag nicht unter Null geht. Die dritte Formel mussten wir auch noch austauschen, da die letzte Rate eines Darlehens meistens kleiner ist - die LEAST()-Funktion ermittelt stets den kleineren Wert zwischen vereinbarter Rate (hier: 1500) und dem Restbetrag des Darlehens.
Now 300 itearions are the maximum. The new UNTIL() clause lets the iterations stop when the remaining loan amount is less or equal to zero. And since the last annuity is less than the others in most cases we need another formula for calculating the annuity. The LEAST() function returns always the smaller value between the negotiated anniuity (here: 1500) and the remaining loan amount.
Übrigens: Es gibt in der deutschen APEX Community einen Tipp, wie man die SQL Model Clause in einer APEX-Anwendung nutzen kann. Also: Probiert es selbst mal aus - viel Spaß dabei!
For german readers the APEX Community contains a how to document showing how the SQL Model Clause can be used within an APEX application. Try it - have fun!

1 Kommentar:

Σωκράτης hat gesagt…

Sehr schoene Einfuehrung in die Model-Clause !
Fuer mich das verstaendlichste, was ich bisher drueber gelesen habe.
Danke !
Hoofe auf Fortsetzung

Σωκράτης

Beliebte Postings