Analytische Funktionen (Teil 1): SUM mit CASE WHEN
Von Tobias Arnhold →
9.27.2015
Es gibt wie in meinem letzten Post beschrieben, sehr viele Artikel zu analytischen Funktionen. Diese zeigen die Fähigkeiten aber nur an der Oberfläche. Deshalb möchte ich in den nächsten Monaten die besten Tricks mal beispielhaft abbilden.
Als Grundlage habe ich eine Tabelle mit Einwohnern je Bundesland und Jahr. Als Quelle gilt das Statistische Bundesamt.
In meinem ersten Beispiel möchte ich eine bedingte Summierung zeigen. Dazu soll für die Top 5 Bundesländer mit der höchsten Einwohnerzahl das Verhältnis zur Gesamtanzahl aller Bundesbürger in Prozent aufgezeigt werden. Zusätzlich werden die TOP 5 Bundesländer FETT markiert.
Info: Wenn meine Beispiel nicht gefallen, dann kann ich es verstehen. Ich löse für gewöhnlich Probleme und denke sie mir nicht aus. :)
Hmm die Reihenfolge mit dem Order By hätte ich etwas besser auswählen können. :O
Als Grundlage habe ich eine Tabelle mit Einwohnern je Bundesland und Jahr. Als Quelle gilt das Statistische Bundesamt. SELECT JAHR, BUNDESLAND, EINWOHNER FROM AS_EINWO_BUNDESL_JAHR WHERE JAHR = 2014 ORDER BY 1,2
| JAHR | BUNDESLAND | EINWOHNER |
|---|---|---|
| 2014 | Baden-Württemberg | 10.666.000 |
| 2014 | Bayern | 12.643.000 |
| 2014 | Berlin | 3.443.000 |
| 2014 | Brandenburg | 2.449.000 |
| 2014 | Bremen | 659.000 |
| 2014 | Hamburg | 1.762.000 |
| 2014 | Hessen | 6.059.000 |
| 2014 | Mecklenburg-Vorpommern | 1.594.000 |
| 2014 | Niedersachsen | 7.799.000 |
| 2014 | Nordrhein-Westfalen | 17.579.000 |
| 2014 | Rheinland-Pfalz | 3.996.000 |
| 2014 | Saarland | 989.000 |
| 2014 | Sachsen | 4.045.000 |
| 2014 | Sachsen-Anhalt | 2.238.000 |
| 2014 | Schleswig-Holstein | 2.819.000 |
| 2014 | Thüringen | 2.156.000 |
In meinem ersten Beispiel möchte ich eine bedingte Summierung zeigen. Dazu soll für die Top 5 Bundesländer mit der höchsten Einwohnerzahl das Verhältnis zur Gesamtanzahl aller Bundesbürger in Prozent aufgezeigt werden. Zusätzlich werden die TOP 5 Bundesländer FETT markiert.
Info: Wenn meine Beispiel nicht gefallen, dann kann ich es verstehen. Ich löse für gewöhnlich Probleme und denke sie mir nicht aus. :)
SELECT
BUNDESLAND,
EINWOHNER,
SUM(EINWOHNER) OVER () as EINWOHNER_GESAMT,
/* Beispiel: Summe über Einwohner nur wenn TOP5 = 1 */
SUM(CASE WHEN IN_TOP5 = 1 THEN EINWOHNER ELSE 0 END) OVER () AS EINWOHNER_TOP5,
/* Prozentwertberechnung */
round(SUM(CASE WHEN IN_TOP5 = 1 THEN EINWOHNER ELSE 0 END) OVER () / SUM(EINWOHNER) OVER () * 100,0) IN_PROZ
FROM
(
SELECT
JAHR,
/* Generierung der TOP 5 mit B-Tag */
case when
row_number () over (order by EINWOHNER desc) <= 5
then
''||BUNDESLAND||''
else
BUNDESLAND
end as BUNDESLAND,
/* Ausgabe TOP-5 mit 1 Sonst 0 */
case when
row_number () over (order by EINWOHNER desc) <= 5
then
1
else
0
end as IN_TOP5,
EINWOHNER
FROM AS_EINWO_BUNDESL_JAHR
WHERE JAHR = 2014
)
ORDER BY 1, 3 desc
| BUNDESLAND | EINWOHNER | EINWOHNER_GESAMT | EINWOHNER_TOP5 | IN_PROZ |
|---|---|---|---|---|
| <b>Baden-Württemberg</b> | 10.666.000 | 80.896.000 | 54.746.000 | 68 |
| <b>Bayern</b> | 12.643.000 | 80.896.000 | 54.746.000 | 68 |
| Berlin | 3.443.000 | 80.896.000 | 54.746.000 | 68 |
| <b>Hessen</b> | 6.059.000 | 80.896.000 | 54.746.000 | 68 |
| <b>Niedersachsen</b> | 7.799.000 | 80.896.000 | 54.746.000 | 68 |
| <b>Nordrhein-Westfalen</b> | 17.579.000 | 80.896.000 | 54.746.000 | 68 |
| Brandenburg | 2.449.000 | 80.896.000 | 54.746.000 | 68 |
| Bremen | 659.000 | 80.896.000 | 54.746.000 | 68 |
| Hamburg | 1.762.000 | 80.896.000 | 54.746.000 | 68 |
| Mecklenburg-Vorpommern | 1.594.000 | 80.896.000 | 54.746.000 | 68 |
| Rheinland-Pfalz | 3.996.000 | 80.896.000 | 54.746.000 | 68 |
| Saarland | 989.000 | 80.896.000 | 54.746.000 | 68 |
| Sachsen | 4.045.000 | 80.896.000 | 54.746.000 | 68 |
| Sachsen-Anhalt | 2.238.000 | 80.896.000 | 54.746.000 | 68 |
| Schleswig-Holstein | 2.819.000 | 80.896.000 | 54.746.000 | 68 |
| Thüringen | 2.156.000 | 80.896.000 | 54.746.000 | 68 |
Hmm die Reihenfolge mit dem Order By hätte ich etwas besser auswählen können. :O
