動的なカラム選択は、強力でありながらあまり使われていない ClickHouse の機能で、各カラム名を個別に指定する代わりに、正規表現を使ってカラムを選択できます。さらに APPLY 修飾子を使うことで、一致したカラムに対して関数を適用できるため、データの分析や変換タスクに非常に有用です。
ここでは、この機能の使い方を New York taxis データセット を例に学びます。このデータセットは ClickHouse SQL playground でも参照できます。
パターンに一致する列の選択
NYC タクシーデータセットから _amount を含む列だけを選択したい、というよくあるシナリオから始めましょう。各列名を手作業で入力する代わりに、正規表現を指定した COLUMNS 式を使用できます。
FROM nyc_taxi.trips
SELECT COLUMNS('.*_amount')
LIMIT 10;
SQL playground でこのクエリを試す
このクエリは最初の 10 行を返しますが、列名がパターン .*_amount（任意の文字列の後に「_amount」が続くもの）に一致する列のみが対象です。
┌─fare_amount─┬─tip_amount─┬─tolls_amount─┬─total_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 Playground でこのクエリを試す
┌─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 │
└─────────────┴─────────┴────────────┴──────────────┴───────────┴──────────────┘
複数のパターンを選択する
1つのクエリで複数のカラムパターンを組み合わせることができます。
SELECT
COLUMNS('.*_amount'),
COLUMNS('.*_date.*')
FROM nyc_taxi.trips
LIMIT 5;
SQL Playground でこのクエリを試す
┌─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 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 │
└──────────────────┴────────────────────┴────────────────────┴────────────────────┴─────────────────────┴────────────────────┘
これらの値は小数点以下の桁数が多くなっていますが、関数をチェーンすることで簡単に整えることができます。この場合は、まず avg 関数を適用し、続けて round 関数を適用します。
SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(round)
FROM nyc_taxi.trips;
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 │
└─────────────────────────┴─────────────────────┴────────────────────────┴──────────────────────────┴───────────────────────┴──────────────────────────┘
しかしこれでは平均値が整数に丸められてしまいます。たとえば小数第2位までなど、任意の小数桁数に丸めたい場合にも対応できます。APPLY 修飾子は関数だけでなくラムダ式も受け付けるため、round 関数を使って平均値を小数第2位まで丸めるといった柔軟な指定が可能です。
SELECT COLUMNS('.*_amount|fee|tax') APPLY(avg) APPLY(x -> round(x, 2))
FROM nyc_taxi.trips;
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 │
└────────────────────────────┴────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴─────────────────────────────┘
列の置き換え
ここまでは順調です。しかし、他の値はそのままにしておきつつ、特定の値だけを調整したいとします。たとえば、合計金額を 2 倍にし、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 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 │
└────────────────────────────┴──────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴──────────────────────────┘
列の除外
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 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 │
└────────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┴──────────────────────────┘
