SQL 레벨 별(Lv 1~5) 핵심 문법

in Google Cloud Platform on BigQuery


  • SQL 역량을 레벨로 정의해봤습니다. 업무를 하고, 학생들을 가르치면서 느낀 레벨을 정리했어요
    • 주관적으로 정의했으며, DB는 BigQuery 기준으로 작성했습니다.
    • 요즘 데이터 웨어하우스쪽은 문법이 유사해서 키워드만 가지고 가셔도 좋을 것 같습니다
  • 키워드 : SQL 필수 문법, BigQuery 문법, SQL 레벨 별 핵심 문법



SQL 문법 레벨별 핵심 체크리스트


SQL 기본 형태

  • SQL의 기본 형태는 다음과 같음(하나의 테이블을 사용할 경우)
SELECT
  col1,
  col2
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT



SQL 문법(Lv 1)

  • 제일 기본적인 개념

SELECT : 컬럼 선택, FROM : 테이블 지정

  • 특정 테이블에 있는 컬럼을 확인
  • AS를 사용해서 Alias(별칭)을 정의할 수 있음
SELECT
  col, 
  col2 AS new_col
FROM Table


WHERE : 테이블의 컬럼에 조건 설정

  • 테이블에 존재하는 컬럼에 조건을 설정하고 싶은 경우 사용
    • 데이터에서 특정 조건을 충족하는 Row를 가지고 오고 싶은 경우 사용
SELECT
  col,
  col2 AS new_col
FROM Table
WHERE
  col2 = 3


GROUP BY : 집계할 기준 명시

  • 데이터를 특정 그룹으로 묶어서(집계) 쓰고 싶을 때 사용
    • GROUP BY에 그룹화할 컬럼을 명시하고, SELECT에도 명시를 해줘야 함
SELECT
  type1,
  type2,
  COUNT(*) AS cnt
FROM Table
GROUP BY 
  type1, 
  type2
  • BigQuery에서는 GROUP BY ALL을 사용하면 알아서 집계할 컬럼을 추론해서 실행함


COUNT(col) : col 개수 세기, DISTINCT : 고유값 추출(중복 제거)

  • COUNT(*) : 컬럼의 Row 수를 센다. Row는 값이 NULL이여도 센다
  • COUNT(컬럼) : 컬럼의 값의 수를 센다. 값이 NULL이면 세지 않는다
  • COUNT(DISTINCT 컬럼) : 컬럼의 값의 고유값(중복 제외한 값)을 센다
SELECT
  type1,
  type2,
  COUNT(*) AS cnt1,
  COUNT(id) AS cnt2,
  COUNT(DISTINCT id) AS cnt3
FROM Table
GROUP BY 
  type1, 
  type2

AVG, MAX, MIN

  • 평균, 최대값, 최소값


HAVING : 집계 후 컬럼에 조건 설정

  • GROUP BY와 같이 사용됨
  • GROUP BY 후에 조건을 설정하고 싶은 경우 사용
    • (비교) WHERE : 테이블의 컬럼에 조건 설정, HAVING은 GROUP BY 후에 조건 설정
SELECT
  type1,
  type2,
  COUNT(*) AS cnt
FROM Table
GROUP BY 
  type1, 
  type2
HAVING cnt >=3


ORDER BY: 정렬

  • 데이터를 정렬하고 싶은 경우 사용
    • 쿼리의 중간에 사용하지 않고 쿼리 제일 바깥(마지막) 쿼리에 실행하는 것이 좋음
    • ORDER BY는 모든 데이터를 다 확인해서 정렬을 하기 때문에 시간이 소요됨
  • ASC(오름차순), DESC(내림차순)
SELECT
  type1,
  type2,
  COUNT(*) AS cnt
FROM Table
GROUP BY 
  type1, 
  type2
HAVING cnt >=3
ORDER BY cnt DESC


LIMIT : 출력할 개수 제한

  • 출력할 개수 제한하고 싶을 때 사용
SELECT
  type1,
  type2,
  COUNT(*) AS cnt
FROM Table
GROUP BY 
  type1, 
  type2
HAVING cnt >=3
ORDER BY cnt DESC
LIMIT 3



SQL 문법(Lv 2)

조건문(CASE WHEN, IF)

  • 조건을 설정하고 싶은 경우
  • 다중 조건은 CASE WHEN 추천
