Wie komplexere Probleme mithilfe von SQL direkt auf der Datenbank gelöst werden können

Komplexe Probleme, clevere SQL-Abfragen
Kommentare

SQL ist die Abfragesprache für relationale Datenbanken – dennoch halten viele Entwickler einen gehörigen Sicherheitsabstand dazu und verlassen sich lieber auf Lösungen in der Applikationslogik. In diesem Artikel wollen wir einige komplexere Probleme beleuchten, die alle mithilfe von SQL-Abfragen gelöst werden können. Der Artikel erhebt keinen Anspruch auf Vollständigkeit und soll eher einen Einstieg bieten, auf den aufbauend weitere Problemstellungen angegangen werden können.

Trotz der Tatsache, dass zunehmend spezialisierte NoSQL-Datenbanken in der Anwendungsentwicklung Einzug halten, bleiben relationale Datenbanksysteme (RDBMS) eine tragende Säule im Entwicklerportfolio. Damit gehört SQL (Structured Query Language) als Abfragesprache für relationale Datenbanken zum Handwerkszeug von Entwicklern wie die eingesetzte Programmiersprache selbst, aber auch zum Beispiel CSS, JavaScript und vieles mehr. Im Zeitalter der oben angesprochenen NoSQL-Datenbanken und im Zeitalter, in dem ORM (Object-Relational Mapping) Komponenten quasi Standard sind (ADO.NET Entity Framework, Hibernate oder Doctrine ORM) hat man als Entwickler immer weniger direkten Kontakt mit SQL oder kann diesen sogar ganz vermeiden. Dabei verliert ein Entwickler auch ein mächtiges Werkzeug aus dem Blickfeld, mit dessen Hilfe er Problemstellungen anders, effizienter und kompakter angehen kann. Leider ist auch dieser Weg nicht ohne Hindernisse.

SQL-Dialekte und Ausprägungen

Eigentlich wäre die SQL-Welt so schön. SQL ist im Grunde eine standardisierte Sprache, deren Ziel es war/ist, Anwendungen schreiben zu können, die vom verwendeten Datenbanksystem unabhängig sind. Wie bei vielen Standards ist es auch mit SQL so, dass sich nicht alle Datenbanksysteme vollständig an den Standard halten, nicht alle Features des Standards implementieren oder aber den Standard um eigene Features erweitern. Die aktuelle Situation im Browsermarkt (JavaScript, CSS und die sog. HTML5-Features) ist ja eine ähnliche. Das führt dazu, dass im Grunde jedes Datenbanksystem eine eigene Syntax bereitstellt – dies wird insbesondere auch bei komplexeren Abfragen zu einem Problem, wenn es darum geht, datenbankunabhängigen Code zu schreiben. Wir wollen uns daher im Rahmen dieses Artikels auf das Datenbanksystem MySQL (> 5.1) beschränken, weil es in der Webentwicklung einen durchaus großen Stellenwert besitzt. An einigen Stellen werde ich auf Lösungen in anderen Datenbanksystemen (vornehmlich Oracle oder Microsoft SQL Server) hinweisen, wenn deren Lösungen wesentlich eleganter sind als die MySQL-Variante.

Voraussetzungen

Für das Nachvollziehen der aufgezeigten Lösungen ist ein grundlegendes Verständnis von SQL als Abfragesprache nötig. Grundkonzepte wie Spaltenauswahl, Abfragebedingungen (WHERE), Sortieren der Ergebnisse (ORDER BY) und Tabellenverknüpfungen (JOIN) sollten dem Leser vertraut sein. Alle Beispiele können mithilfe eines installierten MySQL-Servers und der MySQL-Konsole sehr einfach nachvollzogen werden. Die verwendeten Tabellen und Datensätze sind auf einem GitHub Repository abrufbar und lassen sich über die Konsole einfach in den MySQL-Server einspielen. Mit folgendem Code wird die Datenbank erstellt und die SQL-Datei eingespielt:

$ mysqladmin create sql_demo
$ mysql sql_demo < /pfad/zur/sql_demo.sql

Einführung

Der Einstieg in unsere kleine Tour durch die vielfältigen Möglichkeiten von SQL bildet die Mitarbeiterdatenbank einer Firma, die auch Informationen zu den Gehältern unserer Mitarbeiter enthält. Listing 1 zeigt die relevanten Spalten der Mitarbeitertabelle employee.

