2024-09-27

Eine SQL-only-Extension für Excel-artiges Pivoting in DuckDB anlegen

Alex Monahan

Die Macht von SQL-only-Extensions

SQL ist keine neue Sprache. Deshalb haben ihr historisch einige der modernen Annehmlichkeiten gefehlt, die wir für selbstverständlich halten. Mit Version 1.1 hat DuckDB Community Extensions gestartet und die unglaubliche Macht eines Package Managers in die SQL-Sprache gebracht. Ein kühnes Ziel von uns ist, dass DuckDB ein bequemer Weg wird, jede C++-Bibliothek zu wrappen, so wie Python das heute tut, aber über jede Sprache mit einem DuckDB-Client hinweg.

Für Extension-Builder sind Kompilierung und Verteilung deutlich einfacher. Für die Nutzer-Community ist die Installation so einfach wie zwei Befehle:

INSTALL pivot_table FROM community;
LOAD pivot_table;

Die Extension kann dann in jeder Abfrage über SQL-Funktionen genutzt werden.

Nicht alle von uns sind aber C++-Entwickler! Können wir als SQL-Community eine Menge von SQL-Hilfsfunktionen aufbauen? Was würde es brauchen, diese Extensions nur mit SQL zu bauen?

Wiederverwendbarkeit

Traditionell ist SQL stark auf das Schema der Datenbank zugeschnitten, auf der es geschrieben wurde. Können wir es wiederverwendbar machen? Einige Techniken für Wiederverwendbarkeit wurden im SQL-Gymnastics-Beitrag diskutiert, jetzt können wir aber noch weiter gehen. Mit Version 1.1 macht DuckDBs erstklassiger Friendly-SQL-Dialekt es möglich, Makros anzulegen, die angewendet werden können:

• Auf beliebige Tabellen
• Auf beliebige Spalten
• Mit beliebigen Funktionen

Die neue Fähigkeit, auf beliebigen Tabellen zu arbeiten, verdankt sich den Funktionen query und query_table! Die Funktion query ist ein sicherer Weg, SELECT-Anweisungen auszuführen, die durch SQL-Strings definiert sind, während query_table ein Weg ist, eine FROM-Klausel aus mehreren Tabellen auf einmal ziehen zu lassen. Sie sind sehr mächtig in Kombination mit anderen Friendly-SQL-Features wie dem COLUMNS-Ausdruck und LIST-Lambda-Funktionen.

Community Extensions als zentrales Repository

Traditionell gab es kein zentrales Repository für SQL-Funktionen über Datenbanken hinweg, geschweige denn über Unternehmen hinweg! DuckDBs Community Extensions können diese Wissensbasis sein. DuckDB-Extensions können über alle Sprachen mit einem DuckDB-Client genutzt werden, einschließlich Python, NodeJS, Java, Rust, Go und sogar WebAssembly (Wasm)!

Wenn Sie ein DuckDB-Fan und SQL-Nutzer sind, können Sie Ihre Expertise mit einer Extension an die Community zurückgeben. Dieser Beitrag zeigt Ihnen wie! Kein C++-Wissen nötig – nur ein bisschen Copy/Paste, und GitHub Actions übernimmt die ganze Kompilierung. Wenn ich das kann, können Sie das auch!

Mächtiges SQL

Bei alledem: Wie wertvoll kann ein SQL-MACRO sein? Können wir mehr tun, als kleine Snippets zu machen? Ich argumentiere, dass Sie in DuckDB-SQL ziemlich komplexe und mächtige Operationen machen können, und nutze die Extension pivot_table als Beispiel. Die Funktion pivot_table erlaubt Excel-artige Pivots, einschließlich subtotals, grand_totals und mehr. Sie ist auch der Pandas-Funktion pivot_table sehr ähnlich, aber mit all den Skalierbarkeits- und Geschwindigkeitsvorteilen von DuckDB. Sie enthält über 250 Tests, ist also dafür gedacht, über ein bloßes Beispiel hinaus nützlich zu sein!

Um dieses Maß an Flexibilität zu erreichen, nutzt die Extension pivot_table viele freundliche und fortgeschrittene SQL-Features:

