Bigquery: GROUP BY ALL, 쿼리 자동 그룹화 시키기

 

 

 

02월 26일자로 빅쿼리에 GROUP BY ALL 문법이 추가됐다. 기존에는 쿼리를 묶고 싶으면 GROUP BY에 컬럼을 지정해줘야 했는데, GROUP BY ALL 문법의 추가로 이제 빅쿼리가 SELECT 절에서 그룹화할 키를 알아서 묶게 할 수 있게 됐다.

 

 

묶는 조건은 단순한데, 집계 함수나 윈도우 함수 외 몇 가지 공식 문서에서 지정한 불가 형태 제외하고 나머지 컬럼은 다 그GROUP BY에 포함시키는 식이다. 이 때 상기 조건들을 제외하고 난 후, 남은 SELECT 항목 중 그룹화 불가한 유형이 있는 경우 오류가 발생한다. 

 

 

다음 예제에서 쿼리는 first_name과 last_name 기준으로 그룹화된다. total_points는 집계 함수 값이므로 제외됐다.

 

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
  
SELECT SUM(PointsScored) AS total_points
  		, FirstName AS first_name
  		, LastName AS last_name
FROM PlayerStats
GROUP BY ALL;

 

/*--------------+------------+-----------+
 | total_points | first_name | last_name |
 +--------------+------------+-----------+
 | 7            | Noam       | Adams     |
 | 13           | Jie        | Buchanan  |
 | 1            | Kiran      | Coolidge  |
 +--------------+------------+-----------*/

 

 

다음 예제 역시 쿼리는 first_name과 last_name 기준으로 그룹화된다. total_points는 윈도우 함수 값이므로 제외됐다.

 

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
  
SELECT COUNT(*) OVER () AS total_people
  		, FirstName AS first_name
  		, LastName AS last_name
FROM PlayerStats
GROUP BY ALL;

 

/*--------------+------------+-----------+
 | total_people | first_name | last_name |
 +--------------+------------+-----------+
 | 3            | Noam       | Adams     |
 | 3            | Jie        | Buchanan  |
 | 3            | Kiran      | Coolidge  |
 +--------------+------------+-----------*/

 

 

여러 SELECT 항목이 FROM 절의 동일한 부분을 참고한다면 경로를 가장 직접적으로 지정한 하나만 GROUP BY의 대상이 된다. 아래 예제에서 coordinates의 두 구성 요소인 Values.x와 Values.y가 이미 직접 경로로 지정돼 있는 상태이므로 coordinates 컬럼은 GROUP BY에서 제외됐다.

 

WITH Values AS (
  SELECT 1 AS x, 2 AS y
  UNION ALL SELECT 1 AS x, 4 AS y
  UNION ALL SELECT 2 AS x, 5 AS y
)

SELECT Values.x AS x_coordinate
  	, Values.y AS y_coordinate
  	, [Values.x, Values.y] AS coordinates
FROM Values
GROUP BY ALL

 

/*--------------+--------------+-------------+
 | x_coordinate | y_coordinate | coordinates |
 +--------------+--------------+-------------+
 | 1            | 4            | [1, 4]      |
 | 1            | 2            | [1, 2]      |
 | 2            | 5            | [2, 5]      |
 +--------------+--------------+-------------*/