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분

카일스쿨 유튜브 채널을 만들었습니다. 데이터 분석, 커리어에 대한 내용을 공유드릴 예정입니다.

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

이 글이 도움이 되셨거나 의견이 있으시면 댓글 남겨주셔요.

Buy me a coffeeBuy me a coffee



© 2017. by Seongyun Byeon

Powered by zzsza