[SQL] 데이터 분석을 위한 SQL 레시피 - 3장 6강

6. 여러 개의 값에 대한 조작

 

⚠️본 책에서는 PostgreSQL로 진행하지만 나는 편의상 MySQL로 바꿔서 진행하였다.

 

 

---

 

1. 문자열 연결하기

 

데이터 6-1. 사용자의 주소 정보 mst_user_location 테이블

 

코드 6-1. 문자열을 연결하는 쿼리

select user_id,
concat(pref_name,city_name) as pref_city
from mst_user_location;

#사이에 띄어쓰기 하기
select user_id,
concat(pref_name,' ',city_name) as pref_city
from mst_user_location;

 

✅대부분의 미들웨어에서 concat 함수를 사용해 문자열을 연결할 수 있다. 

 

---

 

2. 여러 개의 값 비교하기

 

데이터 6-2. 4분기 매출 테이블 quarterly_sales

1) 분기별 매출 증감 판정하기

case식을 사용해 q1보다 q2의 매출이 많은 경우에는 +, 같은 경우에는 공백, 적은 경우에는 - 를 출력.

그리고 차이를 구하고 (diff_q2_q1)

sign 함수를 이용해 매개변수가 양수면 0, 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;

 

✅SIGN 함수

투입된 숫자, 또는 레코드 값에 대하여 양수인지 / 음수인지 / 0인지 부호를 판정해 주는 역할을 한다. 

 

 

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

위에는 2개의 컬럼을 대소 비교했지만 3개 이상의 컬럼을 비교할 때도 위와 같은 방법을 사용하면 코드가 복잡해진다. 

컬럼 값에서 최댓값 또는 최솟값을 찾을 때는 greatest 함수, 또는 least 함수를 사용한다. greatest와 least는 sql 표준에 포함되지 않지만 대부분의 sql 쿼리 엔진에서 구현하고 있다. 

 

 

코드 6-3. 연간 최대/최소 4분기 매출을 찾는 쿼리

select year
,greatest(q1,q2,q3,q4) as greatest_sales
,least(q1,q2,q3,q4) as least_sales
from quarterly_sales
order by year;

 

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

q1부터 q4까지 값을 더하고 4로 나누면 평균값을 구할 수 있다. 

#평균값
select year,
(q1+q2+q3+q4)/4 as average
from quarterly_sales
order by year;

null을 사칙연산하려면 coalesce 함수를 사용해 적절한 값으로 바꾸어야 한다. 하지만 q3과 q4의 값을 0으로 바꿔버리면 매출 합계를 4로 나누게 되어 평균값이 크게 낮아진다. 

 

 

코드 6-5.COALESCE를 사용해 NULL을 0으로 변환하고 평균값을 구하는 쿼리

select year,
(coalesce(q1,0)+coalesce(q2,0)+coalesce(q3,0)+coalesce(q4,0))/4
from quarterly_sales
order by year;

2017년의 q1과 q2 매출만으로 평균을 구하려면 null이 아닌 컬럼의 수를 세서 나눠야 한다. coalesce 함수와 sign 함수를 조합해 분모의 값을 계산해야한다. 

 

코드 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;

하나의 레코드 내부에 있는 값끼리 연산할 때는 여러 개의 컬럼에 있는 비교/계산 처리가 간단. 하지만 여러 레코드에 걸쳐 있는 값들을 처리할 때는 집약 함수를 사용해 데이터를 가공하고 처리해야한다. -> 본 책 7강에서 소개한다고 함. 

 

3. 2개의 값 비율 계산

다음 데이터는 광고 통계정보를 나타내는 테이블, 매일의 광고 노출 수와 클릭 수를 집계.

 

데이터 6-3. 광고 통계정보 테이블 advertising_stats

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

하루 데이터에서 각 광고의 CTP(Click Through Rate)을 계산. CTR이란 클릭 수/노출 수.

 

select dt,ad_id,
clicks/impressions as ctr,
100*clicks/impressions as ctr_as_percent
from advertising_stats
where dt='2017-04-01'
order by dt,ad_id;

 

2) 0으로 나누는 것 피하기

2017-04-02 데이터는 impressions이 0이다. 따라서 앞의 코드를 적용하면 0으로 나누게 되어 오류가 발생하게 된다. 

0으로 나누는 것을 피하는 첫번째 방법은 case식을 사용해 impressions가 0인지 확인하는 것. 