mysql> SELECT dept_id, emp_id, emp_no, name, job, salary 
FROM employee;
+---------+--------+--------+-----------+-------------------+--------+
| dept_id | emp_id | emp_no | name      | job               | salary |
+---------+--------+--------+-----------+-------------------+--------+
|       2 |      1 | S001   | Schmidt   | Entwickler        |    800 |
|       3 |      2 | A001   | Albrecht  | Verkäufer         |   1600 |
|       3 |      3 | W001   | Werner    | Verkäufer         |   1250 |
|       2 |      4 | J002   | Jones     | Teamleiter        |   2975 |
|       3 |      5 | M001   | Marbach   | Verkäufer         |   1250 |
|       3 |      6 | B001   | Bürger    | Teamleiter        |   2850 |
|       1 |      7 | C001   | Carl      | Teamleiter        |   2450 |
|       2 |      8 | S002   | Seeberger | QA                |   3000 |
|       1 |      9 | K001   | Karle     | Geschäftsführer   |   5000 |
|       3 |     10 | T001   | Thürmann  | Verkäufer         |   1500 |
|       2 |     11 | A002   | Alessi    | Entwickler        |   1100 |
|       3 |     12 | J001   | Jakulov   | Entwickler        |    950 |
|       2 |     13 | F001   | Färber    | QA                |   3000 |
|       1 |     14 | M002   | Müller    | Entwickler        |   1300 |
+---------+--------+--------+-----------+-------------------+--------+

Im Folgenden übernehmen wir die Rolle von Herrn Karle, dem Geschäftsführer, der sich auf Basis der Mitarbeiterdatenbank und später einiger anderer Tabellen ein kleines Management-Dashboard bauen möchte. Zum Glück, das sei an dieser Stelle erwähnt, ist Herr Karle nicht nur Geschäftsführer, sondern zugleich auch der SQL-Profi der Firma.

Simple Statistik: ein einfacher Einstieg

Wir wollen uns einen ersten groben Überblick über die Gehaltsstruktur unserer Firma verschaffen. Hierfür benötigen wir noch nicht einmal besondere SQL-Kenntnisse, denn eine aggregierende Abfrage (GROUP BY) und die Funktionen MIN(), MAX(), SUM() und AVG() helfen uns, das jeweils kleinste und größte Gehalt, die Summe der Gehälter und das Durchschnittsgehalt pro Abteilung abzufragen. Listing 2 zeigt Minimum, Maximum, Summe und Mittelwert der Gehälter gruppiert nach Abteilung.

mysql> SELECT dept_id, MIN(salary), MAX(salary), SUM(salary), AVG(salary) 
FROM employee 
GROUP BY dept_id;
+---------+-------------+-------------+-------------+-------------+
| dept_id | MIN(salary) | MAX(salary) | SUM(salary) | AVG(salary) |
+---------+-------------+-------------+-------------+-------------+
|       1 |        1300 |        5000 |        8750 |   2916.6667 |
|       2 |         800 |        3000 |       10875 |   2175.0000 |
|       3 |         950 |        2850 |        9400 |   1566.6667 |
+---------+-------------+-------------+-------------+-------------+

Der Modalwert

Im Weiteren wollen wir noch wissen, welche Gehälter am häufigsten bezogen werden und in welchen Abteilungen diese jeweils bezahlt werden. Diese Frage beantwortet uns der Modalwert oder Modus. Wie in Listing 3 zu sehen, werden 1 250 Euro und 3 000 Euro je zweimal gezahlt (Werner und Marbach in Abteilung 3, bzw. Seeberger und Färber in Abteilung 2).

mysql> SELECT salary, GROUP_CONCAT(DISTINCT dept_id) AS dept, COUNT(*)  
FROM employee 
GROUP BY salary 
HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM employee GROUP BY salary);
+--------+------+----------+
| salary | dept | COUNT(*) |
+--------+------+----------+
|   1250 | 3    |        2 |
|   3000 | 2    |        2 |
+--------+------+----------+

Die äußere SELECT-Abfrage liefert die Anzahl der Mitarbeiter mit einem bestimmten Gehalt, während der Vergleich COUNT(*) >= ALL(…) die Liste auf die Gehälter reduziert, die am häufigsten auftreten. Interessant hierbei ist, dass wir die MySQL-spezifische Funktion GROUP_CONCAT(DISTINCT…) benutzen, um eine Liste der Abteilungen zu bekommen, in denen die Gehälter bezahlt werden – standardmäßig verwendet MySQL, als Trennzeichen der Listenelemente (was man aber im vorliegenden Beispiel nicht erkennen kann, weil die Gehälter jeweils nur in einer Abteilung bezahlt werden). Das DISTINCT ist wichtig, damit wir keine Duplikate erhalten.

Wichtig bei Filtern auf gruppierten Ergebnissen ist, dass die Filter nicht in einer WHERE-Bedingung stehen können, da diese bereits vor der Gruppierung ausgewertet werden, sondern in einem HAVING stehen müssen.

Anteile am Gesamten

