Sélection dynamique de colonnes - ClickHouse Documentation

La sélection dynamique de colonnes est une fonctionnalité puissante mais encore peu utilisée de ClickHouse, qui vous permet de sélectionner des colonnes à l’aide d’expressions régulières plutôt que de nommer chaque colonne individuellement. Vous pouvez également appliquer des fonctions aux colonnes correspondantes à l’aide du modificateur APPLY, ce qui en fait une fonctionnalité particulièrement utile pour les tâches d’analyse et de transformation des données. Nous allons voir comment utiliser cette fonctionnalité à l’aide du jeu de données des taxis de New York, que vous pouvez également retrouver dans le ClickHouse SQL playground.

Sélectionner les colonnes correspondant à un motif

Commençons par un cas courant : sélectionner uniquement les colonnes contenant _amount dans le jeu de données des taxis de New York. Au lieu de saisir manuellement chaque nom de colonne, nous pouvons utiliser l’expression COLUMNS avec une expression régulière :

FROM nyc_taxi.trips
SELECT COLUMNS('.*_amount')
LIMIT 10;

Essayez cette requête dans le SQL playground

Cette requête renvoie les 10 premières lignes, mais uniquement pour les colonnes dont le nom correspond au motif .*_amount (n’importe quelle suite de caractères suivie de “_amount”).

    ┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┐
│           9 │          0 │            0 │          9.8 │
│           9 │          0 │            0 │          9.8 │
│         3.5 │          0 │            0 │          4.8 │
│         3.5 │          0 │            0 │          4.8 │
│         3.5 │          0 │            0 │          4.3 │
│         3.5 │          0 │            0 │          4.3 │
│         2.5 │          0 │            0 │          3.8 │
│         2.5 │          0 │            0 │          3.8 │
│           5 │          0 │            0 │          5.8 │
│           5 │          0 │            0 │          5.8 │
    └─────────────┴────────────┴──────────────┴──────────────┘

Supposons que nous voulions également renvoyer les colonnes qui contiennent les termes fee ou tax. Nous pouvons modifier l’expression régulière pour les inclure :

SELECT COLUMNS('.*_amount|fee|tax')
FROM nyc_taxi.trips
ORDER BY rand() 
LIMIT 3;

Essayez cette query dans le Playground SQL

   ┌─fare_amount─┬─mta_tax─┬─tip_amount─┬─tolls_amount─┬─ehail_fee─┬─total_amount─┐
1. │           5 │     0.5 │          1 │            0 │         0 │          7.8 │
2. │        12.5 │     0.5 │          0 │            0 │         0 │         13.8 │
3. │         4.5 │     0.5 │       1.66 │            0 │         0 │         9.96 │
   └─────────────┴─────────┴────────────┴──────────────┴───────────┴──────────────┘

Sélection de plusieurs motifs

Nous pouvons combiner plusieurs motifs de colonnes dans une même requête :

SELECT 
    COLUMNS('.*_amount'),
    COLUMNS('.*_date.*')
FROM nyc_taxi.trips
LIMIT 5;

Essayez cette requête dans le SQL playground

   ┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┬─pickup_date─┬─────pickup_datetime─┬─dropoff_date─┬────dropoff_datetime─┐
│           9 │          0 │            0 │          9.8 │  2001-01-01 │ 2001-01-01 00:01:48 │   2001-01-01 │ 2001-01-01 00:15:47 │
│           9 │          0 │            0 │          9.8 │  2001-01-01 │ 2001-01-01 00:01:48 │   2001-01-01 │ 2001-01-01 00:15:47 │
│         3.5 │          0 │            0 │          4.8 │  2001-01-01 │ 2001-01-01 00:02:08 │   2001-01-01 │ 2001-01-01 01:00:02 │
│         3.5 │          0 │            0 │          4.8 │  2001-01-01 │ 2001-01-01 00:02:08 │   2001-01-01 │ 2001-01-01 01:00:02 │
│         3.5 │          0 │            0 │          4.3 │  2001-01-01 │ 2001-01-01 00:02:26 │   2001-01-01 │ 2001-01-01 00:04:49 │
   └─────────────┴────────────┴──────────────┴──────────────┴─────────────┴─────────────────────┴──────────────┴─────────────────────┘

Appliquer des fonctions à toutes les colonnes

Nous pouvons également utiliser le modificateur APPLY pour appliquer des fonctions à l’ensemble des colonnes. Par exemple, si nous voulions trouver la valeur maximale de chacune de ces colonnes, nous pourrions exécuter la requête suivante :

SELECT COLUMNS('.*_amount|fee|tax') APPLY(max)
FROM nyc_taxi.trips;

Essayez cette requête dans le SQL playground

   ┌─max(fare_amount)─┬─max(mta_tax)─┬─max(tip_amount)─┬─max(tolls_amount)─┬─max(ehail_fee)─┬─max(total_amount)─┐
