[빅쿼리(BigQuery) - SQL] 그룹의 특징 잡기(집약 함수, GROUP BY 구문, 윈도 함수, OVER 구문)

그룹의 특징 잡기 (집약 함수, GROUP BY 구문, 윈도 함수, OVER 구문)

 

 

집약 함수를 사용하는 방법을 알아본다. 집약 함수란 여러 레코드를 기반으로 하나의 값을 리턴하는 함수이다. 예를 들어 모든 레코드의 수를 리턴해주는 COUNT 함수, 값의 합계를 리턴해주는 SUM 함수 등이 있다.

 

다음 데이터 예는 상품 평가 테이블이다. 상품(product_id)에 대한 사용자 평가(score)가 저장된다. 여기서는 이 테이블을 사용해서 SUM 함수와 AVG 함수 등의 집약 함수를 사용하는 방법에 대해 알아본다.

 

 

 

 

 

테이블 전체의 특징량 계산하기

다음 코드 예는 자주 사용되는 집약 함수를 위 테이블에 적용하는 쿼리이다. COUNT 함수는 지정한 컬럼의 레코드를 리턴하는 함수이다. 컬럼 이름 앞에 DISTINCT 구문을 지정하면, 중복을 제외하고 수를 세어준다. 추가로 SUM 함수는 합계, AVG 함수는 평균을 구하는 함수이다. 따라서 SUM 함수와 AVG 함수는 컬럼의 자료형이 정수 또는 실수 등의 숫자 자료형이어야 한다.

 

MAX함수와 MIN 함수는 각각 최댓값과 최솟값을 구하는 함수이다. 따라서 대소 비교가 가능한 자료형 (숫자, 문자열, 타임스탬프 등)에 적용할 수 있다.

 

 

 

 

 

# 집계 함수의 자세한 설명은 https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions?hl=ko 에서 찾아볼 수 있다.

 

 

 

그루핑한 데이터의 특징량 계산하기

데이터를 조금 더 작게 분할하고 싶다면 GROUP BY 구문을 사용해 데이터를 분류할 키를 지정하고, 그러한 키를 기반으로 데이터를 집약한다. 다음 코드 예는 GROUP BY 구문을 사용해서 3명의 user_id를 기반으로 그룹으로 묶고, 각각의 데이터 집합에 집약 함수를 적용한다.

 

 

 

 

 

이때 한 가지 주의할 것이 있다. GROUP BY 구문을 사용한 쿼리에서는, GROUP BY 구문에 지정한 컬럼 또는 집약 함수만 SELECT 구문의 컬럼으로 지정할 수 있다. 예를 들어 앞의 코드에서 SELECT 구문 내부에는 product_id 또는 스코어를 지정할 수 없다.

 

GROUP BY 구문을 사용한 쿼리에서는 GROUP BY 구문에 지정한 컬럼을 유니크 키로 새로운 테이블을 만들게 된다. 이 과정에서 GROUP BY 구문에 지정하지 않은 컬럼은 사라져 버린다. 따라서 집약 함수를 적용한 값과 집약 전의 값은 동시에 사용할 수 없는 것이다.

 

 

 

 

 

집약 함수로 윈도 함수를 사용하려면, 집약 함수 뒤에 OVER 구문을 붙이고 여기에 윈도 함수를 지정한다. OVER 구문에 매개 변수를 지정하지 않으면 테이블 전체에 집약 함수를 적용한 값이 리턴된다. 매개 변수에 PARTITION BY <컬럼이름>을 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약 함수를 적용한다.

 

앞의 코드 예를 보면 테이블 전체의 score 평균값과 user_id들의 score 평균값이 테이블의 원래 레코드를 건드리지 않고 추가된 것을 알 수 있다. 참고로 집약 함수의 결과와 원래 값을 조합해서 계산하므로 원래 score와 user_id들의 score 평균값 차이도 계산할 수 있다.

 

 

# GROUP BY 구문에 대한 자세한 설명은 https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ko#group_by_clause 에서 찾아볼 수 있다.

 

# 윈도 함수의 자세한 설명은 https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls?hl=ko 에서 찾아볼 수 있다.