© saicle/Shutterstock.com
Wie komplexere Probleme mithilfe von SQL direkt auf der Datenbank gelöst werden können

Komplexe Probleme, clevere SQL-Abfragen


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 No­SQL-­Datenbanken in der Anwendungsentwicklung Einzug halten, bleiben relationale Datenbanksysteme (RDBMS) eine tragende Säule im Entwicklerportfolio. Damit gehört SQL (Structured Query Language) [1] 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 [2] 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 Web­entwicklung 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 Abfrage­sprache 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 [3] 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.

Listing 1

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.

Listing 2

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).

Listing 3

mysql> SELECT salary, GROUP_CONCAT(DISTINCTdept_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 DIS­TINCT 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.

Listing 4

mysql> SELECT d.dept_id, d.name, SUM(e.salary)/(SELECT SUM(salary) FROMemployee)*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ä...

Neugierig geworden?

Angebote für Teams

Für Firmen haben wir individuelle Teamlizenzen. Wir erstellen Ihnen gerne ein passendes Angebot.

Das Library-Modell:
IP-Zugang

Das Company-Modell:
Domain-Zugang