Als Geschäftsführer ist es für uns auch wichtig, zu wissen, welchen Anteil an den Gesamtgehaltszahlungen die einzelnen Abteilungen ausmachen. Listing 4 zeigt, dass die Entwicklungsabteilung (2) mit 37 Prozent den höchsten Anteil an den Gehaltszahlungen hat.

mysql> SELECT d.dept_id, d.name,
  SUM(e.salary)/(SELECT SUM(salary) FROM employee)*100 AS percentage 
FROM department d 
LEFT JOIN employee e ON e.dept_id = d.dept_id 
GROUP BY d.dept_id, d.name;
+---------+-------------+------------+
| dept_id | name        | percentage |
+---------+-------------+------------+
|       1 | Buchhaltung |    30.1464 |
|       2 | Entwicklung |    37.4677 |
|       3 | Vertrieb    |    32.3859 |
|       4 | Marketing   |       NULL |
+---------+-------------+------------+

Ohne den skalaren Sub-SELECT in der percentage-Spalte erhalten wir eine Liste aller Abteilungen (dank des LEFT JOIN auch der Abteilung Marketing, die aktuell keine Mitarbeiter hat) mit der Summe der Gehälter in der jeweiligen Abteilung. Der skalare Sub-SELECT liefert dann dazu die Gesamtsumme aller Gehälter – der Rest ist einfache Mathematik.

Skalare Sub-SELECTs eignen sich immer dann, wenn zu jeder Zeile noch weitere Informationen hinzugezogen werden müssen, die sich nicht mithilfe einer JOIN-Bedingung beschaffen lassen. Der Phantasie sind hier keine Grenzen gesetzt – es sollte nur klar sein, dass die Datenbank den Sub-SELECT für jede Zeile der Ergebnismenge ausführen muss – insbesondere dann, wenn es sich um korrelierende Sub-SELECTs handelt, deren Ergebnisse von äußeren Bedingungen beeinflusst werden (Beispiele folgen noch).

Zugriffe auch über Zeilen hinweg

Jetzt werden wir etwas mutiger. Wir wollen uns nun eine Historie unserer Mitarbeiter anzeigen lassen, aus der hervorgeht, wann welcher Mitarbeiter eingestellt wurde und welcher Zeitraum zwischen zwei aufeinanderfolgenden Einstellungen vergangen ist: Mit reinem SQL eine kleine Herausforderung, aber ich erwähnte ja schon, dass Herr Karle, der Geschäftsführer, durchaus versiert ist im Umgang mit SQL. Listing 5 zeigt, von der Einstellung von Müller bis zur Einstellung von Seeberger verging fast ein Jahr

mysql> SELECT x.name, x.hired_date, 
  DATEDIFF(x.hired_date, x.last_hired_date) AS diff 
FROM (
  SELECT e.dept_id, e.name, e.hired_date, (
    SELECT MAX(d.hired_date) 
    FROM employee d 
    WHERE d.hired_date < e.hired_date
  ) AS last_hired_date    
  FROM employee e
  ORDER BY e.hired_date
)  x;
+-----------+------------+------+
| name      | hired_date | diff |
+-----------+------------+------+
| Schmidt   | 2000-12-17 | NULL |
| Bürger    | 2001-05-01 |  135 |
| Albrecht  | 2002-02-20 |  295 |
| Werner    | 2002-02-22 |    2 |
| Jones     | 2002-04-02 |   39 |
| Carl      | 2002-06-09 |   68 |
| Thürmann  | 2002-09-08 |   91 |
| Marbach   | 2002-09-28 |   20 |
| Karle     | 2002-11-17 |   50 |
| Färber    | 2002-12-03 |   16 |
| Jakulov   | 2002-12-03 |   16 |
| Müller    | 2003-01-23 |   51 |
| Seeberger | 2003-12-09 |  320 |
| Alessi    | 2004-01-12 |   34 |
+-----------+------------+------+

Das Hauptproblem bei dieser Art von Fragestellungen ist: Wie bekomme ich Informationen über die nachfolgende (oder vorangegangene) Zeile in meine aktuelle Ergebniszeile?

Schauen wir uns zuerst einmal den inneren Sub-SELECT in der FROM-Bedingung an, in dem eigentlich schon die ganze Lösung steckt. Für jeden Mitarbeiter finden wir in Listing 6 den Mitarbeiter, der vor ihm eingestellt wurde und greifen uns das Einstellungsdatum ab.

