조금 더 현실적인 문제해결을 위한 SQL 쿼리짜기 2탄 - R을 곁들인 코드

0. 들어가며

 

이 문제 유형은 한 헬스케어 회사에서 백엔드 개발자로 있는 쭈니에게 받은 문제유형이다.

쭈니는 나에게도 풀어보라고 던져준다.
현업의 문제를 간접적으로 경험하며 어떤 요청사항들을 받는지 알 수 있어 좋다 :) 진심이다.

이번에 받은 문제유형 데이터가 발생하지 않은 날짜의 데이터 집계하기 로 요약할 수 있다.

이런 유형이 어려운 유형 중 하나다.
하지만 그렇게 큰 걱정은 없다. 나에겐 구글이 있으니깐. 외쳐 갓구글

 

앞선 글인

https://dangdo.tistory.com/70

조금 더 현실적인 문제 해결을 위한 SQL 쿼리짜기

에서의 문제 중 어려운 점 중 하나도 9월 달에 구매한 이력이 없는 고객을 어떻게 알지? 였다.
구매한 이력이 없다는건 데이터가 없다는 것이니깐.

 

1. 어떤 결과물을 원하니

현업에서의 문제는 데이터를 살펴보기 이전에, 어떤 형식의 데이터 결과물을 원하는지?
그 결과물을 현재 가지고 있는 데이터로 만들 수 있는가 여부 순으로 따질 것이다.
쭈니가 요청받은 사항을 좀 더 구체적으로 적으면 날짜별로 특정 이벤트를 일으킨 유저의 수를 카운팅 하는 것 이다.

 

우리가 원하는 데이터 결과물은 아래와 같은 형식이다.

date	Event A	Event B	Event C
2022-01-12	1	0	0
2022-01-13	0	1	0
2022-01-14	0	1	0
2022-01-15	2	1	0
2022-01-16	1	1	1
2022-01-17	0	0	0

2022년 1월 12일 Event A가 발생한 유저의 수는 1명, Event B가 발생한 유저의 수는 0명, Event C가 발생한 유저는 0명.
식으로 날짜별 특정 이벤트의 빈도를 관찰하고자 하는 것이다.

이러한 형태의 분석은 광범위하게 적용될 수 있다. 게임 이라면 일별 아이템 판매 수, 앱 서비스라면 유저들의 주요피처를 얼마나 사용하는지 알아보기 위해서 등.. 카운팅하는 것은 중요하다.

이러한 집계는 결국 Row데이터를 기반으로 한다.

 

그럼 내가 만든 예시 Row데이터를 살펴보자.

테이블명 : user_action_2022

uid	action	ts
1	event A	2022-01-12 08:30:12
2	event B	2022-01-13 08:35:12
1	event B	2022-01-14 08:40:12
2	event A	2022-01-15 08:41:12
3	event A	2022-01-15 08:30:12
3	event B	2022-01-15 11:30:12
4	event B	2022-01-16 12:30:12
5	event A	2022-01-16 11:32:12
5	event C	2022-01-16 11:33:12

 

유저 아이디와 어떤 이벤트가 발생했는지, 그 이벤트의 발생시간을 기록했다.

9개의 관측치와 3개 컬럼으로 이루어져있다.

2. 그래서 정확한 문제가 뭐냐구?

원하는 결과물에서 모든 날짜에 적어도 하나의 이벤트가 발생했다.

단 2022년 1월 17일을 제외하고.

date	Event A	Event B	Event C
2022-01-12	1	0	0
2022-01-13	0	1	0
2022-01-14	0	1	0
2022-01-15	2	1	0
2022-01-16	1	1	1
2022-01-17	0	0	0

 

우리가 가진 데이터에는 2022년 1월 17일 데이터가 없는데 어떻게 SQL을 활용해 위 결과물을 만들까?

 

다양한 문제해결방법이 있겠지만, 내가 해결한 방법은

 

데이터 없어?

 

그럼 데이터를 만들어주자!

 

라는 간단무식한 방법을 썼다.

좀 더 적확히 서술하면 달력 테이블을 만들었다.

1년은 365일이니깐, 365개의 행을 가져야겠지만, 이건 예시이기 때문에

달력테이블은 아래와 같이만 만들어봤다.

테이블명 : calendar_2022

no	date
1	2022-01-12
2	2022-01-13
3	2022-01-14
4	2022-01-15
5	2022-01-16
6	2022-01-17

달력 테이블 완성  ^^

 

3.1 이제 코드만 짜요 짜요

내가 접근한 방식은 캘린더 테이블(calendar_2022)를 기반으로 유저 데이터(user_action_2022)를 조인한다.
아래와 같이.

select cld.date, ua.uid, ua.action, ua.ts
from calendar_2022 as cld
left join user_action_2022 as ua
on cld.date = date(ua.ts)

쿼리 결과

 

캘린더테이블의 date와 유저테이블 ts컬럼값의 연,월,일이 같은 경우로 합쳐졌다!

 

어때요 코드가 참 쉽죠?

 

3.2 집계 그거 어떻게 할거야? (나는 R로 한다)

 

안타깝게도 난 SQL에서 날짜별로 집계하는 방식을 R만큼 하지 못하겠다.

그래서 난 R을 썼다.(어떻게든 결과를 내면 되니깐... 어떤 수로든 해결한다 마인드)

위 쿼리결과를 csv파일로 저장하고 R Studio에서 데이터를 로드한다.

4줄로 해결했다.

글 제목은 쿼리짜기로 하겠습니다. R코드를 곁들인

 

library(reshape2) #dcast를 쓰기위한 라이브러리 로드
setwd("c:/R") #워킹디렉토리 설정
df <- read.csv("calendar.csv") #데이터 로드
dcast(df, date~action, fun.aggregate = length, value.var = "uid") #데이터집계

> dcast(df, date~action, fun.aggregate = length, value.var = "uid")
        date event A event B event C NULL
1 2022-01-12       1       0       0    0
2 2022-01-13       0       1       0    0
3 2022-01-14       0       1       0    0
4 2022-01-15       2       1       0    0
5 2022-01-16       1       1       1    0
6 2022-01-17       0       0       0    1

위와 같이 2022년 1월 17일에 이벤트A, B, C에 대해 집계결과가 0건으로 표시됨을 확인할 수 있다.
NULL컬럼을 보기 싫다면 해당 열을 제거하면 완료.

date	Event A	Event B	Event C
2022-01-12	1	0	0
2022-01-13	0	1	0
2022-01-14	0	1	0
2022-01-15	2	1	0
2022-01-16	1	1	1
2022-01-17	0	0	0

보고 싶었던 결과물과 R 콘솔의 출력물이 같음을 확인할 수 있다.

 

 

4. 마치며

 

쭈니에게 현업의 고민을 전달받으면서 문제를 푸는 재미가 있다.

완전히 풀기 전까지는 '어떻게 풀지? 어떻게 풀지?' 고민을 오랜시간 했다면,

정작 풀고나면 별로 어려운게 아니었네? 라는 느낌을 받는다.