mysql 5.7에서는 Windwo function을 사용할 수 없어서, lag와 같은 함수를 구현하기 위해서는 스칼라 서브쿼리나 Self join의 방법을 통해서 구현해야 한다. 따라서 유저 리텐션을 Mysql5.7에서 구하기가 굉장히 난감했는데, 데이터를 서브쿼리를 구현할 수 있는 Bigquery나 다른 플랫폼으로 옮기기도 애매한 상황이었다.
아래의 테이블을 보자.
| user_id |
로그인 일자 |
| 1 | 2021.03.01 |
| 1 | 2021.03.02 |
| 2 | 2021.03.02 |
| 2 | 2021.03.04 |
| 1 | 2021.04.06 |
| 4 | 2021.01.01 |
| 4 | 2021.03.01 |
| 2 | 2021.01.19 |
위 같은 테이블이 있을때, 요청 사항은 아래와 같다.
Q: user_id와 로그인 일자를 기반으로, 해당 user_id가 로그인 일자 이전에 가장 최근 로그인한 일자가 언제인지 알고 싶다.
-> 추측건대, 요청자가 유저의 최근접속 기록을 통해 유저의 앱 혹은 웹 접속 리텐션을 확인하려고 하는 것 같다.
-> 결국 분석가가 나타내야할 테이블 결과는 가장 최근 로그인 일자가 있으면, 로그인 기준으로 바로 이전 로그인 날짜를 계산한 아래와 같다.
| user_id | 로그인 일자 | 기준 로그인 이전의 최근 로그인 일자 |
| 1 | 2021.03.01 | null |
| 1 | 2021.03.02 | 2021.03.01 |
| 2 | 2021.03.02 | 2021.01.19 |
| 2 | 2021.03.04 | 2021.03.02 |
| 4 | 2021.01.01 | null |
| 4 | 2021.03.01 | 2021.01.01 |
여기서 기준 로그인 이전의 최근 로그인 날짜가 Null값이면, 회원은 초기 진입인것이고 두 날짜를 계산하여 유저가 몇일만에 다시 접속했는지 파악할 수 있다.
레퍼런스에서는 위 테이플을 표현할 수 있는 쿼리 방법이 3가지가 있다고 했는데(e.g., 스칼라 서브쿼리, Self join, @변수 사용), 나는 스칼라 서브쿼리를 활용한게 쿼리를 이해하기도 편해서 자주 활용하게 된다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
-- 스칼라서브쿼리 --
SELECT user_id
, login_dt
, (SELECT login_dt
FROM t b
WHERE b.user_id = a.user_id
AND b.login_dt < a.login_dt
ORDER BY login_dt DESC
LIMIT 1
) lag_dt
FROM t a
ORDER BY user_id, login_dt
;
|
(1) 스칼라 서브쿼리로 같은 유저 아이디 기준으로 최근 로그인 이전의 로그인 일자 테이블을 만들어준다. (where b.user_id = a.user_id AND b.login_dt < a.login_dt)
(2) ORDER BY를 통해 최근 로그인 이전의 로그인 일자값을 최근 날짜 순으로 정렬한다.
(3) 이때, LIMIT이 1인 이유는 최근 로그인 이전의 로그인 일자값 1개를 붙이기 위해서다. 여기서는 가장 최근의 날짜가 될 것이다.
Reference: http://www.gurubee.net/article/84304
'블로그 > SQL' 카테고리의 다른 글
| [SQL] CASE WHEN으로 Pivot 하는 방법 (0) | 2022.08.31 |
|---|---|
| [MYSQL] 예약 테이블을 통해 RFM 테이블 만들기 방법 및 쿼리 (0) | 2022.06.01 |
| [MYSQL] LEFT JOIN과 COUNT시 NULL값 0으로 표시하는 방법 및 쿼리 (0) | 2022.05.22 |
