[데이터 분석을 위한 SQL 레시피] 6강 - 여러 개의 값에 대한 조작

목차

1) 문자열 연결하기

2) 여러개의 값 비교하기

3) 2개의 값 비율 계산하기

4) 두 값의 거리 계산하기

5) 날짜/시간 계산하기

6) IP 주소 다루기

 

1) 문자열 연결하기

#6-1 문자열을 연결하는 쿼리

SELECT
	user_id
	,CONCAT(pref_name, city_name) AS pref_city
# = ,pref_name || city_name AS pref_city (postgresql은 || 연산자도 가능)

FROM
	mst_user_location
;

#실행결과
user_id  |  pref_city
----------------------
U001     |  서울특별시강서구

대부분의 미들웨어에서 CONCAT 함수를 사용해 원하는 만큼의 문자열을 연결할 수 있음.

하지만 Redshift의 concat은 매개변수에 2개의 문자열만 전달할 수 있음. 대신 redshift의 경우는 concat 대신 || 연산자를 사용하면 됨.

 

2) 여러 개의 값 비교하기

• 분기별 매출 증감 판정하기

각 컬럼의 크고 작음 비교는 case 식, 차이는 빼기, 차이와 sign 함수 조합하면 case 식보다 간단하게 값의 증감 판정 가능

sign 함수 → 매개변수가 양수라면 1, 0이라면 0, 음수라면 -1을 리턴하는 함수

SELECT
	year, 
	q1,
	q2
-- q1과 q2의 매출 변화 평가하기
	, CASE
				WHEN q1 < q2 THEN '+'
				WHEN q1 = q2 THEN ' '
				ELSE '-'
		END AS judge_q1_q2
-- q1과 q2의 매출액의 차이 계산하기
	, q2 - q1 AS diff_q2_q1
-- q1과 q2의 매출 변화를 1, 0, -1로 표현하기
	,SIGN(q2-q1) AS sign_q2_q1

FROM
	quarterly_sales
ORDER BY
	year
;

 

• 연간 최대/최소 4분기 매출 찾기

컬럼 값에서 최댓값 또는 최솟값을 찾을 때는 greatest 또는 least 함수 사용

ex) greatest(q1, q2, q3, q4) AS greatest_sales

MIN 과 MAX는 다중 ROW에서 그룹을 지어서 하나의 ROW로 표현할때 최대값과 최소값을 가져오지만 → 집계함수

GREATEST와 LEAST는 하나의 ROW나 여러개의 값들중에 최대,최소값을 가져올 수 있다.

 

MAX, MIN은 레코드중에서 최대/최소값을 구하는 것이고(컬럼 안에서 최대,최소 구할 때), GREATEST, LEAST는 여러 컬럼 값 중에서 최대/최소값을 구하는 것이다.

 

greatest는 여러컬럼중에 최댓값이 나오는 거라 테이블처럼 나오니까 max랑 같이 쓰면 오류남.

max를 groupby랑 같이 쓰면 괜찮음.

 

• 연간 평균 4분기 매출 계산하기

단순한 연산 → (q1 + q2 + q3 + q4) / 4 AS average

-NULL 값을 사칙 연산하려면 COALESCE 함수 사용해 적절한 값으로 변환해야하지만, 0으로 변환해버리면 매출합계를 4로 나누게 되어 평균값이 크게 낮아짐.

→ null 이 아닌 컬럼의 수를 세서 나눠야함.

# 6-6 NULL이 아닌 컬럼만을 사용해서 평균값을 구하는 쿼리

SELECT
	year
	, (COALESCE(q1,0) + COALESCE(q2,0) + COALESCE(q3,0) + COALESCE(q4,0))
	/ (SIGN(COALESCE(q1,0)) + SIGN(COALESCE(q2,0)) + SIGN(COALESCE(q3,0)) + SIGN(COALESCE(q4,0))) AS average
FROM
	quarterly_sales
ORDER BY
	year
;

 

3) 2개의 값 비율 계산하기

• 정수 자료형의 데이터 나누기

postgreSQL의 경우, 정수 자료형이 두 컬럼끼리 나누면 계산 결과도 정수 자료형이 되어버려 0이 나옴.

왜?

→ CAST 함수를 사용해 clicks를 double precision 자료형으로 변환하고 계산해여 결과도 double precision 자료형으로 나옴.

# 6-7

SELECT
	dt
	,ad_id

--hive,redshift 등은 정수를 나눌 때 자동으로 실수로 변환되어 그냥 나눠주면 됨
--postgresql의 경우 정수를 나누면 소수점이 잘리므로 명시적으로 자료형 변환
	, CAST(clicks AS double precision) / impressions AS ctr
-- 실수를 상수로 앞에 두고 계산하면 암묵적으로 자료형 변환이 일어남
	,100.0 * clicks / impressions AS ctr_as_percent

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

 

• 0으로 나누는 것 피하기

1. CASE 식을 사용해 impressions가 0인지 확인하는 것.

impressions가 0보다 큰 경우 CTR을 계산하고 이외의 경우에는 NULL을 출력

→ null 포함한 데이터의 연산 결과가 모두 null 이 되는 SQL의 성질. 0으로 나누는 것을 피할 수 있음.

 

+ifnull vs nullif

ifnull(1,2) : 1이 null 이면 2 반환 -> coalesce 함수와 같은 기능. coalesce는 1,2 뿐만 아니라 여러 값을 넣을 수 있음

nullif(1,2) : 1과 2가 같으면 null 반환 -> nullif(impression, 0) : impression이 0이면 null 반환

# 6-8 0으로 나누는 것을 피해 CTR을 계산하는 쿼리

SELECT
	dt
	,ad_id
--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
ORDER BY
	dt, ad_id
;

 

4) 두 값의 거리 계산하기

• 숫자 데이터의 절댓값, 제곱 평균 제곱근(RMS) 계산하기

1- 절댓값 계산 → ABS 함수(abstract)를 사용

2- 제곱평균제곱근 → 두 값의 차이를 제곱한뒤(POWER 함수), 제곱근을 적용해서 나오는 값(SORT 함수)

#6-9 일차원 데이터의 절댓값과 제곱 평균 제곱근을 계산하는 쿼리

SELECT
	abs(x1-x2) AS abs
	, sqrt(power(x1 - x2, 2)) AS rms
FROM location_1d
;
# 일차원 데이터에선 두 값 같음

 

• xy 평면 위에 있는 두 점의 유클리드 거리 계산하기

→ 제곱 평균 제곱근 사용, postgresql에는 point자료형이라고 불리는 좌표를 다루는 자료구조가 있음.

# 6-10 이차원 테이블에 대해 제곱 평균 제곱근(유클리드 거리)을 구하는 거리

SELECT
	sqrt(power(x1 - x2, 2) + power(y1 - y2, 2)) AS dist
	, point(x1,y1) <-> point(x2,y2) AS dist
FROM location_2d
;

 

5) 날짜/시간 계산하기

나이 → 생년월일 저장하고, 이후에 계산해서 나이 구함.

# 6-11 미래 또는 과거의 날짜, 시간을 계산하는 쿼리
# 회원 등록시간 1시간 후와 30분 전의 시간, 등록일의 다음 날과 1달 전의 날짜를 계산하는 쿼리, postgresql 기준

SELECT 
	user_id
	, register_stamp::timestamp AS register_stamp
	, register_stamp::timestamp + '1 hour' :: interval AS after_1_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_1_day
	,(register_stamp::date - '1 month'::interval)::date AS before_1_month

FROM mst_users_with_dates
;

#실행결과
-[RECORD 1]--------
user_id   |  U001
register_stamp  | 2016-02-28 10:00:00
after_1_hour  |  2016-02-28 11:00:00
before_30_minutes  |  2016-02-28 09:30:00
register_date  |  2016-02-28
after_1_day  |  2016-02-29
before_1_month  |  2016-01-28

 

• 날짜 데이터들의 차이 계산하기

#6-12 두 날짜의 차이를 계산하는 쿼리

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
;

 

• 사용자의 생년월일로 나이 계산하기

age 함수 사용 → 리턴값은 interval 자료형의 날짜 단위이므로, EXTRACT 함수로 연도(YEAR) 부분만 추출해야함.

디폴트로 현재 나이를 리턴하지만, 특정 날짜를 지정하면 해당 날짜에서의 나이를 리턴함.

#6-13 age 함수를 사용해 나이를 계산하는 쿼리

SELECT 
	user_id
	,CURRENT_DATE AS today
	,register_stamp::date AS register_date
	,birth_date::date AS birth_date
	,EXTRACT(YEAR FROM 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
;

#6-16 등록 시점과 현재 시점의 나이를 문자열로 계산하는 쿼리

SELECT
	user_id
	,substring(register_stamp,1,10) AS register_date
	,birth_date
--등록 시점의 나이 계산하기
	,floor(
		( CAST(replace(substring(register_stamp,1,10),'-','') AS integer)
				- CAST(replace(birth_date, '-','') AS integer)
		) / 10000
	) AS register_age
--현재 시점의 나이 계산하기
	,floor(
		( CAST(replace(CAST(CURRENT_DATE AS text),'-','') AS integer)
				- CAST(replace(birth_date, '-' , '') AS integer)
		) /10000
	) AS current_age

FROM mst_users_with_dates
;

 

6) IP 주소 다루기

• IP 주소 자료형 활용하기

inet 자료형 사용.

# 6-17 inet 자료형을 사용한 IP 주소 비교 쿼리

SELECT
	CAST('127.0.0.1' AS inet) < CAST('127.0.0.2' AS inet) AS lt
	,CAST('127.0.0.1' AS inet) > CAST('192.168.0.1' AS inet) AS gt
;

#실행결과
lt | gt
--------
t  | f

추가로, address/y 형식의 네트워크 범위에 IP 주소가 포함되는지도 판정 가능 → >> 또는 << 연산자 사용

 

• 정수 또는 문자열로 IP 주소 다루기
  • IP 주소를 정수 자료형으로 변환하기

  # 6-19 IP 주소에서 4개의 10진수 부분을 추출하는 쿼리
  
  SELECT
  	ip
  	,CAST(split_part(ip, '.', 1) AS integer) AS ip_part_1 #.을 기준으로 나눈 것 중 첫번째 인수
  	,CAST(split_part(ip, ',', 2) AS integer) AS ip_part_2
  
  FROM
  	(SELECT CAST('192.168.0.1' AS text) AS ip) AS t
  ;

  # 6-20 IP 주소를 정수 자료형 표기로 변환하는 쿼리
  
  SELECT
  	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_integer
  
  FROM
  	(SELECT CAST('192.168.0.1' AS text) AS ip) AS t
  ;

  • IP주소를 0으로 메우기
  각 10진수 부분을 3자리 숫자가 되게 앞 부분을 0으로 메워서 문자열로 만드는것

# 6-21 IP 주소를 0으로 메운 문자열로 변환하는 쿼리

SELECT
	ip
	, 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 CAST('192.168.0.1' AS text) AS ip) AS t
;


#실행결과

ip  | ip_padding
----------------
192.168.0.1 | 192168000001

lpad 함수는 지정한 문자수가 되게 문자열의 왼쪽을 메우는 함수.

모든 10진수가 3자리 수가 되게, 문자열의 왼쪽을 0으로 메우고 있음.

그렇게 메운 문자열을 || 연산자로 연결함.