SELECT e.dept_id, e.name, e.hired_date, (
  SELECT MAX(d.hired_date) 
  FROM employee d 
  WHERE d.hired_date < e.hired_date
) AS last_hired_date    
FROM employee e
ORDER BY e.hired_date;
+---------+-----------+------------+-----------------+
| dept_id | name      | hired_date | last_hired_date |
+---------+-----------+------------+-----------------+
|       2 | Schmidt   | 2000-12-17 | NULL            |
|       3 | Bürger    | 2001-05-01 | 2000-12-17      |
|       3 | Albrecht  | 2002-02-20 | 2001-05-01      |
|       3 | Werner    | 2002-02-22 | 2002-02-20      |
|       2 | Jones     | 2002-04-02 | 2002-02-22      |
|       1 | Carl      | 2002-06-09 | 2002-04-02      |
|       3 | Thürmann  | 2002-09-08 | 2002-06-09      |
|       3 | Marbach   | 2002-09-28 | 2002-09-08      |
|       1 | Karle     | 2002-11-17 | 2002-09-28      |
|       2 | Färber    | 2002-12-03 | 2002-11-17      |
|       3 | Jakulov   | 2002-12-03 | 2002-11-17      |
|       1 | Müller    | 2003-01-23 | 2002-12-03      |
|       2 | Seeberger | 2003-12-09 | 2003-01-23      |
|       2 | Alessi    | 2004-01-12 | 2003-12-09      |
+---------+-----------+------------+-----------------+

Wieder benutzen wir einen skalaren Sub-SELECT, um an Daten heranzukommen, die sich anders nicht oder nur schwer beschaffen lassen würden. In diesem Fall sprechen wir von einem korrelierenden (skalaren) Sub-SELECT, da der innere SELECT auf Informationen aus dem äußeren SELECT (e.hired_date) zurückgreift. Der SELECT sucht für jede Zeile (also jeden Mitarbeiter) das späteste Einstellungsdatum, das kleiner ist als das aktuelle – sprich also das Einstellungsdatum des Mitarbeiters, der direkt vor dem aktuellen Mitarbeiter eingestellt wurde.

Durch diesen Kunstgriff haben wir nun Einstellungsdatum und vorheriges Einstellungsdatum in einer Zeile und können mithilfe der DATEDIFF-Funktion ausrechnen, wie viele Tage zwischen den beiden Datumswerten liegen.

Ein kleines Problem können wir an dieser Stelle aber auch schon feststellen – ein Problem, das sich durch alle Lösungen zieht, bei denen auf Vorgänger- oder Nachfolgerzeilen zugegriffen werden muss. Herr Färber und Frau Jakulov wurden beide am 03.12.2002 eingestellt, davor, am 17.11.2002, wurde Herr Karle selbst eingestellt. Unsere Historie zeigt aber für beide (Färber und Jakulov) eine Differenz von 16 Tagen an, obwohl man eigentlich meinen könnte, von Färber auf Jakulov (oder andersherum) wäre die Differenz 0 Tage. Duplikate bzw. nicht eindeutige Sortierungen können bei solchen Lösungen zu einem echten Problem werden. Dies muss man sich bewusst machen, wenn man solche Lösungen implementiert.

Als kleine Anmerkung noch an dieser Stelle: Viele der skalaren Sub-SELECTs lassen sich mithilfe von self-JOINs und GROUP-BY-Bedingungen umschreiben. Je nach Anwendungsfall und Vorlieben kann das eine oder das andere schöner, übersichtlicher, nachvollziehbarer oder auch schneller sein. Als kleines Beispiel sei unser obiger innerer Sub-SELECT kurz in andere Form gebracht:

SELECT e.dept_id, e.name, e.hired_date, MAX(d.hired_date) AS last_hired_date
FROM employee e
LEFT JOIN employee d ON d.hired_date < e.hired_date
 GROUP BY e.dept_id, e.name, e.hired_date
 ORDER BY e.hired_date 

Unschön an dieser Variante ist mitunter aber, dass alle Spalten aus der primären Tabelle, die im Ergebnis aufgeführt werden sollen, in die GROUP-BY-Bedingung aufgenommen werden müssen (jedenfalls nach Standard-SQL; MySQL ist da etwas nachsichtiger).

Zugriff auf Zeilen vor und nach der aktuellen Zeile

Für die Lösung von Problemen mithilfe von SQL-Abfragen wird oft eine Möglichkeit benötigt, auf vorangegangene oder nachfolgende Zeilen des Ergebnisses zuzugreifen, um damit Berechnungen anstellen zu können. In unserem Beispiel haben wir dies mit einem skalaren Sub-SELECT gelöst. Oracle oder der Microsoft-SQL-Server zum Beispiel bietet für dieses Problem eine Erweiterung der SQL-Syntax. Mit LEAD OVER und LAG OVER lassen sich nachfolgende bzw. vorangegangene Zeilen auswählen, sodass unser Beispiel in diesen Datenbanksystemen folgendermaßen aussehen könnte:

SELECT name, hired_date, hired_date - x.last_hired_date diff 
FROM (
  SELECT dept_id, name, hired_date, 
    LAG(hired_date)OVER(ORDER BY hired_date DESC) last_hired_date
  FROM employee
  ORDER BY hired_date
);

