SQL: Pivotisierung einer Rangliste

Aus DBWiki
Wechseln zu: Navigation, Suche

Zurück zur Übersicht



Um eine Rangliste zu erstellen, kann man sich auf die Window Funktion rank() oder dense_rank() stützen. Der so ermittelte Rang lässt sich dann in einer weiteren Abfrage in Gruppen aufteilen, die ihrerseits noch mittels row_number() eine entsprechende Sortierung erfahren.


Es sollen z.B. alle Mitarbeiter mit den höchsten 3 Gehältern in einer Spalte absteigend nach Gehalt sortiert in einer Spalte aufgelistet werden. Daneben soll eine Spalte mit den nächsten drei Gehältern nach dem gleichen Schema stehen. Die letzte Spalte listet die verbleibendenden Mitarbeiter auf.

top_3 naechste_3 andere
König    (5000) Blank    (2850) Dreher   (1500)
Fuhrmann (3000) Kühne    (2450) Müller   (1300)
Scheidt  (3000) Allard   (1600) Mertens  (1250)
Johann   (2975)   Wecker   (1250)
    Adam     (1100)
    Jakobs    (950)
    Schmidt   (800)


Lösung mittels Window Funktionen

SELECT MAX( CASE gruppe WHEN 1 THEN rpad(ma_name, 8) 
                                 || lpad( '(' || gehalt || ')', 7 ) END ) AS top_3,
       MAX( CASE gruppe WHEN 2 THEN rpad(ma_name, 8)
                                 || lpad( '(' || gehalt || ')', 7 ) END ) AS naechste_3,
       MAX( CASE gruppe WHEN 3 THEN rpad(ma_name, 8)
                                 || lpad( '(' || gehalt || ')', 7 ) END ) AS andere
FROM   ( SELECT ma_name,
                gehalt,
                CASE WHEN rang < 4 THEN 1                -- wird die 1. Spalte
                     WHEN rang < 7 THEN 2                -- wird die 2. Spalte
                     ELSE               3 END AS gruppe, -- wird die letzte Spalte
                ROW_NUMBER() OVER ( PARTITION BY CASE WHEN rang < 4 THEN 1
                                                      WHEN rang < 7 THEN 2
                                                      ELSE               3 END
                                    ORDER BY gehalt DESC, ma_name ) AS pos      -- für die Reihenfolge
         FROM ( SELECT ma_name,
                       gehalt,
                       dense_rank() OVER ( ORDER BY gehalt DESC ) AS rang       -- Ränge bilden
                FROM   mitarbeiter
              ) AS a
         ) AS b
GROUP  BY pos
ORDER  BY pos

Das Ergebnis kann als DBFiddle eingesehen werden.

Lösung für Jet-SQL

Hinweis für Access-Anwender
Um die folgenden Abfragen selbst testen zu können, müssen die Tabellen der Übungsdatenbank vorhanden sein. Es wird weiterhin das VBA-Modul mit der Funktion GetRowNumber() aus SQL: Einschränkung der Anzahl der zurückgegebenen Zeilen verwendet.

Die Lösung verwendet als Zwischenschritt eine Tabellenerstellungsabfrage, weil das Gruppieren in der Abfrage über GetRowNumber() nicht funktioniert.

Zwischenschritt

SELECT ma_name,
       gehalt,
       IIf( rang < 4, 1, IIf( rang < 7, 2, 3 ) ) AS gruppe,
       GetRowNumber( ma_name, CStr( gruppe ) ) AS pos
INTO   tmp_mitarbeiter
FROM   ( SELECT ma_name,
                gehalt,
                ( SELECT count( * )
                  FROM   ( SELECT DISTINCT gehalt
                           FROM   mitarbeiter )
                  WHERE  gehalt >= m.gehalt ) AS rang
         FROM   mitarbeiter AS m ) AS v
ORDER  BY gehalt DESC

So sollte die erstellte Tabelle tmp_mitarbeiter ausschauen:

ma_name gehalt gruppe pos
König 5000 1 0
Fuhrmann 3000 1 1
Scheidt 3000 1 2
Johann 2975 1 3
Blank 2850 2 0
Kühne 2450 2 1
Allard 1600 2 2
Dreher 1500 3 0
Müller 1300 3 1
Mertens 1250 3 2
Wecker 1250 3 3
Adam 1100 3 4
Jakobs 950 3 5
Schmidt 800 3 6

Hauptabfrage

SELECT max( IIf( gruppe = 1, ma_name & Space$( 8 - Len( ma_name ) )
                                     & Space$( 5 - Len( CStr( gehalt ) ) )
                                     & '(' & CStr( gehalt ) & ')' ) ) AS top_3,
       max( IIf( gruppe = 2, ma_name & Space$( 8 - Len( ma_name ) )
                                     & Space$( 5 - Len( CStr( gehalt ) ) )
                                     & '(' & CStr( gehalt ) & ')' ) ) AS naechste_3,
       max( IIf( gruppe = 3, ma_name & Space$( 8 - Len( ma_name ) )
                                     & Space$( 5 - Len( CStr( gehalt ) ) )
                                     & '(' & CStr( gehalt ) & ')' ) ) AS andere
FROM   tmp_mitarbeiter
GROUP  BY pos



Zurück zur Übersicht