[데이터 분석을 위한 SQL 레시피] 17강 - 데이터를 조합해서 새로운 데이터 만들기

목차

1) IP 주소를 기반으로 국가와 지역 보완하기

2) 주말과 공휴일 판단하기

3) 하루 집계 범위 변경하기

 

7장 데이터 활용의 정밀도를 높이는 분석 기술

17강 데이터를 조합해서 새로운 데이터 만들기

오픈, 외부 데이터 활용하기

1) IP 주소를 기반으로 국가와 지역 보완하기

사용자 로그에 IP 주소가 있다면 국가와 지역을 보완할 수 있음. → 사용자를 지역별로 구분하거나 타임존에 따라 구분해서 분석할 수 있음.

postgreSQL 에서 IP 주소를 다룰 때는 inet 자료형 사용함.

 

• GeoLite2 지오로케이션 데이터베이스 다운 받기 + 액션 로그의 IP 주소와 결합해서 국가와 지역 정보 보완.

#17-2 액션 로그의 IP 주소로 국가와 지역 정보를 추출하는 쿼리

SELECT
	a.ip
	, l.continent_name
	, l.country_name
	, l.city_name
	, l.time_zone
FROM
	action_log AS a
	LEFT JOIN
		mst_city_ip AS i
		ON a.ip::inet << i.network  #ip 주소가 네트워크에 포함되었는지 확인할 때 << 연산자 사용
	LEFT JOIN
		mst_locations AS l
		ON i.geoname_id = l.geoname_id
;	

 

2) 주말과 공휴일 판단하기

월별로 목표 세울 때, 해당 연도와 해당 월에 있는 주말과 공휴일이 얼마나 되는지 계산하면 더 정확한 목표를 세울 수 있음.

+날짜 데이터 사용하면 토요일, 일요일 파악할 수 있지만, 공휴일은 판정할 수 없음 → 공휴일 정보 테이블 만들어서 사용.

# 17-3 주말과 공휴일을 정의하는 법

CREATE TABLE mst_calendar (
	year integer
	, month integer
	, day integer
	, dow varchar(10)  #ex. Thu, Fri, Sat
	, dow_num integer
	, holiday_name varchar(255)  # ex. 신정
);

 

• 주말과 공휴일 판정하기

# 17-4 주말과 공휴일을 판정하는 쿼리

SELECT
	a.action
	, a.stamp
	, c.dow
	, c.holiday_name
	-- 주말과 공휴일 판정
	, c.dow_num IN(0,6)  --0은 일요일, 6은 토요일.
		OR c.holiday_name IS NOT NULL -- 공휴일 판정하기
		AS is_day_off
FROM
	access_log AS a
	JOIN
		mst_calendar AS c
		-- 액션 로그의 타임스탬프에서 연, 월, 일을 추출하고 결함하기
		ON CAST(substring(a.stamp,1,4) AS int) = c.year
		AND CAST(substring(a.stamp,6,2) AS int) = c.month
		AND CAST(substring(a.stamp, 9,2) AS int) = c.day

 

3) 하루 집계 범위 변경하기

자정 전후의 사용 비율이 꽤 많기 때문에 오전 4시 정도를 기준으로, 오전 4시부터 다음 날 오전 3시 59분 59초까지를 하루로 집계할 수 있게 데이터 가공.

→ 액션 로그 사용해서 오전 0시부터 오전 4시까지의 데이터를 전 날의 데이터로 취급하게 데이터 가공 ( 하루 집계 범위를 오전 4시에서 시작하게 변경)

→ 타임스탬프의 시간을 4시간 당기면 됨.

 

원래 날짜 - raw_date, 당긴 날짜 - mod_date

# 17-5 날짜 집계 범위를 오전 4시부터로 변경하는 쿼리

WITH
action_log_with_mod_stamp AS(
	SELECT *
		--4시간 전의 시간 계산하기
		, CAST(stamp::timestamp - '4 hours'::interval AS text) AS mod_stamp
	FROM action_log
)

SELECT
	session
	, user_id
	, action
	, stamp
	-- 원래 타임스탬프(raw_date)와 4시간 후를 나타내는 타임스탬프(mod_date) 추출하기
	, substring(stamp, 1, 10) AS raw_date
	, substring(mod_stamp, 1, 10) AS mod_date
FROM action_log_with_mod_stamp;



# 실행 결과
-- 11월 4일 오전 0시 ~ 3시 59분에 있는 로그가 11월 3일(mod_date)로 판정되어있음