Eimerweise

Wir schließen nun die Arbeit am Management-Dashboard ab. Herr Karle benötigt nun noch eine Auswertung, die ihm seine Mitarbeiter in Gruppen zu jeweils fünf Personen sortiert nach Stammnummern aufteilt. Das Aufteilen von Daten in buckets (also wörtlich: Eimern) – entweder in Eimern einer fixen Größe oder einer bestimmten Anzahl von Eimern – ist ein klassisches Problem bei der Auswertung von Daten.

Wir erhalten drei Gruppen: fünf Mitarbeiter mit Stammnummern A001–F001, fünf mit Stammnummern J001–M002 und die verbleibenden vier mit den Stammnummern S001–W001 (Listing 7).

SELECT CEIL(x.rank / 5) AS grp, x.name, x.emp_no 
FROM (
  SELECT e.name, e.emp_no, (
    SELECT COUNT(*) 
    FROM employee d 
    WHERE d.emp_no < e.emp_no
  ) + 1 AS rank 
  FROM employee e
) AS x 
ORDER BY grp, emp_no;
+------+-----------+--------+
| grp  | name      | emp_no |
+------+-----------+--------+
|    1 | Albrecht  | A001   |
|    1 | Alessi    | A002   |
|    1 | Bürger    | B001   |
|    1 | Carl      | C001   |
|    1 | Färber    | F001   |
|    2 | Jakulov   | J001   |
|    2 | Jones     | J002   |
|    2 | Karle     | K001   |
|    2 | Marbach   | M001   |
|    2 | Müller    | M002   |
|    3 | Schmidt   | S001   |
|    3 | Seeberger | S002   |
|    3 | Thürmann  | T001   |
|    3 | Werner    | W001   |
+------+-----------+--------+

Wer genau aufgepasst hat, erkennt, dass die Lösung hier der Lösung zum Problem der Einstellungsdaten ähnelt. Wieder suchen wir mit einem skalaren Sub-SELECT Mitarbeiter, deren Stammnummern kleiner sind als die Stammnummer des aktuellen Mitarbeiters. Aus der Anzahl dieser Mitarbeiter generieren wir ein aufsteigendes Ranking der Stammnummern. Listing 8 zeigt das Ranking der Mitarbeiterstammnummern.

SELECT e.name, e.emp_no, (
  SELECT COUNT(*) 
  FROM employee d
  WHERE d.emp_no < e.emp_no
) + 1 AS rank 
FROM employee e;
+-----------+--------+------+
| name      | emp_no | rank |
+-----------+--------+------+
| Schmidt   | S001   |   11 |
| Albrecht  | A001   |    1 |
| Werner    | W001   |   14 |
| Jones     | J002   |    7 |
| Marbach   | M001   |    9 |
| Bürger    | B001   |    3 |
| Carl      | C001   |    4 |
| Seeberger | S002   |   12 |
| Karle     | K001   |    8 |
| Thürmann  | T001   |   13 |
| Alessi    | A002   |    2 |
| Jakulov   | J001   |    6 |
| Färber    | F001   |    5 |
| Müller    | M002   |   10 |
+-----------+--------+------+

Im äußeren SELECT nutzen wir dann CEIL(), also das Aufrunden, und die Division durch die gewünschte Eimergröße, um aus dem Rang eine Gruppennummer zu generieren.

Wie bereits beim Beispiel zu den Einstellungsdaten angemerkt, gilt auch hier, dass der Sub-SELECT zur Bestimmung des Rangs voraussetzt, dass auf einer eindeutigen Spalte operiert wird. Das vorliegende Beispiel würde daher mit einem Ranking nach Gehältern nicht ganz so schön funktionieren, wenn man die Eimergröße 5 als hartes Limit ansieht, denn 3 000 € und 1 250 € tauchen als Gehälter jeweils zweimal auf. Das Problem sieht man sehr gut, wenn wir uns die Ausgabe des Sub-SELECTs anschauen (Listing 9). Gleiche Gehälter erhalten den gleichen Rang, der nachfolgende Rang wird ausgelassen.

SELECT e.name, e.salary, (
  SELECT COUNT(*)
  FROM employee d
  WHERE d.salary < e.salary
) + 1 AS rank
FROM employee e 
ORDER BY rank;
+-----------+--------+------+
| name      | salary | rank |
+-----------+--------+------+
| Schmidt   |    800 |    1 |
| Jakulov   |    950 |    2 |
| Alessi    |   1100 |    3 |
| Werner    |   1250 |    4 | <==
| Marbach   |   1250 |    4 | <==
| Müller    |   1300 |    6 |
| Thürmann  |   1500 |    7 |
| Albrecht  |   1600 |    8 |
| Carl      |   2450 |    9 |
| Bürger    |   2850 |   10 |
| Jones     |   2975 |   11 |
| Färber    |   3000 |   12 | <==
| Seeberger |   3000 |   12 | <==
| Karle     |   5000 |   14 |
+-----------+--------+------+

