2024-03-01

SQL-Gymnastik: SQL in flexible neue Formen biegen

Alex Monahan

DuckDBs besonders freundlicher SQL-Dialekt vereinfacht gängige Abfrageoperationen. Diese Features erschließen aber auch neue, flexible Wege, fortgeschrittenes SQL zu schreiben! In diesem Post kombinieren wir mehrere freundliche Features, um näher an reale Einsatzfälle zu kommen und Ihre Vorstellungskraft zu dehnen. Diese Abfragen sind für sich nützlich, ihre Bausteine sind in Ihrem Werkzeugkasten aber noch wertvoller.

Was ist das Verrückteste, das Sie mit SQL gebaut haben? Wir wollen davon hören! Markieren Sie DuckDB auf X (der Seite, die früher Twitter hieß) oder LinkedIn und treten Sie der DuckDB-Discord-Community bei.

Traditionelles SQL ist zu starr für Wiederverwendung

SQL-Abfragen werden typischerweise speziell für die einzigartigen Tabellen in einer Datenbank gebaut. Das begrenzt die Wiederverwendbarkeit. Haben Sie zum Beispiel je eine Bibliothek hochleveliger SQL-Hilfsfunktionen gesehen? SQL als Sprache ist typischerweise nicht flexibel genug, um wiederverwendbare Funktionen zu bauen. Heute fliegen wir einer flexibleren Zukunft entgegen!

Dynamisches Aggregations-Macro

In SQL müssen die Spalten für SELECT und GROUP BY typischerweise einzeln angegeben werden. In vielen Business-Intelligence-Lasten müssen Gruppierungen und Aggregatfunktionen aber leicht nutzerseitig anpassbar sein. Stellen Sie sich einen interaktiven Charting-Workflow vor – zuerst will ich den gesamten Unternehmensumsatz über die Zeit plotten. Sehe ich dann in diesem ersten Plot einen Umsatzrückgang, will ich den Plot anpassen und den Umsatz nach Geschäftseinheit gruppieren, um zu sehen, welcher Teil des Unternehmens das Problem verursacht hat. Das erfordert typischerweise templatisiertes SQL, eine Sprache, die zu SQL kompiliert (wie Malloy), oder den Bau eines SQL-Strings in einer anderen Programmiersprache. Wie viel können wir mit nur SQL tun?

Schauen wir uns einen flexiblen SQL-only-Ansatz an und zerlegen dann, wie er konstruiert ist.

CREATE OR REPLACE TABLE example AS
    SELECT x % 11 AS col1, x % 5 AS col2, x % 2 AS col3, 1 AS col4
    FROM range(1, 11) t(x);
FROM example;

Das Macro anlegen

Das Macro unten akzeptiert Listen von Spalten zum Ein- oder Ausschließen, eine Liste von Spalten zum Aggregieren und eine anzuwendende Aggregatfunktion. All das kann als Parameter aus der Host-Sprache übergeben werden, die die Datenbank abfragt.

-- We use a table macro (or function) for reusability
CREATE OR REPLACE MACRO dynamic_aggregates(
        included_columns,
        excluded_columns,
        aggregated_columns,
        aggregate_function
    ) AS TABLE (
    FROM example
    SELECT
        -- Use a COLUMNS expression to only select the columns
        -- we include or do not exclude
        COLUMNS(c -> (
            -- If we are not using an input parameter (list is empty),
            -- ignore it
            (list_contains(included_columns, c) OR
             len(included_columns) = 0)
            AND
            (NOT list_contains(excluded_columns, c) OR
             len(excluded_columns) = 0)
            )),
        -- Use the list_aggregate function to apply an aggregate
        -- function of our choice
        list_aggregate(
            -- Convert to a list (to enable the use of list_aggregate)
            list(
                -- Use a COLUMNS expression to choose which columns
                -- to aggregate
                COLUMNS(c -> list_contains(aggregated_columns, c))
            ), aggregate_function
        )
    GROUP BY ALL -- Group by all selected but non-aggregated columns
    ORDER BY ALL -- Order by each column from left to right
);

Das Macro ausführen

Jetzt können wir dieses Macro für viele verschiedene Aggregationsoperationen nutzen. Zur Veranschaulichung zeigen die 3 Abfragen unten verschiedene Wege zu identischen Ergebnissen.

col3 und col4 wählen und die Minimalwerte von col1 und col2 nehmen:

