ClickHouse: Windows-Funktionen von Grund auf

Tutorials
Pandas

ClickHouse ist ein hoch skalierbares, spaltenorientiertes relationelles Datenbankmanagementsystem, das für analytische Workloads optimiert ist. Es handelt sich um ein Open-Source-Produkt, das von Yandex, einer Suchmaschinenfirma, entwickelt wurde. Ein wichtiges Merkmal von ClickHouse ist die Unterstützung für fortgeschrittene analytische Funktionen, einschließlich Fensterfunktionen.

Fensterfunktionen wurden erstmals Ende der 1990er Jahre von SQL Server eingeführt und sind mittlerweile in vielen relationellen Datenbanken, einschließlich ClickHouse, zu einem Standardmerkmal geworden. Heute sind Fensterfunktionen ein unverzichtbares Werkzeug für Datenanalysten und Entwickler und werden in vielen Branchen weit verbreitet eingesetzt.

Diese Funktionen, auch als analytische Funktionen bekannt, sind eine Klasse von Funktionen, die Berechnungen basierend auf einem schiebenden Fenster von Zeilen durchführen. Sie werden zur Durchführung verschiedener Arten von Analysen an Datensätzen verwendet, wie z.B. das Berechnen von laufenden Summen, gleitenden Durchschnitten und Rangfolgen. Fensterfunktionen sind ein mächtiges Werkzeug für die Datenanalyse und können das Schreiben komplexer Abfragen erheblich vereinfachen.

ClickHouse unterstützt eine Vielzahl von Fensterfunktionen, einschließlich integrierter Funktionen für Rangfolge, Prozentrang, kumulative Verteilung, Zeilenbeschriftung und laufende Summen. Darüber hinaus unterstützt es auch benutzerdefinierte Fensterfunktionen, die Benutzern ermöglichen, benutzerdefinierte Funktionen für spezifische Anwendungsfälle zu erstellen.

In diesem Artikel werde ich das Konzept von Fensterfunktionen vorstellen und einen umfassenden Überblick über die verfügbaren Fensterfunktionen in ClickHouse geben. Ich werde auch Beispiele dafür liefern, wie diese Funktionen in realen Szenarien eingesetzt werden können. Dieser Artikel richtet sich an erfahrene Entwickler, die bereits mit SQL vertraut sind und mehr über Fensterfunktionen in ClickHouse erfahren möchten.

Real-World-Beispiele für die Verwendung von Fensterfunktionen

Fensterfunktionen sind ein mächtiges Werkzeug für die Datenanalyse und werden in verschiedenen Branchen wie Finanzen, E-Commerce und Gesundheitswesen weit verbreitet eingesetzt.

Finanzanalyse

Eines der frühesten Anwendungsgebiete von Fensterfunktionen war die Finanzanalyse. Bei der Aktienmarktanalyse können Entwickler Fensterfunktionen verwenden, um gleitende Durchschnitte, laufende Summen und prozentuale Veränderungen zu berechnen. Zum Beispiel ist die Berechnung eines 50-tägigen gleitenden Durchschnitts des Schlusskurses einer Aktie ein übliches Anwendungsbeispiel für Fensterfunktionen in der Finanzwelt. Ein weiteres Beispiel ist die Berechnung der laufenden Summe der Erträge eines Unternehmens über einen bestimmten Zeitraum.

E-commerce Analytics

Im E-Commerce können Fensterfunktionen zur Analyse von Kundenverhalten und Verkaufsmustern eingesetzt werden. Entwickler können Fensterfunktionen verwenden, um die laufende Summe der Verkäufe für jedes Produkt zu berechnen, die Rangfolge der Produkte basierend auf ihren Verkäufen und das Wachstum der Verkäufe über die Zeit. Darüber hinaus können Fensterfunktionen zur Analyse von Kundenverhalten eingesetzt werden, indem der durchschnittliche Kaufzyklus und der durchschnittliche Kaufwert eines Kunden über einen bestimmten Zeitraum berechnet werden.

Gesundheitsanalyse

Windows-Funktionen im Gesundheitswesen können Patientendaten analysieren, wie z.B. Vitalzeichen, Laborresultate und Medikamenteneinnahme. Beispielsweise können Entwickler Windows-Funktionen nutzen, um die gleitende Durchschnittsberechnung des Herzfrequenz eines Patienten, die laufende Summe der Medikamentendosen eines Patienten und die Rangfolge der Patienten basierend auf ihren Laborresultaten zu berechnen.

