SQL vs. Pandas 비교 (2) - JOIN, PIVOT, WINDOW FUNCTIONS

지난 글에 이어서 SQL의 쿼리문과 Pandas 코드를 계속 비교해보도록 하겠습니다. 이번에는 조금 심화 레벨의 JOIN, PIVOT, WINDOW FUNCTIONS 에 대해 알아보겠습니다.

 

지난 글에서는 NBA 선수 연봉 정보 테이블을 예시로 했었다면, 이번에는 렌터카 회사의 예약 데이터를 활용할 예정입니다. 지금부터 여러분은 렌터카 회사의 데이터 분석을 담당하고 있는 직원이고, 다음과 같이 예약정보(Reservation), 고객정보(Customer) 라는 가상의 테이블이 있다고 해보겠습니다.  

 

데이터는 2016년 4월에서 5월까지 렌터카 예약정보를 가지고 있습니다. 

 

예약 정보(Reservation)

Seq	Region	System	Customer_id	Car_name	Start_date	Start_time	End_date	End_time	Price
1	JEJU	App	186	Staria	2016-04-18	8:30:00	2016-05-22	14:52:00	223500
2	GYEONGGI	App	127	Avatne	2016-04-18	14:50:00	2016-05-06	21:45:00	542340
3	GYEONGGI	Homepage	100	G90	2016-04-18	18:30:00	2016-05-01	15:31:00	1230000
4	GYEONGGI	Homepage	126	K3	2016-04-19	9:00:00	2016-05-01	9:11:00	86600
5	JEJU	Homepage	129	Avatne	2016-04-19	9:00:00	2016-05-01	18:02:00	92000
6	BUSAN	App	131	K5	2016-04-20	11:30:00	2016-05-01	16:29:00	91000
7	JEJU	App	190	Staria	2016-04-20	16:00:00	2016-05-30	9:18:00	382000
8	JEJU	App	101	K3	2016-04-20	18:10:00	2016-05-21	9:10:00	1469000
9	BUSAN	Affiliate	132	K5	2016-04-21	12:00:00	2016-05-01	10:05:00	194000
10	BUSAN	Affiliate	108	Carnival	2016-04-21	18:00:00	2016-05-02	11:06:00	100000

 

고객 정보(Customers)

Id	Name	grade
100	최**	Silver
103	김**	Silver
109	박**	Gold
134	홍**	Silver
136	김**	Gold
147	홍**	Gold
173	박**	Gold
175	장**	Gold
186	서**	Gold
188	엄**	Bronze

 

[JOIN] 대여고객의 추가 회원정보 분석

 

첫 번째는 1개가 아닌 2개 이상의 테이블을 활용해 필요한 데이터를 JOIN 해서 붙여주는 작업을 해보겠습니다. 위 테이블에서 보신 것 처럼 예약정보 테이블에는 고객의 이름이나 회원등급을 알 수 없기 때문에 고객정보 테이블에서 데이터를 끌어와야 합니다. 이럴 때 우리는 JOIN을 사용해주면 데이터를 가져 올 수 있습니다.

 

이번에 사용할 JOIN은 예약정보 테이블에 나와 있는 회원ID를 기준으로 LEFT JOIN을 사용하여 고객정보 테이블에 회원 이름과 등급을 가져와보도록 하겠습니다. 

 

SQL

SELECT *
FROM reservation r LEFT JOIN customers cs ON r.Customer_id = cs.id;

 

Pandas

pd.merge(reservation, customers, how='left', left_on='Customer_id', right_on='Id')

 

✏️ 실행결과

 

Seq	Region	System	Customer_id	Car_model	Price	Id	Name	Customer_grade
1	JEJU	App	186	C010	223500	186	서**	Silver
2	GYEONGGI	App	127	C003	542340	127	김**	Silver
3	GYEONGGI	Homepage	100	C007	1230000	100	최**	Gold
4	GYEONGGI	Homepage	126	C002	86600	126	박**	Silver
5	JEJU	Homepage	129	C003	92000	129	김**	Gold
6	BUSAN	App	131	C004	91000	131	최**	Gold
7	JEJU	App	190	C010	382000	190	남**	Gold
8	JEJU	App	101	C002	1469000	101	운**	Gold
9	BUSAN	Affiliate	132	C004	194000	132	최**	Gold
10	BUSAN	Affiliate	108	C009	100000	108	김**	Bronze

 