• Die Funktion query, um einen SQL-String auszuführen
• Die Funktion query_table, um eine Liste von Tabellen abzufragen
• Den COLUMNS-Ausdruck, um eine dynamische Liste von Spalten zu selektieren
• Listen-Lambda-Funktionen, um die an query übergebene SQL-Anweisung aufzubauen
  • list_transform für String-Manipulation wie Quoting
  • list_reduce, um Strings zusammenzuketten
  • list_aggregate, um mehrere Spalten zu summieren und Subtotal- und Grand-Total-Zeilen zu identifizieren
• Klammer-Notation für String-Slicing
• UNION ALL BY NAME, um Daten nach Spaltenname für Subtotals und Grand Totals zu stapeln
• SELECT * REPLACE, um Subtotal-Spalten dynamisch zu bereinigen
• SELECT * EXCLUDE, um intern erzeugte Spalten aus dem Endergebnis zu entfernen
• GROUPING SETS und ROLLUP, um Subtotals und Grand Totals zu erzeugen
• UNNEST, um Listen in getrennte Zeilen für values_axis := 'rows' umzuwandeln
• MACROs, um den Code zu modularisieren
• ORDER BY ALL, um das Ergebnis dynamisch zu ordnen
• ENUMs, um festzulegen, welche Spalten horizontal gepivoted werden
• Und natürlich die Funktion PIVOT für horizontales Pivoting!

DuckDBs innovative Syntax macht diese Extension möglich!

Wir haben jetzt also alle 3 Zutaten, die wir brauchen: einen zentralen Package Manager, wiederverwendbare Makros und genug syntaktische Flexibilität, um wertvolle Arbeit zu tun.

Legen Sie Ihre eigene SQL-Extension an

Gehen wir die Schritte durch, um Ihre eigene SQL-only-Extension anzulegen.

Die Extension schreiben

Extension-Setup

Der erste Schritt ist, Ihr eigenes GitHub-Repo aus dem DuckDB Extension Template for SQL anzulegen, indem Sie Use this template klicken.

Dann clonen Sie Ihr neues Repository auf Ihre lokale Maschine im Terminal:

git clone --recurse-submodules \
    https://github.com/⟨your_github_username⟩/⟨your_extension_repo⟩.git

Beachten Sie, dass --recurse-submodules sicherstellt, dass DuckDB geholt wird, was nötig ist, um die Extension zu bauen.

Als Nächstes ersetzen Sie den Namen der Beispiel-Extension durch den Namen Ihrer Extension an allen richtigen Stellen, indem Sie das Python-Skript unten ausführen.

Note Wenn Sie Python nicht installiert haben, gehen Sie zu python.org und folgen Sie den Anweisungen. Dieses Skript braucht keine Bibliotheken, Python reicht! (Keine Umgebungen nötig.)

python3 ./scripts/bootstrap-template.py ⟨extension_name_you_want⟩

Erster Extension-Test

An diesem Punkt können Sie den Anweisungen im README folgen, um lokal zu bauen und zu testen, wenn Sie möchten. Noch einfacher: Sie können Ihre Änderungen einfach nach git committen und nach GitHub pushen, und GitHub Actions kann die Kompilierung für Sie erledigen! GitHub Actions führt auch Tests auf Ihrer Extension aus, um zu prüfen, dass sie richtig funktioniert.

Note Die Anweisungen sind nicht für ein Windows-Publikum geschrieben, deshalb empfehlen wir in dem Fall GitHub Actions!

git add -A
git commit -m "Initial commit of my SQL extension!"
git push

Schreiben Sie Ihre SQL-Makros

Es ist wahrscheinlich etwas schneller zu iterieren, wenn Sie Ihre Makros direkt in DuckDB testen. Nachdem Sie Ihr SQL geschrieben haben, verschieben wir es in die Extension. Das Beispiel, das wir nutzen, zeigt, wie man eine dynamische Menge von Spalten aus einem dynamischen Tabellennamen (oder View-Namen!) zieht.

