SQL Ergebnisse in UNION Statement berechnen

[frad80]

Hallo, ich habe 3 SQL Statements in einem UNION verbunden.

Das erste Ergebnis liefert eine 20, das zweite liefert 30 und das dritte liefert die Gesamtsumme 50 (20+30) - nun will ich den Prozentualen Anteil berechnen:

30 * 100 / 50 = 60%

Wie kann ich diesen Rechen weg über SQL abbilden?

 

[ukulele]

Das Ergebnis der ganzen UNIONs wäre eine Tabelle. Möglich wäre das z.B. wenn du die Summe zu deiner Tabelle dazu joinst.

WITH tabelle(eintrag,zahl) AS (
    SELECT 'A',20 UNION ALL
    SELECT 'B',30 UNION ALL
    SELECT 'S',50
    )
SELECT    tabelle.*,
        100 / t.zahl * tabelle.zahl
FROM    tabelle
LEFT JOIN tabelle t
ON        t.eintrag = 'S'

Aber eventuell geht es mit Window-Functions noch eleganter. Nur: Warum genau baust du die drei SQL Statements mit UNION zusammen? Sind das wirklich unterschiedliche Datenquellen oder nur unterschiedliche Abfragen auf die selbe Tabelle?

 

[frad80]

Das Ergebnis der ganzen UNIONs wäre eine Tabelle. Möglich wäre das z.B. wenn du die Summe zu deiner Tabelle dazu joinst.

WITH tabelle(eintrag,zahl) AS (
    SELECT 'A',20 UNION ALL
    SELECT 'B',30 UNION ALL
    SELECT 'S',50
    )
SELECT    tabelle.*,
        100 / t.zahl * tabelle.zahl
FROM    tabelle
LEFT JOIN tabelle t
ON        t.eintrag = 'S'

Aber eventuell geht es mit Window-Functions noch eleganter. Nur: Warum genau baust du die drei SQL Statements mit UNION zusammen? Sind das wirklich unterschiedliche Datenquellen oder nur unterschiedliche Abfragen auf die selbe Tabelle?

Hi, es sind im Grunde 3 Abfragen auf die gleiche Tabelle mit unterschiedlichen Bedingungen...

select count(*) from autos where ps < 100
select count(*) from autos where ps > 100
select count(*) from autos

Ich möchte vereinfacht ausgedrückt den prozentualen Anteil der Autos größer 100 PS haben...

 

[akretschmer]

Berechne erst einmal die Anzahlen je Kategorie. Da sollte sowas wie folgt rauskommen:

postgres=# select * from frad80 ;
 bereich | anzahl 
---------+--------
 unter   |     20
 über    |     30
(2 rows)

postgres=#

Dann:

postgres=# with gesamt as (select sum(anzahl) as gesamt from frad80 ) select frad80.*, anzahl / gesamt.gesamt::numeric * 100 as prozent from frad80 cross join gesamt;
 bereich | anzahl |         prozent         
---------+--------+-------------------------
 unter   |     20 | 40.00000000000000000000
 über    |     30 | 60.00000000000000000000
(2 rows)

Das könnte man via CTE auch in einem Select machen - überlasse ich Dir zur Übung.

 

[ukulele]

Hier mal noch ein Beispiel mit einer zugegeben etwas verrückten Kombination aus GROUP BY ROLLUP(), das liefert eine Gruppensumme zur Gruppierung, und einer Windows-Function in Form einer laufenden Summe.

WITH tabelle(eintrag,zahl) AS (
    SELECT 'A',20 UNION ALL
    SELECT 'B',30
    )
SELECT    (CASE WHEN GROUPING(tabelle.eintrag) = 0 THEN tabelle.eintrag ELSE 'S' END),
        sum(tabelle.zahl),
        100 / sum(CASE WHEN GROUPING(tabelle.eintrag) = 0 THEN sum(tabelle.zahl) ELSE NULL END) OVER () * sum(tabelle.zahl)
FROM    tabelle
GROUP BY ROLLUP(tabelle.eintrag)

Das, kombiniert mit einem CASE für deine Autotabelle, spart dir deine UNIONs. Ist allerdings noch undurchsichtiger, dafür kein CROSS JOIN, kein UNION, alles nur gruppiert und aggregiert

WITH autos(ps) AS (
    SELECT 1 UNION ALL
    SELECT 99 UNION ALL
    SELECT 100 UNION ALL
    SELECT 101 UNION ALL
    SELECT 500
    )
