動的カラム選択 - ClickHouse Documentation

動的カラム選択は、各カラムを個別に指定する代わりに正規表現を使ってカラムを選択できる、強力でありながらあまり活用されていない ClickHouse の機能です。さらに、APPLY 修飾子を使って一致したカラムに関数を適用することもできるため、データ分析や変換作業で非常に役立ちます。この機能の使い方を、ニューヨークのタクシーデータセットを使って学んでいきます。このデータセットは ClickHouse SQL playground でも確認できます。

パターンに一致するカラムを選択する

まずはよくある例から始めましょう。NYC taxi データセットから、_amount を含むカラムだけを選択します。各カラム名を手入力する代わりに、正規表現を使った COLUMNS 式を使用できます。

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

このクエリを SQL Playground で試す

このクエリは最初の 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;

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─┐
│           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;

このクエリを 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 │
   └──────────────────┴──────────────┴─────────────────┴───────────────────┴────────────────┴───────────────────┘

あるいは、代わりに平均を見てみましょう：

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 修飾子は関数だけでなくラムダも受け取れるため、平均値を小数点以下2桁に丸めるように round 関数を柔軟に適用できます:

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

カラムの置き換え

ここまでは順調です。では、ほかの値はそのままにして、そのうちの1つだけを調整したいとします。たとえば、合計金額を2倍にし、MTA tax を1.1で割りたい場合です。これには REPLACE 修飾子を使用します。これにより、ほかのカラムはそのままにして、1つのカラムだけを置き換えられます。

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

​パターンに一致するカラムを選択する

​複数のパターンの選択

​すべてのカラムに関数を適用する

​カラムの置き換え

​カラムの除外

パターンに一致するカラムを選択する

複数のパターンの選択

すべてのカラムに関数を適用する

カラムの置き換え

カラムの除外