잘 외워지지 않는 유용한 쿼리 모음

대부분의 쿼리들은 많이 사용하기 때문에 외우고 있지만 잘 외워지지 않는 가끔 사용되는 쿼리들이 있습니다. 이러한 쿼리들을 정리해놓고 필요할 때 마다 복사해서 사용하려고 합니다. 

1. COUNTIF + DISTINCT

특정 조건에 해당하는 중복을 제거한 개수를 확인하고 싶은 경우가 있습니다. 예를 들면 넥스트 차량에 호출을 보낸 중복을 제거한 유저수 등이 있습니다. COUNT + DISTINCT 를 사용하고 where 조건에 값을 넣을 수도 있지만, 아래와 같은 쿼리를 통해 원하는 결과 값을 더 쉽게 조회할 수 있습니다.

COUNT(DISTINCT IF(type='NXT', rider_id, NULL))

2. 5단위로 반올림 하고 싶다.

5단위로 반올림을 하고 싶은 경우가 있다. 예를들면 102는 105로 117은 120으로 말이죠.

ROUND(value * 2, -1) / 2

3. 10분 단위로 시간을 binning 하고 싶다.

시간대별로 수치를 집계하고 싶을 때 시간 단위를 분단위로 Binning 하고 싶을 때가 있습니다. 예를 들면 2021-01-01T00:31:60 을 10분 단위로 Binning 해서 2021-01-01T00:30:00 으로 바꾸고 싶다면, 아래의 쿼리에서 time_unit 대신 10을 사용하면 됩니다. 만약 10분이 아니라 30분 단위로 Binning 하고 싶다면 time_unit 대신 30을 사용하면 됩니다. 

DATETIME_ADD(DATETIME_TRUNC(ts, HOUR), INTERVAL CAST(FLOOR(EXTRACT(MINUTE FROM ts) / time_unit) * time_unit AS INT64) MINUTE)

4. 30분 Rolling 으로 지표를 구하고 싶다.

10분 단위로 데이터를 집계했다고 가정해봅시다. 시간의 기준은 10분 단위인데 30분 Rolling 으로 지표를 집계하고 싶은 경우가 있습니다. 예를들어서 time_interval_kr 이 12:10 인 경우 12:00 ~ 12:30 의 데이터를 알고 싶은 경우이지요. 보통 Rolling 을 통해 집계된 데이터를 Smoothing 하고 싶을 때 주로 사용합니다. 

SUM(value) OVER (ORDER BY time_interval_kr ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

5. 지난 30일 동안의 평균 값으로 집계하고 싶다.

지난 30일 동안의 평균 값으로 데이터를 집계하고 싶은 경우도 존재합니다. 예를들어서 지난 30일 동안 하루 평균 얼마나 돈을 벌었는지 알고 싶을 수 있습니다. 아래의 쿼리를 사용하시면 됩니다. 

# 현재 날짜를 포함해서 30일
AVG(receipt) OVER (ORDER BY time_interval_kr ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)

6. 백분위수 (percentile) 를 집계하고 싶다.

백분위수(Percentile) : 크기가 있는 값들로 이뤄진 자료를 순서대로 나열햇을 때 백분율로 나타낸 특정 위치의 값

APPROX_QUANTILES(x, 100)

 

7. 전체 테이블에서 일부에만 쿼리해서 대략적인 경향을 보고싶을때

원하는 쿼리에 TABLESAMPLE SYSTEM ( n PERCENT ) 만 붙여주시면 테이블의 데이터를 알아서 샘플링해서 n% 만 스캔합니다. 비용도 n%만 청구합니다. 참고로 limit 을 사용하는 것은 빅쿼리의 리소스를 모두 사용하는 방식입니다. 

SELECT *
FROM {데이터셋.테이블명} TABLESAMPLE SYSTEM (5 PERCENT)

8. null 인 값은 제외하고 LAG 함수를 사용하고 싶을 때

빅쿼리에서 LAG 함수에는 IGNORE NULLS 를 쓸 수 없다. 따라서 아래의 방식으로 우회해야 된다. 다른 좋은 방법이 있을 것 같긴한데 여러 삽질 끝에 알아낸 방법중 하나는 아래 쿼리이다. 

MAX(col) OVER (PARTITION BY col ORDER BY col ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

9. 특정 ID 값 기준으로 가장 마지막 발생한 이벤트 날짜를 집계하고 싶을 때

빅쿼리에서 LAST_VALUE 함수는 생각했던 것 처럼 작동하지 않습니다.

SELECT DISTINCT col,
       LAST_VALUE(date_kr) OVER (PARTITION BY col ORDER BY date_kr ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_date_kr
FROM {테이블명}

10. int64 형식으로된 데이터를 타임스템프로 변환하고 싶을 때 

파이어베이스 앱 로그를 보면 이벤트가 발생한 시간이 int64 형태로 로깅된 경우를 많이 볼 수 있다. 이와 같은 형식은 분석에 용이하지 않으니 TIMESTAMP 나 DATETIME 형식으로 바꾸는 것이 적합하다. TIMESTAMP_MICROS 함수를 통해서 쉽게 변환할 수 있다. 

-- int64 to timestamp
SELECT TIMESTAMP_MICROS(event_timestamp) AS event_timestamp

-- int64 to datetime
SELECT DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime