Динамический выбор столбцов

Динамический выбор столбцов — это мощная, но недооценённая возможность ClickHouse, которая позволяет выбирать столбцы с помощью регулярных выражений вместо того, чтобы перечислять каждый столбец отдельно. Вы также можете применять функции к совпадающим столбцам с помощью модификатора APPLY, что делает эту возможность особенно полезной для задач анализа и преобразования данных. Мы разберём, как использовать эту возможность, на примере датасета New York taxis, который также доступен в Песочнице ClickHouse.

Выбор столбцов, соответствующих шаблону регулярного выражения

Начнём с распространённого сценария: выберем только столбцы с _amount из набора данных NYC taxi. Вместо того чтобы вручную вводить имя каждого столбца, можно использовать выражение COLUMNS с регулярным выражением:

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

Попробуйте этот запрос в Песочнице ClickHouse

Этот запрос возвращает первые 10 строк и только те столбцы, чьи имена соответствуют шаблону регулярного выражения .*_amount (любые символы, за которыми следует “_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 │
    └─────────────┴────────────┴──────────────┴──────────────┘

Допустим, мы также хотим вернуть столбцы, содержащие fee или tax. Мы можем обновить регулярное выражение, чтобы добавить их:

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

Попробуйте выполнить этот запрос в Песочнице ClickHouse

   ┌─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 │
   └─────────────┴─────────┴────────────┴──────────────┴───────────┴──────────────┘

Выбор по нескольким шаблонам

Мы можем объединить несколько шаблонов столбцов в одном запросе:

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

Попробуйте этот запрос в Песочнице ClickHouse

   ┌─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 │
   └─────────────┴────────────┴──────────────┴──────────────┴─────────────┴─────────────────────┴──────────────┴─────────────────────┘

Применение функций ко всем столбцам

Мы также можем использовать модификатор APPLY, чтобы применять функции сразу ко всем столбцам. Например, если мы хотим найти максимальное значение для каждого из этих столбцов, можно выполнить следующий запрос:

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

Попробуйте выполнить этот запрос в Песочнице ClickHouse

   ┌─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 │
   └──────────────────┴──────────────┴─────────────────┴───────────────────┴────────────────┴───────────────────┘

Или, возможно, вместо этого мы хотим увидеть среднее значение:

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

Попробуйте выполнить этот запрос в Песочнице ClickHouse

   ┌─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 │
   └──────────────────┴────────────────────┴────────────────────┴────────────────────┴─────────────────────┴────────────────────┘

В этих значениях много знаков после запятой, но это легко исправить, объединив функции в цепочку. В этом случае мы сначала применим функцию avg, а затем — функцию round:

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

Выполните этот запрос в Песочнице ClickHouse

   ┌─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 │
   └─────────────────────────┴─────────────────────┴────────────────────────┴──────────────────────────┴───────────────────────┴──────────────────────────┘

Но при этом средние значения округляются до целых чисел. Если мы хотим округлять, например, до 2 знаков после запятой, это тоже можно сделать. Помимо функций, модификатор APPLY принимает лямбда-выражение, что позволяет функции round округлять средние значения до 2 знаков после запятой:

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

Попробуйте выполнить этот запрос в Песочнице ClickHouse

   ┌─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 │
   └────────────────────────────┴────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴─────────────────────────────┘

Замена столбцов

Пока всё хорошо. Но предположим, что мы хотим изменить одно из значений, оставив остальные как есть. Например, мы хотим удвоить общую сумму и разделить налог MTA на 1.1. Это можно сделать с помощью модификатора REPLACE, который заменяет один столбец, не затрагивая остальные.

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

Попробуйте выполнить этот запрос в Песочнице ClickHouse

   ┌─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 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴──────────────────────────┘

Исключение столбцов

Мы также можем исключить столбец с помощью модификатора EXCEPT. Например, чтобы убрать столбец tolls_amount, нужно написать следующий запрос:

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

Попробуйте этот запрос в Песочнице ClickHouse

   ┌─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 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┴──────────────────────────┘

​Выбор столбцов, соответствующих шаблону регулярного выражения

​Выбор по нескольким шаблонам

​Применение функций ко всем столбцам

​Замена столбцов

​Исключение столбцов

Выбор столбцов, соответствующих шаблону регулярного выражения

Выбор по нескольким шаблонам

Применение функций ко всем столбцам

Замена столбцов

Исключение столбцов