보시는 것 처럼 기존 테이블에 고객정보인 이름과 회원등급이 붙여진걸 확인하실 수 있습니다.

 

 

[PIVOT] 월별/지역별 대여건수를 크로스탭으로 요약

 

데이터를 추출했다면 이번에는 크로스탭 형태로 추출한 데이터를 요약해보도록 하겠습니다. 참고로 데이터가 많지 않다면 굳이 SQL이나 Pandas를 이용하지 않아도 엑셀의 피봇 테이블을 이용하면 쉽게 만들 수도 있습니다. 

 

테이블을 보시면 대여지역이 서울, 경기, 부산, 제주로 나뉘어져 있습니다. 각 월별로 해당 지역에서 발생한 대여건수가 얼마나 되는지 크로스탭 형태로 요약할 것입니다. 아래 실행 결과를 먼저 보시면 어떤 식으로 데이터를 가공하려고 하는지 이해하실 수 있습니다.

 

SQL

SELECT start_month
	 , MAX(CASE WHEN region = 'Seoul' THEN cnt ELSE NULL END) AS 'Seoul'
	 , MAX(CASE WHEN region = 'Busan' THEN cnt ELSE NULL END) AS 'Busan'
	 , MAX(CASE WHEN region = 'Gyeonggi' THEN cnt ELSE NULL END) AS 'Gyeonggi'
	 , MAX(CASE WHEN region = 'Jeju' THEN cnt ELSE NULL END) AS 'Jeju'
FROM (
	SELECT LEFT(start_date, 7) AS Start_month, region, COUNT(*) AS cnt
	FROM reservation
	GROUP BY LEFT(start_date, 7), region
	ORDER BY start_month
) AS t
GROUP BY start_month;

 

Pandas

reservation['Start_month'] = reservation['Start_date'].str[:7]
reservation.pivot_table(index='Start_month', columns='Region', values='Reservation_id', aggfunc='count').round()

 

✏️ 실행결과

 

start_month	Seoul	Busan	Gyeonggi	Jeju
2016-04	8	8	13	12
2016-05	16	10	13	12

 

[WINDOW FUNCTIONS] 7일 이동평균 매출 구하기

 

이번에는 윈도우 함수를 이용해 매출의 7일 이동평균을 구해보겠습니다. 이동평균은 주로 주식시장에서 기술적 분석을 할때 쓰이는 분석 방법 중 하나인데요. 여기서는 SQL과 Pandas를 이용해 이동평균을 어떻게 계산하는 지를 먼저 보여드리고 이동평균에 대한 자세한 설명은 다음 번에 좀 더 깊이있게 포스팅을 하겠습니다.

 

SQL

SELECT start_date
     , SUM(price) AS total_amount
     , CASE WHEN 7 = COUNT(*) OVER(ORDER BY start_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
     THEN AVG(SUM(price)) OVER(ORDER BY start_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
     END AS moving_avg
FROM reservation
GROUP BY start_date
ORDER BY start_date;

 

Pandas

total_amount = reservation.pivot_table(index='Start_date', values='Price', aggfunc='sum')
total_amount['moving_avg'] = total_amount['Price'].rolling(window=7).mean().round(0)
total_amount

 

 

✏️ 실행결과

 

start_date	total_amount	moving_avg
2016-04-18	1995840
2016-04-19	178600
2016-04-20	1942000
2016-04-21	294000
2016-04-22	953100
2016-04-23	621700
2016-04-24	854399	977091.2857
2016-04-25	672700	788071.2857
2016-04-26	237450	796478.4286
2016-04-27	909500	648978.4286

 

이것으로 SQL 과 Pandas를 비교하는 포스팅을 마치겠습니다.

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.