CREATE OR REPLACE MACRO select_distinct_columns_from_table(table_name, columns_list) AS TABLE (
    SELECT DISTINCT
        COLUMNS(lambda column_name: list_contains(columns_list, column_name))
    FROM query_table(table_name)
    ORDER BY ALL
);

FROM select_distinct_columns_from_table('duckdb_types', ['type_category']);

SQL-Makros hinzufügen

Technisch ist das der C++-Teil, aber wir machen etwas Copy/Paste und nutzen GitHub Actions zum Kompilieren, sodass es sich nicht so anfühlt!

DuckDB unterstützt sowohl skalare als auch Tabellenmakros, und sie haben etwas unterschiedliche Syntax. Das Extension-Template hat ein Beispiel für jedes (und auch Code-Kommentare!) in der Datei namens ⟨your_extension_name⟩.cpp. Fügen wir hier ein Tabellenmakro hinzu, weil es das komplexere ist. Wir kopieren das Beispiel und passen es an!

static const DefaultTableMacro ⟨your_extension_name⟩_table_macros[] = {
    {DEFAULT_SCHEMA, "times_two_table", {"x", nullptr}, {{"two", "2"}, {nullptr, nullptr}}, R"(SELECT x * two AS output_column;)"},
    {
        DEFAULT_SCHEMA, // Leave the schema as the default
        "select_distinct_columns_from_table", // Function name
        {"table_name", "columns_list", nullptr}, // Parameters
        {{nullptr, nullptr}}, // Optional parameter names and values (we choose not to have any here)
        // The SQL text inside of your SQL Macro, wrapped in R"( )", which is a raw string in C++
        R"(
            SELECT DISTINCT
                COLUMNS(lambda column_name: list_contains(columns_list, column_name))
            FROM query_table(table_name)
            ORDER BY ALL
        )"
    },
    {nullptr, nullptr, {nullptr}, {{nullptr, nullptr}}, nullptr}
    };

Das war’s! Alles, was wir angeben mussten, waren der Name der Funktion, die Namen der Parameter und der Text unseres SQL-Makros.

Die Extension testen

Wir empfehlen auch, ein paar Tests für Ihre Extension in die Datei ⟨your_extension_name⟩.test einzufügen. Das nutzt sqllogictest, um nur mit SQL zu testen! Fügen wir das Beispiel von oben hinzu.

Note In sqllogictest zeigt query I an, dass es 1 Spalte im Ergebnis geben wird. Dann fügen wir ---- und das Resultset im Tab-separierten Format ohne Spaltennamen hinzu.

query I
FROM select_distinct_columns_from_table('duckdb_types', ['type_category']);
----
BOOLEAN
COMPOSITE
DATETIME
NUMERIC
STRING
NULL

Jetzt einfach adden, committen und Ihre Änderungen wie zuvor nach GitHub pushen, und GitHub Actions kompiliert Ihre Extension und testet sie!

Wenn Sie Ihre Extension weiter ad-hoc testen möchten, können Sie die Extension aus den Artifacts Ihres GitHub-Actions-Laufs herunterladen und dann lokal mit diesen Schritten installieren.

Ins Community-Extensions-Repository hochladen

Wenn Sie mit Ihrer Extension zufrieden sind, ist es Zeit, sie mit der DuckDB-Community zu teilen! Folgen Sie den Schritten im Community-Extensions-Beitrag. Eine Zusammenfassung dieser Schritte:

1. Senden Sie einen PR mit einer Metadaten-Datei description.yml, die die Beschreibung der Extension enthält. Zum Beispiel nutzt die Community Extension h3 die folgende YAML-Konfiguration:

   extension:
     name: h3
     description: Hierarchical hexagonal indexing for geospatial data
     version: 1.0.0
     language: C++
     build: cmake
     license: Apache-2.0
     maintainers:
       - isaacbrodsky
   
   repo:
     github: isaacbrodsky/h3-duckdb
     ref: 3c8a5358e42ab8d11e0253c70f7cc7d37781b2ef

2. Warten Sie auf die Freigabe durch die Maintainer.

Und da haben Sie es! Sie haben eine teilbare DuckDB-Community-Extension angelegt. Schauen wir uns jetzt die Extension pivot_table als Beispiel an, wie mächtig eine SQL-only-Extension sein kann.

Fähigkeiten der Extension pivot_table

Die Extension pivot_table unterstützt fortgeschrittene Pivoting-Funktionalität, die zuvor nur in Spreadsheets, Dataframe-Bibliotheken oder eigenen Host-Sprachfunktionen verfügbar war. Sie nutzt die Excel-Pivoting-API: values, rows, columns und filters – und behandelt 0 oder mehr von jedem dieser Parameter. Aber nicht nur das: Sie unterstützt subtotals und grand_totals. Werden mehrere values übergeben, erlaubt der Parameter values_axis dem Nutzer zu wählen, ob jeder Wert seine eigene Spalte oder seine eigene Zeile bekommen soll.

Warum ist das ein gutes Beispiel dafür, wie DuckDB über traditionelles SQL hinausgeht? Die Excel-Pivoting-API verlangt dramatisch unterschiedliche SQL-Syntax, je nachdem, welche Parameter genutzt werden. Werden keine columns nach außen gepivoted, reicht ein GROUP BY. Sobald aber columns im Spiel sind, ist ein PIVOT nötig.

Diese Funktion kann auf einem oder mehreren table_names operieren, die als Parameter übergeben werden. Jede Menge von Tabellen (oder Views!) wird zuerst vertikal gestapelt und dann gepivoted.

Beispiel mit pivot_table

Schauen Sie sich ein Live-Beispiel mit der Extension in der DuckDB-Wasm-Shell hier an!

CREATE OR REPLACE TABLE business_metrics (
    product_line VARCHAR,
    product VARCHAR,
    year INTEGER,
    quarter VARCHAR,
    revenue INTEGER,
    cost INTEGER
);

INSERT INTO business_metrics VALUES
    ('Waterfowl watercraft', 'Duck boats', 2022, 'Q1', 100, 100),
    ('Waterfowl watercraft', 'Duck boats', 2022, 'Q2', 200, 100),
    ('Waterfowl watercraft', 'Duck boats', 2022, 'Q3', 300, 100),
    ('Waterfowl watercraft', 'Duck boats', 2022, 'Q4', 400, 100),
    ('Waterfowl watercraft', 'Duck boats', 2023, 'Q1', 500, 100),
    ('Waterfowl watercraft', 'Duck boats', 2023, 'Q2', 600, 100),
    ('Waterfowl watercraft', 'Duck boats', 2023, 'Q3', 700, 100),
    ('Waterfowl watercraft', 'Duck boats', 2023, 'Q4', 800, 100),

    ('Duck Duds', 'Duck suits', 2022, 'Q1', 10, 10),
    ('Duck Duds', 'Duck suits', 2022, 'Q2', 20, 10),
    ('Duck Duds', 'Duck suits', 2022, 'Q3', 30, 10),
    ('Duck Duds', 'Duck suits', 2022, 'Q4', 40, 10),
    ('Duck Duds', 'Duck suits', 2023, 'Q1', 50, 10),
    ('Duck Duds', 'Duck suits', 2023, 'Q2', 60, 10),
    ('Duck Duds', 'Duck suits', 2023, 'Q3', 70, 10),
    ('Duck Duds', 'Duck suits', 2023, 'Q4', 80, 10),

    ('Duck Duds', 'Duck neckties', 2022, 'Q1', 1, 1),
    ('Duck Duds', 'Duck neckties', 2022, 'Q2', 2, 1),
    ('Duck Duds', 'Duck neckties', 2022, 'Q3', 3, 1),
    ('Duck Duds', 'Duck neckties', 2022, 'Q4', 4, 1),
    ('Duck Duds', 'Duck neckties', 2023, 'Q1', 5, 1),
    ('Duck Duds', 'Duck neckties', 2023, 'Q2', 6, 1),
    ('Duck Duds', 'Duck neckties', 2023, 'Q3', 7, 1),
    ('Duck Duds', 'Duck neckties', 2023, 'Q4', 8, 1),
;

FROM business_metrics;

Als Nächstes installieren wir die Extension aus dem Community-Repository:

INSTALL pivot_table FROM community;
LOAD pivot_table;

Jetzt können wir Pivot-Tabellen wie die unten bauen. Es ist ein bisschen Boilerplate nötig, und die Details, wie das funktioniert, werden gleich erklärt.

DROP TYPE IF EXISTS columns_parameter_enum;

CREATE TYPE columns_parameter_enum AS ENUM (
    FROM build_my_enum(['business_metrics'],    -- table_names
                       ['year', 'quarter'],     -- columns
                       [])                      -- filters
);

FROM pivot_table(['business_metrics'],          -- table_names
                 ['sum(revenue)', 'sum(cost)'], -- values
                 ['product_line', 'product'],   -- rows
                 ['year', 'quarter'],           -- columns
                 [],                            -- filters
                 subtotals := 1,
                 grand_totals := 1,
                 values_axis := 'rows'
                 );

Wie die Extension pivot_table funktioniert

Die Extension pivot_table ist eine Sammlung mehrerer skalarer und Tabellen-SQL-Makros. Das erlaubt es, die Logik zu modularisieren. Unten sehen Sie, dass die Funktionen als Bausteine genutzt werden, um komplexere Funktionen anzulegen. Das ist in SQL typischerweise schwierig, in DuckDB aber einfach!

Die Funktionen und eine kurze Beschreibung jeder folgt.

Baustein-skalare Funktionen

• nq: „No quotes“ – Semikolons in einem String escapen, um SQL-Injection zu verhindern
• sq: „Single quotes“ – Einen String in einfache Anführungszeichen packen und eingebettete einfache Anführungszeichen escapen
• dq: „Double quotes“ – In doppelte Anführungszeichen packen und eingebettete doppelte Anführungszeichen escapen
• nq_list: Semikolons für jeden String in einer Liste escapen. Nutzt nq.
• sq_list: Jeden String in einer Liste in einfache Anführungszeichen packen. Nutzt sq.
• dq_list: Jeden String in einer Liste in doppelte Anführungszeichen packen. Nutzt dq.
• nq_concat: Eine Liste von Strings mit Semikolon-Escaping zusammenketten. Nutzt nq_list.
• sq_concat: Eine Liste von Strings zusammenketten und jeden in einfache Anführungszeichen packen. Nutzt sq_list.
• dq_concat: Eine Liste von Strings zusammenketten und jeden in doppelte Anführungszeichen packen. Nutzt dq_list.

Funktionen, die beim Refactoring für Modularität entstanden

• totals_list: Eine Liste aufbauen als Teil der Aktivierung von subtotals und grand_totals.
• replace_zzz: subtotal- und grand_total-Indikatoren nach dem Sortieren umbenennen, damit sie freundlicher sind.

Kern-Pivoting-Logikfunktionen

• build_my_enum: Festlegen, welche neuen Spalten beim horizontalen Pivoting angelegt werden. Gibt eine Tabelle zurück. Details siehe unten.
• pivot_table: Anhand der Eingaben entscheiden, ob no_columns, columns_values_axis_columns oder columns_values_axis_rows aufgerufen wird. Führt query auf dem erzeugten SQL-String aus. Gibt eine Tabelle zurück. Details siehe unten.
  • no_columns: Den SQL-String für query aufbauen, wenn keine columns herausgepivoted werden.
  • columns_values_axis_columns: Den SQL-String für query aufbauen, wenn horizontal gepivoted wird und jeder Eintrag in values eine eigene Spalte bekommt.
  • columns_values_axis_rows: Den SQL-String für query aufbauen, wenn horizontal gepivoted wird und jeder Eintrag in values eine eigene Zeile bekommt.
• pivot_table_show_sql: Den SQL-String zurückgeben, der von query ausgeführt worden wäre, zum Debuggen.

Die Funktion build_my_enum

Der erste Schritt bei der Nutzung der Fähigkeiten der Extension pivot_table ist, ein ENUM (einen nutzerdefinierten Typ) namens columns_parameter_enum zu definieren, das alle neuen Spaltennamen enthält, die beim horizontalen Pivoting angelegt werden. DuckDBs automatische PIVOT-Syntax kann das automatisch definieren, in unserem Fall brauchen wir aber 2 explizite Schritte. Der Grund ist, dass automatisches Pivoting hinter den Kulissen 2 Anweisungen ausführt, ein MACRO aber nur eine einzelne Anweisung sein darf. Wird der Parameter columns nicht genutzt, ist dieser Schritt im Wesentlichen ein No-op, er kann also weggelassen oder der Konsistenz halber mitgenommen werden (empfohlen).

Die Funktionen query und query_table unterstützen nur SELECT-Anweisungen (aus Sicherheitsgründen), der dynamische Teil der ENUM-Erzeugung passiert also in der Funktion build_my_enum. Wird diese Art der Nutzung häufig, könnten Features zu DuckDB hinzugefügt werden, um eine CREATE OR REPLACE-Syntax für ENUM-Typen zu ermöglichen, oder möglicherweise sogar temporäre Enums. Das würde dieses Muster von 3 Anweisungen auf 2 reduzieren. Lassen Sie es uns wissen!

Die Funktion build_my_enum nutzt eine Kombination aus query_table, um aus mehreren Eingabetabellen zu ziehen, und der Funktion query, damit doppelte Anführungszeichen (und korrektes Zeichen-Escaping) abgeschlossen werden können, bevor die Liste der Tabellennamen übergeben wird. Sie nutzt ein ähnliches Muster wie die Kernfunktion pivot_table: eine SQL-Abfrage als String aufbauen, dann mit query aufrufen. Der SQL-String wird mit Listen-Lambda-Funktionen und den Baustein-Funktionen für Quoting konstruiert.

Die Funktion pivot_table

Im Kern bestimmt die Funktion pivot_table das SQL, das nötig ist, um den gewünschten Pivot anhand der genutzten Parameter zu erzeugen.

Da diese SQL-Anweisung am Ende des Tages ein String ist, können wir eine Hierarchie skalarer SQL-Makros statt eines einzelnen großen Makros nutzen. Das ist ein übliches traditionelles Problem mit SQL – es neigt dazu, nicht sehr modular oder wiederverwendbar zu sein, mit DuckDBs Syntax können wir unsere Logik aber abschotten.

Note Wird ein nicht-optionaler Parameter nicht genutzt, sollte eine leere Liste ([]) übergeben werden.

• table_names: Eine Liste von Tabellen- oder View-Namen zum Aggregieren oder Pivoting. Mehrere Tabellen werden mit UNION ALL BY NAME kombiniert, bevor irgendetwas anderes verarbeitet wird.
• values: Eine Liste von Aggregationsmetriken im Format ['agg_fn_1(col_1)', 'agg_fn_2(col_2)', ...].
• rows: Eine Liste von Spaltennamen für SELECT und GROUP BY.
• columns: Eine Liste von Spaltennamen, die mit PIVOT horizontal in eine eigene Spalte pro Wert in der ursprünglichen Spalte gepivoted werden. Werden mehrere Spaltennamen übergeben, werden nur eindeutige Kombinationen von Daten, die im Datensatz vorkommen, gepivoted.
  • Bsp.: Wird ein Parameter columns wie ['continent', 'country'] übergeben, werden nur gültige continent/country-Paare aufgenommen.
  • (Es würde keine Spalte Europe_Canada erzeugt).
• filters: Eine Liste von WHERE-Klausel-Ausdrücken, die auf den Rohdatensatz vor dem Aggregieren angewendet werden, im Format ['col_1 = 123', 'col_2 LIKE ''woot%''', ...].
  • Die filters werden mit AND kombiniert.
• values_axis (optional): Werden mehrere values übergeben, festlegen, ob eine eigene Zeile oder Spalte für jeden Wert angelegt werden soll. Entweder rows oder columns, Standard columns.
• subtotals (optional): Wenn aktiviert, die Aggregatmetrik auf mehreren Detailebenen anhand des Parameters rows berechnen. Entweder 0 oder 1, Standard 0.
• grand_totals (optional): Wenn aktiviert, die Aggregatmetrik über alle Zeilen in den Rohdaten zusätzlich zur durch rows definierten Granularität berechnen. Entweder 0 oder 1, Standard 0.

