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.
