ClickHouse : Fonctions Windows Depuis le Début

Tutoriels
Pandas

ClickHouse est un système de gestion de base de données relationnelle hautement scalable, orienté colonne, optimisé pour les charges de travail analytiques. Il s’agit d’un produit open-source développé par Yandex, une entreprise de moteur de recherche. L’une des caractéristiques clés de ClickHouse est son support pour des fonctions analytiques avancées, y compris les fonctions de fenêtre.

Les fonctions de fenêtre ont été introduites pour la première fois à la fin des années 1990 par SQL Server, et depuis lors, sont devenues une fonction standard dans de nombreuses bases de données relationnelles, dont ClickHouse. Aujourd’hui, les fonctions de fenêtre sont des outils indispensables pour les analystes de données et les développeurs et sont largement utilisées dans de nombreux secteurs.

Ces fonctions, également connues sous le nom de fonctions analytiques, sont une classe de fonctions qui effectuent des calculs basés sur une fenêtre glissante de lignes. Elles sont utilisées pour effectuer diverses types d’analyse sur des ensembles de données, telles que le calcul de totaux courants, de moyennes mobiles et de classements. Les fonctions de fenêtre sont un outil puissant pour l’analyse des données et peuvent considérablement simplifier l’écriture de requêtes complexes.

ClickHouse prend en charge un large éventail de fonctions de fenêtre, y compris des fonctions intégrées pour classement, rang en pourcentage, distribution cumulative, numérotation des lignes, et totaux courants. De plus, il prend également en charge les fonctions de fenêtre définies par l’utilisateur, ce qui permet aux utilisateurs de créer des fonctions personnalisées pour des cas d’utilisation spécifiques.

Dans cet article, je vais présenter le concept des fonctions de fenêtre et fournir une vue d’ensemble complète des fonctions de fenêtre disponibles dans ClickHouse. Je fournirai également des exemples de la manière d’utiliser ces fonctions dans des scénarios du monde réel. Cet article s’adresse aux développeurs expérimentés qui sont déjà familiers avec SQL et souhaitent en apprendre davantage sur les fonctions de fenêtre dans ClickHouse.

Exemples concrets d’utilisation des fonctions de fenêtre

Les fonctions de fenêtre sont un outil puissant pour l’analyse des données et sont largement utilisées dans divers secteurs, tels que la finance, le commerce électronique et la santé.

Analyse Financière

L’une des premières applications des fonctions de fenêtre était dans l’analyse financière. Dans l’analyse des marchés boursiers, les développeurs peuvent utiliser les fonctions de fenêtre pour calculer les moyennes mobiles, les totaux courants et les variations en pourcentage. Par exemple, calculer une moyenne mobile de 50 jours du cours de clôture d’une action est un cas d’utilisation courant des fonctions de fenêtre en finance. Un autre exemple est le calcul du total courant des bénéfices d’une entreprise sur une période de temps.

E-commerce Analytics

Dans le commerce électronique, les fonctions de fenêtre peuvent analyser le comportement des clients et les modèles de vente. Les développeurs peuvent utiliser les fonctions de fenêtre pour calculer le total courant des ventes pour chaque produit, le classement des produits en fonction de leurs ventes et le pourcentage de croissance des ventes au fil du temps. De plus, les fonctions de fenêtre peuvent être utilisées pour analyser le comportement des clients en calculant la fréquence moyenne des achats et la valeur moyenne d’un achat d’un client sur une période de temps.

Analytique en Santé

Les fonctions Windows dans le secteur de la santé peuvent analyser les données des patients, telles que les signes vitaux, les résultats des tests de laboratoire et l’utilisation de médicaments. Par exemple, les développeurs peuvent utiliser des fonctions Windows pour calculer la moyenne mobile de la fréquence cardiaque d’un patient, le total courant des doses de médicaments d’un patient et le classement des patients en fonction de leurs résultats de laboratoire.

Ce ne sont là que quelques exemples des nombreux scénarios réels où les développeurs peuvent utiliser des fonctions Windows. L’aspect clé est que les fonctions Windows peuvent être utilisées pour effectuer une analyse avancée sur une grande variété de jeux de données et peuvent grandement simplifier l’écriture de requêtes complexes.

Syntaxe des Fonctions Windows dans ClickHouse

Dans ClickHouse, les fonctions Windows sont utilisées dans la clause SELECT d’une requête pour effectuer des calculs sur un ensemble de lignes. La syntaxe de base pour utiliser une fonction Windows dans ClickHouse est la suivante:

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

