Die Suche nach „Fensterfunktionen in Clickhouse“ liefert keine aussagekräftigen Ergebnisse. Dieser Artikel ist ein Versuch, verstreute Daten aus dem Internet zusammenzufassen, Beispiele mit ClickHouseMeetup und meine eigenen Erfahrungen.
Fensterfunktionen - Syntax
Ich möchte Sie an die Syntax der Fensterfunktionen und die Art des Ergebnisses erinnern, das wir erhalten. In den Beispielen verwenden wir den Google BigQuery-Dialekt von Standart SQL. Hier ist ein Link zur Dokumentation über Fensterfunktionen (sie werden in der Dokumentation als Analysefunktion bezeichnet - eine genauere Übersetzung klingt nach Analysefunktionen). Und hier ist die Liste der Funktionen selbst.
Die allgemeine Syntax sieht folgendermaßen aus:
analytic_function_name ( [ argument_list ] ) OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
window_frame_clause:
{ rows_range } { frame_start | frame_between }
rows_range:
{ ROWS | RANGE }Gehen wir Schritt für Schritt vor:
- Die Fensterfunktion wird auf das im Ausdruck over_clause definierte Recordset angewendet.
- Das Recordset wird durch die PARTITION BY-Klausel definiert. Hier können Sie ein oder mehrere Felder auflisten, anhand derer das Recordset bestimmt wird. Funktioniert ähnlich wie GROUP BY.
Die Sortierung der Datensätze innerhalb eines Satzes wird mit ORDER BY festgelegt. - Sie können zusätzlich einen vordefinierten Satz von Datensätzen als Fenster einschränken. Das Fenster kann statisch definiert werden. Sie können beispielsweise 5 Datensätze als Fenster verwenden, 2 vor und 2 nach dem aktuellen Datensatz und dem aktuellen Datensatz selbst. Es wird so aussehen: REIHEN ZWISCHEN 2 VORANGEGANGENEN UND 2 FOLGENDEN.
Ein Beispiel für ein Konstrukt zum Festlegen eines dynamisch definierten Fensters sieht folgendermaßen aus: BEREICH ZWISCHEN UNBEGRENZTER VORANGEGANGENER UND AKTUELLER REIHE. Dieses Konstrukt definiert ein Fenster vom ersten bis zum aktuellen Datensatz gemäß der angegebenen Sortierreihenfolge.
Betrachten Sie als Beispiel die Berechnung der kumulierten Summe (Beispiel aus der Dokumentation):
SELECT item, purchases, category, SUM(purchases)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS total_purchases
FROM ProduceErgebnis:
+-------------------------------------------------------+
| item | purchases | category | total_purchases |
+-------------------------------------------------------+
| orange | 2 | fruit | 2 |
| apple | 8 | fruit | 10 |
| leek | 2 | vegetable | 2 |
| cabbage | 9 | vegetable | 11 |
| lettuce | 10 | vegetable | 21 |
| kale | 23 | vegetable | 44 |
+-------------------------------------------------------+
Was kann in Clickhouse getan werden?
Versuchen wir, dieses Beispiel in ClickHouse zu wiederholen. Natürlich verfügt ClickHouse über die Funktionen runningAccumulate , arrayCumSum und groupArrayMovingSum . Im ersten Fall müssen Sie jedoch den Status in einer Unterabfrage bestimmen ( weitere Details ), und im zweiten Fall gibt die Funktion ein Array zurück, das dann erweitert werden muss.
Wir werden die allgemeinste Abfrage erstellen. Die Anfrage selbst könnte folgendermaßen aussehen:
SELECT
items,
summ as purchases,
category,
sumArray(cum_summ) as total_purchases
FROM (SELECT
category,
groupArray(item) AS items,
groupArray(purchases) AS summ,
arrayMap(x -> arraySlice(summ, 1, x), arrayEnumerate(summ)) AS cum_summ
FROM (SELECT
item,
purchases,
category
FROM produce
ORDER BY category, purchases)
GROUP BY category)
ARRAY JOIN items, summ, cum_summ
GROUP BY category, items, summ
ORDER BY category, purchasesGehen wir Schritt für Schritt vor:
- Zunächst erstellen wir eine Unterabfrage, in der die erforderliche Datensortierung stattfindet (ORDER BY-Kategorie, Käufe). Die Sortierung muss mit den Feldern in den Ausdrücken PARTITION BY und ORDER BY der Fensterfunktion übereinstimmen.
- , , PARTITION BY. item .
purchases , summ . - — ArrayMap. , func arr.
arr — [1, 2, …, length(summ)], arrayEnumerate.
func — arraySlice(summ, 1, x), x — arr, . summ x. , cum_sum , , .
ArrayMap arrayEnumerate , , . ( 3), ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.
arrayMap(x -> arraySlice(summ, if(x-1 > 0, x-1, 1), if(x-1 > 0, 3, 2)), arrayEnumerate(summ))
, . 2 ClickHouse:
- —
, , arrayMap arrayFilter.. — ( — ) (alias) arrayMap, arrayFilter . - — . , , arrayReverse arraySlice.
- —
- Der letzte Schritt ist, dass wir die Arrays mit ARRAY JOIN zu einer Tabelle erweitern müssen. Wir müssen auch die Summenaggregatfunktion mit dem Modifikator -Array anwenden (daher sieht die Aggregatfunktion wie sumArray aus) auf das von der ArrayMap-Funktion zurückgegebene Ergebnis.
Ausgabe
Es ist möglich, den Betrieb von Fensterfunktionen in ClickHouse zu emulieren. Nicht sehr schnell und nicht sehr hübsch. Kurz gesagt besteht die Pipeline aus 3 Schritten:
- Sortierte Abfrage. Dieser Schritt bereitet das Recordset vor.
- Gruppieren in Arrays und Ausführen von Array-Operationen. Dieser Schritt definiert das Fenster unserer Fensterfunktion.
- Erweitern einer Tabelle mithilfe von Aggregatfunktionen.