동적 컬럼 선택 은 각 컬럼 이름을 개별적으로 지정하지 않고 정규 표현식을 사용해 컬럼을 선택할 수 있게 해 주는, 강력하지만 아직 충분히 활용되지 않는 ClickHouse 기능입니다. 또한 APPLY 수정자를 사용해 일치하는 컬럼에 함수를 적용할 수도 있어 데이터 분석과 변환 작업에 매우 유용합니다.
New York taxis 데이터셋 을 활용하여 이 기능을 사용하는 방법을 살펴보겠습니다. 이 데이터셋은 ClickHouse SQL playground 에서도 확인할 수 있습니다.
VIDEO
특정 패턴과 일치하는 컬럼 선택
우선 흔히 있는 경우부터 살펴보겠습니다. 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 │
└─────────────┴─────────┴────────────┴──────────────┴───────────┴──────────────┘
여러 패턴 선택하기
단일 쿼리에서 여러 컬럼 패턴을 함께 사용할 수 있습니다:
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 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 수정자는 FUNCTION뿐만 아니라 람다도 받을 수 있으므로, round FUNCTION을 사용해 평균값을 소수점 이하 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 │
└────────────────────────────┴────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴─────────────────────────────┘
컬럼 대체하기
지금까지는 문제없습니다. 하지만 일부 값만 조정하고 나머지 값들은 그대로 두고 싶을 수 있습니다. 예를 들어, 총액을 두 배로 늘리고 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 │
└────────────────────────────┴──────────────────────────┴───────────────────────────┴─────────────────────────────┴──────────────────────────┴──────────────────────────┘
컬럼 제외
EXCEPTtolls_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 │
└────────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┴──────────────────────────┘
