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

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

Мы изучим, как использовать эту функцию, на примере набора данных по нью-йоркскому такси, который также доступен в ClickHouse SQL playground.

Выбор столбцов по шаблону

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

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

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

Этот запрос возвращает первые 10 строк, но только для столбцов, имена которых совпадают с шаблоном .*_amount (любые символы, после которых следует «_amount»).

    ┌─стоимость_проезда─┬─чаевые─┬─дорожные_сборы─┬─общая_сумма─┐
 1. │           9 │          0 │            0 │          9.8 │
 2. │           9 │          0 │            0 │          9.8 │
 3. │         3.5 │          0 │            0 │          4.8 │
 4. │         3.5 │          0 │            0 │          4.8 │
 5. │         3.5 │          0 │            0 │          4.3 │
 6. │         3.5 │          0 │            0 │          4.3 │
 7. │         2.5 │          0 │            0 │          3.8 │
 8. │         2.5 │          0 │            0 │          3.8 │
 9. │           5 │          0 │            0 │          5.8 │
10. │           5 │          0 │            0 │          5.8 │
    └─────────────┴────────────┴──────────────┴──────────────┘

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

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

Попробуйте выполнить этот запрос в 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 │
   └─────────────┴─────────┴────────────┴──────────────┴───────────┴──────────────┘

Выбор нескольких шаблонов

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

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

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

   ┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_amount─┬─pickup_date─┬─────pickup_datetime─┬─dropoff_date─┬────dropoff_datetime─┐
1. │           9 │          0 │            0 │          9.8 │  2001-01-01 │ 2001-01-01 00:01:48 │   2001-01-01 │ 2001-01-01 00:15:47 │
2. │           9 │          0 │            0 │          9.8 │  2001-01-01 │ 2001-01-01 00:01:48 │   2001-01-01 │ 2001-01-01 00:15:47 │
3. │         3.5 │          0 │            0 │          4.8 │  2001-01-01 │ 2001-01-01 00:02:08 │   2001-01-01 │ 2001-01-01 01:00:02 │
4. │         3.5 │          0 │            0 │          4.8 │  2001-01-01 │ 2001-01-01 00:02:08 │   2001-01-01 │ 2001-01-01 01:00:02 │
5. │         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;

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

   ┌─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

Выполнить этот запрос в SQL‑песочнице

   ┌─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;

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

   ┌─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 принимает лямбда-выражение, что дает нам гибкость и позволяет округлять средние значения до 2 знаков после запятой:

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

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

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

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

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

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

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