ClickHouse: Funzioni di Windows Partendo da Zero

ClickHouse è un sistema di gestione di database relazionali altamente scalabile e orientato ai dati in colonna ottimizzato per carichi di lavoro analitici. Si tratta di un prodotto open-source sviluppato da Yandex, azienda specializzata nella ricerca su internet. Una delle caratteristiche chiave di ClickHouse è il suo supporto per funzioni analitiche avanzate, inclusa la compatibilità con le funzioni finestra.

Le funzioni finestra sono state introdotte per la prima volta alla fine degli anni ’90 da SQL Server e da allora sono diventate una caratteristica standard in molti database relazionali, compreso ClickHouse. Oggi, le funzioni finestra sono uno strumento indispensabile per gli analisti dei dati e i sviluppatori e sono ampiamente utilizzate in molte industrie.

Queste funzioni, note anche come funzioni analitiche, sono una classe di funzioni che eseguono calcoli basati su una finestra scorrevole di righe. Vengono utilizzate per eseguire vari tipi di analisi su set di dati, come il calcolo di totali in avanzamento, medie mobili e classificazioni. Le funzioni finestra sono uno strumento potente per l’analisi dei dati e possono notevolmente semplificare la scrittura di query complesse.

ClickHouse supporta un’ampia gamma di funzioni finestra, comprese funzioni integrate per rank, percent_rank, distribuzione cumulativa, numerazione delle righe, e totalizzazione in avanzamento. Inoltre, supporta anche funzioni finestra definite dall’utente, che consentono agli utenti di creare funzioni personalizzate per casi d’uso specifici.

In questo articolo, introdurrò il concetto di funzioni finestra e fornirò una panoramica completa delle funzioni finestra disponibili in ClickHouse. Inoltre, fornirò esempi di come utilizzare queste funzioni in scenari del mondo reale. Questo articolo è destinato a sviluppatori esperti che sono già familiari con SQL e vogliono imparare di più sulle funzioni finestra in ClickHouse.

Esempi del Mondo Reale di Utilizzo delle Funzioni Finestra

Le funzioni finestra sono uno strumento potente per l’analisi dei dati e sono ampiamente utilizzate in vari settori, come finanza, e-commerce e sanità.

Analisi Finanziaria

Uno dei primi impieghi delle funzioni finestra è stato nell’analisi finanziaria. Nell’analisi del mercato azionario, gli sviluppatori possono utilizzare le funzioni finestra per calcolare medie mobili, totali in corso e variazioni percentuali. Ad esempio, il calcolo di una media mobile a 50 giorni del prezzo di chiusura di un titolo è un caso d’uso comune per le funzioni finestra in finanza. Un altro esempio è il calcolo del totale in corso dei guadagni di una società su un periodo di tempo.

E-commerce Analytics

Nell’e-commerce, le funzioni finestra possono analizzare il comportamento del cliente e i modelli di vendita. Gli sviluppatori possono utilizzare le funzioni finestra per calcolare il totale in corso delle vendite per ogni prodotto, il ranking dei prodotti in base alle loro vendite e la crescita percentuale delle vendite nel tempo. Inoltre, le funzioni finestra possono essere utilizzate per analizzare il comportamento del cliente calcolando la frequenza media di acquisto e il valore medio di acquisto di un cliente su un periodo di tempo.

Analisi Sanitaria

Le funzioni di Windows nella sanità possono analizzare i dati dei pazienti, come segni vitali, risultati di laboratorio e uso di farmaci. Ad esempio, i sviluppatori possono utilizzare le funzioni di Windows per calcolare la media mobile della frequenza cardiaca di un paziente, il totale in corso delle dosi di farmaci assunte da un paziente e il ranking dei pazienti in base ai loro risultati di laboratorio.

Questi sono solo alcuni esempi dei molti scenari del mondo reale in cui gli sviluppatori possono utilizzare le funzioni di Windows. Il punto chiave è che le funzioni di Windows possono essere utilizzate per eseguire analisi avanzate su una vasta gamma di set di dati e possono semplificare notevolmente la scrittura di query complesse.

Sintassi delle Funzioni di Finestra in ClickHouse

In ClickHouse, le funzioni di finestra vengono utilizzate nella clausola SELECT di una query per eseguire calcoli su un set di righe. La sintassi di base per l’uso di una funzione di finestra in ClickHouse è la seguente:

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];

Analizziamo ogni parte della sintassi:

1. [column_list]: Questa è la lista delle colonne che si desidera restituire nella query.
2. [windows_function_name]([argument_list]): Questo è il nome della funzione di finestra che si desidera utilizzare e la lista degli argomenti per quella funzione.
3. AS [alias_name]: Questa clausola è opzionale e viene utilizzata per assegnare un nome alias all’output della funzione di finestra.
4. OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]]): Questa è la specifica del frame della finestra per la funzione di finestra.