Dies sind nur einige Beispiele der vielen realen Szenarien, in denen Entwickler Windows-Funktionen einsetzen können. Der Hauptpunkt ist, dass Windows-Funktionen zur Durchführung von fortgeschrittenen Analysen auf einer Vielzahl von Datensätzen verwendet werden können und das Schreiben komplexer Abfragen erheblich vereinfachen können.

Syntax von Windows-Funktionen in ClickHouse

In ClickHouse werden Windows-Funktionen in der SELECT-Klausel einer Abfrage verwendet, um Berechnungen an einer Reihe von Zeilen durchzuführen. Die grundlegende Syntax zur Verwendung einer Windows-Funktion in ClickHouse ist wie folgt:

SQL

  

SELECT
  [column_list],
  [windows_function_name]([argument_list])
    OVER ([PARTITION BY [partition_column_list]]
         [ORDER BY [order_column_list]]
         [ROWS [BETWEEN [start_offset] AND [end_offset]]])
  AS [alias_name]
FROM [table_name];

Lassen Sie uns jedes Teil der Syntax genauer betrachten:

  1. [column_list]: Dies ist die Liste der Spalten, die Sie in der Abfrage zurückgeben möchten.
  2. [windows_function_name]([argument_list]): Hierbei handelt es sich um den Namen der Windows-Funktion, die Sie verwenden möchten, sowie die Liste der Argumente für diese Funktion.
  3. AS [alias_name]: Diese Klausel ist optional und wird verwendet, um ein Alias-Name für die Ausgabe der Windows-Funktion zu vergeben.
  4. OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]]): Dies ist die Spezifikation des Fensterrahmens für die Windows-Funktion.
  • PARTITION BY [partition_column_list]: Dieser Ausdruck ist optional und teilt das Ergebnisset in Partitionen auf, basierend auf den Werten in den angegebenen Spalten.
  • ORDER BY [order_column_list]: Dieser Ausdruck ist erforderlich, um die Reihenfolge festzulegen, in der die Fensterfunktion die Zeilen verarbeitet.
  • ROWS [BETWEEN [start_offset] AND [end_offset]]: Dieser Ausdruck ist optional und wird verwendet, um den Zeilenbereich für die Fensterfunktion festzulegen. Die start_offset und end_offset können positive oder negative ganze Zahlen oder spezielle Werte wie UNBOUNDED PRECEDING oder CURRENT ROW sein.

Hier ist ein Beispiel für die Verwendung einer Fensterfunktion in ClickHouse:

SQL

  

SELECT
  date,
  product_id,
  sales,
  SUM(sales) OVER (PARTITION BY product_id ORDER BY date) AS running_total
FROM sales_data;

I use the SUM windows function to calculate the running total of sales for each product, grouped by the product_id column. The window frame is specified with PARTITION BY product_id to divide the result set into partitions based on the product_id and ORDER BY date to specify the order in which the windows function processes the rows. The output of the windows function is given an alias name running_total.

Es ist wichtig zu bemerken, dass Fensterfunktionen in ClickHouse nur in der SELECT-Klausel einer Abfrage verwendet werden können und nicht in der WHERE- oder HAVING-Klausel. Darüber hinaus können Fensterfunktionen mit anderen Funktionen, wie z.B. Aggregatfunktionen, kombiniert werden, um noch fortgeschrittenere Datenanalyse durchzuführen.

Finanzielle Analyse mit Fensterfunktionen

In der Finanzbranche ist die Verfolgung der Investitionsleistung über die Zeit entscheidend für die Entscheidungsfindung. Fensterfunktionen in ClickHouse können eine anspruchsvolle Analyse von Finanzdaten durchführen, wie z.B. das Berechnen von gleitenden Durchschnitten und laufenden Summen.

Betrachten wir ein Szenario, in dem wir eine Tabelle mit täglichen Aktienkursen für eine einzelne Aktie haben. Unser Ziel ist es, den 50-Tage-gleitenden Durchschnitt des Schlusskurses und die laufende Summe der täglichen Rendite der Investition zu berechnen.

Datenerzeugung:

SQL

  

CREATE TABLE stock_prices (
  date Date,
  symbol String,
  open Float32,
  close Float32,
  high Float32,
  low Float32,
  volume UInt64
) ENGINE = MergeTree(date, (symbol, date), 8192);

INSERT INTO stock_prices
SELECT
  toDate('yyyy-MM-dd', d),
  'AAAA',
  rand(),
  rand(),
  rand(),
  rand(),
  rand() * 100000
