SQL 중급: 다시 1일차

교재

https://www.inflearn.com/course/bigquery-%ED%99%9C%EC%9A%A9%ED%8E%B8/dashboard

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석) 강의 | 카일스쿨 - 인프런

 

1. 입문편 복습

쿼리로 일을 한다는 것은 무엇일까

 

1) 업무를 할당 받았을 때 2) 목표가 무엇인지 파악하고 3) 알맞은 데이터가 있을지 탐색해서 4) 추출하고 5) 검증해서 6) 활용하는 것이다.

쿼리를 어떻게 사용하는지만 알아서 되는 것이 아니라, 데이터가 어떻게 쌓이고 있는지 혹은 내가 활용할 수 있는 데이터가 있는지 부터 알 수 있어야 한다.

그리고 제일 중요한 것은 역시 검증인데, 전체 공유를 하고 난 이후 다시 조회해 보았을 때 다른 값이 나온다거나 하는 실수를 예방할 수 있도록 한다.

데이터 타입을 아는 것이 중요하다

 

크게 숫자, 문자, 시간/날짜, 부울로 나눌 수 있다.

숫자의 경우 0으로 나누었을 때 이상한 값이 나오지 않도록 SAFE_DIVIDE 라는 함수도 있다.

시간/날짜는 꼭 한번 자세히 살펴보는게 좋을 것 같다. 회사에서 DATETIME vs TIMESTAMP 어느 것으로 기록하고 있는지에 따라서 잘 활용할 수 있어야 하기 때문이다.

부울은 TRUE, FALSE를 의미한다.

 

쿼리를 작성 한다는 것은 무엇일까

 

한번에 잘 작성할 생각은 하지 말자. 욕심히 과하면 안된다. 나중에 검증할 것까지 생각해야 한다.

원하는 데이터로 갈 때까지 순서대로 하나씩 쪼개서 쿼리를 만들고, 조회해 보고, 원하는 결과물인지 확인하고, 맞다면 이후 쿼리를 이어서 작성해 나가는 것이 중요하다.

요새는 기술이 좋아져서 오류가 발생하면 구글에 검색하거나 ChatGPT에게 물어본다. 코드를 수정해가면서 원하는 결과물이 나오는지 본다.

 

 

2. 제품 현황 분석

앱 로그 데이터가 CSV가 아닌 ORC 확장자를 사용한다. 처음 보는 단어.

데이터는 행으로 쌓여 저장되어 있는데, ORC는 컬럼 별로 압축해서 저장하기 때문에 더 많은 데이터를 적은 용량으로 만들 수 있다.

 

*이 부분은 사내 동아리 스터디에서 귀동냥 한 것이므로 틀릴 수 있으니 주의*

 

ORC를 쓰면 좋은 이유는 컬럼의 타입까지 정의되어 있기 때문에, 날짜 형식이 텍스트로 저장되어 있거나 하는 오류를 사전에 예방할 수 있다.

이렇게 데이터가 잘못 저장되어 있으면 나중에 활용할 때 작성하는 쿼리가 복잡해진다.

그래서 테이블의 데이터 타입이 중요하고 DBMS 마다 다르다.

DMBS 종류는 AWS 레드시프트, 구글 Bigquery, Snowflake 제품 등이 대표적이다.

 

*이 부분도 사내 동아리 스터디에서 귀동냥 한 것이므로 틀릴 수 있으니 주의*

 

https://console.cloud.google.com/bigquery

Google 클라우드 플랫폼

 

구글 Bigquery 사용은 어렵지 않았다. 별도로 가이드 영상 시청하지 않고도 데이터 불러오기 성공.

파티션을 나눈다는 것을 잘 이해하지 못했었는데, 구글이 친절하게 알려준다.

https://cloud.google.com/bigquery/docs/partitioned-tables?hl=ko&_gl=1*9owwmg*_ga*Mjk4MTM2OC4xNzI5OTE0NTMx*_ga_WH2QY8WWF5*MTcyOTkxNDUzMC4xLjEuMTcyOTkxNTY4OC40Mi4wLjA.

파티션을 나눈 테이블 소개  |  BigQuery  |  Google Cloud

 

ARRAY (배열) 만드는 방법 중 하나를 해본다.

SELECT [1, 3, 4] AS some_numbers

UNION ALL

SELECT [1, 2]

 

some_numbers, UNION ALL

 

some_numbers 하면 행 번호를 1, 2, 3 ... 매겨주는 것이다.

UNION ALL 하면 세로로 데이터를 붙여준다.

 