• PARTITION BY [partition_column_list]: Questa clausola è opzionale e suddivide il set di risultati in partizioni in base ai valori nelle colonne specificate.
• ORDER BY [order_column_list]: Questa clausola è obbligatoria per specificare l’ordine in cui la funzione finestra elabora le righe.
• ROWS [BETWEEN [start_offset] AND [end_offset]]: Questa clausola è opzionale e viene utilizzata per specificare l’intervallo di righe su cui la funzione finestra deve operare. Il start_offset e il end_offset possono essere numeri interi positivi o negativi o valori speciali come UNBOUNDED PRECEDING o CURRENT ROW.

Ecco un esempio di utilizzo di una funzione finestra in ClickHouse:

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.

È importante notare che le funzioni finestra in ClickHouse possono essere utilizzate solo nella clausola SELECT di una query e non possono essere utilizzate nella clausola WHERE o HAVING. Inoltre, le funzioni finestra possono essere combinate con altre funzioni, come funzioni aggregate, per eseguire analisi dei dati ancora più avanzate.

Analisi Finanziaria con Funzioni Finestra

Nel settore finanziario, tracciare le prestazioni degli investimenti nel tempo è cruciale per la decisione. Le funzioni finestra in ClickHouse possono eseguire analisi sofisticate dei dati finanziari, come il calcolo delle medie mobili e dei totali in corsa.

Consideriamo uno scenario in cui abbiamo un tavolo delle quotazioni giornaliere di un titolo azionario. Il nostro obiettivo è calcolare la media mobile a 50 giorni del prezzo di chiusura e il totale in corsa del ritorno giornaliero sull’investimento.

Generazione dei Dati:

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.

Richiesta 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): Questa funzione finestra calcola la media mobile a 50 giorni del prezzo di chiusura prendendo la media delle 50 righe precedenti la riga corrente e la riga corrente stessa (o meno se il numero di giorni è inferiore a 50), ordinati per data. Il frame della finestra è specificato con ORDER BY date per specificare l’ordine in cui la funzione finestra elabora le righe e ROWS BETWEEN 49 PRECEDING AND CURRENT ROW per specificare l’intervallo di righe su cui la funzione finestra opera.
2. SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100: Questa funzione finestra calcola il totale in corso del rendimento giornaliero sul investimento sommando i rendimenti giornalieri, calcolati come differenza tra il prezzo di chiusura attuale e il prezzo di chiusura precedente diviso per il prezzo di chiusura precedente. La funzione lag viene utilizzata per recuperare il valore di una riga precedente nella stessa partizione, e il frame della finestra è specificato con ORDER BY date per garantire che i rendimenti siano calcolati nell’ordine corretto.

L’output della query restituisce la data, il simbolo, il prezzo di chiusura, la media mobile a 50 giorni e il totale in corso del rendimento giornaliero sul investimento per il simbolo AAAA.

Con le funzioni finestra in ClickHouse, gli analisti finanziari possono eseguire analisi sofisticate dei dati finanziari in tempo reale e prendere decisioni informate basate sui risultati.

E-commerce Analytics With Windows Functions

L’analisi dei dati delle vendite nel settore del commercio elettronico è cruciale per comprendere il comportamento dei clienti e prendere decisioni d’affari solide. Le funzioni finestra di ClickHouse possono effettuare analisi complesse dei dati del commercio elettronico, come il calcolo dei totali in avanzamento e il ranking dei prodotti in base alle vendite.

Immaginiamo di avere una tabella delle informazioni sulle vendite giornaliere per un solo sito di commercio elettronico. Per classificare i prodotti in base alle vendite totali, calcoleremo il totale in avanzamento delle vendite.

Generazione dei dati:

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.

Richiesta 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): Questa funzione finestra calcola il totale in avanzamento delle vendite per ogni prodotto sommando le vendite per ogni riga, suddivise per nome del prodotto, e ordinata per data. Il frame della finestra è specificato con PARTITION BY product_name per dividere i dati in partizioni in base al nome del prodotto, e ORDER BY date per specificare l’ordine in cui la funzione finestra elabora le righe.
2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC): Questa funzione finestra calcola il rango di ogni prodotto all’interno della sua categoria in base alle sue vendite totali. La funzione ROW_NUMBER genera un numero unico per ogni riga all’interno di una partizione, e il frame della finestra è specificato con PARTITION BY product_category per dividere i dati in partizioni in base alla categoria del prodotto, e ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC per ordinare i dati all’interno di ogni partizione in ordine decrescente in base al totale in avanzamento delle vendite.

L’output della query restituisce il nome del prodotto, la categoria del prodotto, il totale in corso delle vendite e il rango di ciascun prodotto in base alle sue vendite totali all’interno della sua categoria di prodotto.

Con le funzioni finestra in ClickHouse, gli analisti del commercio elettronico possono eseguire analisi sofisticate dei dati delle vendite in tempo reale e prendere decisioni informate in base ai risultati.

