[BigQuery] 다양한 쿼리 예제

지난 포스팅에서는 빅쿼리에서의 array 그리고 struct 에 대해서 살펴보았었다.

https://walkingfox.tistory.com/169

[BigQuery] Array, Struct 그리고 unnest

 

이번 포스팅에서는 여러 케이스의 쿼리 샘플을 살펴보려고 한다.

 

(1) unnest 를 이용한 array 를 펼치기

with 문을 이용하여 위와 같은 테이블을 먼저 구성하였다. 

id, some_numbers 라는 두개의 컬럼으로 구성되어 있고, some_numbers 라는 컬럼을 array 로 되어있다.

 

이렇게 array 로 되어있는 것을 펼치기 위해서는 unnest 한 후에 cross join 으로 연결하는 것이다.

WITH sequences AS
  ( SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
    UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
    UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers
  )
SELECT id, flattened_numbers
FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers

(2) 좀 더 복잡한 data set 을 아래와 같이 구성해보았다.

하나의 행(row)인 테이블인데 race, participants 라는 컬럼으로 구성되어 있고

participants 라는 컬럼은 array 인데 그 arrayrk name 과 splits 라는 array의 struct 로 구성되어 있다.

 

(2-1) 우선 위의 (1)번과 같이 participants 라는  array를 풀어보자 (unnest)

여기에서 cross join unnest 는 다음과 같이 줄여서 쓸 수 있다. 즉 cross join 이 그냥 콤마(,) 로 대체된 것이다. 

WITH races AS (
  SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
     STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
     STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
     STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
     STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
     STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
     STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
     STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
       AS participants
    )
SELECT
  race, participant
FROM races r, UNNEST(r.participants) as participant

 

(2-2) array 안의 struct 에 있는 array 를 연속으로 풀어보자 

그런데 여기서 unnest도 아래와 같이 줄여서 사용할 수 있다.

WITH races AS (
  SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
     STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
     STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
     STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
     STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
     STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
     STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
     STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
       AS participants
    )
SELECT  race, participants.name, split
FROM races, races.participants, participants.splits as split

결과는 위와 같은 내용을 얻는다.

 

(2-3) unnest를 한단계만 하고 splits 은 sum 을 구하여 큰 순으로 나열해보자

위와 같이 select 구문 단계에서 array 를 aggregate 할 수 있다.