데이터 엔지니어링 스타터 키트 - 3주차

데이터 엔지니어링 스타터 키트 - 3주차

이번 주차의 주제는 SQL for Data Engineers 였다. 맥스님 말씀으로는 백엔드 엔지니어가 다루는 SQL과 데이터 엔지니어가 다루는 SQL은 차이가 있다고 해주셨는데 현재 SQL을 다루는 입장으로서 매우 공감이 갔다.
이번 주차도 지난 주차와 마찬가지로 숙제에 대한 리뷰로 시작을 했고 숙제가 SQL을 사용하는 쿼리였으므로 자연스럽게 이번 주차 내용과도 이어졌다. SQL의 장단점, 빅데이터 시대의 SQL, DDL과 DML, SQL의 기초 및 심화과정에 대한 언급, 실습 순서로 세션이 진행되었고 마찬가지로 숙제가 주어졌다.
2시간 반 정도 SQL과 관련하여 많은 내용이 다뤄졌는데 모든 내용을 포스팅하기에는 어렵기에 이번에는 숙제를 하며 개인적으로 정리가 더 필요하다고 생각했던 SQL의 Window Function에 대해 주로 정리하는 포스팅을 진행하겠다.

 

빅데이터 시대의 SQL 입지

2000년대 중반 하둡의 등장과 빅데이터 시대의 전환으로 SQL에 대한 인기가 떨어졌지만 역설적으로 하둡의 Map Reduce를 사용하려면 개발이 필요하고 컨셉적으로도 어렵기 때문에 다시 SQL의 인기가 올라갔다는 이야기를 해주셨다. 이것이 Hive나 Presto나 Spark SQL등과 같이 분산환경에서의 SQL을 사용할 수 있게하는 기술이 등장한 배경이기도 하고 SQL이 그만큼 파워풀하다는 증거이기도 한것 같다.

 

SQL의 장단점

• SQL - Structured Query Language의 약자 / 1970년대 IBM에서 개발
• 이론적으로 연구가 많이 되어 있고 최적화가 많이 되어 있는 믿을 수 있는 기술
• 2000년대 중반 하둡의 등장으로 주춤했으나, 맵리듀스가 생산성이 떨어지는 단점때문에 역설적으로 더 인기가 많아진 느낌
• 이름에서도 볼 수 있듯이 구조화된 데이터를 처리하는데에는 파워풀하지만 비구조화된 데이터를 처리하기에는 오버헤드가 크고 적합하지 않음

 

SQL - Window Function (윈도우 함수)

• 투플 별로 통계치를 제공하는 함수
• 테이블을 파티션(Partition)으로 나누어 파티션마다 투플별 통계치를 제공
• 종류
  • 집단 함수 : COUNT, SUM, AVG, MIN, MAX
  • 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER
  • 비율 함수 : PERCENT_RANK, CUME_DIST, NTILE
  • 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• PARTITION BY 절을 이용해 파티션(투플의 일부)을 생성
• 윈도우 함수는 파티션 별로 투플들에 각각 적용되고, 계산된 값은 투플마다 추가
  • PARTITION BY 절이 생략되면 전체 투풀에 적용 (전체 투플을 하나의 파티션으로 간주)
• 윈도우 함수는 결과로 “파티션의 모둔 투플들을 리턴”

윈도우 함수의 일반 형식

• Partition 정의, Order 정의, Frame 정의로 구성됨