SELECT
  CASE 
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ELSE 모두아닐때
  END AS case_result
FROM Table

SELECT
  IF(조건, 조건이 참일 때 값, 조건이 거짓일 때 값) AS if_result
FROM Table


시간 데이터 다루기

  • 시간, 월 추출
  • 특정 일자 기준으로 버리기
  • TIMESTAMP, DATETIME 개념
  • UTC 개념도 알면 좋음
  • BigQuery 기준으로 설명
SELECT
  EXTRACT(HOUR FROM datetime_col) AS hour
FROM Table


JOIN : 여러 테이블을 연결할 때 사용

  • INNER, LEFT, RIGHT, CROSS JOIN
  • ON 사용해서 공통 Key 연결
SELECT
  lt.id,
  lt.col1,
  rt.col4
FROM left_table AS lt
LEFT JOIN right_table AS rt
ON lt.id = rt.id



SQL 문법(Lv 3)

윈도우 함수

  • 코딩 테스트에서 자주 출제되는 유형
  • 이전/이후 : LAG/LEAD
  • 처음, 마지막 값 : FIRST_VALUE, LAST_VALUE
  • 순서 확인 : ROW_NUMBER, RANK, DENSE_RANK
  • 윈도우 함수 문법
    • 함수(컬럼) OVER(PARTITION BY 파티션컬럼 ORDER BY 정렬컬럼)
SELECT
  LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time,
  FIRST_VALUE(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS first_event_time
FROM Table

SELECT
  DENSE_RANK() OVER (PARTITION BY team ORDER BY cnt) AS rank
FROM Table


WITH문(CTE)

  • 쿼리를 잘 정리하고 싶을 때 사용
  • 서브쿼리를 작성하고 정리할 때 자주 사용함
WITH cte AS (...
  SELECT
    col2, col3
  FROM Table
)

SELECT 
  * 
FROM cte



SQL 문법(Lv 4)

윈도우 함수(Frame 조절)

  • AVG, SUM 함수와 함께 Frame 조절
  • 이동 평균 구할 때 활용
  • 함수(컬럼) OVER(PARTITION BY 파티션컬럼 ORDER BY 정렬컬럼 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  • CURRENT ROW : 현재 ROW
  • UNBOUNDED : 끝까지
  • PRECEDING : 이전
  • FOLLOWING 이후
  • 윈도우 함수의 ORDER BY 뒤에 쓰면 됨
SELECT
  SUM(value) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_sum
FROM table


ARRAY, STRUCT

  • 배열, 구조체 데이터를 다룰 때 사용
  • GA4, Firebase 데이터 다룰 때 필수
  • PostgreSQL, BigQuery, Apache Spark 등에 존재하며 문법이 다양함


GROUP BY GROUPING SETS/ROLLUP/CUBE

  • GROUP BY의 확장판
  • 집계를 더 편하게 할 수 있음
    • 아래 쿼리는 category, region 별 집계 + region 별 집계 + 전체 집계
SELECT 
  category,
  region,
  SUM(sales_amount) AS total_sales,
  COUNT(*) AS transaction_count
FROM sales
GROUP BY GROUPING SETS (
  (category, region),
  (region),
  ()
)



SQL 문법(Lv 5)

JSON

  • JSON 형태에서 데이터 추출
  • JSON_QUERY, JSON_VALUE
  • DB마다 문법 다양함


정규표현식

  • 텍스트 데이터에서 데이터 추출
  • REGEXP_CONTAINS
  • DB마다 문법 다양함


UDF(Used Defined Function)

  • 사용자 정의 함수
  • 파이썬에서 함수 쓰는 것처럼 정의해서 쓸 수 있음
  • DB마다 문법 다양함
  • 글 작성하는데 걸린 시간 : 23분

카일스쿨 유튜브 채널을 만들었습니다. 데이터 사이언스, 성장, 리더십, BigQuery 등을 이야기할 예정이니, 관심 있으시면 구독 부탁드립니다 :)

PM을 위한 데이터 리터러시 강의를 만들었습니다. 문제 정의, 지표, 실험 설계, 문화 만들기, 로그 설계, 회고 등을 담은 강의입니다

이 글이 도움이 되셨거나 다양한 의견이 있다면 댓글 부탁드립니다 :)

Buy me a coffeeBuy me a coffee



© 2017. by Seongyun Byeon

Powered by zzsza