다음 코드 6-8dml ctr_as_percent_by_case 컬럼은 impressions가 0보다 큰 경우에는 CTR을 계산하고 이외의 경우에는 null을 출력한다. 

 

추가로 null 전파를 사용하면 0으로 나누는 것을 피할 수 있다. null 전파란 null을 포함한 데이터의 연산 결과가 모두 null이 되는 sql의 성질이다. 코드 6-8의 ctr_as_percent_by_null처럼 nullif(impressions,0)부분은 impressions 값이 0이라면 null이 된다. 그 결과 impressions의 값이 0이라면 null 전반으로 ctr값도 null이 되어 case 식을 사용한 것과 같은 결과를 얻을 수 있다. 

 

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

select dt,ad_id,
case
#case 식으로 분모가 0인경우 0으로 나누지 않게 하는 방법
when impressions>0 then 100*clicks/impressions end as ctr_as_percent_by_case,
#분모가 0이라면 null로 변환해서 0으로 나누지 않게 만드는 방법
100*clicks/nullif(impressions,0) as ctr_as_percent_by_null
from advertising_stats
order by dt,ad_id;

 

✅정수로 나누거나 0으로 나누는 등의 실수를 할 수 있으므로 잘 사용해야한다. 

 

4. 두 값의 거리 계산하기

물리적인 공간의 길이가 아니다.(이과 특, 거리라고 하면 유클리드 생각부터 함🤬) 

데이터 분석에서는 예를 들어 시험을 보았을 때 평균에서 어느 정도 멀리 떨어져 있는지, 작년 매출과 올해 매출에 어느 정도 차이가 있는지 등을 모두 거리라고 부른다. 추가로 어떤 사용자가 있을 때 해당 사용자와 구매 경향이 비슷한 사용자를 뽑는 등의 응용상황에서도 거리 개념이 굉장히 중요하게 작용한다. 

 

데이터6-4. 일차원 위치 정보 테이블 location_1d

 

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

x1,x2를 기반으로 거리를 구해보겠다.

• 절댓값 : ABS 함수
• 제곱 평균 제곱근 : 두 값의 차이를 제곱한 뒤 제곱근을 적용해서 나오는 값을 의미
  • POWER(제곱) 함수
  • SQRT(제곱근) 함수

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

select 
abs(x1-x2) as abs,
sqrt(power(x1-x2,2)) as rms
from location_1d;

 

2) xy 평면 위에 있는 두 접의 유클리드 거리 계산하기

아는거 나왔다. 유클리드 거리는 물리적인 공간에서 거리를 구할 때 사용하는 일반적인 방법~!

 

데이터 6-5. 이차원 정보 테이블 location_2d

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

select 
sqrt(power(x1-x2,2)+power(y1-y2,2)) as dist
from location_2d;

 

5. 날짜 / 시간 계산하기

 

데이터 6-6 등록 시간과 생일을 포함하는 사용자 마스터 테이블 - mst_users_with_dates

DROP TABLE IF EXISTS mst_users_with_dates;

CREATE TABLE mst_users_with_dates (
    user_id  varchar(255),
    register_stamp varchar(255),
    birth_date varchar(255)
);

INSERT INTO mst_users_with_dates
VALUES
    ('U001', '2016-02-28 10:00:00', '2000-02-29')
  , ('U002', '2016-05-15 10:00:00', '2000-02-29')
  , ('U003', '2001-02-04 10:00:00', '2000-02-29')
;

첨부 파일에 6-6 데이터가 없어서 직접 만들었다. (왜 없슈)

 

날짜나 시간을 더하거나 빼는 방법은

date_add, date_sub을 사용하면 된다. 

date_add(date,interval value interval_type)
date_sub(date,interval value interval_type)

 

코드 6-11. 회원 등록 시간 1시간 후와 30분 전의 시간, 등록일의 다음날과 1달 전의 날짜를 계산하는 쿼리

#timestamp 으로 변경해준다음 계산. 문자열로는 날짜계산 할 수 없음. 
select user_id,
timestamp(register_stamp) as register_stamp,
date_add(timestamp(register_stamp),interval 1 hour) as after_1_hour,
date_sub(timestamp(register_stamp), interval 30 minute) as before_30_minutes,

date(timestamp(register_stamp)) as register_date,
date_add(date(timestamp(register_stamp)),interval 1 day) as after_1_day,
date_sub(date(timestamp(register_stamp)),interval 1 month) as before_1_month

