구글 빅쿼리 완벽 가이드 - 2장(3) - 배열과 구조체 기초

▣ 배열과 구조체 기초

• 배열을 간단히 설명하고, Array, Unnest를 함께 사용하면 쿼리, 함수 및 데이터 타입을 빠르게 실험할 수 있다.

■ 배열과 구조체 기초

• 다음 쿼리로 문자열의 SPLIT 함수가 어떻게 작동하는지 확인한다.

SELECT
  city, SPLIT(city,' ') AS parts
FROM (
  SELECT
    *
  FROM
    UNNEST([ 'Seattle WA', 'New York', 'Singapore'
  ]) AS city
)

• SQL 쿼리에 배열을 하드코딩할 수 있으므로 쿼리를 작성할 때 적절한 데이터셋을 찾거나 시간이 오래 걸리는 쿼리를 실행할 필요 없이 배열과 데이터 타입을 사용해 필요한 쿼리를 작성할 수 있다.

---

• 여러 데이터를 빠르게 실험하는 다른 방법은 UNION ALL로 단일 행을 반환하는 SELECT 문을 결합하는 방법이다.
• 이렇게 하면 대여 횟수가 2,000회 미만인 두 행의 데이터를 가진 작은 인라인 데이터셋을 만들 수 있다.

WITH
  example AS(
  SELECT
    'Sat' AS day, 1451 AS numrides, 1018 AS oneways
  UNION ALL SELECT 'Sun', 2376, 936
  UNION ALL SELECT 'Mon', 1476, 736
)
SELECT * FROM example
WHERE numrides < 2000

---

■ ARRAY_AGG로 배열 만들기

• 성별 및 연도별 대여 횟수를 찾아보자

SELECT
  gender,
  EXTRACT (YEAR FROM starttime) AS year,
  COUNT(*) AS numtrips
FROM
  `bigquery-public-data`.new_york_citibike.citibike_trips
WHERE
  gender != 'unknown' AND starttime IS NOT NULL
GROUP BY
  gender, year
HAVING
  year > 2016

SELECT 절을 보면 컬럼 이름 앞에 쉼표가 있는데, 그 이유는 무엇일까? 책이 처음 출간되었던 시점(2019년10월)의 빅쿼리 표준 SQL은 후행 쉼표를 지원하지 않았으므로, 쉼표를 다음 줄로 이동하면 행을 쉽게 재정렬할 수 있고, 특정 줄에 주석을 달아도 다음 예제와 같이 주석 처리된 줄 위 또는 아래의 줄에서 문법 오류가 발생하지 않으므로 쿼리가 여전히 잘 작동한다. SELECT  gender , EXTRACT (YEAR FROM starttime) AS year FROM etc 현재 빅쿼리는 후행 쉼표를 지원하므로, 굳이 선행 쉼표를 사용할 필요는 없다.

---

• 여러 해에 걸쳐 성별과 관련된 대여 횟수를 시계열로 얻으려면 어떻게 해야 할까.
• 대여 횟수의 배열을 만들어야 한다. ARRAY 타입을 사용해 해당 배열을 SQL로 표시하고 ARRAY_AGG 함수로 배열을 생성하면 된다.

SELECT
  gender,
  ARRAY_AGG(numtrips order by year) AS numtrips
FROM(
    SELECT 
     gender,
     EXTRACT (YEAR FROM starttime) AS year,
     COUNT(1) AS numtrips
     FROM
     `bigquery-public-data`.new_york_citibike.citibike_trips
     WHERE
      gender != 'unknown' AND starttime IS NOT NULL
     GROUP BY
      gender, year
    HAVING
      year > 2016
)
GROUP BY gender

• 일반적으로 성별로 그룹화할 때는 AVG(numtrips) 함수로 같은 구룹의 단일 스칼라 값을 계산해 모든 연도의 평균 대여 횟수를 찾는다. 그런데 ARRAY_AGG를 사용하면 개별 값을 수집해 순서가 있는 리스트 또는 ARRAY에 넣을 수 있다.
• ARRAY 타입은 다음과 같은 JSON 배열도 입력 데이터로 사용할 수 있다,

[
  {
    "gender": "male",
    "numtrips": [
      "9306602",
      "3955871"
    ]
  },
  {
    "gender": "female",
    "numtrips": [
      "3236735",
      "1260893"
    ]
  }
]

• 이런 JSON 파일을 수집해서 테이블을 생성하면 numtrips 열이 ARRAY 유형인 테이블이 생성된다. 배열은 NULL이 아닌 요소의 순서 리스트다. 예를 들어 ARRAY <INT64>는 정수의 배열이다.

 

기술적으로 배열은 NULL 값을 가질 수 있지만 그 배열을 테이블에 저장할 수는 없다. 예를 들어 다음 쿼리는 결과를 보관하는 임시 테이블에 배열 [1, NULL, 2]를 저장하려고 하기 때문에 작동하지 않을 것이다. WITH example AS(     SELECT true AS is_vowel, 'a' AS letter, 1 AS position     UNION ALL SELECT false, 'b', 2     UNION ALL SELECT false, 'c', 3 ) SELECT ARRAY_AGG( IF(position = 2, NULL, position )) AS positions FROM example 그러나 다음 쿼리는 NULL 값을 포함한 배열이 임시 테이블로 저장되지 않으므로 정상적으로 작동한다. WITH example AS(     SELECT true AS is_vowel, 'a' AS letter, 1 AS position     UNION ALL SELECT false, 'b', 2     UNION ALL SELECT false, 'c', 3 ) SELECT ARRAY_LENGTH( ARRAY_AGG( IF(position = 2, NULL, position )) FROM example

---

■ 구조체의 배열

• 구조체는 순서를 갖는 필드의 그룹이다. 필드에는 원하는 이름을 지정할 수 있는데, 가독성을 위해서라도 이름을 지정하는 것이 좋다.

SELECT
[
    STRUCT('male' AS gender, [9306602,3955871] AS numtrips),
    STRUCT('female' AS gender, [3236735,1260893] AS numtrips)
] AS bikerides

---

■ 튜플

• STRUCT 키워드와 필드 이름을 생략하면 튜플tuple 또는 익명 구조체anonymous struct가 생성된다. 빅쿼리는 쿼리 결과에서 이름이 지정되지 않은 컬럼 및 구조체 필드에 임의의 이름을 할당한다.

SELECT
[
    ('male', [9306602,3955871]),
    ('female', [3236735,1260893])
]

• 컬럼 이름의 별칭을 생략하면 이후 쿼리는 읽기도 어렵고 유지보수도 어렵게 된다. 일회성의 실험용 쿼리를 작성하는 것이 아니라면 반드시 컬럼 이름을 사용한다.

---

■ 배열 활용하기

• 배열을 생성했다면 배열의 길이를 찾을 수도 있고 배열 내의 개별 항목을 탐색할 수도 있다.

SELECT
    ARRAY_LENGTH(bikerides) AS num_items,
    bikerides[OFFSET(0)].gender AS first_gender
FROM
(SELECT 
[
    STRUCT ('male' AS gender, [9306602,3955871] AS numtrips),
    STRUCT ('female'AS gender, [3236735,1260893] AS numtrips)
] AS bikerides)

• 오프셋(OFFSET)은 0부터 시작하므로 OFFSET(0)은 배열의 첫 번째 값을 제공한다.

---

■ 배열 풀기

SELECT 
[
    STRUCT ('male' AS gender, [9306602,3955871] AS numtrips),
    STRUCT ('female'AS gender, [3236735,1260893] AS numtrips)
]

• 이 쿼리에서 SELECT 절은 배열을 포함하는 행 1개만을 반환하므로, 두 성별 데이터가 같은 행에 반환된다.

---

• UNNEST는 배열의 요소를 행으로 반환하는 함수로, 결과 배열을 풀면(UNNEST 하면) 배열의 각 항목에 해당하는 행을 가져올 수 있다.

SELECT * FROM UNNEST(
[
    STRUCT ('male' AS gender, [9306602,3955871] AS numtrips),
    STRUCT ('female'AS gender, [3236735,1260893] AS numtrips)
])

---

• UNNEST는 from_item이므로 SELECT 절에서 사용할 수 있다. 또한 배열의 일부만 선택할 수도 있다. 예를 들어 다음 쿼리는 numtrips 컬럼의 값만 얻는다.

SELECT numtrips FROM UNNEST(
[
    STRUCT ('male' AS gender, [9306602,3955871] AS numtrips),
    STRUCT ('female'AS gender, [3236735,1260893] AS numtrips)
])