[SQL] PostgreSQL 값 조작

DATA

[SQL] PostgreSQL 값 조작

진자이 2023. 8. 4. 20:01

안녕하세요 진자이입니다 :) 이번 포스팅에서는 '데이터 분석을 위한 SQL 레시피' 책의 일부 내용을 정리해보고자 합니다. 미들웨어는 PostgreSQL 기준으로 작성되었으며, 값 조작에 대한 내용을 다룹니다.

OVERVIEW : 값 조작

(단일) 코드 값 레이블로 변경
(단일) URL에서 요소 추출
(단일) 문자열을 배열로 분해
(단일) 날짜와 타임스탬프 다루기
(단일) NULL값 처리
(다중) 문자열 연결
(다중) 여러 개 값 비교
(다중) 2개의 값 비율 계산
(다중) 날짜/시간 계산
(다중) IP 주소

(단일) 코드 값 레이블로 변경

마스터 테이블(mst_users)이 다음과 같이 구성되어 있다고 하고, 사용 장치를 레이블로 바꾸려고 한다.

SELECT
	user_id
	,CASE
		WHEN register_device = 1 THEN ' 데스크톱'
		WHEN register.device = 2 THEN '스마트폰'
		WHEN register.device = 3 THEN ' 애플리케이션'
	END AS device_name
FROM mst_users

(단일) URL에서 요소 추출

접근 로그(access_log) 테이블이 다음과 같이 구성되어 있고, 레퍼러로 어떤 웹 페이지를 거쳐 넘어왔는지 판별하고자 한다.

SELECT
	stamp
	,substring(referrer from 'https?:〃(「/]*)') AS referrer_host
FROM access_log

referrer_host의 결과는 www.other.com / www.other.net / www.ohter.com 처럼 나온다.

URL에서 경로와 요청 매개변수 값을 추출할 수도 있다.

SELECT
	stamp
	,url
	,substring(url from '//[A/]+([A?#]+)') AS path
	,substring(url from 'id=([A&]*)') AS id
FROM access_log
;

path의 결과는 다음과 같다. /video/detail, /video, /book/detail. id의 결과는 다음과 같다. 001, NULL, 002

+URL을 다루는 예는 책의 다른 예제에서 자세하게 다루므로 지금은 넘어간다.

(단일) 문자열을 배열로 분해

앞선 access_log 테이블을 사용하여 URL 경로를 슬래시로 분할해서 계층을 추출해보자.

