SQL – Food for iThought

BigQuery 에서 PIVOT 하는 방법

BigQuery 에서2021년 7월 19일, PIVOT 기능이 신규 추가되었다.
PIVOT 이란 테이블의 열과 행을 변경하는 것을 말한다.
해당 PIVOT 기능에 대한 문법과 유의점에 대해 알아보자.

1. Syntax

--PIVOT 연산자
FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias

2. Example

다음과 같은 테이블이 있다고 해보자.

product	sales	quarter	year
Kale	51	Q1	2020
Kale	23	Q2	2020
Kale	45	Q3	2020
Kale	3	Q4	2020
Kale	70	Q1	2021
Kale	85	Q2	2021
Apple	77	Q1	2020
Apple	0	Q2	2020
Apple	1	Q1	2021

table_A

위 table A 를 아래 table B 와 같이 변경하고 싶다면,

product	year	Q1	Q2	Q3	Q4
Apple	2020	77	0	NULL	NULL
Apple	2021	1	NULL	NULL	NULL
Kale	2020	51	23	45	3
Kale	2021	70	85	NULL	NULL

table_B

다음과 같이 쿼리를 작성하면 된다.

SELECT * FROM
 table_A
 PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

3. 유의사항

PIVOT 하고자 하는 테이블에서 내가 참조하지 않는 속성이 있는 경우,
명확하게 PIVOT 앞의 FROM 절에서 명시를 해주어야 한다.

그렇지 않을 경우, PIVOT 결과 값이 왜곡된다.
(행 중복)

3.1. Bad Case

단순히 쿼터 별로 합계를 보고 싶다고 할 때(연도와 상관 없이),

아래와 같이 쿼리를 작성하지 말자.

SELECT product, Q1, Q2, Q3, Q4 FROM
 table_A
 PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

이에 대한 Result 는 다음과 같다.

product	Q1	Q2	Q3	Q4
Kale	51	23	45	3
Kale	70	85	NULL	NULL
Apple	77	0	NULL	NULL
Apple	1	NULL	NULL	NULL

3.1. 잘못된 결과

3.2. Good Case

참조하고자 하는 속성 중 year 가 없는 경우, 다음과 같이 명시해주어야 한다.

SELECT product, Q1, Q2, Q3, Q4 FROM
(SELECT product, quarter, sales FROM table_A)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

이에 대한 Result 는 다음과 같다.

product	Q1	Q2	Q3	Q4
Kale	121	108	45	3
Apple	78	0	NULL	NULL

3.2. 잘된 결과

BigQuery 랜덤 샘플링

RAND() 함수를 활용한 BigQuery 랜덤 샘플링

랜덤 샘플링은 RAND() 함수를 사용하면 된다.

N개의 행(row)을 랜덤 샘플링하는 쿼리는 다음과 같다.

--샘플 크기가 100개 행이라고 가정했을 때,

SELECT *
FROM `project.dataset.table`
WHERE RAND() < 100/(SELECT COUNT(*) FROM  `project.dataset.table`)
;

BigQuery 에서 TO_CHAR 사용해서 date 로부터 YYYY-MM 추출하기

BigQuery 를 사용하다보면, Oracle 이나 타 SQL 에 비교해서 가장 짜증나는 부분이
BigQuery 에서는 TO_CHAR 함수가 없다는 것이다.

즉, 특정 date 속성에서 ‘YYYY-MM’ string 을 추출하고자 한다면

Oracle 에서는

--Oracle SQL
SELECT TO_CHAR(<date>, 'YYYY-MM') AS <colname>

위와 같이 간결한 쿼리문이 BigQuery 에서는

--BigQuery
SELECT CONCAT(CAST(FORMAT_DATE("%E4Y", CAST(<date> as date)) AS string),'-',CAST(FORMAT_DATE("%m", CAST(<date> as date)) AS string)) AS <colname>

위와 같이 매우 번거롭게 쿼리를 작성해야 한다.

BigQuery ARRAY 속성 EXCEPT 적용, REPLACE

1. * EXCEPT

BigQuery 는 편리하게도 SELECT 문에서 전체 속성을 지정하는 Wildcard(*) 를 사용 시,
특정 속성을 제외할 수 있는 문법을 제공한다.

가령, 속성 a, b, c, d, e, f 중 a, b, d, e, f 만 선택하고 싶을 경우 (=c 제외)

--아래와 같이 일일히 속성을 지정하는 것 대신,
SELECT a, b, d, e
FROM table


--다음과 같이 * EXCEPT 를 적용하면 효율적이다
SELECT * EXCEPT(c)
FROM table

2. ARRAY 속성 관련 제한사항

그러나 * EXCEPT 는 그 편리함에도 불구하고,
제외할 속성으로 ARRAY 내 nested column 을 특정할 수 없다는 단점이 있다.

(ARRAY, UNNEST 에 대해 잘 모른다면 다음 포스팅 참고)

3. REPLACE 사용하기

