SQL 중급: 7일차 #2

쿼리가 너무 재미있어서 윈도우 함수까지 넘어갔다.

 

오라클에서 처음 만들어서 Analytics Function 이라고 이름 붙였지만

지금은 구글 빅쿼리를 포함해서 대표적으로 Window 함수라고 불린다고 한다.

 

사실상 이름만 다르고 같은 기능이라고 보면 된다.

 

 

윈도우 함수로 무엇을 알 수 있을까?

 

 

서비스에서는 "리텐션"이 중요한데

A 유저는 3일 동안 연속해서 접속했고, B 유저는 오늘 들어왔다가 3일 후에 들어왔을지 여부를 구분해서 알 수 있다.

 

함수를 통해 B 유저와 같이 3일 동안 이탈했다가 접속하는 유저군이 많이 포착된다고 하면,

왜 이러한 행동이 패턴으로 나타나는 것일까? 등을 고민해서 서비스 개선 포인트로 적용할 수 있다.

 

또한 서비스에서 신규, 복귀 유저를 구분하는 것도 중요한데

왜냐하면 새로 온 신규 유저에게는 하나부터 열까지 기능을 알려주는 친절한 가이드가 필요하지만,

복귀 유저에게는 귀찮은 거 다 빼고 혜택부터 보여주는게 구매 전환에 도움이 많이 될 수 있기 때문이다.

 

이러한 측면에서 "첫 접속한 일자"를 FIRST_VALUE()로 구하고

첫 접속한 일자 - 최근에 마지막으로 접속한 일자 = 차이를 구해서,

• 차이가 7일이나 14일 이상 나면 특정 화면은 스킵하게 한다거나
• 차이가 몇 개월 단위로 나면 추가 혜택을 준다거나 하는 식으로 활용할 수 있다.

 

윈도우 함수에는 대표적으로 LEAD, LAG, FIRST_VALUE, LAST_VALUE 가 있다.

 

 

종류가 많아서 당황할 수 있겠지만,

결론적으로 4개 모두 사용하는 방법이 같다.

 

SELECT 안에다가 아래와 같이 작성해 주면 된다.

 LEAD(delivery_month) OVER(PARTITION BY name ORDER BY delivery_month) AS after_delivery_month,

 

LEAD 쪽에다가 LAG, FIRST_VALUE, LAST_VALUE를 쓰면 된다.

물론 LAST_VALUE를 사용할 때에는 주의 점이 있어서 있다가 추가로 기재하겠다.

 

설명하자면, LEAD = 괄호 안에 있는 셀의 숫자 기준으로 이후의 값을 보여주세요

• LEAD = 리드하다, 첫 번째, 대가리라고 생각해서
• LEAD가 대장이다, 이거 쓰면 그 뒤에 값을 보여 달라고 하는 것이다라고 이해

뒤에 나오는 OVER(PARTITION BY ~ ORDER BY ~) 는 그냥 짝꿍이라고 보면 된다.

 

OVER = 어떻게 할거냐면요~

PARTITION BY = 적힌 기준으로 나누어서

ORDER BY = LEAD 값과 동일하게 입력, 왜냐하면 LEAD에서 순서를 정하는 기준이기 때문

 

 

예시를 들어 살펴보자.

 

 

아래와 같은 데이터가 있다고 할 때,

홍길동 사원이 1월에 납품하고 난 다음에 3월에 납품한 것을 옆에다가 적고 싶다면 어떻게 해야 할까?

 

 

예를 들면 아래와 같이 after_delivery_month 행을 추가하는 것이다.

 

name	delivery_month	after_delivery_month
홍길동	1	3
홍길동	3	5

 

왜 추가해야 하나?

앞서 설명했던 것처럼, 1월에 납품하고 3월에 납품했으면 2개월이라는 텀이 소요 되었는데

혹시 다른 사원한테서도 동일한 주기가 나타나는지 알고 싶은 상황인 것이다.

 

