조금 더 현실적인 SQL 쿼리짜기 - R 코드로 같은 결과만들기

0. 들어가며

 

앞선 두 글을 통해 쿼리문을 통해 특정상황의 데이터를 추출하고,

다음 글에서는 리팩토링하는 과정을 거쳤다.

그동안 SQL을 공부한다고 R을 잘 안다루었다.

겸사겸사 같은 Raw데이터가 주어져있을 때 SQL과 같은 결과물을

R코드로 작성해보고자 마음 먹었다.

1. 데이터 살펴보기

R에서 작업하기 위해서는

데이터를 다루기 위해서 데이터와 일부 라이브러리를 불러와야한다.
패키지는 dplyr과 lubridate를 사용했다.

 

library(dplyr)
library(lubridate)
setwd("c:/R")
df <- read.csv("mysql_test.csv")
str(df)

 

df이란 이름으로 데이터를 불러오고, 데이터의 구조를 확인한다.

'data.frame':	17 obs. of  3 variables:
 $ id         : int  1 2 3 4 5 6 7 8 9 10 ...
 $ customer_id: int  11 22 33 44 55 66 77 11 33 55 ...
 $ pay_date   : Factor w/ 13 levels "2021-08-01","2021-08-02",..: 1 2 2 3 3 3 4 5 6 7 ...

pay_date 컬럼의 데이터 형식이 Factor이다.
날짜 데이터를 다루기 위해서 이를 적절한 형태로 변환시켜야한다.
이 때 필요한 것이 lubridate 패키지이다.

 

앞서 lubridate 패키지를 다룬 적이 있다.

아래 글을 참조하자.

https://dangdo.tistory.com/36?category=302418 

시계열 데이터 밑바닥부터 다루기 in R (1)

 

df$pay_date <- as.Date(df$pay_date)

위와 같은 코드를 통해 데이터 형식을 Date 포맷으로 바꾼다.

 

> str(df)
'data.frame':	17 obs. of  3 variables:
 $ id         : int  1 2 3 4 5 6 7 8 9 10 ...
 $ customer_id: int  11 22 33 44 55 66 77 11 33 55 ...
 $ pay_date   : Date, format: "2021-08-01" "2021-08-02" "2021-08-02" "2021-08-03" ...

포맷이 Date로 잘 바뀐 것을 확인할 수 있다.

 

2. 특정조건의 데이터 선택하기

첫 글에서 그랬듯

우선 8월에 구매를 했고(조건1), 구매건수는 1건(조건2) 의 데이터를 뽑아보자.

R에 가장 강력한 기능 중 하나는 %>% 체인 연산자인데, 조금 과장해서 무지성으로 해도

체인 연산자 하나만 있으면 뚝딱이다. (체인연산자 사랑해)

조건1과 조건2에 해당하는 데이터를 df2라는 이름으로 저장했다.

 

처음 글에서 join기능을 사용했기 때문에 R에서도 마찬가지로

join 기능을 사용하려고 하기 때문이다.

 

df2 <- df %>%
  filter(month(pay_date)==8) %>% #8월달 구매 #조건1
  count(customer_id) %>% #customerid로 갯수 카운팅
  filter(n==1) #구매 건수 1건 #조건 2​

df2결과를 출력하면 아래와 같다.

앞서 SQL에서 조건1과 조건2를 걸어 중간결과를 출력했을 때와 같은 customer_id 와 같다.

df2 
  customer_id n
1          22 1
2          44 1
3          66 1
4          77 1

 

3. 조건3 만족시키기

 

가장 첫 글에서는 조인을 하면 아래와 같다.

left_join(df2,df, by="customer_id")
  customer_id n id   pay_date
1          22 1  2 2021-08-02
2          44 1  4 2021-08-03
3          66 1  6 2021-08-03
4          77 1  7 2021-08-04
5          77 1 15 2021-09-05

위 결과와 같이 customer_id '77'이 '2021-08-04', '2021-09-05'에 주문한 것을 알 수 있다.

조건 3 : 9월에 구매한 기록이 없는 회원 제외 이므로

따라서 9월에 구매한 기록이 없는 고객을 출력하길 원하기 때문에 customer_id를 제거해야한다.

 

이를 위해서서는 아래와 같이 코드를 짤 수 있다.

> left_join(df2,df, by="customer_id") %>% #df2를 기준으로 레프트조인
+   count(customer_id) %>% #customer_id 카운팅
+   filter(n==1) # 중복되지 않은 데이터만 필터링
Using `n` as weighting variable
ℹ Quiet this message with `wt = n` or count rows with `wt = 1`
  customer_id n
1          22 1
2          44 1
3          66 1

결국 22,44,66 회원이 우리가 목표로 했던

 

8월에 한 번 구매한 고객 중 9월 달에 구매한 이력이 없는 고객

 

의 답이 된다.

다시 한 번 코드만을 정리하자면

df2 <- df %>%
  filter(month(pay_date)==8) %>%
  count(customer_id) %>%
  filter(n==1)

left_join(df2,df, by="customer_id") %>%
  count(customer_id) %>%
  filter(n==1)

위의 코드로 정리할 수 있겠다.

 

4. 마무리하며

 

개인적으로 R의 %>% 체인연산자가 너무나 강력해

높은 가독성을 확보함과 동시에

큰 고민없이 코드를 짤 수 있었다.

 

또 오랜만에 R을 사용할 수 있어서 재밌었다.