Mittwoch, 28. Dezember 2016

Oracle SQL "Eingemachtes" (Kreuztabellen / Pivot, kumulierte Summen, Gruppierungen, virtuelle Tabellen)

In diesem Artikel möchte ich ein paar nette Dinge zeigen, die mit Oracle SQL recht fix gehen, aber dabei schon relativ "ins Eingemachte" gehen. Dabei möchte ich ohne Erklärungen zu den eingesetzten Befehlen oder theoretischen Konstrukten einfach nur "Bestpractice SQL"-Beispiele aufzeigen.

Das ganze ist also eher für Leute gedacht, die schon Kenntnisse in Oracle-SQL haben und ein konkretes Problem mit ein wenig "try and error" lösen möchten.

Konkret werden im Folgenden diese Oracle SQL Statements verwendet:
  • GROUP BY
    (Gruppierung um z.B. eine Summe zu ermitteln)
  • OVER / PARTITION BY / LAST_VALUE bzw. FIRST_VALUE
    (Gruppierung um z.B. den ersten Wert jeder Gruppe einer sortierten Liste zu ermitteln)
  • PIVOT
    (Kreuztabelle macht aus einer eindimensionalen Liste eine zweidimensionale Matrix)

0. So, jetzt geht es los ... gegeben sei folgende Tabelle...

CREATE TABLE "TV_FVDB"."MITARBEITER" 
( "MA_NR" NUMBER, 
"NACHNAME" VARCHAR2(20 BYTE), 
"VORNAME" VARCHAR2(20 BYTE), 
"GEHALT" VARCHAR2(20 BYTE), 
"NIEDERLASSUNG" VARCHAR2(20 BYTE), 
"ARBEITSGEBIET" VARCHAR2(20 BYTE)
);
Insert into TV_FVDB.MITARBEITER (MA_NR,NACHNAME,VORNAME,GEHALT,NIEDERLASSUNG,ARBEITSGEBIET) values ('100','Müller','Willi','3100','Köln','Backoffice');
Insert into TV_FVDB.MITARBEITER (MA_NR,NACHNAME,VORNAME,GEHALT,NIEDERLASSUNG,ARBEITSGEBIET) values ('200','Schmitt','Günther','2900','München','Frontoffice');
Insert into TV_FVDB.MITARBEITER (MA_NR,NACHNAME,VORNAME,GEHALT,NIEDERLASSUNG,ARBEITSGEBIET) values ('300','im Glück','Hans','1900','Hamburg','Frontoffice');
Insert into TV_FVDB.MITARBEITER (MA_NR,NACHNAME,VORNAME,GEHALT,NIEDERLASSUNG,ARBEITSGEBIET) values ('400','Courage','Mutter','3400','Köln','Frontoffice');
Insert into TV_FVDB.MITARBEITER (MA_NR,NACHNAME,VORNAME,GEHALT,NIEDERLASSUNG,ARBEITSGEBIET) values ('500','Geißlein','Sieben','3300','Hamburg','Backoffice');
Insert into TV_FVDB.MITARBEITER (MA_NR,NACHNAME,VORNAME,GEHALT,NIEDERLASSUNG,ARBEITSGEBIET) values ('600','Wittchen','Schnee','2100','Hamburg','Backoffice');
COMMIT;







1. Welche Gehaltssumme wird je Niederlassung und Arbeitsgebiet ausgezahlt?

SELECT niederlassung, arbeitsgebiet, sum(gehalt) as GEHALTSSUMME
FROM MITARBEITER
GROUP BY niederlassung, arbeitsgebiet
ORDER BY niederlassung, arbeitsgebiet;



2. Welche Mitarbeiternummer hat der Bestverdiener jeder Niederlassung?