SELECT

 GENERATE_DATE_ARRAY('2024-10-01', '2024-10-30', INTERVAL 1 WEEK) AS result

 

GENERATE_DATE_ARRAY

 

시작 날짜부터 종료 날짜까지 7일 간격으로 날짜를 세준다.

엑셀에서 이 함수를 찾으려도 엄청 고민했던 것 같은데, 강의를 듣다가 우연히 찾게 되다니 신기하다.

 

참고로 위에 만든 "배열"은 아래와 같이 1, 2, 3, 4, 5 행 별로 데이터가 입력된 것과는 다른 데이터 타입니다.

배열은 1 행 하나에 2024-10-01, 2024-10-08, ..., 2024-10-29 까지 5개의 데이터가 입력된 것이므로 주의하자.

 

행	result
1	2024-10-01
2	2024-10-08
3	2024-10-15
4	2024-10-22
5	2024-10-29

 

ARRAY(배열) 형태로 데이터를 저장하는 이유는 무엇일까

 

압축해서 데이터를 저장하기에 매우 효율적인 구조이기 때문이다.

하지만 데이터를 활용하는 입장에서는 골치가 아픈데, 그건 내가 아직 익숙하지 않아서 인것 같다.

어떻게 추출해서 활용하는지만 알게 되면, 더 많은 장점을 발견하게 될수도 있다.

 

배열 데이터에 접근하려면 OFFSET(0부터 시작), ORDINAL(1부터 시작) 함수를 사용해주면 된다.

무슨 소리인가?

WITH array_samples AS (

  SELECT [1, 3, 4] AS some_numbers

UNION ALL

SELECT [1, 2]

)

SELECT

 some_numbers [SAFE_OFFSET(1)] AS first_value

FROM array_samples

 

SAFE_OFFSET(1)

 

OFFSET(1) 이지만 1행 = 1, 2행 = 1 값이 나온게 아니라, 1행 = 3, 2행 = 2 값이 나왔다.

그리고 앞에 SAFE를 쓰는 이유는, SAFE_ORDINAL(2) 하면 2행 = null 값이 나오지만, 안쓰면 쿼리가 오류난다.

 

 

ARRAY(배열)과 STRUCT(구조체) 차이는 무엇인가

ARRAY는 학용품이라고 하면 떠오르는 [연필, 필통, 지우개] 같이 동일한 형태(명사) 값을 저장한 것이고

STRUCT는 학용품마다 브랜드, 가격, 입고일, 출고일 등이 있을 수 있는데, 각 속성을 STRUCT 필드로 저장한다.

 

SELECT

 struct_temp.name,

 struct_temp.price

FROM (

  SELECT

   STRUCT<name STRING, price INT64>('모니미', 30) AS struct_temp

)

 

 

STRUCT 만들기

 

글로 길게 설명하면 어렵다. 바로 쿼리를 만들어서 돌려보면 빠르게 이해할 수 있다.

*모니미 오타 아닙니다. 일부러 저렇게 썼습니다*

 

UNNEST는 무엇인가

 

ARRAY, STRUCT를 통해 중첩으로 구성된 데이터 구조를 풀어서 FLATTEN(평면화) 하는 것이다.

왜 푸냐고? 그래야 원하는 형식으로 데이터를 피벗 테이블로 만들던 해서 분석할 수 있기 때문이다.

 

UNNEST는 GIF 하나로 설명 끝낸다

출처: https://medium.com/firebase-developers/how-to-use-select-from-unnest-to-analyze-multiple-parameters-in-bigquery-for-analytics-5838f7a004c2

How to use SELECT FROM UNNEST to analyze multiple parameters in BigQuery for Analytics

 

WITH study_temp AS (

 SELECT

   '모니미' AS name,

   ['2024-10-01','2024-10-08'] AS input_date,

   30 AS sell_price

 UNION ALL

 SELECT

   '형굉팬' AS name,

   ['2024-10-08','2024-10-15','2024-10-22'] AS input_date,

   50 AS sell_price

)

SELECT

 *

FROM study_temp

ARRAY, STRUCT 만들기

 

~앞의 WITH 문을 쓰고~

SELECT

 name, input_dt, sell_price

FROM study_temp

CROSS JOIN UNNEST(input_date) AS input_dt

UNNEST 쿼리를 함께 돌려주면 아래와 같이 원하는 결과를 얻을 수 있다.
*형굉팬 오타 아닙니다. 모니미랑 친구입니다*

 

CROSS JOIN, UNNEST 결과