SELECT    (CASE WHEN GROUPING(
            (CASE WHEN ps < 100 THEN '<100' ELSE '>=100' END)
        ) = 1 THEN 'Summe'
        ELSE (CASE WHEN ps < 100 THEN '<100' ELSE '>=100' END)
        END),
        count(*),
        100 / sum(CASE WHEN GROUPING((CASE WHEN ps < 100 THEN '<100' ELSE '>=100' END)) = 0 THEN count(*) ELSE NULL END) OVER () * count(*)
FROM    autos
GROUP BY ROLLUP((CASE WHEN ps < 100 THEN '<100' ELSE '>=100' END))

 

[dabadepdu]

spart dir deine UNIONs. Ist allerdings noch undurchsichtiger, dafür kein CROSS JOIN, kein UNION,

Gute Sache, bei großen Datenmengen müsste man sich auch mal die Ausführungspläne anschauen.
Noch eine Variante, die mir übersichtlicher und flexibler scheint, mal als Idee bzw. genereller Ansatz, für solche Gelegenheiten case when Konstrukte zu vermeiden. Man kann ohne weiteres die Gruppierung variieren, sowohl Anzahl der Gruppen als auch Grenzwerte. Ob das dann per CTE eingeblendet wird oder aus vorhandenen Tabellen stammt, kann man nach Bedarf festlegen.

with autos(ps) as (
      select 1 union all
      select 99 union all
      select 100 union all
      select 101 union all
      select 500
    ),
    powerrange(pr_from,pr_to, pr_label) as (
      select 1,    99, '< 100' union all
      select 100, 699, '>= 100' 
    )
select pr_label, 
       count(*) as pr_label_count_, 
       count(*) *100 / sum(count(*)) over ()  pr_label_percent_
  from autos a 
  join powerrange p
    on a.ps between p.pr_from and p.pr_to
 group by pr_label;

 

[ukulele]

PS: Ich meinte Window-Funktion, nicht Windows :-/

 

[ukulele]

Was das WITH angeht nutze ich das nur für die Testdaten. Du kannst aber auch in einem Vorschritt eine Spalte für die Gruppierung erzeugen (sie wie @dabadepdu ) und als WITH einbinden. Es geht aber auch ohne WITH im FROM-Teil. in beiden Fällen ist das deutlich übersichtlicher als das CASE mehrfach in den Code zu knallen.

WITH autos(ps) AS (
    SELECT 1 UNION ALL
    SELECT 99 UNION ALL
    SELECT 100 UNION ALL
    SELECT 101 UNION ALL
    SELECT 500
    ), t(gruppe,ps) AS (
    SELECT    (CASE WHEN ps < 100 THEN '<100' ELSE '>=100' END) AS gruppe,
            ps
    FROM    autos
    )
SELECT    (CASE WHEN GROUPING(t.gruppe) = 1 THEN 'Summe' ELSE t.gruppe END) AS gruppe,
        count(*) AS anzahl,
        100 / sum(CASE WHEN GROUPING(t.gruppe) = 0 THEN count(*) ELSE NULL END) OVER () * count(*) AS prozent
FROM    t
GROUP BY ROLLUP(t.gruppe)

oder

WITH autos(ps) AS (
    SELECT 1 UNION ALL
    SELECT 99 UNION ALL
    SELECT 100 UNION ALL
    SELECT 101 UNION ALL
    SELECT 500
    )
SELECT    (CASE WHEN GROUPING(t.gruppe) = 1 THEN 'Summe' ELSE t.gruppe END) AS gruppe,
        count(*) AS anzahl,
        100 / sum(CASE WHEN GROUPING(t.gruppe) = 0 THEN count(*) ELSE NULL END) OVER () * count(*) AS prozent
FROM    (

SELECT    (CASE WHEN ps < 100 THEN '<100' ELSE '>=100' END) AS gruppe,
        ps
FROM    autos
   
    ) t
GROUP BY ROLLUP(t.gruppe)

Ob du dann die Spalte zur Gruppierung mit CASE erzeugst oder anders ist egal, es ginge ja auch UNION:

SELECT    '<100' AS gruppe,
        ps
FROM    autos
WHERE    ps < 100
UNION ALL
SELECT    '>=100' AS gruppe,
        ps
FROM    autos
WHERE    ps >= 100

Am elegantesten finde ich allerdings immer noch CASE, vermutlich auch schneller als UNION + WHERE oder JOIN, wobei das hier vermutlich nicht die Rolle spielt. Noch toller wäre FILTER, das unterstüzt aber kaum ein SQL (ja natürlich PostgreSQL) als Syntax und technisch ist es das selbe.