▣ 배열과 구조체 기초
- 배열을 간단히 설명하고, 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)
])
'프로그래밍 > BigQuery' 카테고리의 다른 글
| 구글 빅쿼리 완벽 가이드 - 2장(5) - 저장 및 공유 (0) | 2022.02.06 |
|---|---|
| 구글 빅쿼리 완벽 가이드 - 2장(4) - 테이블 조인 (0) | 2022.02.05 |
| 구글 빅쿼리 완벽 가이드 - 2장(2) - 집계 (0) | 2022.02.02 |
| 구글 빅쿼리 완벽 가이드 - 2장(1) - 간단한 쿼리 (0) | 2022.02.02 |
| 구글 빅쿼리 완벽 가이드 - 1장 (0) | 2022.02.02 |