FROM generateDates('2022-01-01', '2023-02-10') d;

I create a table stock_prices to store daily stock prices for the symbol AAAA. I then insert randomly generated data into the table for the years 2022–2023.

SQL-Anfrage:

SQL

  

SELECT
  date,
  symbol,
  close,
  AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS moving_average,
  SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100 AS running_return
FROM stock_prices
WHERE symbol = 'AAAA';

I use windows functions to perform financial analysis on the stock price data.

  1. AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW): Diese Fensterfunktion berechnet den 50-Tage-gleitenden Durchschnitt des Schlusskurses, indem sie den Durchschnitt der 50 Zeilen vor der aktuellen Zeile und der aktuellen Zeile selbst (oder weniger, wenn die Anzahl der Tage weniger als 50 ist) nimmt, geordnet nach dem Datum. Das Fensterrahmen wird mit ORDER BY date festgelegt, um die Reihenfolge festzulegen, in der die Fensterfunktion die Zeilen verarbeitet, und mit ROWS BETWEEN 49 PRECEDING AND CURRENT ROW wird der Zeilenbereich für die Fensterfunktion festgelegt.
  2. SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100: Diese Fensterfunktion berechnet die laufende Summe der täglichen Rendite auf das eingesetzte Kapital, indem die täglichen Renditen summiert werden, die als Differenz zwischen dem aktuellen Schlusskurs und dem vorherigen Schlusskurs dividiert durch den vorherigen Schlusskurs berechnet werden. Die lag-Funktion wird verwendet, um den Wert einer vorherigen Zeile in der gleichen Partition abzurufen, und der Fensterrahmen wird mit ORDER BY date festgelegt, um sicherzustellen, dass die Renditen in der korrekten Reihenfolge berechnet werden.

Das Ergebnis der Abfrage gibt das Datum, das Symbol, den Schlusskurs, den 50-Tage-gleitenden Durchschnitt und die laufende Summe der täglichen Rendite auf das eingesetzte Kapital für das Symbol AAAA zurück.

Mit Fensterfunktionen in ClickHouse können Finanzanalysten in Echtzeit anspruchsvolle Analysen von Finanzdaten durchführen und fundierte Entscheidungen basierend auf den Ergebnissen treffen.

E-commerce Analytics With Windows Functions

Die Analyse von Verkaufsdaten in der E-Commerce-Branche ist entscheidend für das Verständnis von Kundenverhalten und die Entscheidungsfindung im Geschäft. ClickHouses Fensterfunktionen können komplexe E-Commerce-Datenanalysen durchführen, wie z.B. das Berechnen von laufenden Gesamtsummen und das Rangieren von Produkten nach Verkaufszahlen.

Stellen wir uns vor, wir haben eine Tabelle mit täglichen Verkaufsinformationen für nur eine E-Commerce-Website. Um die Produkte basierend auf den Gesamtverkäufen zu rangieren, berechnen wir die laufende Gesamtsumme der Verkäufe.

Datenerzeugung:

SQL

  

CREATE TABLE sales_data (
  date Date,
  product_name String,
  product_category String,
  sales UInt64
) ENGINE = MergeTree(date, (product_name, date), 8192);

INSERT INTO sales_data
SELECT
  toDate('yyyy-MM-dd', d),
  'Product ' || toString(intDiv(rand() * 100, 1)),
  'Category ' || toString(intDiv(rand() * 5, 1)),
  rand() * 1000
FROM generateDates('2022-01-01', '2023-02-10') d;

I create a table sales_data to store daily sales data for a single e-commerce store. I then insert randomly generated data into the table for the years 2022–2023.

SQL-Anfrage:

SQL

  

SELECT
  product_name,
  product_category,
  SUM(sales) OVER (PARTITION BY product_name ORDER BY date) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC) AS rank
FROM sales_data;

I use windows functions to perform e-commerce analytics on sales data.

  1. SUM(sales) OVER (PARTITION BY product_name ORDER BY date): Diese Fensterfunktion berechnet die laufende Gesamtsumme der Verkäufe für jedes Produkt, indem sie die Verkäufe für jede Zeile summiert, geteilt nach Produktname und geordnet nach Datum. Der Fensterrahmen wird mit PARTITION BY product_name festgelegt, um die Daten basierend auf dem Produktnamen in Partitionen aufzuteilen, und mit ORDER BY date wird die Reihenfolge festgelegt, in der die Fensterfunktion die Zeilen verarbeitet.
  2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC): Diese Fensterfunktion berechnet den Rang jedes Produkts in seiner Kategorie basierend auf den Gesamtverkäufen. Die ROW_NUMBER-Funktion generiert eine eindeutige Nummer für jede Zeile innerhalb einer Partition, und der Fensterrahmen wird mit PARTITION BY product_category festgelegt, um die Daten basierend auf der Produktkategorie in Partitionen aufzuteilen, und mit ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC werden die Daten innerhalb jeder Partition in absteigender Reihenfolge basierend auf der laufenden Gesamtsumme der Verkäufe sortiert.

Die Abfrageausgabe gibt den Produktnamen, die Produktkategorie, die laufende Summe der Verkäufe und den Rang jedes Produkts basierend auf seinen Gesamtverkäufen innerhalb seiner Produktkategorie zurück.

Mit Fensterfunktionen in ClickHouse können E-Commerce-Analysten komplexe Verkaufsdatenanalysen in Echtzeit durchführen und fundierte Entscheidungen auf Basis der Ergebnisse treffen.

Gesundheitsanalyse mit Fensterfunktionen

Um im Gesundheitssektor Patientenergebnisse zu verbessern und fundierte Entscheidungen über die Patientenversorgung zu treffen, ist die Analyse von Patientendaten unerlässlich. Die Fensterfunktionen von ClickHouse bieten fortgeschrittene Fähigkeiten zur Gesundheitsdatenanalyse, einschließlich der Möglichkeit, Patienten nach verschiedenen Kriterien zu bewerten und laufende Summen zu berechnen.

Betrachten Sie folgende Situation: Wir haben eine Patientendatenbank für ein Krankenhaus, die Patientenbeschreibungen, medizinische Vorgeschichte und aktuelle Behandlung enthält. Wir möchten die laufende Summe der Krankenhausaufenthalte jedes Patienten ermitteln und sie nach ihren gesamten Krankenhausaufenthalten rangieren.

Datenerzeugung:

SQL

  

CREATE TABLE patient_data (
  admission_date Date,
  discharge_date Date,
  patient_id String,
  age UInt16,
  gender String,
  condition String
) ENGINE = MergeTree(admission_date, (patient_id, admission_date), 8192);

INSERT INTO patient_data
SELECT
  toDate('yyyy-MM-dd', d1),
  toDate('yyyy-MM-dd', d2),
  'Patient ' || toString(intDiv(rand() * 10000, 1)),
  rand() % 90 + 10,
  if(rand() % 2 = 0, 'Male', 'Female'),
  'Condition ' || toString(intDiv(rand() * 100, 1))
FROM generateDates('2022-01-01', '2023-02-10') d1
JOIN generateDates('2022-01-01', '2023-02-10') d2 ON d1 <= d2;

I create a table patient_data to store patient data for a hospital. I then inserted randomly generated data into the table for the years 2022–2023. Each row represents a patient’s hospitalization, including the admission date, discharge date, patient ID, age, gender, and medical condition.

SQL-Anfrage #1:

SQL

  

SELECT
  patient_id,
  age,
  gender,
  condition,
  SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date) AS running_total_days
FROM patient_data;

Für jeden Patienten habe ich die laufende Summe der im Krankenhaus verbrachten Tage unter Verwendung einer Fensterfunktion berechnet.

SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date): Diese Fensterfunktion berechnet die laufende Summe der Krankenhausaufenthalte für jeden Patienten, indem sie die Anzahl der Tage zwischen dem Aufnahmedatum und dem Entlassungsdatum für jede Zeile addiert, partitioniert nach Patienten-ID und geordnet nach Aufnahmedatum. Das Fensterrahmen wird mit PARTITION BY patient_id angegeben, um die Daten basierend auf der Patienten-ID in Partitionen aufzuteilen, und mit ORDER BY admission_date wird die Reihenfolge festgelegt, in der die Fensterfunktion die Zeilen verarbeitet.

Die Abfrageergebnisse geben die Patienten-ID, das Alter, das Geschlecht, den Zustand und die laufende Summe der Tage, die im Krankenhaus verbracht wurden, für jeden Patienten an.

In der zweiten, komplexeren SQL-Anfrage werde ich Fensterfunktionen verwenden, um die Patienten nach ihren gesamten Krankenhausaufenthalten zu rangieren.

SQL-Anfrage #2:

SQL

  

SELECT
  patient_id,
  age,
  gender,
  condition,
  running_total_days,
  ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank
FROM (
  SELECT
    patient_id,
    age,
    gender,
    condition,
    SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date) AS running_total_days
  FROM patient_data
)

  1. ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank: Diese Fensterfunktion weist jedem Patienten eine eindeutige Rangfolge basierend auf ihren laufenden Gesamttagen der Krankenhausbehandlung zu. Die Funktion weist jeder Zeile eine Rangfolge basierend auf der mit ORDER BY running_total_days DESC festgelegten Reihenfolge zu, was bedeutet, dass den Patienten mit den höchsten laufenden Gesamttagen der Krankenhausbehandlung die niedrigsten Ränge zugewiesen werden. Die ROW_NUMBER()-Funktion ist eine integrierte Funktion in ClickHouse, die jedem Datensatz innerhalb des spezifizierten Fensterrahmens eine eindeutige Nummer zuweist.
  2. (SELECT ...): Die innere Abfrage berechnet die laufende Summe der Krankenhausaufenthalte für jeden Patienten unter Verwendung der SUM Fensterfunktion. Das Ergebnis der inneren Abfrage wird dann als Eingabe für die äußere Abfrage verwendet, bei der die ROW_NUMBER Fensterfunktion angewendet wird, um die Patienten nach ihren gesamten Krankenhausaufenthalten zu ordnen.

Die Abfrageergebnisse geben den Rang jedes Patienten, sein Alter, Geschlecht, Zustand und die laufende Summe der Tage an, die im Krankenhaus verbracht wurden.

Hier ist ein Beispiel für die Daten, die diese Abfrage generieren könnte:


Zur Visualisierung der Abfrageergebnisse können verschiedene Datenvisualisierungstools wie Matplotlib, Seaborn, Plotly usw. in Python verwendet werden. Hier ist ein Beispiel, wie Sie die Ergebnisse der Abfrage mit Matplotlib visualisieren können:

Python

  

import matplotlib.pyplot as plt
import pandas as pd
from sqlalchemy import create_engine

# Stelle eine Verbindung zum ClickHouse-Datenbank
engine = create_engine("clickhouse://:/")

# Führe die SQL-Abfrage aus und speichere die Ergebnisse in einem Pandas DataFrame
df = pd.read_sql_query("", engine)

# Erstelle das Ergebnis mit einem Balkendiagramm
plt.bar(df['patient_id'], df['running_total_days'], color=df['rank'])
plt.xlabel("Patient ID")
plt.ylabel("Running Total of Hospitalization Days")
plt.title("Healthcare Analytics with Windows Functions in ClickHouse")
plt.show()

In diesem Code verwende ich zuerst die create_engine Funktion aus der SQLAlchemy-Bibliothek, um eine Verbindung zur ClickHouse-Datenbank herzustellen. Die read_sql_query Funktion wird dann verwendet, um die SQL-Abfrage auszuführen und die Ergebnisse in einem Pandas DataFrame zu speichern. Schließlich wird das Balkendiagramm mit der bar Funktion aus der Matplotlib-Bibliothek erstellt, wobei die x-Achse die Patienten-ID darstellt, die y-Achse die laufende Summe der Krankenhausaufenthalte und die Farbe jedes Balkens den Patientenrang darstellt.

I successfully used ClickHouse’s windows functions in those examples to evaluate healthcare data and rank patients based on their total hospitalization days. This analysis can uncover patterns and trends in patient data, which can help to inform clinical decision-making and improve patient outcomes.

Schlussfolgerung

Schließlich sind mit ClickHouse Fensterfunktionen ein großartiges Werkzeug für umfangreiche Datenanalyse und Aggregationsverfahren. Sie ermöglichen Entwicklern komplexe Berechnungen innerhalb einer Abfrage durchzuführen, wie z.B. laufende Summen, Rangfolgen und Perzentile, die normalerweise mehrere Abfragen oder sogar Datenvorverarbeitung erfordern würden. Fensterfunktionen können die Datenanalyse und Aggregation erheblich erleichtern, indem sie einen prägnanten und schnellen Mechanismus zur Durchführung dieser Berechnungen bieten.

Dennoch ist zu bedenken, dass Fensterfunktionen rechenintensiv sein können, insbesondere bei großen Datensätzen. Dies kann durch die Verwendung geeigneter Indizes und die geschickte Konstruktion von Abfragen minimiert werden. Es ist jedoch immer noch entscheidend, die Leistungsfolgen von Fensterfunktionen zu verstehen und sie sparsam einzusetzen.

Source:
https://dzone.com/articles/clickhouse-windows-functions-from-scratch