SELECT
	stamp
	,url
	,split_part(substring(url from '//[A/]+([A?#]+)'),ソ'，2) AS pathl
	,split_part(substring(url from '//[A/]+([A?#]+)'), , 3) AS path2
FROM access_log

path1의 결과는 다음과 같다. video, video, book. path의 결과는 다음과 같다. detail, NULL, detail

(단일) 날짜와 타임스탬프 다루기

현재 날짜와 타임스탬프를 추출하는 쿼리는 다음과 같다.

SELECT
	CURRENT-DATE AS dt
	,CURRENT-TIMESTAMP AS stamp
	-- CURRENT_TIMESTAMP는 타임존이 적용된 타임스탬프이므로 타임존 적용을 하고싶지 않다면 LOCALTIMESTAMP 사용
   	 ,LOCALTIMESTAMP AS stamp2
;

문자열에서 지정한 값의 날짜와 시각 데이터를 추출하는 쿼리는 다음과 같다.

SELECT
	CAST('2016-01-30' AS date) AS dt
	,CAST(2016-01-30 12:00:00' AS timestamp) AS stamp
    
    -- type value를 사용할 수도 있다.
    date '2016-01-30' AS dt
    ,timestamp '2016-01-30 12:00:00' AS stamp
    
    -- value::type도 사용할수 있다.
    '2016-01-30'::date AS dt
    ,'2016-01-30 12:00:00'::timestamp AS stamp
;

타임스탬프 자료형에서 날짜/시각에서 특정 필드를 추출하는 쿼리는 다음과 같다.

SELECT
	stamp
	,EXTRACT(YEAR FROM stamp) AS year
	,EXTRACT(MONTH FROM stamp) AS month
	,EXTRACT(DAY FROM stamp) AS day
	,EXTRACT(HOUR FROM stamp) AS hour
FROM
	(SELECT CASTC2016-01-30 12：00：00' AS timestamp) AS stamp) AS t
;

타임스탬프를 나타내는 문자열에서 연,월,일,시을 추출하는 쿼리는 다음과 같다.

SELECT
	stamp
    -- substr 함수를 사용해도 같은 결과가 나온다.
	,substring(stamp, 1, 4) AS year
	,substring(stamp, 6, 2) AS month
	,substring(stamp, 9, 2) AS day
	,substring(stamp, 12, 2) AS hour
;

(단일) NULL값(결측값) 처리

쿠폰 사용 여부가 있는 구매 로그(purchase_log_with_coupon) 테이블에서 결손 값을 처리하는 쿼리를 짜보자.

구매액에서 할인 쿠폰 값을 제외한 매출 금액을 구하는 쿼리를 만들어보자.

SELECT
	purchase_id
	,amount
	,coupon
	,amount - coupon AS discount.amountl --결측값 있을 시 null 값으로 반환
	,amount - COALESCE(coupon, 0) AS discount_amount2 --결측값 0으로 반환해 처리
FROM
	purchase_log_with_coupon
;

* CTR(클릭 비율 : Click Through Rate)

* CVR(컨버전 비율 : Conversion Rate)

(다중) 문자열 연결

사용자 주소 정보(mst_user_location) 테이블에서 pref_name, city_name을 연결하는 코드를 만들어보자.

SELECT
	user_id
	,CONCAT(pref_name, city.name) AS pref_city
	,pref_name || city_name AS pref_city2 -- 같은 결과
FROM
	mst_user_location
;

(다중) 여러 개 값 비교

4분기 매출(quartely_sales) 테이블에서 여러 개의 값을 비교하는 쿼리를 만들어보자.

q1,q2 컬럼을 비교하여 매출 증감을 판정하는 쿼리는 다음과 같다.

SELECT
	year
	，ql
	，q2
	-- Ql과 Q2의 매출 변화 평가하기
	,CASE
		WHEN ql < q2 THEN ' + '
		WHEN ql = q2 THEN ''
	ELSE '-'
	END AS judge_ql_q2

	-- Ql과 Q2의 매출액의 차이 계산하기
	,q2 - ql AS diff_q2_ql
	
    -- Ql과 Q2의 매출 변화를 1, 0, -1로 표현하기
	,SIGN(q2 - ql) AS sign_q2_ql
FROM
	quarterly.sales
ORDER BY
	year
;

연간 최대/최소 4분기 매출을 찾는 쿼리는 다음과 같다.

SELECT
	year
	
    -- Q1~Q4의 최대 매출 구하기
	,greatest(ql, q2, q3, q4) AS greatest.sales

	-- Q1~Q4 최소 매줄 구하기
	,least(ql, q2, q3, q4) AS least.sales
FROM
	quarterly_sales
ORDER BY
	year
;

연간 평균 4분기 매출을 계산하는 쿼리는 다음과 같다.

SELECT
	year
	,(ql + q2 + q3 + q4) / 4 AS average
    
    -- NULL을 0으로 변환하고 평균값을 구하는 쿼리는 다음과 같다.
    ,(COALESCE(ql, 0) + C0ALESCE(q2, 0) + C0ALESCE(q3, 0) + COALESCE8% 0)) / 4
    AS average2
    
    -- NULL이 아닌 컬럼만을 사용해서 평균값을 구하는 쿼리
    (COALESCE(ql, 0) + C0ALESCE(q2, 0) + C0ALESCE(q3, 0) + C0ALESCE(q4, 0))
	/ (SIGN(COALESCE(ql, 0)) + SIGN(C0ALESCE(q2, 0))
	+ SIGN(C0ALESCE(q3, 0)) + SIGN(C0ALESCE(q4, 0)))
    AS average3
FROM
	quarterly.sales
ORDER BY
	year
;

(다중) 2개의 값 비율 계산

광고 통계 정보(advertisting_stats) 테이블에서 정수 자료형 데이터를 나누는 쿼리는 다음과 같다.

SELECT
	dt
	,ad_id
	
    -- 정수를 나눌 때는 자동적으로 실수로 변환
	,clicks / impressions AS ctr
	,CAST(clicks AS double precision) / impressions AS ctr2 --자료형 변환하고 사용하는 게 좋다.
	
    -- 실수를 상수로 앞에 두고 계산하면 암묵적으로 자료형 변환이 일어남
	,100.0 * clicks / impressions AS ctr_as_percent
    
    -- CASE 식으로 분모가 0일 경우를 분기해서 0으로 나누지 않게 만드는 방법
    ,CASE
		WHEN impressions > 0 THEN 100.0 * clicks / impressions
	END AS ctr_as_percent_by_case
    
    -- 분모가 0이라면 NULL로 변환한 뒤 0으로 나누지 않게 만드는 방법
	,100.0 * clicks / NULLIF(impressions, 0) AS ctr_as_percent_by_null

FROM
	advertising_stats
WHERE
	dt = '2017-04-01'
ORDER BY
	dt, ad_id
3

(다중) 날짜/시간 계산

등록 시간/생일을 포함하는 마스터(mst_users_with_dates) 테이블에서 회원 등록 1시간 후, 30분 전 시간, 등록일의 당므 달과 1달 전의 날짜를 계산하는 쿼리를 만들어보자.

SELECT
	user_id
	,register_stamp::timestamp AS register_stamp
	,register_stamp::timestamp + '1 hour'::interval AS after_l_hour
	,register_stamp::timestamp - '30 minutes'::interval AS before_30_minutes
    
	,register_stamp::date AS register.date
	,(register.stamp::date + '1 day'::interval)::date AS after_l_day
	,(register_stamp::date - '1 month'::interval)::date AS before_1_month
FROM mst_users_with_dates

두 날짜의 차이를 계산하는 쿼리도 계산해보자

SELECT
	user_id
	,CURRENT-DATE AS today
	,register_stamp：：date AS register.date
	,CURRENT-DATE - register.stamp：：date AS diff.days
FROM 
	mst.users.with.dates
;

사용자의 생년월일로 나이를 계산하는 쿼리는 다음과 같다.

SELECT
	user_id
	,CURRENT-DATE AS today
	,register_stamp::date AS register_date
	,birth_date::date AS birth.date
	, EXTRACT(YEAR FRO서 age(birth_date::date)) AS current.age
	,EXTRACT(YEAR FROM age(register_stamp::date, birth.date::date)) AS register_age
FROM 
	mst_users_with_dates
;

(다중) IP 주소

PostgreSQL에는 IP 주소를 다루기 위한 inet 자료형이 구현되어 있다.

SELECT
	-- inet 자료형의 대소 비교
	CAST('127.0.0.1' AS inet) < CAST('127.0.0.2' AS inet) AS It
	,CAST('127.0.0.1' AS inet) > CAST('192.168.0.1' AS inet) AS gt
    
    -- 네트워크 범위에 IP 주소가 포함되는지 판정
    ,CAST('127.0.0.1' AS inet) << CAST('127.0.0.0/8' AS inet) AS is_contained
    
    -- IP 주소에서 4개의 10진수 부분을 정수로 추출하는 쿼리
	,CAST(split_part(ip, '.', 1) AS integer) AS ip_part_l
	,CAST(split_part(ip, '.', 2) AS integer) AS ip_part_2
	,CAST(split_part(ip, '.', 3) AS integer) AS ip_part_3
	,CAST(split_part(ip, '.', 4) AS integer) AS ip_part_4
    
    -- IP 주소를 정수 !자료형!로 추출하는 쿼리
    ,CAST(split_part(ip,'.', 1) AS integer) * 2^24
	+ CAST(split_part(ip,'.', 2) AS integer) * 2^16
	+ CAST(split_part(ip,'.', 3) AS integer) * 2^8
	+ CAST(split_part(ip,'.', 4) AS integer) * 2^0
	AS ip_integer2
    
    -- IP 주소를 0으로 메울수도 있다. 
    ,lpad(split_part(ip, '.', 1), 3, '0')
	|| lpad(split_part(ip, '.', 2), 3, '0')
	|| lpad(split_part(ip, '.', 3), 3, '0')
	|| lpad(split_part(ip, '.', 4), 3, '0')
	AS ip_padding

FROM
	(SELECT '192.168.0.1, AS ip) AS t
;

저작자표시