SELECT

 *,

 LEAD(delivery_month) OVER(PARTITION BY name ORDER BY delivery_month) AS after_delivery_month,

FROM test.test_table

 

 

값이 없는 경우에는 NULL로 입력이 되지만

1월 이후 3월, 3월 이후 5월과 같이 잘 추출되는 것을 볼 수 있다.

 

 

그렇다면 이전에 납품한 기록도 옆에다가 붙여볼 수 있을까?

 

SELECT

 *,

 LEAD(delivery_month) OVER(PARTITION BY name ORDER BY delivery_month) AS after_delivery_month,

 LAG(delivery_month) OVER(PARTITION BY name ORDER BY delivery_month) AS before_delivery_month,

FROM test.test_table

 

 

 

 

그런데 왜 여기서 name 순서가 달라졌는지는 모르겠다.

앞서 작성한 LEAD 쿼리에 LAG 쿼리를 붙였을 뿐인데.. 혹시 답을 알고 계신다면 댓글로 가이드를 부탁드립니다..

 

 

각각의 첫 번째 값과 두 번째 값도 알 수 있을까?

 

SELECT

 *,

 FIRST_VALUE(delivery_month) OVER(PARTITION BY name ORDER BY delivery_month) AS first_delivery_month,

 LAST_VALUE(delivery_month) OVER(PARTITION BY name ORDER BY delivery_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_delivery_month,

FROM test.test_table

 

 

 

FIRST_VALUE 함수와 LAST_VALUE 함수를 쓰면 된다.

LAST_VALUE 함수에는 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 라고 작성된 게 있다.

 

 

왜 LAST_VALUE 함수에는 ROWS BETWEEEN 문구가 추가되어 있을까?

 

 

 

해당 문구를 제거하고 쿼리를 실행해 보면 답을 알 수 있다.

 

SELECT

 *,

 FIRST_VALUE(delivery_month) OVER(PARTITION BY name ORDER BY delivery_month) AS first_delivery_month,

 LAST_VALUE(delivery_month) OVER(PARTITION BY name ORDER BY delivery_month ) AS last_delivery_month,

FROM test.test_table

 

 

윈도우 함수는 행 별로 작동하기 때문에

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  문구를 추가하지 않으면

기존의 데이터 셋과 동일한 값이 나온다.

 

---

 

기본 집계 함수는 NULL 값을 제외하고 계산한다.

 

 

WITH null_data AS (

  SELECT '홍길동' AS name, NULL AS delivery_period UNION ALL

  SELECT '김철수', 2 UNION ALL

  SELECT '김미영', 4

)

SELECT

 COUNT(delivery_period) AS cnt1,

 SUM(delivery_period) AS sum1,

 AVG(delivery_period) AS avg1,

FROM null_data;

 

위와 같이 데이터를 COUNT, SUM AVG 해보면 다음과 같이 나온다.

CNT1 보면 바로 알 수 있듯이, 총 데이터는 홍길동/김철수/김미영 3개이지만, null 제외하고 2개라고 반환한다.

SUM1, AVG1 또한 동일하게 2개의 값을 기준으로 계산한 것을 알 수 있다.

 

 

윈도우 함수는 NULL 값을 포함하여 계산한다.

WITH null_data AS (

  SELECT '홍길동' AS name, NULL AS delivery_period UNION ALL

  SELECT '김철수', 2 UNION ALL

  SELECT '김미영', 4

)

SELECT

 *,

 FIRST_VALUE(delivery_period) OVER(ORDER BY name) AS first_period

FROM null_data;

 

동일한 데이터에 윈도우 함수 FIRST_VALUE를 사용했을 때

홍길동 사원에게는 delivery_period가 NULL 값이었지만,

name 기준으로 delivery_period 가장 첫 번째 값을 가져오게 되면 4가 일괄적으로 찍히는 것을 볼 수 있다.