ClickHouse : Fonctions Windows à partir de zéro

ClickHouse est un système de gestion de base de données relationnelle hautement évolutif, orienté colonnes, optimisé pour les charges de travail analytiques. Il s’agit d’un produit open source développé par Yandex, une société de moteur de recherche. L’une des principales caractéristiques de ClickHouse est sa prise en charge des fonctions analytiques avancées, y compris les fonctions Windows.

Les fonctions Windows ont été introduites pour la première fois à la fin des années 1990 par SQL Server, et depuis lors, elles sont devenues une fonctionnalité standard dans de nombreuses bases de données relationnelles, y compris ClickHouse. Aujourd’hui, les fonctions Windows sont un outil indispensable pour les analystes de données et les développeurs et sont largement utilisées dans de nombreux secteurs.

Ces fonctions, également appelées fonctions analytiques, sont une classe de fonctions qui effectuent des calculs basés sur une fenêtre glissante de lignes. Ils sont utilisés pour effectuer divers types d’analyses sur des ensembles de données, tels que le calcul de totaux cumulés, de moyennes mobiles et de classements. Les fonctions Windows 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 une large gamme de fonctions Windows, y compris des fonctions intégrées pour rang, percent_rank, distribution cumulative, numérotation des ligneset totaux cumulés. En outre, il prend également en charge les fonctions Windows définies par l’utilisateur, qui permettent aux utilisateurs de créer des fonctions personnalisées pour des cas d’utilisation spécifiques.

Dans cet article, je présenterai le concept de fonctions Windows et fournirai un aperçu complet des fonctions Windows disponibles dans ClickHouse. Je fournirai également des exemples d’utilisation de ces fonctions dans des scénarios réels. Cet article est destiné aux développeurs expérimentés qui connaissent déjà SQL et qui souhaitent en savoir plus sur les fonctions Windows de ClickHouse.

Exemples concrets d’utilisation des fonctions Windows

Les fonctions Windows 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’analyse financière a été l’une des premières applications des fonctions Windows. Dans l’analyse boursière, les développeurs peuvent utiliser les fonctions Windows pour calculer les moyennes mobiles, les totaux cumulés et les variations en pourcentage. Par exemple, le calcul d’une moyenne mobile sur 50 jours du cours de clôture d’une action est un cas d’utilisation courant pour les fonctions Windows en finance. Un autre exemple consiste à calculer le total cumulé des bénéfices d’une entreprise sur une période donnée.

Analyse du commerce électronique

Dans le commerce électronique, les fonctions Windows peuvent analyser le comportement des clients et les modèles de vente. Les développeurs peuvent utiliser les fonctions Windows pour calculer le total cumulé des ventes de 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 Windows peuvent être utilisées pour analyser le comportement des clients en calculant la fréquence d’achat moyenne et la valeur d’achat moyenne d’un client sur une période donnée.

Analytique des soins de 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 de laboratoire et l’utilisation des médicaments. Par exemple, les développeurs peuvent utiliser les fonctions Windows pour calculer la moyenne mobile de la fréquence cardiaque d’un patient, le total cumulé 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 les fonctions Windows. L’essentiel est que les fonctions Windows peuvent être utilisées pour effectuer une analyse avancée sur un large éventail d’ensembles 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 :

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

Décomposons chaque partie de la syntaxe :

[column_list]: Il s’agit de la liste des colonnes que vous souhaitez renvoyer dans la requête.
[windows_function_name]([argument_list]): Il s’agit du nom de la fonction Windows que vous souhaitez utiliser et de la liste des arguments pour cette fonction.
AS [alias_name]: Cette clause est facultative et est utilisée pour donner un nom d’alias à la sortie de la fonction windows.
OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]]): Il s’agit de 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 des colonnes spécifiées.
ORDER BY [order_column_list]: Cette clause est nécessaire pour spécifier l’ordre dans lequel la fonction windows 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 windows doit opérer. Le 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 Windows dans ClickHouse :

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

Je utilise l SUM Fonction Windows pour calculer le total cumulé des ventes pour chaque produit, regroupées par le product_id colonne. Le cadre de la fenêtre est spécifié avec PARTITION BY product_id pour diviser le jeu de résultats en partitions en fonction de la id_produit et ORDER BY date pour spécifier l’ordre dans lequel la fonction windows traite les lignes. La sortie de la fonction windows reçoit un nom d’alias running_total.

Il est important de noter que les fonctions Windows 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 Windows 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 les fonctions Windows

Dans le secteur financier, le suivi de la performance des investissements dans le temps est essentiel pour la prise de décision. Les fonctions Windows de ClickHouse peuvent effectuer une analyse sophistiquée des données financières, telles que le calcul des moyennes mobiles et des totaux cumulés.

Considérons un scénario où nous avons un tableau des cours quotidiens des actions pour un seul titre. Notre objectif est de calculer la moyenne mobile sur 50 jours du cours de clôture et le total cumulé du retour sur investissement quotidien.

Génération de données :

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;

je crée un tableau stock_prices pour stocker les cours boursiers quotidiens pour le symbole AAAA. J’insère ensuite des données générées aléatoirement dans le tableau pour les années 2022-2023.

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

J’utilise les fonctions Windows pour effectuer une analyse financière sur les données du cours des actions.

AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW): Cette fonction windows calcule la moyenne mobile sur 50 jours du cours 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), ordonnées par date . Le cadre de la fenêtre est spécifié avec ORDER BY date pour spécifier l’ordre dans lequel la fonction windows traite les lignes et ROWS BETWEEN 49 PRECEDING AND CURRENT ROW pour spécifier la plage de lignes sur laquelle la fonction Windows doit opérer.
SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100: Cette fonction Windows calcule le total cumulé du retour sur investissement quotidien 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. Le lag La fonction 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 renvoie la date, le symbole, le cours de clôture, la moyenne mobile sur 50 jours et le total cumulé du retour sur investissement quotidien pour le symbole. AAAA.

Avec les fonctions Windows de 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.

Analyse du commerce électronique avec les fonctions Windows

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 judicieuses. Les fonctions Windows de ClickHouse peuvent effectuer des analyses complexes de données de commerce électronique, telles que le calcul des totaux cumulés et le classement des produits en fonction des ventes.

Imaginons que nous disposions d’un tableau d’informations sur les ventes quotidiennes pour un seul site de commerce électronique. Pour classer les produits en fonction des ventes totales, nous calculerons le total cumulé des ventes.