from mst_users_with_dates;

 

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

두 날짜 데이터를 사용해 날짜 차이를 계산해보자. 

#현재날짜
current_date

#날짜 차이 
datediff(날짜, 날짜)

 

코드 6-12. 회원 등록일과 현재 날짜의 차이, 그리고 회원 등록일과 생년월일과의 날짜 차이를 계산. 

select user_id,
current_date as today,
date(timestamp(register_stamp)) as regitster_date,
datediff(current_date, date(timestamp(register_stamp))) as diff_days
from mst_users_with_dates;

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

책에서는 postgresql로만 가능하다고 써놨는데 mysql도 됨. 

왜냐면 year만 추출해서 쓸 수 있기 때문이다. 

select user_id,
year(current_date)-year(birth_date)+1 as age
from mst_users_with_dates;

증명. 

 

6. ip 주소 다루기

일반적인 웹 서비스는 로그 데이터에 사용자 IP 주소를 저장한다. 보통 IP 주소를 로그로 저장할 떄는 문자열로 저장한다.

 

1) 주소 자료형 활용하기

ip 주소를 mysql에 넣을 때 쉽게 변환해주는 함수가 있다. 바로 INET_ATON 함수이다. 

INET_ATON(IP 주소)=정수
INET_ATON(정수)=IP 주소

INET_ATON을 사용하면  IP주소를 쉽게 비교할 수 있다. 

 

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

select
inet_aton('127.0.0.1') < inet_aton('127.0.0.2') as lt,
inet_aton('127.0.0.1') > inet_aton('192.168.0.1') as gt;

 

2) 정수 또는 문자열로 IP 주소 다루기

inet_aton 자료형 처럼 ip 주소 전용 자료형이 제공되지 않는 미들웨어의 경우 ip 주소등을 비교할 때 조금 다른 방법을 사용해야 한다. 

 

- IP 주소를 정수 자료형으로 변환하기

 

첫번째 방법은 ip주소를 정수 자료형으로 변환하는 것이다. 이렇게 하면 숫자 대소 비교 등이 가능해진다. 

근데 mysql에 split 기능이 허접해서 ㅠㅜ 이게 맞나 싶다... split을 더 효율적으로 하는 방법을 아시는 분은 댓글 부탁드립니다. 

SELECT ip,
substring_index(ip,'.',1) as ip_part_1,
substring_index(substring_index(ip,'.',2),'.',-1) as ip_part_2,
substring_index(substring_index(ip,'.',-2),'.',1) as ip_part_3,
substring_index(ip,'.',-1) as ip_part_4
from (select '192.168.0.1' as ip) as t;

 

그리고 앞에서 추출한 4개의 10진수 부분을 각각 2^24, 2^26, 2^8,2^0 만큼 곱하고 더하면 정수 자료형 표기가 된다. 이와 같은 방법을 사용하면 다음 코드의 결과 처럼 ip 주소가 정수 자료형으로 변환되므로 대소 비교 또는 범위 판정을 할 수 있다. 

 

SELECT ip,
substring_index(ip,'.',1)*pow(2,24)
+substring_index(substring_index(ip,'.',2),'.',-1)*pow(2,16)
+substring_index(substring_index(ip,'.',-2),'.',1)*pow(2,8)
+substring_index(ip,'.',-1)*pow(2,0)
as ip_integer
from (select '192.168.0.1' as ip) as t;

✅mysql에서 제곱계산을 하고 싶으면, 
2^16 으로 하면 틀림. 
pow(2,16) 으로 해야함. 

 

- ip 주소를 0으로 메우기

 

ip 주소들을 비교하는 또 다른 방법은 각 10 진수 부분을 3 자리 숫자가 되게 앞 부분을 0으로 메워서 문자열로 만드는 것이다. 

SELECT ip,
concat(
lpad(substring_index(ip,'.',1),3,'0'),
lpad(substring_index(substring_index(ip,'.',2),'.',-1),3,'0'),
lpad(substring_index(substring_index(ip,'.',-2),'.',1),3,'0'),
lpad(substring_index(ip,'.',-1),3,'0'))
as ip_padding
from (select '192.168.0.1' as ip) as t;

ipad 함수는 지정한 문자수가 되게 문자열의 왼쪽을 메우는 함수. 10진수가 3자리수가 되게 문자열의 왼쪽을 0으로 메우고 있다 이렇게 만들면 문자열 상태로 대소 비교 등을 할 수 있다.