Die Gruppierung in Eimern mit fünf Elementen würde hier zwar zufällig funktionieren, aber beim Gruppieren nach vier oder sechs Elementen hätten wir Eimer, die größer als die geforderten fünf Elemente sind. Listing 10 zeigt, dass bei einer Eimergröße von 5 alles noch gut aussieht.

SELECT CEIL(x.rank / 5) AS grp, x.name, x.salary
FROM (
  SELECT e.name, e.salary, (
    SELECT COUNT(*)
    FROM employee d
    WHERE d.salary < e.salary
  ) + 1 AS rank
  FROM employee e
) AS x 
ORDER BY grp, salary;
+------+-----------+--------+
| grp  | name      | salary |
+------+-----------+--------+
|    1 | Schmidt   |    800 |
|    1 | Jakulov   |    950 |
|    1 | Alessi    |   1100 |
|    1 | Marbach   |   1250 |
|    1 | Werner    |   1250 |
|    2 | Müller    |   1300 |
|    2 | Thürmann  |   1500 |
|    2 | Albrecht  |   1600 |
|    2 | Carl      |   2450 |
|    2 | Bürger    |   2850 |
|    3 | Jones     |   2975 |
|    3 | Seeberger |   3000 |
|    3 | Färber    |   3000 |
|    3 | Karle     |   5000 |
+------+-----------+--------+

Listing 11 zeigt aber, bei einer Eimergröße von 6 fallen die 3 000 € genau auf die Eimergrenze, was dazu führt, dass die zweite Gruppe sieben Mitarbeiter enthält (dasselbe gilt für die Eimergröße von 3).

+------+-----------+--------+
| grp  | name      | salary |
+------+-----------+--------+
|    1 | Schmidt   |    800 |
|    1 | Jakulov   |    950 |
|    1 | Alessi    |   1100 |
|    1 | Marbach   |   1250 |
|    1 | Werner    |   1250 |
|    1 | Müller    |   1300 |
|    2 | Thürmann  |   1500 |
|    2 | Albrecht  |   1600 |
|    2 | Carl      |   2450 |
|    2 | Bürger    |   2850 |
|    2 | Jones     |   2975 |
|    2 | Seeberger |   3000 |
|    2 | Färber    |   3000 |
|    3 | Karle     |   5000 |
+------+-----------+--------+

Je nach Fragestellung kann dies ein Problem sein oder auch nicht. Wichtig ist an dieser Stelle nur, dass man sich der Problematik bewusst ist.

Schauen wir uns noch an, wie wir die Mitarbeiter in eine bestimmte Anzahl von Eimern aufteilen, ohne die Eimergröße zu kennen. Die Mitarbeiter werden aufsteigend ihrer Stammnummer reihum in die geforderten 4 Eimer verteilt (Listing 12).

SELECT MOD(COUNT(*) - 1, 4) + 1 AS grp, e.name, e.emp_no
FROM employee e, employee d
WHERE e.emp_no >= d.emp_no
GROUP BY e.emp_no, e.name
ORDER BY 1, e.name;
+------+-----------+--------+
| grp  | name      | emp_no |
+------+-----------+--------+
|    1 | Albrecht  | A001   |
|    1 | Färber    | F001   |
|    1 | Marbach   | M001   |
|    1 | Thürmann  | T001   |
|    2 | Alessi    | A002   |
|    2 | Jakulov   | J001   |
|    2 | Müller    | M002   |
|    2 | Werner    | W001   |
|    3 | Bürger    | B001   |
|    3 | Jones     | J002   |
|    3 | Schmidt   | S001   |
|    4 | Carl      | C001   |
|    4 | Karle     | K001   |
|    4 | Seeberger | S002   |
+------+-----------+--------+

In diesem Fall hilft uns jetzt ein self-JOIN, um den Rang der Stammnummer zu ermitteln. Mithilfe der Modulo-Funktion (MOD()) teilen wir den Rang in vier Eimer auf (das „-1“ und „+1“ sorgt dafür, dass die Aufteilung im ersten Eimer beginnt und die Gruppennummer bei 1 startet). Zu beachten ist, dass die Verteilung der Mitarbeiter auf die Eimer reihum (round-robin) erfolgt. Jeder nächste Mitarbeiter landet damit immer in dem Eimer, in dem sich am wenigsten Mitarbeiter befinden. Dadurch macht diese Lösung auch wenig Sinn, um unsere Mitarbeiter nach Gehalt in „Eimer“ einzusortieren.

Zusammenfassen von Zeiträumen