FROM dynamic_aggregates(
    ['col3', 'col4'], [], ['col1', 'col2'], 'min'
);

Alle Spalten außer col1 und col2 wählen und die Minimalwerte von col1 und col2 nehmen:

FROM dynamic_aggregates(
    [], ['col1', 'col2'], ['col1', 'col2'], 'min'
);

Steht dieselbe Spalte in der Include- und der Exclude-Liste, wird sie ausgeschlossen (Ausschlüsse gewinnen Unentschieden). Nehmen wir col2, col3 und col4 auf, schließen aber col2 aus, ist es, als hätten wir nur col3 und col4 aufgenommen:

FROM dynamic_aggregates(
    ['col2', 'col3', 'col4'], ['col2'], ['col1', 'col2'], 'min'
);

Jede dieser Abfragen liefert dieses Ergebnis:

Das Design verstehen

Der erste Schritt unseres flexiblen Tabellen-Macros ist, eine konkrete Tabelle mit DuckDBs FROM-first-Syntax zu wählen. Das ist nicht besonders dynamisch! Wenn wir wollten, könnten wir das umgehen, indem wir eine Kopie dieses Macros für jede Tabelle erzeugen, die wir unserer Anwendung exponieren wollen. Im nächsten Beispiel zeigen wir aber einen anderen Ansatz und lösen das Problem in einem Folge-Blogpost komplett mit einem DuckDB-Feature in Entwicklung. Bleiben Sie dran!

Dann SELECTen wir unsere Gruppierungsspalten anhand der übergebenen Listenparameter. Der COLUMNS-Ausdruck führt eine Lambda-Funktion aus, um zu entscheiden, welche Spalten die Kriterien für die Auswahl erfüllen.

Der erste Teil der Lambda-Funktion prüft, ob ein Spaltenname in der Liste included_columns übergeben wurde. Wenn wir uns aber entscheiden, keine Include-Regel zu nutzen (indem wir eine leere Liste included_columns übergeben), wollen wir diesen Parameter ignorieren. Ist die Liste leer, wertet len(included_columns) = 0 zu true aus und deaktiviert effektiv das Filtern nach included_columns. Das ist ein gängiges Muster für optionales Filtern, das über eine Vielzahl von SQL-Abfragen hinweg nützlich ist. (Dank an meinen Mentor und Freund Paul Bloomquist, der mir dieses Muster beigebracht hat!)

Wir wiederholen das Muster für excluded_columns, sodass sie genutzt wird, wenn sie gefüllt ist, aber ignoriert wird, wenn sie leer bleibt. Die Liste excluded_columns gewinnt auch Unentschieden, sodass eine Spalte in beiden Listen ausgeschlossen wird.

Als Nächstes wenden wir unsere Aggregatfunktion auf die Spalten an, die wir aggregieren wollen. Am leichtesten folgt man der Logik dieses Teils der Abfrage, indem man von innen nach außen arbeitet. Der COLUMNS-Ausdruck holt die Spalten, die in unserer Liste aggregated_columns stehen. Dann machen wir ein bisschen Gymnastik (es musste irgendwann passieren…).

Würden wir eine typische Aggregatfunktion (wie sum oder min) anwenden, müsste sie statisch in unserem Macro stehen. Um sie dynamisch als String zu übergeben (potenziell den ganzen Weg aus dem Anwendungscode, der diese SQL-Anweisung aufruft), nutzen wir eine einzigartige Eigenschaft der Funktion list_aggregate. Sie akzeptiert den Namen einer Funktion (als String) als zweites Parameter. Um diese einzigartige Eigenschaft zu nutzen, verwenden wir die Aggregatfunktion list, um alle Werte in jeder Gruppe in eine Liste zu verwandeln. Dann nutzen wir die Funktion list_aggregate, um die ins Macro übergebene aggregate_function auf jede Liste anzuwenden.

Fast fertig! Jetzt wählt GROUP BY ALL automatisch, nach den vom ersten COLUMNS-Ausdruck zurückgegebenen Spalten zu gruppieren. Der Ausdruck ORDER BY ALL sortiert jede Spalte aufsteigend, von links nach rechts.

Geschafft!

Extra Credit! Im nächsten DuckDB-Release, Version 0.10.1, können wir dem Ergebnis eines COLUMNS-Ausdrucks einen dynamischen Alias geben. Zum Beispiel könnte jede neue Aggregatspalte im Muster agg_[der ursprüngliche Spaltenname] umbenannt werden. Das erschließt die Möglichkeit, diese Art von Macros zu verketten, weil die Benennung vorhersagbar wird.