SELECT DISTINCT niederlassung, LAST_VALUE(ma_nr)
 OVER (PARTITION BY niederlassung ORDER BY niederlassung, gehalt 
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "MA_NR__BESTVERDIENER"
FROM MITARBEITER;




3. In welchem Arbeitsgebiet arbeitet der Bestverdiener jeder Niederlassung?

SELECT DISTINCT niederlassung, LAST_VALUE(arbeitsgebiet)
 OVER (PARTITION BY niederlassung ORDER BY niederlassung, gehalt 
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "GEBIET_BESTVERDIENER"
FROM MITARBEITER;




4. Welche Mitarbeiternummer hat der schlechteste Verdiener je Niederlassung und Arbeitsgebiet?

SELECT DISTINCT niederlassung, arbeitsgebiet, FIRST_VALUE(ma_nr)
 OVER (PARTITION BY niederlassung, arbeitsgebiet ORDER BY niederlassung, arbeitsgebiet, gehalt 
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "MA_NR__SCHLECHTVERDIENER"
FROM MITARBEITER
ORDER BY niederlassung, arbeitsgebiet;




5 Wie kann ich eine kumulierende (laufende) Summe der Gehälter je Zeile erzeugen, die bei jeder Niederlassung wieder bei 0 beginnt?

SELECT Mitarbeiter.*, 
 SUM(gehalt) OVER (PARTITION BY niederlassung order by niederlassung ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as KUM_SUM
FROM mitarbeiter;


6. Wie kann ich eine Kreuztabelle (Pivot-Tabelle) mit Anzahlen erzeugen?
Ermittelt wird die Anzahl der Datensätze (=Mitarbeiter) im Schnittpunkt der Matrix. PIVOT funktioniert ab Oracle Version 11g.

SELECT * FROM
(SELECT niederlassung, arbeitsgebiet FROM mitarbeiter) -- Benennung der Felder für ZEILE, (ggf. ZEILE2...), SPALTE
PIVOT (count(*) -- Bei COUNT(*) wird kein Feld benannt   (vrgl. SUM)
FOR arbeitsgebiet IN ('Frontoffice','Backoffice')) -- Benennung Spaltenüberschriften inkl. Spaltensortierung
ORDER by niederlassung; -- Zeilensortierung



7. Wie kann ich eine Kreuztabelle (Pivot-Tabelle) mit Summen erzeugen?
Ermittelt wird die Summe der Gehälter aller Mitarbeiter im Schnittpunkt der Matrix. PIVOT funktioniert ab Oracle Version 11g.

SELECT * FROM
(SELECT niederlassung, arbeitsgebiet, gehalt FROM mitarbeiter)   -- Benennung der Felder für ZEILE, (ggf. ZEILE2...), SPALTE, ZELLE
PIVOT (sum(GEHALT) -- Welche Zellenfunktion? (min, max, sum, avg, etc.)
FOR arbeitsgebiet IN ('Backoffice' as "BO", 'Frontoffice' AS "FO")); -- Spaltenüberschriften





8. Kleiner Exkurs: Kumulierte Summen in Microsoft Access
Dieser Artikel beschäftigt sich eigentlich nur mit SQL Statements in der Oracle Datenbank. Da es für kumulierte Summen in Access SQL jedoch auch eine elegante kleine Lösung mittels DOMSUMME (DSUM- Funktion)gibt, will ich diese hier nicht verheimlichen. 

Mini-Erläuterungen zum folgenden DOMSUMME Beispiel:

  • NIEDERLASSUNG das Gruppierungselement,- jede neue Niederlassung führt zu einem Gruppenwechsel - KUM_SUM wird also wieder auf 0 gesetzt.
  • MA_NR ist das eindeutige Erkennungsmerkmal jedes Satzes. Nur durch den <= Ausdruck kommt es zum zeilenweisen Hochaddieren. Ähnlich könnte auch ein Feld mit wechselnden Datum-Angaben verwendet werden.


SELECT Mitarbeiter.*, DSum("GEHALT","Mitarbeiter","NIEDERLASSUNG = '" & [NIEDERLASSUNG] & "' and MA_NR <='" & [MA_NR] & "'") AS KUM_SUM
FROM Mitarbeiter
ORDER BY Mitarbeiter.NIEDERLASSUNG, Mitarbeiter.MA_NR;







9. Wie kann ich temporäre, kleine, virtuelle Tabellen in Oracle erzeugen?
Manchmal benötigt man eine Mini-Tabelle um z.B. Filterbedingungen für ein SELECT- Statement vorzugeben. In einem Profi- DBMS wie Oracle kann man natürlich nicht "mir nichts dir nichts" Tabellen anlegen und anschließend wieder löschen. Es geht statt dessen mit einem Trick viel einfacher, in dem man via Sub- Selects eine virtuelle Tabelle erzeugt.

SELECT *
FROM (
  SELECT 351 AS KNR, 'Manny' AS VORNAME, 'Marmelade' AS NACHNAME FROM DUAL UNION ALL
  SELECT 684 AS KNR, 'Jonny' AS VORNAME, 'Jolly' AS NACHNAME FROM DUAL UNION ALL
  SELECT 461 AS KNR, 'Ludwig' AS VORNAME, 'Laber' AS NACHNAME FROM DUAL
) HILFSTABELLE






10. Viel Spaß beim Ausprobieren! :-)

Nachtrag: Eine wunderbare Auflistung von 10 atemberaubenden SQL Tricks (sogar auf deutsch) gibt es unter folgendem Link:
https://jaxenter.de/10-sql-tricks-39974


P.S.
Ich übernehme wie immer keine Haftung für jegliche Probleme, die aus der Anwendung der Codeschnipsel entstehen. 

Bildquellenangabe: 
Screenshots aus dem Oracle SQL Developer (www.oracle.com)


Keine Kommentare:

Kommentar veröffentlichen