Kein horizontales Pivoting (keine columns in Nutzung)

Wird der Parameter columns nicht genutzt, müssen keine Spalten horizontal gepivoted werden. Deshalb wird eine GROUP BY-Anweisung genutzt. Sind subtotals in Nutzung, wird der Ausdruck ROLLUP genutzt, um die values auf den verschiedenen Granularitätsebenen zu berechnen. Sind grand_totals in Nutzung, aber nicht subtotals, wird statt ROLLUP der Ausdruck GROUPING SETS genutzt, um über alle Zeilen auszuwerten.

In diesem Beispiel bauen wir eine Zusammenfassung von revenue und cost jeder product_line und jedes product.

FROM pivot_table(['business_metrics'],
                 ['sum(revenue)', 'sum(cost)'],
                 ['product_line', 'product'],
                 [],
                 [],
                 subtotals := 1,
                 grand_totals := 1,
                 values_axis := 'columns'
                 );

Horizontal pivoten, eine Spalte pro Metrik in values

Eine PIVOT-Anweisung aufbauen, die alle gültigen Kombinationen von Rohdatenwerten innerhalb des Parameters columns herauspivoted. Sind subtotals oder grand_totals in Nutzung, mehrere Kopien der Eingabedaten machen, aber passende Spaltennamen im Parameter rows durch eine String-Konstante ersetzen. Alle Ausdrücke in values an die USING-Klausel der PIVOT-Anweisung übergeben, sodass jeder seine eigene Spalte bekommt.

Wir erweitern unser vorheriges Beispiel, um eine eigene Spalte für jede Kombination year/value herauszupivoten:

DROP TYPE IF EXISTS columns_parameter_enum;

CREATE TYPE columns_parameter_enum AS ENUM (
    FROM build_my_enum(['business_metrics'],
                       ['year'],
                       [])
);

FROM pivot_table(['business_metrics'],
                 ['sum(revenue)', 'sum(cost)'],
                 ['product_line', 'product'],
                 ['year'],
                 [],
                 subtotals := 1,
                 grand_totals := 1,
                 values_axis := 'columns'
                 );

Horizontal pivoten, eine Zeile pro Metrik in values

Eine eigene PIVOT-Anweisung für jede Metrik in values aufbauen und sie mit UNION ALL BY NAME kombinieren. Sind subtotals oder grand_totals in Nutzung, mehrere Kopien der Eingabedaten machen, aber passende Spaltennamen im Parameter rows durch eine String-Konstante ersetzen.

Um das Erscheinungsbild etwas zu vereinfachen, passen wir einen Parameter in unserer vorherigen Abfrage an und setzen values_axis := 'rows':

DROP TYPE IF EXISTS columns_parameter_enum;

CREATE TYPE columns_parameter_enum AS ENUM (
    FROM build_my_enum(['business_metrics'],
                       ['year'],
                       [])
);

FROM pivot_table(['business_metrics'],
                 ['sum(revenue)', 'sum(cost)'],
                 ['product_line', 'product'],
                 ['year'],
                 [],
                 subtotals := 1,
                 grand_totals := 1,
                 values_axis := 'rows'
                 );

Fazit

Mit DuckDB 1.1 war das Teilen Ihres SQL-Wissens mit der Community noch nie einfacher! DuckDBs Community-Extension-Repository ist wirklich ein Package Manager für die SQL-Sprache. Makros in DuckDB sind jetzt hoch wiederverwendbar (dank query und query_table), und DuckDBs SQL-Syntax bietet genug Macht, um komplexe Aufgaben zu erledigen.

Lassen Sie uns wissen, ob die Extension pivot_table für Sie hilfreich ist – wir sind offen für Beiträge und Feature Requests! Zusammen können wir die ultimative Pivoting-Fähigkeit einmal schreiben und überall nutzen.

In Zukunft haben wir Pläne, das Anlegen von SQL-Extensions weiter zu vereinfachen. Natürlich würden wir uns über Ihr Feedback freuen! Kommen Sie zu uns auf Discord im Kanal community-extensions.

Happy analyzing!