MySQL 8: Window Functions und Common Table Expressions für MySQL

Erwachsen geworden

Daniel Bößwetter

Ein erweitertes SQL-Vokabular, eine bessere Konsistenzprüfung, Anbindung an dokumentenorientierte (NoSQL-)Datenbanken – mit MySQL 8.0 macht Oracle deutlich, dass der Datenbankhersteller die Weiterentwicklung der weitverbreiteten relationalen Datenbank ernst nimmt.

MySQL ist in diesem Frühjahr 23 Jahre alt geworden und nun – unter Oracles Fittichen – in seiner achten Auflage erschienen. Dass die unbestreitbar populärste Open-Source-Datenbank den Kinderschuhen entwachsen ist, zeigt ein Blick auf die neuen Features, die das RDBMS noch besser für den Unternehmenseinsatz geeignet machen. Vor allem bei der Erweiterung des implementierten SQL-Sprachumfangs, bei der Performance und bei den Konsistenzgarantien haben die Entwickler nachgezogen. Anhänger des MySQL-Forks MariaDB dürften allerdings wenig überrascht sein: Ein Teil der neuen Features gilt in ihrer Datenbank schon seit Längerem als stabil.

Während sich die Datenbankabfragesprache SQL in ihrer langen Geschichte beständig weiterentwickelt hat (die aktuelle Version ist SQL:2016), hinkte die Implementierung neuerer Features in MySQL dem Standard deutlich hinterher. Eine lang vermisste Eigenschaft, die das Konkurrenzprodukt PostgreSQL bereits seit Version 8.4 aus dem Jahr 2009 kennt, sind die Common Table Expressions (CTEs). Sie erlauben es, komplexe Querys in überschaubare Teile zu zerlegen, die anschließend ähnlich wie Views anhand ihres Namens verwendet werden können (im Gegensatz zu einfachen Subquerys auch mehrfach).

WITH
german_sales AS
(SELECT * FROM sales WHERE country='DE'),
monthly_sales AS
(SELECT MONTH(created_at) mon, SUM(price) total_price
FROM german_sales group by 1)
SELECT MONTH(created_at), price/total_price
FROM german_sales JOIN monthly_sales ON mon=month(created_at);

Wie in Listing 1 zu sehen ist, werden die CTEs vor der Haupt-Query mit WITH definiert. Im Beispiel werden zunächst die Verkäufe in Deutschland aus einer imaginären Sales-Tabelle selektiert und dann in der zweiten CTE nach Monaten aggregiert. Die Haupt-Query berechnet den Anteil eines einzelnen Verkaufs am Umsatz des Gesamtmonats. Common Table Expressions reduzieren Redundanz und verringern die Fehleranfälligkeit. MariaDB implementiert sie seit Version 10.1 und seit 10.2 (im Mai 2017 erschienen) auch rekursiv.

SQL mit Fensterblick

Eine andere wichtige syntaktische SQL-Erweiterung sind die Window Functions. Diese analytischen Fensterfunktionen greifen nicht nur auf die aktuelle Zeile zu, sondern auf ein definiertes Fenster von Datensätzen. Im Gegensatz zu Aggregatfunktionen verdichten sie die Eingabezeilen allerdings nicht zu einer einzelnen Ausgabezeile. Es ist damit zum Beispiel möglich, auf die vorherige (LAG()) oder folgende Zeile (LEAD()) zuzugreifen oder Zeilen zu nummerieren (ROW_NUMBER()).

Das Fenster definiert der Entwickler hinter dem jeweiligen Funktionsaufruf mittels OVER(), wobei in Klammern ein PARTITION BY, ORDER BY und eine Frame-Spezifikation stehen können. Optional können Fenster auch am Ende der Query definiert und benannt werden, sodass mehrere Window-Funktionen anhand des Namens darauf zugreifen können. Die Partitionierung legt ähnlich wie GROUP BY fest, welche Spalten die Zugehörigkeit zu einem Fenster bestimmen. Die Sortierung spielt für die genannten Beispielfunktionen LEAD(), LAG() und ROW_NUMBER() natürlich ebenfalls eine Rolle. Mit der Angabe eines Frames lässt sich zudem festlegen, wie viele Zeilen vor und hinter der aktuellen Zeile für die Berechnung von Aggregaten verwendet werden sollen, um zum Beispiel moving und rolling averages zu berechnen.

WITH t1 AS
(SELECT created_at AS t1, LEAD(created_at) OVER w AS t2
FROM  measurements WINDOW w AS (ORDER BY created_at))
SELECT * FROM t1 WHERE t2-t1 > 1;

Ein Beispiel zeigt Listing 2. Es macht Unterbrechungen von mehr als einer Sekunde in den Messdaten einer fiktiven IoT-Anwendung ausfindig. Ohne Window Functions wäre eine solche Funktion nur über einen Self-Join der (vermutlich riesigen) Tabelle möglich. Allerdings gelingt dies nicht ohne Subquerys oder CTEs.

ACID: Nicht ganz sauer

SELECT MONTH(created_at), price/sum(price) OVER w FROM sales
WINDOW w AS (PARTITION by country,MONTH(created_at));

Auf Fenstern können auch Aggregatfunktionen ausgeführt werden, wodurch sich die Query aus Listing 1 sogar noch kürzer schreiben lässt (Listing 3).

In MariaDB finden sich Window Functions seit Version 10.2 in vergleichbarem Umfang, wobei laut Dokumentation nicht alle Aggregatfunktionen als Window Functions verwendbar sind. MySQL 8.0 ist hier weniger restriktiv.