Analisi della Sanità con Funzioni Finestra

Per migliorare i risultati dei pazienti e prendere decisioni sagge sull’assistenza ai pazienti nel settore sanitario, è essenziale analizzare i dati dei pazienti. Le funzionalità finestra di ClickHouse offrono capacità avanzate di analisi dei dati sanitari, inclusa la possibilità di classificare i pazienti in base a vari criteri e calcolare totali in corso.

Considerate il seguente scenario: abbiamo una tabella di dati dei pazienti per un ospedale che include demografia dei pazienti, storia medica e trattamento attuale. Intendiamo determinare il totale in corso di giorni di degenza per ciascun paziente e classificarli in base ai loro giorni complessivi di degenza.

Generazione dei Dati:

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.

Richiesta SQL #1:

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;

Per ogni paziente, ho calcolato il totale in corso di giorni trascorsi in ospedale utilizzando una funzione finestra.

`SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date)`: Questa funzione finestra calcola il totale cumulativo dei giorni di degenza ospedaliera per ogni paziente sommando il numero di giorni tra la data di ammissione e la data di dimissione per ogni riga, suddivisa per ID paziente e ordinata per la data di ammissione. Il frame finestra è specificato con `PARTITION BY patient_id` per dividere i dati in partizioni in base all’ID paziente e con `ORDER BY admission_date` per specificare l’ordine in cui la funzione finestra elabora le righe.

I risultati della query forniscono l’ID paziente, l’età, il genere, la condizione e il totale cumulativo dei giorni trascorsi in ospedale per ogni paziente.

Nella seconda richiesta SQL più complessa, utilizzerò funzioni finestra per classificare i pazienti in base ai loro giorni totali di degenza ospedaliera.

Richiesta SQL #2:

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`: Questa funzione finestra assegna un rango unico a ogni paziente in base al loro totale cumulativo di giorni di degenza ospedaliera. La funzione assegna un rango a ogni riga in base all’ordine specificato con `ORDER BY running_total_days DESC`, il che significa che ai pazienti con il totale cumulativo più alto di giorni di degenza ospedaliera verrà assegnato il rango più basso. La funzione `ROW_NUMBER()` è una funzione incorporata in ClickHouse che assegna un numero unico a ogni riga all’interno del frame finestra specificato.
2. (SELECT ...): L’inner query calcola il totale cumulativo dei giorni di degenza per ogni paziente utilizzando la funzione finestra SUM. Il risultato dell’inner query viene poi utilizzato come input per l’outer query, dove la funzione finestra ROW_NUMBER viene applicata per classificare i pazienti in base ai loro giorni totali di degenza.

I risultati della query forniscono il rango di ogni paziente, l’età, il genere, la condizione e il totale cumulativo dei giorni trascorsi in ospedale.

Ecco un campione dei dati che questa query potrebbe generare:

Per visualizzare i risultati della query, è possibile utilizzare vari strumenti di visualizzazione dei dati come Matplotlib, Seaborn, Plotly, ecc. in Python. Ecco un esempio di come è possibile visualizzare i risultati della query utilizzando Matplotlib:

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

# Stabilire una connessione al database ClickHouse
engine = create_engine("clickhouse://:/")

# Eseguire la query SQL e memorizzare i risultati in un DataFrame Pandas
df = pd.read_sql_query("", engine)

# Tracciare i risultati utilizzando un grafico a barre
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 questo codice, utilizzo innanzitutto la funzione create_engine dalla libreria SQLAlchemy per stabilire una connessione al database ClickHouse. La funzione read_sql_query viene quindi utilizzata per eseguire la query SQL e memorizzare i risultati in un DataFrame Pandas. Infine, il grafico a barre viene creato utilizzando la funzione bar dalla libreria Matplotlib, dove l’asse x rappresenta l’ID del paziente, l’asse y rappresenta il totale cumulativo dei giorni di degenza e il colore di ogni barra rappresenta il rango del paziente.

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.

Conclusione

Infine, con ClickHouse, le funzioni window sono uno strumento eccezionale per l’analisi approfondita dei dati e i procedimenti di aggregazione. Permettono ai sviluppatori di eseguire calcoli complessi all’interno di una query, come totali in corso, classificazioni e percentili, che normalmente richiederebbero numerose interrogazioni o addirittura un pre-processing dei dati. Le funzioni window possono semplificare notevolmente l’analisi e l’aggregazione dei dati fornendo un meccanismo conciso e rapido per eseguire questi calcoli.

Tuttavia, tieni presente che le funzioni window possono essere computazionalmente costose, specialmente con dataset di grandi dimensioni. Ciò può essere mitigato utilizzando indici appropriati e formulando le query in modo intelligente. Tuttavia, è ancora cruciale comprendere le conseguenze sulle prestazioni delle funzioni window e utilizzarle con moderazione.