1. │           998310 │     500000.5 │       3950588.8 │           7999.92 │           1.95 │         3950611.5 │
   └──────────────────┴──────────────┴─────────────────┴───────────────────┴────────────────┴───────────────────┘

Ou peut-être voulons-nous plutôt voir la moyenne :

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg)
FROM nyc_taxi.trips

Essayez cette requête dans le SQL playground

   ┌─avg(fare_amount)─┬───────avg(mta_tax)─┬────avg(tip_amount)─┬──avg(tolls_amount)─┬──────avg(ehail_fee)─┬──avg(total_amount)─┐
1. │ 11.8044154834777 │ 0.4555942672733423 │ 1.3469850969211845 │ 0.2256511991414463 │ 3.37600560437412e-9 │ 14.423323722271563 │
   └──────────────────┴────────────────────┴────────────────────┴────────────────────┴─────────────────────┴────────────────────┘

Ces valeurs comportent beaucoup de chiffres après la virgule, mais heureusement, nous pouvons corriger cela en chaînant des fonctions. Ici, nous allons appliquer la fonction avg, suivie de la fonction round :

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(round)
FROM nyc_taxi.trips;

Essayez cette requête dans le SQL playground

   ┌─round(avg(fare_amount))─┬─round(avg(mta_tax))─┬─round(avg(tip_amount))─┬─round(avg(tolls_amount))─┬─round(avg(ehail_fee))─┬─round(avg(total_amount))─┐
1. │                      12 │                   0 │                      1 │                        0 │                     0 │                       14 │
   └─────────────────────────┴─────────────────────┴────────────────────────┴──────────────────────────┴───────────────────────┴──────────────────────────┘

Mais cela arrondit les moyennes à des nombres entiers. Si nous voulons les arrondir, par exemple, à 2 décimales, nous pouvons aussi le faire. En plus d’accepter des fonctions, le modificateur APPLY accepte une expression lambda, ce qui nous permet de faire en sorte que la fonction d’arrondi arrondisse nos valeurs moyennes à 2 décimales :

SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(x -> round(x, 2))
FROM nyc_taxi.trips;

Essayez cette requête dans le SQL playground

   ┌─round(avg(fare_amount), 2)─┬─round(avg(mta_tax), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(tolls_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(total_amount), 2)─┐
1. │                       11.8 │                   0.46 │                      1.35 │                        0.23 │                        0 │                       14.42 │
   └────────────────────────────┴────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴─────────────────────────────┘

Remplacer des colonnes

Jusqu’ici, tout va bien. Mais supposons que nous voulions modifier l’une des valeurs, tout en laissant les autres inchangées. Par exemple, nous pouvons vouloir doubler le montant total et diviser la taxe MTA par 1,1. Pour cela, utilisez le modificateur REPLACE, qui remplace une colonne tout en laissant les autres inchangées.

FROM nyc_taxi.trips 
SELECT 
  COLUMNS('.*_amount|fee|tax')
  REPLACE(
    total_amount*2 AS total_amount,
    mta_tax/1.1 AS mta_tax
  ) 
  APPLY(avg)
  APPLY(col -> round(col, 2));

Testez cette requête dans le SQL playground

   ┌─round(avg(fare_amount), 2)─┬─round(avg(di⋯, 1.1)), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(tolls_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(mu⋯nt, 2)), 2)─┐
1. │                       11.8 │                     0.41 │                      1.35 │                        0.23 │                        0 │                    28.85 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴──────────────────────────┘

Exclusion de colonnes

On peut également choisir d’exclure un champ à l’aide du modificateur EXCEPT. Par exemple, pour supprimer la colonne tolls_amount, on écrirait la requête suivante :

FROM nyc_taxi.trips 
SELECT 
  COLUMNS('.*_amount|fee|tax') EXCEPT(tolls_amount)
  REPLACE(
    total_amount*2 AS total_amount,
    mta_tax/1.1 AS mta_tax
  ) 
  APPLY(avg)
  APPLY(col -> round(col, 2));

Essayez cette requête dans le SQL playground

   ┌─round(avg(fare_amount), 2)─┬─round(avg(di⋯, 1.1)), 2)─┬─round(avg(tip_amount), 2)─┬─round(avg(ehail_fee), 2)─┬─round(avg(mu⋯nt, 2)), 2)─┐
1. │                       11.8 │                     0.41 │                      1.35 │                        0 │                    28.85 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┴──────────────────────────┘

​Sélectionner les colonnes correspondant à un motif

​Sélection de plusieurs motifs

​Appliquer des fonctions à toutes les colonnes

​Remplacer des colonnes

​Exclusion de colonnes

Sélectionner les colonnes correspondant à un motif

Sélection de plusieurs motifs

Appliquer des fonctions à toutes les colonnes

Remplacer des colonnes

Exclusion de colonnes