window_function_name ([expression [,expression ...]) OVER
(
    [ existing_window_name ]
    [ PARTITION BY expression [, ...] ]
    [ ORDER BY expression [ASC | DESC | USING Operator] [ NULLS { FIRST | LAST } ] [, ...] ]
  [ RANGE | ROWS {frame_start | frame_between} ]
)

frame_start ::= UNBOUNDED PRECEDING | N PRECEDING | CURRENT ROW
frame_between ::= BETWEEN frame_boundary_start AND frame_boundary_end
frame_boundary ::= UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING |
                   N PRECEDING | N FOLLOWING |
                   CURRENT ROW

SELECT 문에서의 적용 형식

SELECT window_function_name (컬럼명_리스트) OVER
            (
                [PARTITION BY 컬럼명_리스트]
                [ORDER BY 컬럼명_리스트]
                [ROWS | RANGE {frame_start | frame_between}]
            )
FROM 테이블명;

• PARTITION BY 절
  • 기준 컬럼 값이 같은 투플끼리 묶어, 테이블을 여러 개의 파티션(partition)으로 나눔
• ORDER BY 절
  • 파티션 내에서 어떤 컬럼을 기준으로 투플을 정렬할 지 기술
• ROWS / RANGE 절
  • 파티션 내에서 프레임(frame)을 정의

프레임(Frame)

• 파티션 내의 현재 행에 대해 윈도우 함수를 적용할 때, 입력(계산의 대상)되는 행의 집합
• 프레임의 범위는 파티션의 현재 행에 상대적으로 정의하여 프레임이 파티션 내에서 움직일 수 있음
• 프레임 단위(frame unit)
  • ROWS 절 : 현재 행과 프레임 시작/끝 행과의 차이를 행의 개수로 나타냄
  • RANGE 절 : 현재 행과 프레임 시작/끝 행과의 차이를 ORDER BY 절의 기준 컬럼 값으로 나타냄
  • 프레임의 시작/끝에 사용 가능한 값
    • UNBOUNDED PRECEDING : 파티션의 첫번째 행
    • UNBOUNDED FOLLOWING : 파티션의 마지막 행
    • N PRECEDING : 현재 행 앞의 N번째 행
    • N FOLLOWING : 현재 행 뒤의 N번째 행
    • CURRENT ROW : 현재 행

Window 함수 예제

• Windows 함수 중에 가장 많이 사용하는 함수인 ROW_NUMBER 예제를 살펴본다.
• 원본 데이터
  • 필드 : userid, ts, channel - 유저아이디, 접속시간(초단위), 접속 채널
  • 예시 데이터

userid    ts                          channel
1491    2019-10-18 14:14:05.100000    Organic
59      2019-07-29 12:39:41.817000    Naver
117      2019-08-10 16:33:14.980000    Youtube
572      2019-07-06 19:54:15.083000    Organic
935      2019-05-22 08:02:29.360000    Google
1363    2019-07-17 14:24:16.880000    Naver
780      2019-05-02 15:24:33.963000    Naver
1917    2019-08-21 14:48:36            Organic
1357    2019-09-21 21:16:29            Instagram
1247    2019-08-13 15:27:51.850000    Naver
887      2019-05-29 15:26:37.620000    Facebook
2728    2019-10-12 00:01:28.397000    Naver
1037    2019-06-03 16:48:03.620000    Instagram
2037    2019-09-13 17:18:24            Facebook
1473    2019-06-27 16:12:57.657000    Google
197      2019-08-14 15:14:58.460000    Naver
2081    2019-08-18 19:12:59            Youtube
278      2019-08-21 16:52:18.307000    Naver
971      2019-11-22 01:02:24            Facebook
1967    2019-09-04 22:10:02            Naver
1871    2019-10-12 17:54:49            Instagram
1317    2019-09-26 17:40:07            Google
599      2019-10-14 13:49:54.297000    Google
1393    2019-10-05 22:03:15.480000    Facebook
1493    2019-09-19 05:51:06            Youtube
2650    2019-11-26 16:29:30            Naver
29      2019-06-17 18:17:28.663000    Youtube
772      2019-11-17 17:20:44            Google
2544    2019-10-04 16:27:32.147000    Instagram
199      2019-08-27 15:29:53.780000    Naver
1301    2019-09-13 16:43:34            Youtube
1717    2019-08-22 15:32:06            Google
1757    2019-10-05 15:14:16            Facebook

• 위 테이블에서 유저별, 시간순서대로 번호를 붙여 출력하고 싶다면?
  • Window Fucntion 중 하나인 ROW_NUMBER 함수를 사용하면 유저별 시간순서대로 번호를 붙일 수 있음
  • Window Function을 사용한 SQL

SELECT 
  ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts) AS seq
  , userid
  , ts
  , channel
FROM test_table;

seq	userid	ts	                        channel
1       68	2019-05-02 21:26:54.900000	Youtube
2	  68	2019-05-04 20:19:28.463000	Google
3	  68	2019-05-04 20:20:39.770000	Instagram
4  	  68	2019-05-05 22:27:28.390000	Youtube
5	  68	2019-05-06 20:22:16.997000	Instagram
6	  68	2019-05-07 19:56:45.297000	Instagram
7	  68	2019-05-08 19:33:20.870000	Instagram
8	  68	2019-05-09 18:52:13.113000	Organic
9	  68	2019-05-10 19:30:23.227000	Instagram
10	  68	2019-05-13 19:06:37.683000	Youtube
11	  68	2019-05-14 23:40:21.997000	Google
12	  68	2019-05-16 18:31:32.123000	Google
13	  68	2019-05-18 16:25:32.673000	Organic
1	  173	2019-09-25 16:54:04.823000	Google
2	  173	2019-09-25 17:03:55.507000	Organic
1	  184	2019-05-01 00:31:26.120000	Youtube
2	  184	2019-05-01 00:35:59.897000	Naver
3	  184	2019-05-02 00:56:01.303000	Instagram
4	  184	2019-05-03 01:14:56.537000	Instagram
5	  184	2019-05-05 04:19:50.687000	Google
6	  184	2019-05-05 21:45:16.723000	Naver
7	  184	2019-05-07 00:29:44.007000	Facebook
8	  184	2019-05-08 04:40:55.670000	Google
9	  184	2019-05-09 01:53:51.947000	Google
...

• SELECT 문에서 ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ts) AS seq  함수를 사용해서 맨 왼쪽의 seq 필드를 만들었다. 그 결과 유저별 접속시간순으로 번호가 생성이 된다. 이 점이 window 함수와 단순 집계함수(group by문) 와의 가장 큰 차이점이다. userid 별로 그룹화를 시키고 그 안에서 시간순으로 정렬을 했기 때문에 seq필드의 번호가가 user별 시간순으로 생성이 된다.
• 이를 통해 유저별로 시간순서에 따라 처음 접속한 채널 또는 마지막에 접속한 채널 등을 비교적 쉽게 알 수 있다. (물론 이 경우에는 다른 Window Function인 FIRST_VALUE와 LAST_VALUE를 사용하는 게 더 간편할 수 있다.)

 

3주차 소감

지난 주부터 Redshift 사용을 위한 SQL에 대해 수업을 이어나가고 있다. 프로젝트에서도 SQL을 심화되게 사용하고 있는 중에 비슷한 내용을 다루는 스터디를 하다보니 시너지가 나서 도움이 정말 많이 되고 있다. 이번 포스팅에서는 한번쯤은 다시 정리하고 넘어가야 하는 Window 함수에 대해 정리해봤다. 다음 주는 Airflow를 배우게 되는데 이 또한 프로젝트에서 사용해야 하기 때문에 기대가 많이 된다.

 

참고자료

• 프로그래머스 스터디 페이지
• https://en.wikipedia.org/wiki/Window_function_(SQL)
• https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html