동적 컬럼 선택 - ClickHouse Documentation

동적 컬럼 선택은 각 컬럼을 하나씩 지정하지 않고 정규식을 사용해 컬럼을 선택할 수 있는 강력하지만 잘 활용되지 않는 ClickHouse 기능입니다. 또한 APPLY 수정자를 사용하면 일치하는 컬럼에 함수를 적용할 수 있어 데이터 분석 및 변환 작업에 매우 유용합니다. 이 기능의 사용 방법은 뉴욕 택시 데이터셋을 통해 알아보겠습니다. 이 데이터셋은 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─┐
│           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 │
   └─────────────┴─────────┴────────────┴──────────────┴───────────┴──────────────┘

여러 패턴 선택

하나의 쿼리에서 여러 컬럼 패턴을 조합할 수 있습니다:

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 수정자는 함수를 받을 뿐만 아니라 람다도 허용하므로, 이를 통해 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 tax는 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 │
   └────────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┴──────────────────────────┘

​패턴과 일치하는 컬럼 선택하기

​여러 패턴 선택

​모든 컬럼에 함수 적용

​컬럼 대체하기

​컬럼 제외하기

패턴과 일치하는 컬럼 선택하기

여러 패턴 선택

모든 컬럼에 함수 적용

컬럼 대체하기

컬럼 제외하기