Takeaways

Mehrere der in diesem Macro genutzten Ansätze lassen sich auf vielfältige Weise in Ihren SQL-Workflows anwenden. Eine Lambda-Funktion in Kombination mit dem COLUMNS-Ausdruck erlaubt es, eine beliebige Liste von Spalten zu wählen. Der Trick OR len(my_list) = 0 erlaubt es, Listenparameter zu ignorieren, wenn sie leer sind. Haben Sie diese beliebige Menge von Spalten, können Sie sogar eine dynamisch gewählte Aggregatfunktion mit list und list_aggregate darauf anwenden.

Wir mussten aber am Anfang eine Tabelle angeben. Wir sind auch auf Aggregatfunktionen beschränkt, die mit list_aggregate genutzt werden können. Lockern wir diese beiden Einschränkungen!

Version 2 des Macros anlegen

Dieser Ansatz nutzt zwei Schlüsselkonzepte:

• Macros können temporäre Aggregatfunktionen erzeugen
• Ein Macro kann einen Common Table Expression (CTE) / eine WITH-Klausel abfragen, der zur Ausführungszeit im Scope ist

CREATE OR REPLACE MACRO dynamic_aggregates_any_cte_any_func(
    included_columns,
    excluded_columns,
    aggregated_columns
    /* No more aggregate_function */
) AS TABLE (
    FROM any_cte -- No longer a fixed table!
    SELECT
        COLUMNS(c -> (
            (list_contains(included_columns, c) OR
            len(included_columns) = 0)
            AND
            (NOT list_contains(excluded_columns, c) OR
            len(excluded_columns) = 0)
            )),
        -- We no longer convert to a list,
        -- and we refer to the latest definition of any_func
        any_func(COLUMNS(c -> list_contains(aggregated_columns, c)))
    GROUP BY ALL
    ORDER BY ALL
);

Version 2 ausführen

Wenn wir dieses Macro aufrufen, gibt es zusätzliche Komplexität. Wir führen keine einzelne Anweisung mehr aus, und unsere Logik ist nicht mehr vollständig parametrisierbar (etwas Templating oder SQL-Konstruktion wird nötig sein). Wir können dieses Macro aber gegen jeden beliebigen CTE mit jeder beliebigen Aggregatfunktion ausführen. Ziemlich mächtig und sehr wiederverwendbar!

-- We can define or redefine any_func right before calling the macro
CREATE OR REPLACE TEMP FUNCTION any_func(x)
    AS 100.0 * sum(x) / count(x);

-- Any table structure is valid for this CTE!
WITH any_cte AS (
    SELECT
        x % 11 AS id,
        x % 5 AS my_group,
        x % 2 AS another_group,
        1 AS one_big_group
    FROM range(1, 101) t(x)
)
FROM dynamic_aggregates_any_cte_any_func(
    ['another_group', 'one_big_group'], [], ['id', 'my_group']
);

Version 2 verstehen

Statt der sehr kühn benannten Tabelle example fragen wir die möglicherweise generischer benannte any_cte ab. Beachten Sie, dass any_cte ein anderes Schema hat als unser vorheriges Beispiel – die Spalten in any_cte können alles sein! Wenn das Macro erzeugt wird, existiert any_cte noch nicht einmal. Wenn das Macro ausgeführt wird, sucht es nach einem tabellenartigen Objekt namens any_cte, und das wurde im CTE definiert, als das Macro aufgerufen wurde.

Ähnlich existiert any_func anfangs nicht. Es muss nur irgendwann vor der Ausführung des Macros erzeugt (oder neu erzeugt) werden. Seine einzigen Anforderungen: eine Aggregatfunktion zu sein, die auf einer einzelnen Spalte arbeitet.

FUNCTION und MACRO sind in DuckDB Synonyme und können austauschbar genutzt werden!

Takeaways aus Version 2

Ein Macro kann auf jede beliebige Tabelle wirken, indem zur Aufrufzeit ein CTE genutzt wird. Das macht unser Macro weit wiederverwendbarer – es kann auf jeder Tabelle arbeiten! Nicht nur das: Jede eigene Aggregatfunktion kann genutzt werden.