Analysons chaque partie de la syntaxe:

  1. [liste_de_colonnes]: C’est la liste des colonnes que vous souhaitez retourner dans la requête.
  2. [nom_de_la_fonction_windows]([liste_d'arguments]): C’est le nom de la fonction Windows que vous souhaitez utiliser et la liste des arguments pour cette fonction.
  3. AS [nom_d'alias]: Cette clause est facultative et est utilisée pour donner un nom d’alias au résultat de la fonction Windows.
  4. OVER ([PARTITION BY [liste_de_colonnes_de_partition]] [ORDER BY [liste_de_colonnes_d'ordre]] [ROWS [BETWEEN [décalage_début] AND [décalage_fin]]]): C’est la spécification du cadre de fenêtre pour la fonction Windows.
  • PARTITION BY [partition_column_list]: Cette clause est facultative et divise le jeu de résultats en partitions en fonction des valeurs dans les colonnes spécifiées.
  • ORDER BY [order_column_list]: Cette clause est requise pour spécifier l’ordre dans lequel la fonction de fenêtre traite les lignes.
  • ROWS [BETWEEN [start_offset] AND [end_offset]]: Cette clause est facultative et est utilisée pour spécifier la plage de lignes sur laquelle la fonction de fenêtre doit opérer. Les start_offset et end_offset peuvent être des entiers positifs ou négatifs ou des valeurs spéciales comme UNBOUNDED PRECEDING ou CURRENT ROW.

Voici un exemple d’utilisation d’une fonction de fenêtre dans 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.

Il est important de noter que les fonctions de fenêtre dans ClickHouse ne peuvent être utilisées que dans la clause SELECT d’une requête et ne peuvent pas être utilisées dans la clause WHERE ou HAVING. De plus, les fonctions de fenêtre peuvent être combinées avec d’autres fonctions, telles que les fonctions d’agrégation, pour effectuer une analyse de données encore plus avancée.

Analyse Financière avec Fonctions de Fenêtre

Dans l’industrie financière, suivre la performance des investissements dans le temps est crucial pour la prise de décision. Les fonctions de fenêtre dans ClickHouse peuvent effectuer une analyse sophistiquée des données financières, telles que le calcul des moyennes mobiles et des totaux courants.

Considérons un scénario où nous avons une table des cours quotidiens d’une seule action. Nous visons à calculer la moyenne mobile sur 50 jours du prix de clôture et le total courant du rendement journalier sur l’investissement.

Génération des données:

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.

Requête SQL:

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): Cette fonction de fenêtrage calcule la moyenne mobile sur 50 jours du prix de clôture en prenant la moyenne des 50 lignes précédant la ligne courante et la ligne courante elle-même (ou moins si le nombre de jours est inférieur à 50), triées par la date. Le cadre de la fenêtre est spécifié avec ORDER BY date pour indiquer l’ordre dans lequel la fonction de fenêtrage traite les lignes et ROWS BETWEEN 49 PRECEDING AND CURRENT ROW pour spécifier la plage de lignes sur lesquelles la fonction de fenêtrage doit agir.
  2. SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100: Cette fonction de fenêtrage calcule le total courant du rendement quotidien de l’investissement en additionnant les rendements quotidiens, qui sont calculés comme la différence entre le prix de clôture actuel et le prix de clôture précédent divisé par le prix de clôture précédent. La fonction lag est utilisée pour récupérer la valeur d’une ligne précédente dans la même partition, et le cadre de la fenêtre est spécifié avec ORDER BY date pour s’assurer que les rendements sont calculés dans le bon ordre.

La sortie de la requête retourne la date, le symbole, le prix de clôture, la moyenne mobile sur 50 jours et le total courant du rendement quotidien de l’investissement pour le symbole AAAA.

Avec les fonctions de fenêtrage dans ClickHouse, les analystes financiers peuvent effectuer des analyses sophistiquées des données financières en temps réel et prendre des décisions éclairées en fonction des résultats.

E-commerce Analytics With Windows Functions

L’analyse des données de vente dans l’industrie du commerce électronique est cruciale pour comprendre le comportement des clients et prendre des décisions commerciales éclairées. Les fonctions Windows de ClickHouse peuvent effectuer une analyse complexe des données de commerce électronique, telles que le calcul des totaux courants et le classement des produits par ventes.

Imaginons que nous disposons d’une table des informations de vente quotidiennes pour un seul site de commerce électronique. Pour classer les produits en fonction des ventes totales, nous allons calculer le total courant des ventes.

Génération des données:

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.

Requête SQL:

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) : Cette fonction window calcule le total courant des ventes pour chaque produit en additionnant les ventes pour chaque ligne, partitionnées par le nom du produit, et ordonnées par la date. Le cadre de la fenêtre est spécifié avec PARTITION BY product_name pour diviser les données en partitions en fonction du nom du produit, et ORDER BY date pour spécifier l’ordre dans lequel la fonction window traite les lignes.
  2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC) : Cette fonction window calcule le rang de chaque produit dans sa catégorie en fonction de ses ventes totales. La fonction ROW_NUMBER génère un numéro unique pour chaque ligne dans une partition, et le cadre de la fenêtre est spécifié avec PARTITION BY product_category pour diviser les données en partitions en fonction de la catégorie du produit, et ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC pour trier les données dans chaque partition en ordre décroissant en fonction du total courant des ventes.

La sortie de la requête renvoie le nom du produit, la catégorie du produit, le total courant des ventes et le rang de chaque produit en fonction de ses ventes totales au sein de sa catégorie de produit.

Grâce aux fonctions de fenêtre dans ClickHouse, les analystes du commerce électronique peuvent effectuer des analyses sophistiquées des données de vente en temps réel et prendre des décisions éclairées en fonction des résultats.

Analytique des soins de santé avec les fonctions de fenêtre

Pour que le secteur de la santé améliore les résultats des patients et prenne des décisions judicieuses concernant les soins aux patients, l’analyse des données des patients est essentielle. Les fonctionnalités de fenêtre de ClickHouse offrent des capacités avancées d’analyse des données de santé, notamment la possibilité de classer les patients selon divers critères et de calculer des totaux courants.

Considérons le scénario suivant : nous avons une table de données de patients pour un hôpital qui inclut les caractéristiques démographiques des patients, leur histoire médicale et leur traitement actuel. Nous avons l’intention de déterminer le total courant de jours d’hospitalisation de chaque patient et de les classer en fonction de leurs jours d’hospitalisation totaux.

Génération de données:

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.

Requête SQL #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;

Pour chaque patient, j’ai calculé le total courant de jours passés à l’hôpital en utilisant une fonction de fenêtre.

SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date): Cette fonction de fenêtrage calcule le total courant de jours d’hospitalisation pour chaque patient en additionnant le nombre de jours entre la date d’admission et la date de sortie pour chaque ligne, partitionnée par l’ID du patient, et triée par la date d’admission. Le cadre de la fenêtre est spécifié avec PARTITION BY patient_id pour diviser les données en partitions en fonction de l’ID du patient et ORDER BY admission_date pour spécifier l’ordre dans lequel la fonction de fenêtrage traite les lignes.

Les résultats de la requête donnent l’ID du patient, l’âge, le sexe, l’état et le total courant de jours passés à l’hôpital pour chaque patient.

Dans la deuxième requête SQL plus complexe, je vais utiliser des fonctions de fenêtrage pour classer les patients en fonction de leur nombre total de jours d’hospitalisation.

Requête SQL #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: Cette fonction de fenêtrage attribue un rang unique à chaque patient en fonction de leur total courant de jours d’hospitalisation. La fonction attribue un rang à chaque ligne en fonction de l’ordre spécifié avec ORDER BY running_total_days DESC, ce qui signifie que les patients avec le total courant le plus élevé de jours d’hospitalisation recevront le rang le plus bas. La fonction ROW_NUMBER() est une fonction intégrée dans ClickHouse qui attribue un numéro unique à chaque ligne dans le cadre de fenêtre spécifié.
  2. (SELECT ...): La requête interne calcule le total cumulatif de jours d’hospitalisation pour chaque patient en utilisant la fonction de fenêtre SUM. Le résultat de la requête interne est ensuite utilisé comme entrée pour la requête externe, où la fonction de fenêtre ROW_NUMBER est appliquée pour classer les patients par leurs jours totaux d’hospitalisation.

Les résultats de la requête donnent le rang de chaque patient, l’âge, le sexe, l’état de santé et le total cumulatif de jours passés à l’hôpital.

Voici un exemple des données que cette requête pourrait générer:


Pour visualiser les résultats de la requête, vous pouvez utiliser divers outils de visualisation de données tels que Matplotlib, Seaborn, Plotly, etc. en Python. Voici un exemple de la façon dont vous pouvez visualiser les résultats de la requête à l’aide de Matplotlib:

Python

  

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

# Établir une connexion au base de données ClickHouse
engine = create_engine("clickhouse://:/")

# Exécuter la requête SQL et stocker les résultats dans un DataFrame Pandas
df = pd.read_sql_query("", engine)

# Tracer les résultats à l'aide d'un diagramme à barres
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()

Dans ce code, j’utilise d’abord la fonction create_engine de la bibliothèque SQLAlchemy pour établir une connexion à la base de données ClickHouse. La fonction read_sql_query est ensuite utilisée pour exécuter la requête SQL et stocker les résultats dans un DataFrame Pandas. Enfin, le diagramme à barres est créé à l’aide de la fonction bar de la bibliothèque Matplotlib, où l’axe des x représente l’identifiant du patient, l’axe des y représente le total cumulatif de jours d’hospitalisation, et la couleur de chaque barre représente le rang du patient.

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.

Conclusion

Enfin, avec ClickHouse, les fonctions de fenêtre sont un excellent outil pour l’analyse de données étendue et les procédures d’agrégation. Elles permettent aux développeurs de réaliser des calculs complexes dans une requête, tels que les totaux courants, le classement et les percentiles, qui nécessiteraient normalement plusieurs requêtes ou même un prétraitement des données. Les fonctions de fenêtre peuvent grandement faciliter l’analyse et l’agrégation des données en offrant un mécanisme concis et rapide pour effectuer ces calculs.

Cependant, gardez à l’esprit que les fonctions de fenêtre peuvent être coûteuses en termes de calcul, surtout avec de grandes quantités de données. Cela peut être réduit en utilisant des index appropriés et en construisant des requêtes avec sagesse. Cependant, il est toujours crucial de comprendre les conséquences de performance des fonctions de fenêtre et de les utiliser avec parcimonie.

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