Clickhouse - des fonctions de fenêtre qui n'existent pas ...

J'ai commencé à travailler avec des bases de données en colonnes avec BigQuery. Quand j'ai dû "déménager" chez Clickhouse, j'ai été désagréablement surpris par le manque de fonctions de fenêtre à part entière. Il existe, bien sûr, de nombreuses fonctions pour travailler avec des tableaux, des fonctions d'ordre supérieur et d'autres fonctions (une fonction exécutantDifferenceStartingWithFirstValue en vaut la peine). Le gagnant de 1999 pour le titre du mot le plus long Donaudampfschifffahrtsgesellschaftskapitänswitwe vient immédiatement à l'esprit. Ce qui signifie en traduction de l'allemand "la veuve du capitaine de la compagnie maritime sur le Danube".



La recherche de «fonctions de fenêtre dans Clickhouse» ne renvoie pas de résultats significatifs. Cet article est une tentative de résumer des données dispersées sur Internet, des exemples avec ClickHouseMeetup et ma propre expérience.

Fonctions de fenêtre - syntaxe

Permettez-moi de vous rappeler la syntaxe des fonctions de fenêtre et le type de résultat que nous obtenons. Dans les exemples, nous utiliserons le dialecte Standart SQL Google BigQuery. Voici un lien vers la documentation sur les fonctions de fenêtre (elles sont appelées fonction analytique dans la documentation - des sons de traduction plus précis comme des fonctions analytiques). Et voici la liste des fonctions elle-même.



La syntaxe générale ressemble à ceci:

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 }

Prenons les choses étape par étape:

1. La fonction window est appliquée au jeu d'enregistrements défini dans l'expression over_clause,
2. Le jeu d'enregistrements est défini par la clause PARTITION BY. Ici, vous pouvez lister un ou plusieurs champs par lesquels le jeu d'enregistrements sera déterminé. Fonctionne de la même manière que GROUP BY.
   
   Le tri des enregistrements dans un ensemble est spécifié à l'aide de ORDER BY.
3. Vous pouvez en outre restreindre un ensemble prédéfini d'enregistrements en tant que fenêtre. La fenêtre peut être définie de manière statique. Par exemple, vous pouvez prendre 5 enregistrements comme fenêtre, 2 avant et 2 après l'enregistrement actuel et l'enregistrement actuel lui-même. Cela ressemblera à ceci: RANGÉES ENTRE 2 PRÉCÉDENT ET 2 SUIVANTES.
   
   Un exemple de construction pour spécifier une fenêtre définie dynamiquement ressemble à ceci - RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Cette construction définit une fenêtre du premier à l'enregistrement en cours selon l'ordre de tri spécifié.

A titre d'exemple, considérons le calcul de la somme cumulée (exemple de la documentation):

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

Résultat:

+-------------------------------------------------------+
| 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              |
+-------------------------------------------------------+

Que peut-on faire dans Clickhouse

Essayons de répéter cet exemple dans ClickHouse. Bien sûr, ClickHouse a les fonctions runningAccumulate , arrayCumSum et groupArrayMovingSum . Mais dans le premier cas, vous devez déterminer l'état dans une sous-requête ( plus de détails ), et dans le second cas, la fonction renvoie un tableau, qui doit ensuite être développé.



Nous construirons la requête la plus générale. La demande elle-même pourrait ressembler à ceci:

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, purchases

Prenons les choses étape par étape:

1. Tout d'abord, nous construisons une sous-requête, dans laquelle le tri des données requis a lieu (catégorie ORDER BY, achats). Le tri doit correspondre aux champs des expressions PARTITION BY et ORDER BY de la fonction de fenêtre.
2. , , PARTITION BY. item .
   
   purchases , summ .
3. — 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:
   
   
   
   
   • — [edited] — . [/edited]. , , arrayMap arrayFilter. . — ( — ) (alias) arrayMap, arrayFilter .
   • — . , , arrayReverse arraySlice.
   
   
   
4. La dernière étape consiste à développer les tableaux dans une table à l'aide de ARRAY JOIN. Nous devons également appliquer la fonction d'agrégation de somme avec le modificateur -Array (en conséquence, la fonction d'agrégation ressemble à sumArray) au résultat renvoyé par la fonction ArrayMap.

Production

Il est possible d'émuler le fonctionnement des fonctions de fenêtre dans ClickHouse. Pas très rapide et pas très joli. En bref, le pipeline se compose de 3 étapes:

1. Requête triée. Cette étape prépare le jeu d'enregistrements.
2. Regroupement en baies et exécution d'opérations sur les baies. Cette étape définit la fenêtre de notre fonction de fenêtre.
3. Expansion dans une table à l'aide de fonctions d'agrégation.