가령, ARRAY 속성 b 를 가진 테이블을 UNNEST 하여
속성 a, b.1, b.2, b.3, c 중 a, b.2, b.3, c 만 선택하고 싶을 경우 (=b.1 제외)

--직관적으로는 아래와 같이 쿼리를 하고 싶지만 실행 오류가 뜬다.
SELECT * EXCEPT(b.1)
FROM table, UNNEST(b) AS b


--이 때는 REPLACE 를 적용해 다음과 같이 쿼리해야 한다.
SELECT * REPLACE (ARRAY(SELECT AS STRUCT * EXCEPT(1) FROM UNNEST(b)) AS b)
FROM table

1. 테이블 구성

BigQuery 는 GA 와 연계해서 사용하는 경우,
일반적으로 GA 데이터는 일 (daily) 단위의 별개 테이블로 BigQuery에 적재된다.

events_ & events_intraday_ tables in BigQuery for GA4 (Google Analytics 4) - Optimize Smart

출처 : Optimize Smart

가령, 위 화면 좌측 Explorer에 events_ (9) 라는 테이블은
events_20210115
events_20210114
events_20210113
…
events_20210107
총 9개의 일 별 테이블이 있는 것이다.

events 라는 테이블 명에 ‘_YYYYMMDD’ 형태의 Suffix 가 적용되었고,
총 테이블 개수는 괄호 안에 ‘_(9)’ 와 같이 표현된다.

2. 여러 테이블 쿼리하기

위와 같은 테이블 구성에서, 그렇다면 여러 테이블들을 어떻게 한 번에 호출할까?

여러 테이블을 일일히 UNION 하는 방식도 있겠으나,
BigQuery 에서는 보다 효율적인 문법을 제시한다.

Wildcard 와 _TABLE_SUFFIX 를 사용을 통해서다.

events 테이블 중 2021-01-09 부터 2021-01-12 까지의 테이블을 조회해야 한다고 가정해보자.

--아래와 같은 방식 대신,
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_20210109'
UNION ALL 
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_20210110'
UNION ALL 
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_20210111'
UNION ALL 
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_20210112'



-- Wildcard 와 _TABLE_SUFFIX 를 사용하는 것이 더 효율적이다.
SELECT *
FROM 'dbrt-ga4.analytics_207472454.events_*'
WHERE PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) BETWEEN DATE('2021-01-09') AND DATE('2021-01-12')

참고
https://cloud.google.com/bigquery/docs/querying-wildcard-tables

1. ARRAY 란?

Oracle 과 같이 기존에 전통적인 RDBMS 문법을 기반으로 쿼리를 배웠을 경우,
BigQuery 에서 이런 저런 데이터를 다루다 보면 간혹 낯선 풍경과 조우하는 경우가 있다.

그 중 하나가 BigQuery 의 데이터형(data type) 중 하나인 ARRAY 이다.

BigQuery에서 배열은 데이터 유형이 동일한 0개 이상의 값으로 구성된 순서가 지정된 목록을 의미합니다.
Google BigQuery Doc

별로 와닿지 않는 정의다…
풀어서 설명을 해보도록 하겠다.

일반적으로 기업에서 BigQuery 를 사용한다고 하면,
Google Analytics (이하 GA) 와 연동해서 사용하는 경우가 많으므로
GA 데이터를 샘플 삼아 설명하도록 하겠다.

GA 는 Web 에서 고객이 남기는 행동들을 Log 형태로 수집을 하고,
수집된 raw data는 JSON 형식에서 대략 다음과 같다.

[
  {
    "visitId": "123456789",
    "visitStartTime": "1622124798",
    "totals": {
      "visits": "1",
      "hits": "1",
      "pageviews": "1"
    }
  }
]

(참고) visitStartTime 값이 왜 1622124798 인지 궁금하신 분들은 관련 포스팅을 참고

JSON 문법을 잘 모르더라도,
위 내용을 아래와 같이 직관적으로 파악할 수 있을 것이다.

👨 visitId 가 123456789 이고
🕒 1622124798 UNIX time 에 들어와서
🔢 총 방문 수를 1회, hit 수는 1회, 페이지 조회 수는 1회 기록했구나.

자, 그러면 위 JSON 데이터를 쿼리가 가능하도록 table 형태로 보면 어떨까?

2. Why UNNEST?

위 데이터를 표로 만들 경우, 결과는 다음과 같을 것이다.

visitId	visitStartTime	totals
123456789	1622124798	“visits”: “1”, “hits”: “1”, “pageviews”: “1”

table1. UNNEST 전

자, 한 번 보자.
딱 봐도 문제가 있지 않은가? (불편 +1)

totals 컬럼의 값을 펼쳐서 추가적인 컬럼으로 만들고 싶은 욕구가 생긴다.
마치 R 에서의 reshape 패키지 cast 함수처럼.

visitId	visitStartTime	totals.visits	totals.hits	totals.pageviews
123456789	1622124798	1	1	1

table2. UNNEST 후

table1 에서 table2 로 변경을 해주는 작업.
이것이 바로 UNNEST 함수다.

3. UNNEST Syntax

UNNEST 의 사용 문법은 다음과 같다.

--visitId 별 페이지 조회 수

SELECT visitId, t.pageviews
FROM table1, UNNEST(totals) AS t

테이블 내 값을 컬럼으로 펼치는 과정에서 CROSS JOIN 이 들어가기 때문에,
FROM table1 뒤에 쉼표(,) 가 붙는다.

UNNEST(totals) 에 대한 alias 가 필수는 아니지만,
복잡한 JOIN 이 들어간 쿼리에서 해당 속성을 특정할 수 있어 사용을 권고한다.

외 참고 링크

Google 에서 직접 GA 샘플 데이터셋을 제공하기도 하니,
실무적인 연습이 필요한 분은 아래 링크를 참고.
Google Analytics sample dataset for BigQuery

UNIX time 이란? (= Epoch = POSIX ) & 쿼리 시 주의 사항

1. UNIX time 이란?

UNIX time 이란, 1970년 1월 1일 00:00:00 UTC 로부터 현재까지의 누적된 초(seconds) 값을 의미한다.

가령,
‘2021-05-23 09:00:00 KST’
위 시간을 UNIX time으로 표현한다면

① KST(local time)를 UTC 로 변환해서, ‘2021-05-23 00:00:00 UTC’
② UNIX time 의 기준 시간인 ‘1970-01-01 00:00:00 UTC’ 에서부터 누적 초를 계산

아래 숫자 값을 도출하게 된다.
‘1621728000’

2. 왜 UNIX time 인가?

UNIX time 은 UNIX 운영체제를 개발한 벨 연구소에서 정의한 개념이다.

Date/Timestamp 데이터형을 Numeric 데이터형으로 표현 시,
기존에 Date/Timestamp가 갖는 한계점을 해결할 수 있어 UNIX time 의 개념이 도입되었다.

Date/Timestamp 의 한계점
1. 로컬 시간대(ex. KST) 명시 필요
2. 비연속적, 비선형적인 값이므로 계산 시 변환 필요

하필 기준 시간이 1970년 1월 1일 00시 00분 00초 UTC 인 이유는,
UNIX 운영체제의 최초 출시년도가 1971년이어서 그렇다.
근방에 그럴싸한 시간을 임의로 잡은 것이다.

UNIX time 을 epoch time 또는 POSIX time 이라고도 부르는데,
이는 epoch 라는 단어가 특정 시대를 구분짓는 기준점(reference point from which time is measured = reference epoch) 이라는 의미를 가졌기 때문이고,
POSIX 는 UNIX 운영체제를 기반으로 둔 운영체제 인터페이스여서 같은 의미로 사용된다.

3. 쿼리 시 주의사항

UNIX time 을 취급할 때 쿼리 시 간과하기 쉬운 부분은, 다름 아닌 UTC 기준이라는 것이다.

하여 당신이 영국이나 스페인 같이 UTC +00:00 timezone 국가에 거주하지 않는 이상,
UNIX time 을 취급할 때는 항상 로컬 시간대로 변환하는 추가 작업을 해야 한다.
(이게 은근 까먹기 쉽다)

가령, Google Analytics 에서 수집한 고객 행동 데이터를 기반으로
접속 시간(hour)을 추출하는 쿼리를 작성한다고 하면,
다음과 같이 9시간에 해당되는 초(32,400)를 더해주어야 한다.

–BigQuery 기준
EXTRACT(HOUR FROM TIMESTAMP_SECONDS(CAST(visitStartTime+32400 AS INT64))) hh

(참고) BigQuery 의 Google Analytics 스키마에서는 시간을 UNIX time 으로 제공한다.

그외. 흥미로운 점

UNIX time 관련해서 2038년 문제라고 해서,
2038년이 되면 UNIX time 값이 32bit 시스템에서는
0 또는 음수로 표현되버리는 사태가 벌어질 것이라고 한다.
(2038년 이후에는 32bit 시스템의 허용된 숫자 표현 범위를 넘어버리게 되므로)

2038년이면 필자가 아직 살아있을 때일 것 같은데,
어떤 일이 일어날 지가 궁금하다.
해결책은 64bit 시스템으로 변경하는 것이라고 하는데 과연 시스템 교체가 수월할지 의문…

PostgreSQL UTC를 KST로 변경하기 (interval)

UTC 시간 timestamp + interval ‘9hrs‘

hrs → hours, hrs, hr, hour 모두 허용된다 (flexible)

PostgreSQL 시간 단위 자르기 (DATE_TRUNC)

PostgreSQL

DATE_TRUNC(‘최소 시간단위‘, 날짜컬럼)

최소 시간단위 – ex. hours, minutes, seconds …

(비교) Oracle

TRUNC(날짜컬럼, ‘데이터형에 준한 최소 시간단위‘)

데이터형에 준한 최소 시간단위 – ex. HH24, MM, SS

PostrgreSQL date interval 초 단위로 구하기 (epoch)

date_part(‘epoch’::text, 테이블.날짜2 – 테이블.날짜2)

[카테고리:] SQL