Nachdem Herr Karle, unser Geschäftsführer, seine Arbeit an seinem Mitarbeiter-Dashboard beendet hat, wendet er sich noch einem letzten Problem zu: Aus einer Tabelle mit Projektdaten möchte er nun eine Auflistung der Zeiträume finden, die durch jeweils aufeinanderfolgende Projekte geblockt sind, um so Leerzeiten besser erkennen zu können. Die Tabelle project sieht aus wie in Listing 13.

mysql> SELECT * FROM project;
+---------+------------+------------+
| proj_id | proj_start | proj_end   |
+---------+------------+------------+
|       1 | 2010-01-01 | 2010-01-02 |
|       2 | 2010-01-02 | 2010-01-06 |
|       3 | 2010-01-06 | 2010-01-10 |
|       4 | 2010-01-10 | 2010-01-15 |
|       5 | 2010-01-18 | 2010-01-20 |
|       6 | 2010-01-20 | 2010-01-23 |
|       7 | 2010-01-23 | 2010-01-24 |
|       8 | 2010-01-24 | 2010-01-27 |
|       9 | 2010-01-27 | 2010-02-02 |
|      10 | 2010-02-03 | 2010-02-07 |
|      11 | 2010-02-07 | 2010-02-08 |
|      12 | 2010-02-12 | 2010-02-14 |
|      13 | 2010-02-14 | 2010-02-15 |
|      14 | 2010-02-15 | 2010-02-22 |
+---------+------------+------------+

Man erkennt, dass einige Projekte direkt aufeinanderfolgen. Diese wollen wir in Projektgruppen zusammenfassen und das Start- und Enddatum der Projektgruppe ausgeben. Listing 14 zeigt, unsere 14 Projekte lassen sich in vier Projektgruppen aufteilen.

mysql> SELECT z.proj_group, 
  MIN(z.proj_start) AS proj_start, 
  MAX(z.proj_end) AS proj_end 
FROM (
  SELECT x.proj_id, x.proj_start, x.proj_end, (
    SELECT SUM(y.flag) 
    FROM (
      SELECT a.*, 
      CASE 
      WHEN (
        SELECT b.proj_id 
        FROM project b 
        WHERE a.proj_start = b.proj_end
      ) IS NOT NULL 
      THEN 0 
      ELSE 1 
      END AS flag 
      FROM project a
    ) y 
    WHERE y.proj_id <= x.proj_id
  ) AS proj_group 
  FROM project x
) z 
GROUP BY z.proj_group;
+------------+------------+------------+
| proj_group | proj_start | proj_end   |
+------------+------------+------------+
|          1 | 2010-01-01 | 2010-01-15 |
|          2 | 2010-01-18 | 2010-02-02 |
|          3 | 2010-02-03 | 2010-02-08 |
|          4 | 2010-02-12 | 2010-02-22 |
+------------+------------+------------+

Lasst uns das ganze einmal „aufdröseln“, denn diese Abfrage stellt alle bisher vorgestellten in den Schatten. Am einfachsten gehen wir in diesem Fall von innen nach außen vor. Der innerste Sub-SELECT enthält eine CASE-Anweisung, deren Aufgabe es ist, den Wert 1 zurückzugeben, wenn es kein Projekt gibt, dessen Ende auf den Start des aktuellen Projekts passt, also dann, wenn das aktuelle Projekt der Anfang einer Projektgruppe ist. Die flag-Spalte enthält eine 1, wenn das Projekt der Anfang einer Projektgruppe ist (Listing 15).

mysql> SELECT a.*, 
  CASE 
WHEN (
  SELECT b.proj_id 
  FROM project b 
  WHERE a.proj_start = b.proj_end
) IS NOT NULL 
THEN 0 
ELSE 1 
END AS flag 
FROM project a;
+---------+------------+------------+------+
| proj_id | proj_start | proj_end   | flag |
+---------+------------+------------+------+
|       1 | 2010-01-01 | 2010-01-02 |    1 |
|       2 | 2010-01-02 | 2010-01-06 |    0 |
|       3 | 2010-01-06 | 2010-01-10 |    0 |
|       4 | 2010-01-10 | 2010-01-15 |    0 |
|       5 | 2010-01-18 | 2010-01-20 |    1 |
|       6 | 2010-01-20 | 2010-01-23 |    0 |
|       7 | 2010-01-23 | 2010-01-24 |    0 |
|       8 | 2010-01-24 | 2010-01-27 |    0 |
|       9 | 2010-01-27 | 2010-02-02 |    0 |
|      10 | 2010-02-03 | 2010-02-07 |    1 |
|      11 | 2010-02-07 | 2010-02-08 |    0 |
|      12 | 2010-02-12 | 2010-02-14 |    1 |
|      13 | 2010-02-14 | 2010-02-15 |    0 |
|      14 | 2010-02-15 | 2010-02-22 |    0 |
+---------+------------+------------+------+

Der nächste Sub-SELECT auf unserem Weg sorgt nun dafür, dass jede Projektgruppe eine eindeutige ID erhält, indem die flag-Spalte für alle vorangegangenen Projekte der aktuellen Projektzeile aufsummiert wird. proj_group wird durch Aufsummieren der flag-Spalte generiert (Listing 16).

mysql> SELECT x.proj_id, x.proj_start, x.proj_end, (
  SELECT SUM(y.flag) 
  FROM ( ... ) y 
  WHERE y.proj_id <= x.proj_id
) AS proj_group 
FROM project x;
+---------+------------+------------+------------+
| proj_id | proj_start | proj_end   | proj_group |
+---------+------------+------------+------------+
|       1 | 2010-01-01 | 2010-01-02 |          1 |
|       2 | 2010-01-02 | 2010-01-06 |          1 |
|       3 | 2010-01-06 | 2010-01-10 |          1 |
|       4 | 2010-01-10 | 2010-01-15 |          1 |
|       5 | 2010-01-18 | 2010-01-20 |          2 |
|       6 | 2010-01-20 | 2010-01-23 |          2 |
|       7 | 2010-01-23 | 2010-01-24 |          2 |
|       8 | 2010-01-24 | 2010-01-27 |          2 |
|       9 | 2010-01-27 | 2010-02-02 |          2 |
|      10 | 2010-02-03 | 2010-02-07 |          3 |
|      11 | 2010-02-07 | 2010-02-08 |          3 |
|      12 | 2010-02-12 | 2010-02-14 |          4 |
|      13 | 2010-02-14 | 2010-02-15 |          4 |
|      14 | 2010-02-15 | 2010-02-22 |          4 |
+---------+------------+------------+------------+

Mit dieser Vorarbeit ist das Erstellen der finalen Ergebnisliste eine einfache aggregierende Abfrage mit einem MIN() auf dem Projektstart und einem MAX() auf dem Projektende gruppiert auf der Projektgruppe. Die äußere Abfrage gestaltet sich dann als relativ einfach:

mysql> SELECT z.proj_group, 
  MIN(z.proj_start) AS proj_start, 
  MAX(z.proj_end) AS proj_end 
FROM ( ... ) z 
GROUP BY z.proj_group;

Zugegeben, dieses Beispiel war schon ein recht exotischer Vertreter. Es zeigt jedoch, wie sich Probleme aufteilen und einzeln lösen lassen, sodass sich die Teillösungen dann am Ende zur Gesamtlösung zusammensetzen lassen. Zentraler Bestandteil der vorgestellten Lösungen sind fast immer Sub-SELECTs, die dem Entwickler eine Vielzahl von Möglichkeiten an die Hand geben, um aus dem strikt zeilenbasierten Vorgehen einfacher SQL-Abfragen auszubrechen.

Natürlich erhebe ich keinen Anspruch auf Vollständigkeit und der eine oder andere SQL-Wizard wird sicher auch noch bessere Lösungen für die aufgezeigten Problemstellungen kennen. Wichtig war mir aber im Rahmen des Artikels, den Lesern einen kleinen Einblick in die Mächtigkeit von SQL-Anfragen zu geben, und Interesse dahingehend zu wecken.

Natürlich spielt auch Performance in der Wahl der Mittel eine Rolle und gerade mehrfach verschachtelte Abfragen auf großen Datenbeständen sind nicht gerade dafür bekannt, sonderlich performant zu sein. Dazu kommt noch, dass Query-Pläne (wie z. B. aus einem EXPLAIN SELECT) mit steigender Komplexität der Abfrage natürlich auch zunehmend komplizierter werden und sich die Abfragen dann auch nicht mehr so einfach optimieren lassen. Gerade Abfragen aber, die größere Reporting-Probleme lösen sollen, sind auch nicht immer dazu gedacht, ad hoc ausgeführt werden zu können. Ein geeignetes Caching ist hier natürlich unerlässlich.

Weiterführende Literatur

Interessierten Lesern sei ganz besonders das Buch „SQL Cookbook“ von Anthony Molinaro und Jonathan Gennick ans Herz gelegt (Molinaro, Anthony; Gennick, Jonathan: „SQL Cookbook“, 1. Auflage, 2006, O’Reilly). Dort finden sich hunderte weiterer SQL-Rezepte für alltägliche und nicht so alltägliche Problemstellungen – alle für die Datenbanksysteme SQL Server, PostgreSQL, Oracle, DB2 und MySQL aufbereitet (sofern möglich). Einige der hier gezeigten Lösungen und Probleme basieren ebenfalls auf diesem Buch.

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -