22. Dezember 2011

Zum Ende von 2011: Oracle 4.1.4 ...

At the end of 2011: Oracle 4.1.4
Das letzte Blog Posting in 2011 soll nichts technisches mehr sein - die letzten Tage im Jahr kann man ja ein wenig zur Ruhe kommen. Daher hier nur ein paar Screenshots, die ich gemacht habe, als ich eine alte Oracle 4.1.4 nochmals installiert habe. Kein XML, kein PL/SQL, kein Oracle TEXT, kein APEX ... aber EMP ... und DEPT!
Frohe Weihnachten und ein gutes neues Jahr 2012!
My last blog posting in 2011 will not contain technical stuff. The last few days in a year are a good chance to relax a bit - and so I just would like to post some screenshots which I have taken while installing the Oracle 4.1.4 files which I found a couple of weeks ago. No XML, no PL/SQL, no Oracle TEXT, no APEX ... but EMP ... and DEPT.
Merry Christmas and a happy new year 2012!

7. Dezember 2011

Anteile in Prozent in einem Bericht kalkulieren - nur mit SQL!

How to calculate "ratio to report" in SQL
Heute morgen erreichte mich eine SQL-Frage - und das Problem taucht sicherlich öfter auf, daher veröffentliche ich meine Antwort hier für alle. Ausgangspunkt ist eine Tabelle.
|art   |anzahl     |kosten       |anteil    |
|------|-----------|-------------------------
| A    |        55 |       16,95 |     null |
| B    |        55 |        5,45 |     null |
| C    |        55 |        3,20 |     null |
| D    |        55 |        1,95 |     null |
Die rechte Spalte anteil soll nun den prozentualen (hier:) Kostenanteil der Zeile im Verhältnis zur Summe über alle Zeilen enthalten. Damit ich die Tabelle nicht neu anlegen muss, übertrage ich das Beispiel auf die uns allen bekannte Tabelle EMP: Wir wollen also zu jeder das Gehalt als prozentualen Anteil an der Gesamt-Gehaltssumme ausgedrückt sehen. Fangen wir mit dem "klassischen" Ansatz an ...
with summe as (
  select sum(sal) sal from emp
)
select 
  e.ename, 
  e.sal,
  e.sal / s.sal * 100 anteil
from emp e, summe s
/ 

ENAME            SAL  ANTEIL
---------- --------- -------
SMITH           1280    2,76
ALLEN           2560    5,51
WARD            2000    4,31
JONES           4760   10,25
MARTIN          2000    4,31
Diese Lösung dürfte auf nahezu allen Datenbanksystemen funktionieren - mit der WITH-Klausel wird zunächst die Gehaltsumme ermittelt und dann per Join in die eigentliche Abfrage integriert. Wenn man den Gehaltsanteil pro Abteilung (DEPTNO) sehen möchte, müsste man die Abfrage in der WITH-Klausel mit einem GROUP BY versehen und eine Joinbedingung in die Hauptabfrage einbauen ...
with summe as (
  select deptno, sum(sal) sal from emp
  group by deptno
)
select 
  e.ename, 
  e.deptno,
  e.sal,
  e.sal / s.sal * 100 anteil_dept
from emp e join  summe s on (e.deptno = s.deptno)
/

ENAME          DEPTNO       SAL ANTEIL_DEPT
---------- ---------- --------- -----------
SMITH              20      1280        7,36
ALLEN              30      2560       17,02
WARD               30      2000       13,30
JONES              20      4760       27,36
MARTIN             30      2000       13,30
BLAKE              30      4560       30,32
So weit - so gut. Aber es geht auch wesentlich schöner: Setzt man analytische Funktionen ein, so kann man sich die Inline-View sparen - das macht dann alles die Datenbank ... so könnte man die analytische Variante von SUM wie folgt verwenden.
select 
  e.ename, 
  e.deptno,
  e.sal,
  (e.sal / sum(sal) over ()) * 100 anteil
from emp e
/
ENAME          DEPTNO       SAL  ANTEIL
---------- ---------- --------- -------
SMITH              20      1280    2,76
ALLEN              30      2560    5,51
WARD               30      2000    4,31
Hier wird SUM ohne GROUP BY verwendet - einfach weil es die analytische Variante ist. Das sog. Query Window, welches angibt, über welche Zeilen das Aggregat gebildet werden soll, wird in der OVER -Klausel festgelegt. Und wenn die Klammern leer sind, heißt das soviel wie "über alles". Aber auch die Variante mit den Anteil pro Abteilung ist machbar.
select 
  e.ename, 
  e.deptno,
  e.sal,
  (e.sal / sum(sal) over (partition by deptno)) * 100 anteil
from emp e
/

ENAME          DEPTNO       SAL  ANTEIL
---------- ---------- --------- -------
CLARK              10      3920   28,00
KING               10      8000   57,14
MILLER             10      2080   14,86
JONES              20      4760   27,36
FORD               20      4800   27,59
Jetzt steht etwas in der OVER()-Klausel drin. Mit PARTITION BY wird festgelegt, dass das Aggregat (die Summe) nach Abteilungen berechnet werden soll. Das Schlüsselwort PARTITION BY ist übrigens nicht zu verwechseln mit der Tabellenpartitionierung und im Gegensatz zu dieser erfordern die analytischen Funktionen keine separaten Lizenzen. Aber es geht sogar noch einfacher: Denn für diese Aufgabe gibt es eine spezielle analytische Funktion: RATIO_TO_REPORT.
select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over () * 100 anteil
from emp e
/

ENAME          DEPTNO        SAL  ANTEIL
---------- ---------- ---------- -------
SMITH              20       1280    2,76
ALLEN              30       2560    5,51
WARD               30       2000    4,31
JONES              20       4760   10,25
MARTIN             30       2000    4,31
Und natürlich geht auch hier die PARTITION BY-Klausel ...
select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over (partition by deptno) * 100 anteil
from emp e
/
Analytische Funktionen lohnen auf jeden Fall eine genauere Betrachtung. Jeder, der mit SQL und der Oracle-Datenbanken arbeitet, sollte sie kennen. Neben den hier beschriebenen Dingen lassen sich damit auch gleitende Durchschnitte, fortlaufende Summen, Rankings und andere Dinge, die sonst recht kompliziertes SQL mit Subselects erfordern, mit einfachen Funktionsaufrufen erledigen. Zum Nachlesen ist hier noch ein Link zur Dokumentation.
This morning I got a SQL question - nothing difficult - but I think, this kind of requirement is more frequent, and therefore I decided to publish the answer for everyone. The question is based on table data (as always) ...
|art   |count      |cost         |cost_share |
|------|-----------|--------------------------
| A    |        55 |       16,95 |      null |
| B    |        55 |        5,45 |      null |
| C    |        55 |        3,20 |      null |
| D    |        55 |        1,95 |      null |
The right column cost_share should contain the row's share of total cost - expressed in percent. So the cost column needs to be divided by SUM(COST) over all rows. I'm lazy: so instead of creating this table I decided to solve the problem for the SAL column in the well known EMP table. Let's start with "classic" SQL.
with total as (
  select sum(sal) sal from emp
)
select 
  e.ename, 
  e.sal,
  e.sal / s.sal * 100 share
from emp e, total s
/ 

ENAME            SAL   SHARE
---------- --------- -------
SMITH           1280    2,76
ALLEN           2560    5,51
WARD            2000    4,31
JONES           4760   10,25
MARTIN          2000    4,31
This query should work on almost every database system. First we compute the total salary amount with an inline view using the WITH clause. This inline view will be joined in the main query. It returns only one row, so we don't need any join criteria and we can access the total salary amount just like a table column. And this can be extended - if we'd like to see the salary share expressed as "percentage of the department total", we add a GROUP BY to the inline view and extend the join in the main query as follows ...
with total as (
  select deptno, sum(sal) sal from emp
  group by deptno
)
select 
  e.ename, 
  e.deptno,
  e.sal,
  e.sal / s.sal * 100 share_dept
from emp e join  total s on (e.deptno = s.deptno)
/

ENAME          DEPTNO       SAL  SHARE_DEPT
---------- ---------- --------- -----------
SMITH              20      1280        7,36
ALLEN              30      2560       17,02
WARD               30      2000       13,30
JONES              20      4760       27,36
MARTIN             30      2000       13,30
BLAKE              30      4560       30,32
So far, so good. This works, but there are much more elegant approaches. Now I'll drop my inline view and use the analytic variant of SUM() for the problem.
select 
  e.ename, 
  e.deptno,
  e.sal,
  (e.sal / sum(sal) over ()) * 100 share
from emp e
/
ENAME          DEPTNO       SAL   SHARE
---------- ---------- --------- -------
SMITH              20      1280    2,76
ALLEN              30      2560    5,51
WARD               30      2000    4,31
This query uses SUM without GROUP BY, because it's the analytic SUM function. The query window , which defines the rows to be aggregated, is specified in the OVER() clause. The aggregate is then calculated to each row of the query result set. An empty OVER clause means as much as "over all rows". But we can also do the calculation on the department level.
select 
  e.ename, 
  e.deptno,
  e.sal,
  (e.sal / sum(sal) over (partition by deptno)) * 100 share
from emp e
/

ENAME          DEPTNO       SAL   SHARE
---------- ---------- --------- -------
CLARK              10      3920   28,00
KING               10      8000   57,14
MILLER             10      2080   14,86
JONES              20      4760   27,36
FORD               20      4800   27,59
Now we have the keywords PARTITION BY inside the OVER() clause. So the aggregate is being computed for each department. Don't confuse this PARTITION BY clause with table partitioning. Opposed to the latter, PARTITION BY within an analytic function has nothing to do with table storage and does not require an additional license. But we can solve the original problem even more elegant. Why? Because there is a special function for this purpose: RATIO_TO_REPORT.
select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over () * 100 share
from emp e
/

ENAME          DEPTNO        SAL   SHARE
---------- ---------- ---------- -------
SMITH              20       1280    2,76
ALLEN              30       2560    5,51
WARD               30       2000    4,31
JONES              20       4760   10,25
MARTIN             30       2000    4,31
And this (of course) also works with PARTITION BY ...
select 
  e.ename, 
  e.deptno,
  e.sal,
  ratio_to_report(e.sal) over (partition by deptno) * 100 share
from emp e
/
Analytic functions are absolutely worth the learing effort. Beyond the problem described here, analytic functions provide an easy query syntax for things like moving averages, rankings or other aggregates with flexible query windows. Solving this with "classic" SQL is possible (of course), but this most often gets cumbersome and difficult to read. You'll find more information in the documentation.

Beliebte Postings