✔ Gruppierung und Berechnung vom prozentualen Anteil pro Gruppe (Oracle 10g)

H

hamburger83

Grünschnabel
Hallo zusammen,.

ich Habe eine Tabelle mit Produkten. Diese Produkte besitzen eine Länge und gehören unterschiedlichen Kategorien (Draht oder Kabel) an.

Beispiel:

Produkt1 Kabel 10m
Produkt1 Draht 11m
Produkt1 Kabel 15m
Produkt2 Draht 12m
Produkt3 Kabel 8m
Produkt3 Kabel 5m
Produkt4 Kabel 3m
Produkt5 Draht 1m

u.s.w.

Ich möchte nun eine Aufstellung haben die ermittelt wieviel Kabel bzw. Drähte in die Produkte geflossen ist.
Sie soll so aussehen:

Produkt1 Kabel 25m
Produkt1 Draht 11m
Produkt2 Draht 12m
Produkt3 Kabel 13m
Produkt4 Kabel 3m
Produkt5 Draht 1m

Ich gruppiere dabei über die Produkte und die Kategorien und errechne jeweils die Summe. So weit so gut.

Mein Problem ist jetzt ich möchte eine zweite berechnete Spalte haben, die prozentual angibt wieviel Kabel bzw. Drähte ins Produkt gingen bezogen auf die Gesamtlänge über beide Kategorien (Draht und Kabel).

Beispiel für Produkt 1:

Produkt1 Kabel 25m 0,69 (25/(11+25))
Produkt1 Draht 11m 0,31 (11/(11+25))

Wie kann ich die zweite Spalte errechnen. Mein Problem ist, dass ich durch die Gruppierung immer nur die Werte pro Kategorie mit der Funktion "Sum" ermitteln kann. Die Gesamtlänge nur über das Produkt (in diesem Fall die 36) nicht.

Kann mir hier jemand weiterhelfen? Nutze Oracle 10g.

Vielen Dank und viele Grüße,

Stefan
 
Hallo,

ich würde das über ein 2tes select machen oder mit case when, ifnull u.s.w. schau mal obs Dir hilft, da ich deine Tabellen nicht vom Namen Herr kenne nehme ich einfach Tabelle "t_kabel" und die spalten, naja wie deine Argumente:

das ist meine tabelle:
PHP: 
create table test.t_kabel (
a_produkt varchar(20),
a_kategorie varchar(20),
a_strecke integer(11)
)

udn hier das select dazu:

PHP: 
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

Grüße
 
Zuletzt bearbeitet:
Hallo hamburger83,

das geht im Grunde ganz easy mit den analytischen Funktionen. Wenn man sich erstmal reingedacht hat ist es simpel und extrem mächtig. Hier meine Demo und drunter die Erklärungen:

Code: 
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

8 Zeilen ausgewählt.

COL ANTEIL FOR A10
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 %

6 Zeilen ausgewählt.

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 wllen aber den Anteil je Produkt, also zerlegen wir mit der PARTITION BY Klausel unser Ergebnis in viele Töpfe, sortiert nach Produkt. Die RATION_TO_REPORT Klausel berechnet nun also den prozentuellen Anteil der aktuellen Zeile (Laenge) im Vergleich zur Gesamtlänge in unserem Topf (Produkt).

Ich hoffe, das ist genau das was du gesucht hast ?!
 
Hallo Exceptionfault,

sehr genial deine Lösung! Hast Du evtl. eine gute Quelle "Wo gibt es eine Übersicht dieser . Funktionen evtl. mit kurzen Beispielen" für uns parat, das spart ja einen Haufen Arbeit.

VIele Grüße
 
Ein kleiner Bericht von Oracle Deutschland geht auf Reporting mit analytischen Funktionen ein:

http://www.oracle.com/global/de/community/tipps/analyticFunctions/index.html

Ansonsten ist die beste Quelle immer das Handbuch der entsprechend eingesetzten Version, genauer die SQL Referenz.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407

oder :google: "oracle database online documentation 10.2"

Wers ganz krass haben will der sollte sich mal die MODEL Klausel ansehen :)
Code: 
SELECT country,prod,year,s
  FROM sales_view_ref
  MODEL
    PARTITION BY (country)
    DIMENSION BY (prod, year)
    MEASURES (sale s)
    IGNORE NAV
    UNIQUE DIMENSION
    RULES UPSERT SEQUENTIAL ORDER
    (
      s[prod='Mouse Pad', year=2001] =
        s['Mouse Pad', 1999] + s['Mouse Pad', 2000],
      s['Standard Mouse', 2002] = s['Standard Mouse', 2001]
    )
  ORDER BY country, prod, year;
 
Danke für Eure HIlfe.

Die analytischen Funktionen sind echt mächtig, kannte die bisher nicht.

Viele Grüße,

Hamburger
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück