Exceptionfault

Heute morgen war ich mal wieder im Datenbankforum bei Tutorials.de unterwegs und bin (seit langem) wieder auf eine spannende und berechtigte Frage zu SQL gestoßen. Folgendes Problem liegt vor:

SELECT * FROM PRODUCTS;

PRODUKT      INHALT     LAENGE
------------ -------- --------
Produkt1     Kabel          10
Produkt1     Draht          11
Produkt1     Kabel          15
Produkt2     Draht          12
Produkt3     Kabel           8
Produkt3     Kabel           5
Produkt4     Kabel           3
Produkt5     Draht           1

Die Datensätze sollen nun nach Produkt und Inhalt gruppiert werden. Ermittelt werden soll neben der Gesamtlänge des Inhalts pro Produkt ( SUM( LAENGE ) .. GROUP BY PRODUKT, INHALT ) auch der prozentuale Anteil des Inhalts am Produkt. Im Falle vom Produkt1 hieße dies, 25m Kabel im Verhältnis zu 11m Draht.

Der Vorschlag eines anderen Users hierzu funktioniert, die Probleme liegen allerdings auf der Hand: Was wenn neben Kabel und Draht weitere Anteile hinzukommen? Abgesehen davon: Wer versteht sowas auf einen kurzen Blick?

select a_produkt
,ifnull (sum(case when a_kategorie = 'Kabel' THEN a_strecke END),0)a_strecke_kabel
,ifnull(sum(CASE WHEN a_kategorie = 'Draht' THEN a_strecke END),0) a_strecke_draht
,ifnull(sum(case when a_kategorie = 'Kabel' THEN a_strecke END),0)+
ifnull (sum(CASE WHEN a_kategorie = 'Draht' THEN a_strecke END),0) a_strecke_gesamt
,ifnull (sum(case when a_kategorie = 'Kabel' THEN a_strecke END),0)/
(ifnull(sum(case when a_kategorie = 'Kabel' THEN a_strecke END),0)+
ifnull (sum(CASE WHEN a_kategorie = 'Draht' THEN a_strecke END),0)) a_strecke_kabel_all_verhältnis
,ifnull (sum(case when a_kategorie = 'Draht' THEN a_strecke END),0)/
(ifnull(sum(case when a_kategorie = 'Kabel' THEN a_strecke END),0)+
ifnull (sum(CASE WHEN a_kategorie = 'Draht' THEN a_strecke END),0)) a_strecke_drant_all_verhältnis
from test.t_kabel
group by a_produkt

Die Rettung bieten analytische Funktionen, in diesem speziellen Beispiel die Funktion RATIO_TO_REPORT.

SELECT
Produkt,
Inhalt,
TO_CHAR(  RATIO_TO_REPORT(Laenge)
OVER ( PARTITION BY Produkt ) * 100,
'999D99'
) || ' %' AS ANTEIL
FROM (
SELECT 	Produkt,
Inhalt,
SUM( Laenge ) AS Laenge
FROM    products
GROUP   BY Produkt, Inhalt
);

PRODUKT     INHALT   ANTEIL
----------- -------- ----------
Produkt1    Kabel      69,44 %
Produkt1    Draht      30,56 %
Produkt2    Draht     100,00 %
Produkt3    Kabel     100,00 %
Produkt4    Kabel     100,00 %
Produkt5    Draht     100,00 %

Das Stement besteht aus einem äußeren Teil, dem analytischen Statement, und dem inneren Subselect zur Aufsummierung gleicher Produkte mit gleichem Inhalt. D.h. zuerst werden alle Längen summiert, die zusammengehören.

Der Kern des analytischen Statements ist der Ausdruck RATIO_TO_REPORT(Laenge) OVER ( PARTITION BY Produkt ), der Rest ist Layoutgeschnörckel. Die Funktion RATIO_TO_REPORT errechnet den prozentualen Anteil des Wertes “Laenge”. Mit der OVER Klausel geben wir an, was der Bezug zur Berechnung des Prozentwertes ist. Lassen wir die OVER Klausel einfach leer, also “()”, dann erhalten wir den prozentualen Anteil über alle Produkte mit allen Inhalten. Wir wollen aber den Anteil je Produkt, also zerlegen wir mit der PARTITION BY Klausel unser Ergebnis in viele Töpfe, sortiert nach Produkt. Die RATIO_TO_REPORT Klausel berechnet nun also den prozentuellen Anteil der aktuellen Zeile (Laenge) im Vergleich zur Gesamtlänge in unserem Topf (Produkt).