빅쿼리(Bigquery) - APPROX_QUANTILES

APPROX_QUANTILES는 BigQuery에서 분위수를 계산하는 함수로, 대량의 데이터를 효율적으로 처리하기 위해 근사치를 반환한다.

 

 

사용법

APPROX_QUANTILES(expression, number_of_quantiles)

 

• expression: 분위수를 계산할 숫자 열(예: 가격, 매출액 등)
• number_of_quantiles: 원하는 분위수 개수 (예: 5이면 0%, 25%, 50%, 75%, 100% 분위수 반환)

 

예시

 

 1. 매출의 분위수 계산

SELECT 
  APPROX_QUANTILES(sales, 5) AS quantiles
FROM sales_data;

 

• sales 열의 5개 분위수(0%, 25%, 50%, 75%, 100%)를 반환

 

2. 분위수별 매출 값 개별 출력

SELECT 
  quantiles[OFFSET(0)] AS min_value,        -- 최솟값 (0% 분위수)
  quantiles[OFFSET(1)] AS first_quartile,   -- Q1 (25% 분위수)
  quantiles[OFFSET(2)] AS median,           -- Q2 (50% 분위수, 중앙값)
  quantiles[OFFSET(3)] AS third_quartile,   -- Q3 (75% 분위수)
  quantiles[OFFSET(4)] AS max_value         -- 최댓값 (100% 분위수)
FROM (
  SELECT APPROX_QUANTILES(sales, 5) AS quantiles
  FROM sales_data
);

• OFFSET(n)을 사용해 특정 분위수 값을 가져올 수 있음

 

특징

• 근사값 반환 → 정확한 분위수가 아닌 대략적인 값이지만 속도가 빠름
• 대량 데이터 처리에 적합 → 메모리 사용을 줄이면서 분위수를 구할 수 있음
• 정확한 분위수가 필요하면 PERCENTILE_CONT 사용

BigQuery에서 APPROX_QUANTILES 함수는 분위수(quantiles)를 배열 형태로 반환한다.

예를 들어, APPROX_QUANTILES(sales, 5)를 실행하면 [최솟값, Q1, Q2(중앙값), Q3, 최댓값] 이런 식의 배열이 나온다.

각 값은 배열의 인덱스 로 접근할 수 있는데, BigQuery에서 OFFSET(index) 를 사용해서 가져와야 한다.
(파이썬처럼 quantiles[1] 이렇게 못 씀)

 

 

이번에 유저별 평균 체류시간을 구한 후, 전체 평균값과 중앙값을 계산할 때 처음으로 APPROX_QUANTILES 을 썼는데 최솟값, 최대값을 구할 때도 잘 써먹을 수 있을 것 같다.