Schauen Sie, wie weit wir SQL gedehnt haben – wir haben eine wirklich wiederverwendbare SQL-Funktion gebaut! Die Tabelle ist dynamisch, die Gruppierungsspalten sind dynamisch, die aggregierten Spalten sind dynamisch, und die Aggregatfunktion auch. Unsere täglichen Gymnastikdehnungen haben sich ausgezahlt. Bleiben Sie aber dran für einen Weg, ähnliche Ergebnisse mit einem einfacheren Ansatz in einem zukünftigen Post zu erreichen.

Eigene Zusammenfassungen für jeden Datensatz

Als Nächstes ein wirklich produktionsreifes Beispiel! Diese Abfrage treibt einen Teil der Column-Explorer-Komponente der MotherDuck-Web-UI. Hamilton Ulmer hat die Erstellung dieser Komponente geleitet und ist auch Autor dieser Abfrage! Zweck des Column Explorers und dieser Abfrage ist, so schnell und einfach wie möglich einen Überblick über die Daten in allen Spalten eines Datensatzes zu bekommen.

DuckDB hat ein eingebautes Schlüsselwort SUMMARIZE, das ähnliche Metriken über eine ganze Tabelle berechnen kann. Bei größeren Datensätzen kann SUMMARIZE aber ein paar Sekunden zum Laden brauchen. Diese Abfrage bietet eine eigene Zusammenfassungsfähigkeit, die auf die Eigenschaften Ihrer Daten zugeschnitten werden kann, die Sie am meisten interessieren.

Traditionell verlangten Datenbanken, dass jede Spalte explizit genannt wird, und funktionieren am besten, wenn Daten in getrennten Spalten angeordnet sind. Diese Abfrage nutzt DuckDBs Fähigkeit, Funktionen auf alle Spalten auf einmal anzuwenden, die Fähigkeit, Spalten zu UNPIVOTen (oder zu stapeln), und den Datentyp STRUCT, um Schlüssel/Wert-Paare zu speichern. Das Ergebnis ist eine saubere, pivotierte Zusammenfassung aller Zeilen und Spalten einer Tabelle.

Schauen wir uns die gesamte Funktion an und zerlegen sie dann Stück für Stück.

Dieser Beispieldatensatz kommt von Hugging Face, das DuckDB-zugängliche Parquet-Dateien für viele seiner Datensätze hostet. Zuerst erzeugen wir eine lokale Tabelle, gefüllt aus dieser entfernten Parquet-Datei.

Erzeugung

CREATE OR REPLACE TABLE spotify_tracks AS
    FROM 'https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset/resolve/refs%2Fconvert%2Fparquet/default/train/0000.parquet?download=true';

Dann erzeugen und führen wir unser Macro custom_summarize aus. Wir nutzen denselben any_cte-Trick von oben, damit das auf jedem Abfrageergebnis oder jeder Tabelle wiederverwendet werden kann.

CREATE OR REPLACE MACRO custom_summarize() AS TABLE (
    WITH metrics AS (
        FROM any_cte
        SELECT
            {
                name: first(alias(COLUMNS(*))),
                type: first(typeof(COLUMNS(*))),
                max: max(COLUMNS(*))::VARCHAR,
                min: min(COLUMNS(*))::VARCHAR,
                approx_unique: approx_count_distinct(COLUMNS(*)),
                nulls: count(*) - count(COLUMNS(*)),
            }
    ), stacked_metrics AS (
        UNPIVOT metrics
        ON COLUMNS(*)
    )
    SELECT value.* FROM stacked_metrics
);

Ausführung

Der Datensatz spotify_tracks wird effektiv in any_cte umbenannt und dann zusammengefasst.

WITH any_cte AS (FROM spotify_tracks)
FROM custom_summarize();

Das Ergebnis enthält eine Zeile für jede Spalte im Rohdatensatz und mehrere Spalten mit Zusammenfassungsstatistiken.

Wie wurde diese Abfrage konstruiert? Zerlegen wir jeden CTE Schritt für Schritt.

Schritt-für-Schritt-Zerlegung

Metrics-CTE

Zuerst schauen wir uns den CTE metrics und die Form der zurückgegebenen Daten an:

FROM any_cte
SELECT
    {
        name: first(alias(COLUMNS(*))),
        type: first(typeof(COLUMNS(*))),
        max: max(COLUMNS(*))::VARCHAR,
        min: min(COLUMNS(*))::VARCHAR,
        approx_unique: approx_count_distinct(COLUMNS(*)),
        nulls: count(*) - count(COLUMNS(*)),
    };

Dieses Zwischenergebnis behält dieselbe Spaltenzahl wie der ursprüngliche Datensatz, gibt aber nur eine einzelne Zeile Zusammenfassungsstatistiken zurück. Die Namen der Spalten sind wegen ihrer Länge gekürzt. Die Standardbenennung von COLUMNS-Ausdrücken wird in DuckDB 0.10.1 verbessert, sodass Namen viel sauberer sein werden!

Die Daten in jeder Spalte sind in einem STRUCT aus Schlüssel-Wert-Paaren organisiert. Sie sehen auch, dass ein sauberer Name der ursprünglichen Spalte dank der Funktion alias im STRUCT gespeichert ist. Die Zusammenfassungsstatistiken sind berechnet, das Format dieser Statistiken ist aber schwer visuell zu interpretieren.

Die Abfrage erreicht diese Struktur mit dem Ausdruck COLUMNS(*), um mehrere Zusammenfassungsmetriken auf alle Spalten anzuwenden, und der Syntax {...}, um einen STRUCT zu erzeugen. Die Schlüssel des Structs stehen für die Namen der Metriken (und das, was wir als Spaltennamen im Endergebnis nutzen wollen). Wir nutzen diesen Ansatz, weil wir die Spalten zu Zeilen transponieren und dann die Zusammenfassungsmetriken in eigene Spalten aufteilen wollen.

CTE stacked_metrics

Als Nächstes werden die Daten unpivotiert, um die Tabelle von einer Zeile und mehreren Spalten in zwei Spalten und mehrere Zeilen umzuformen.

UNPIVOT metrics
ON COLUMNS(*);

Durch Unpivotieren auf COLUMNS(*) nehmen wir alle Spalten und pivotieren sie nach unten in zwei Spalten: eine für den automatisch erzeugten name der Spalte und eine für den value, der in dieser Spalte stand.

Die Ergebnisse zurückgeben

Der letzte Schritt ist der gymnastikähnlichste Teil dieser Abfrage. Wir explodieren das Struct-Format der Spalte value, sodass jeder Schlüssel mit der Syntax STRUCT.* zur eigenen Spalte wird. Das ist ein weiterer Weg, eine Abfrage weniger von Spaltennamen abhängig zu machen – die Aufteilung geschieht automatisch anhand der Schlüssel im Struct.

SELECT value.*
FROM stacked_metrics;

Wir haben die Daten jetzt in mehrere Spalten aufgeteilt, sodass die Zusammenfassungsmetriken schön interpretierbar sind.

Fazit

Wir haben gezeigt, dass es jetzt möglich ist, wiederverwendbare SQL-Macros auf hochflexible Weise zu bauen. Sie können jetzt ein Macro bauen, das:

• auf jedem Datensatz arbeitet
• beliebige Spalten wählt
• nach beliebigen Spalten gruppiert
• eine beliebige Zahl von Spalten mit einer beliebigen Funktion aggregiert.

Puh!

Unterwegs haben wir einige nützliche Tricks für Ihren Werkzeugkasten behandelt:

• Ein Macro mit einem CTE auf jeden Datensatz anzuwenden
• Eine dynamische Spaltenliste zu wählen, indem man den COLUMNS-Ausdruck mit einem Lambda und der Funktion list_contains kombiniert
• Eine Aggregatfunktion als String mit list_aggregate zu übergeben
• Jede eigene Aggregatfunktion in einem Macro anzuwenden
• Listenparameter mit OR len(list_parameter) = 0 optional zu machen
• Die Funktion alias mit einem COLUMNS-Ausdruck zu nutzen, um den ursprünglichen Namen aller Spalten zu speichern
• Alle Spalten zusammenzufassen und diese Zusammenfassung mit UNPIVOT und STRUCT.* zu transponieren

Die Kombination dieser freundlichen SQL-Features ist mächtiger als jedes einzelne für sich. Wir hoffen, Sie dazu inspiriert zu haben, Ihr SQL an neue Grenzen zu bringen!

Wie immer begrüßen wir Ihr Feedback und Ihre Vorschläge. Wir haben auch mehr Flexibilität im Sinn, die in zukünftigen Posts gezeigt wird. Bitte teilen Sie die Male, in denen Sie SQL auf einfallsreiche Weise gedehnt haben!

Viel Spaß beim Analysieren!