2022-06-28-데이터분석을-위한-SQL레시피-1일차

728x90

01. 5강 하나의 값 조작하기

데이터 가공해야하는 이유
- 다룰 데이터가 데이터 분석 용도로 상정되지 않은 경우
  - 여러 개의 정보가 하나의 문자열로 저장되어 있는 경우
    - SQL에서 다루기 어려움 그래서 데이터 분석에 적합한 형태로 미리 가공해서 저장해두어야함
- 연산할 때 비교 가능한 상태로 만들고 오류를 회피하기 위한 경우
  - 두 데이터 형식이 일치 하지 않은 경우 이를 집계하는 경우 같은 데이터 형식으로 통일하는 것이 좋음
  - 어떤 값과 NULL을 연산하면 결과가 NULL이 되기 때문에
    - 미리 데이터를 가공해서 NULL이 발생하지 않도록 하는것이 좋음

01.1 코드 값을 레이블로 변경하기

DROP TABLE IF EXISTS mst_users;
CREATE TABLE mst_users(
    user_id         varchar(255)
  , register_date   varchar(255)
  , register_device integer
);

INSERT INTO mst_users
VALUES
    ('U001', '2016-08-26', 1)
  , ('U002', '2016-08-26', 2)
  , ('U003', '2016-08-27', 3)
;

select * from mst_users;

-- 5-1 코드를 레이블로 변경하는 쿼리
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;

case 구문
- case 뒤에 when <조건식> then <조건을 만족할 때의 값> end 형태
  - 만약 조건식에 해당하는 경우가 없는 경우 NULL이지만
  - ELSE <값> 형태를 사용해서 디폴트 값을 별도로 지정해줄 수 있음

01.2 URL에서 요소 추출하기

사전 준비

DROP TABLE IF EXISTS access_log ;
CREATE TABLE access_log (
    stamp    varchar(255)
  , referrer text
  , url      text
);

INSERT INTO access_log 
VALUES
    ('2016-08-26 12:02:00', 'http://www.other.com/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.example.com/video/detail?id=001')
  , ('2016-08-26 12:02:01', 'http://www.other.net/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.example.com/video#ref'          )
  , ('2016-08-26 12:02:01', 'https://www.other.com/'                               , 'http://www.example.com/book/detail?id=002' )
;

select * from access_log;

레퍼러로 어떤 웹 페이지를 커쳐 넘어왔는지 판별하기
- 페이지 단위로 집계시 밀도 너무 작아 복잡
- 호스트 단위로 집계하는 것이 일반적
  - 정규표현식으로 호스트 이름의 패턴을 추출
```
-- 5-2 레퍼러 도메인을 추출하는 쿼리
select 
	stamp,
	substring(referrer from 'https?://([^/]*)') as referrer_host
from access_log;
```

URL에서 경로와 요청 매개변수 값 추출하기

URL을 가공하여 정보 뽑아내기

-- 5-3 URL 경로와 GET 매개변수에 있는 특정 키 값을 추출하는 쿼리
select 
	stamp,
	url,
	substring(url from '//[^/]+[^?#]+')as path,
	substring(url from 'id=([^&]*)') as id
from access_log;

01.3 문자열을 배열로 분해하기

세부적으로 분해해서 사용하는 경우가 많음
- 예를들어 영어 문장을 공백으로 분할해서 하나하나의 단어로 구분
- 쉼표로 연결된 데이터를 잘라 하나하나의 값을 추출하는 경우
  - 아래의 예시는 URL 경로를 슬래시로 분할해서 계층을 추출하는 경우
사전 준비하기

DROP TABLE IF EXISTS access_log ;
CREATE TABLE access_log (
    stamp    varchar(255)
  , referrer text
  , url      text
);

INSERT INTO access_log 
VALUES
    ('2016-08-26 12:02:00', 'http://www.other.com/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.example.com/video/detail?id=001')
  , ('2016-08-26 12:02:01', 'http://www.other.net/path1/index.php?k1=v1&k2=v2#Ref1', 'http://www.example.com/video#ref'          )
  , ('2016-08-26 12:02:01', 'https://www.other.com/'                               , 'http://www.example.com/book/detail?id=002' )
;

select * from access_log;

실습 쿼리

--- 5-4 URL경로를 슬래시로 분할해서 계층을 추출하는 쿼리
select 
	stamp,
	url,
	split_part(substring(url from '//[^/]+([^?#]+)'),'/',2) as path1,
	split_part(substring(url from '//[^/]+([^?#]+)'),'/',3) as path2
from access_log;

01.4 날짜와 타임스탬프 다루기

PostgreSQL에서는 CURRENT_TIMESTAMP의 리턴 값으로 타임존에 적용된 것이 나옴
- 타임존이 없는 타임스탬프의 경우는 LOCALTIMESTAMP를 사용하는 것이 좋음
실습 쿼리

--- 5-5 현재 날짜와 타임스탬프를 추출하는 쿼리
---- current_timestamp
select 
	current_date as dt,
	current_timestamp as stamp
;

---- localhimestamp
select 
	current_date as dt,
	localtimestamp as stamp
;

지정한 값의 날짜/ 시각 데이터 추출하기

자료형 변환 하는 것 CAST()

--- 5-6 문자열 날짜 자료형, 타임스탬프 자료형으로 변환하는 쿼리
select 
	cast('2022-06-28' as date) as dt,
	cast('2022-06-28 10:39:00' as timestamp) as stamp
;

날짜/ 시각에서 특정 필드 추출하기

타임스탬프 자료형의 데이터에서 년과 월등의 특정 필드 값을 추출시
- EXTRACT함수 사용
- substring, substr을 사용할 수도 있음

--- 5-7 타임스탬프 자료형의 데이터에서 연, 월, 일 등을 추출하는 쿼리
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 cast('2022-06-28 10:41:00' as timestamp) as stamp) as t
 ;
 
--- 5-8 타임스탬프를 나타내는 문자열에서 연, 월, 일 등을 추출하는 쿼리
---- substring
select 
	stamp,
	substring(stamp, 1, 4) as year,
	substring(stamp, 6, 2) as month,
	substring(stamp, 9, 2) as day,
	substring(stamp, 12, 2) as hour,
	substring(stamp, 1, 7) as year_month
from (select cast('2022-06-28 10:45:00'as text)as stamp)as t;

---- substr
select 
	stamp,
	substr(stamp, 1, 4) as year,
	substr(stamp, 6, 2) as month,
	substr(stamp, 9, 2) as day,
	substr(stamp, 12, 2) as hour,
	substr(stamp, 1, 7) as year_month
from (select cast('2022-06-28 10:45:00'as text)as stamp)as t;

01.5 결손 값을 디폴트 값으로 대치하기

문자열 또는 숫자를 다룰 때 중간에 NULL이 들어있는 경우 주의
- 이유: NULL + 문자열 = NULL
  - NULL + 숫자 = NULL이 됨
- 해결: COALESCE 함수를 이용하여 0으로 대치해서 해결
사전 준비

DROP TABLE IF EXISTS purchase_log_with_coupon;
CREATE TABLE purchase_log_with_coupon (
    purchase_id varchar(255)
  , amount      integer
  , coupon      integer
);

INSERT INTO purchase_log_with_coupon
VALUES
    ('10001', 3280, NULL)
  , ('10002', 4650,  500)
  , ('10003', 3870, NULL)
;

select * from purchase_log_with_coupon;

사용 쿼리

-- 5-9 구매액에서 할인 쿠폰 값을 제외한 매출 금액을 구하는 쿼리
select 
	purchase_id,
	amount,
	coupon,
	amount - coupon as discount_amount1,
	amount - coalesce(coupon, 0) as discount_amount2
from
	purchase_log_with_coupon
;

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

데이터 분석시 여러 값을 집약해서 하나의 값을 만들거나
- 다양한 값을 비교하는 경우가 많음
새로운 지표 정의하기
- 웹사이트에서 방문한 사용자 수 중에서 특정한 행동( 클릭 또는구매 등)을 실행한 사용자의 비율을 구해서
  - CTR(클릭 비율), CVR(컨버전 비율)이라고 부르는 지표를 정의하고 활용하는 경우가 매우 많음

02.1 문자열 연결하기

문자열 연결시 사용하는 CONCAT 함수 , || 연산자
사전 준비

DROP TABLE IF EXISTS mst_user_location;
CREATE TABLE mst_user_location (
    user_id   varchar(255)
  , pref_name varchar(255)
  , city_name varchar(255)
);

INSERT INTO mst_user_location
VALUES
    ('U001', '서울특별시', '강서구')
  , ('U002', '경기도수원시', '장안구'  )
  , ('U003', '제주특별자치도', '서귀포시')
;

select * from mst_user_location;

사용 쿼리

--- 6-1 문자열을 연결하는 쿼리
---- concat() 사용
select 
	user_id,
	concat(pref_name, city_name) as pref_city
from
	mst_user_location
;

---- || 연산자 사용
select 
	user_id,
	pref_name||city_name as pref_city
from
	mst_user_location
;

02.2 여러 개의 값 비교하기

사전 준비

DROP TABLE IF EXISTS quarterly_sales;
CREATE TABLE quarterly_sales (
    year integer
  , q1   integer
  , q2   integer
  , q3   integer
  , q4   integer
);

INSERT INTO quarterly_sales
VALUES
    (2015, 82000, 83000, 78000, 83000)
  , (2016, 85000, 85000, 80000, 81000)
  , (2017, 92000, 81000, NULL , NULL )
;

select * from quarterly_sales;

분기별 매출 증감 판정하기

SIGN은
- 매개변수가 양이면 1
- 매개변수가 0이면 0
- 매개변수가 음이면 -1을 리턴하는 함수

-- 6-2 q1, q2 컬럼을 비교하는 쿼리
select 
	year,
	q1,
	q2,
	case
		when q1 < q2 then '+'
		when q1 = q2 then ' '
		else '-'
	end as judge_q1_q2,
	q2 - q1 as diff_q2_q1,
	sign(q2 - q1) as sign_q2_q1
from
	quarterly_sales
order by
	year
;

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

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

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

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

--- 6-4 단순한 연산으로 평균 4분기 매출을 구하는 쿼리
select 
	year,
	(q1 + q2 + q3 + q4) / 4 as average
from
	quarterly_sales 
order by
	year
;

--- 6-5 COALESCE를 사용해 NULL을 0으로 변환하고 평균값을 구하는 쿼리
select 
	year,
	(coalesce(q1,0) + coalesce(q2,0) + coalesce(q3,0) + coalesce(q4,0)) / 4 as average
from
	quarterly_sales 
order by
	year
;

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

02.3 2개의 값 비율 계산하기

사전 준비

DROP TABLE IF EXISTS advertising_stats;
CREATE TABLE advertising_stats (
    dt          varchar(255)
  , ad_id       varchar(255)
  , impressions integer
  , clicks      integer
);

INSERT INTO advertising_stats
VALUES
    ('2017-04-01', '001', 100000,  3000)
  , ('2017-04-01', '002', 120000,  1200)
  , ('2017-04-01', '003', 500000, 10000)
  , ('2017-04-02', '001',      0,     0)
  , ('2017-04-02', '002', 130000,  1400)
  , ('2017-04-02', '003', 620000, 15000)
;

select * from advertising_stats;

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

CTR = 클릭 / 노출 수
cast ( 컬럼명 as double precision)
- 100.0 * 의 결과는 같음

--- 6-7 정수 자료형의 데이터를 나누는 쿼리
select 
	dt,
	ad_id,
	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으로 나누는 것 피하기
- NULL 전파를 사용하면 0으로 나누는 것을 피할 수 있음
  - NULL전파란 NULL을 포함한 데이터의 연산 결과가 모두 NULL이 되는 SQL 성질
- NULLIF(컬럼명, 0)
  - 컬럼명의 값이 0이라면 NULL
    - CASE 식을 사용한 방법과 같은 결과가 됨
```
--- 6-8 0으로 나누는 것을 피해 CTR을 계산하는 쿼리
select
	dt,
	ad_id,
	case
		when impressions > 0 then 100.0 * clicks / impressions
	end as ctr_as_percent_by_case,
	100.0 * clicks / nullif(impressions, 0) as ctr_as_percent_by_null
from
	advertising_stats
order by
	dt, ad_id
;
```

02.4 두 값의 거리 계산하기

시험을 보았을 떄 평균에서 어느 정도 떨어져 있는지
작년 매출과 올해 매출에 어느 정도 차이가 있는지 등을 거리라고 부름
- 추가로 어떤 사용자가 있을때, 해당 사용자와 구매 경향이 비슷한 사용자를 뽑는 등 사용
사전 준비

DROP TABLE IF EXISTS location_1d;
CREATE TABLE location_1d (
    x1 integer
  , x2 integer
);

INSERT INTO location_1d
VALUES
    ( 5 , 10)
  , (10 ,  5)
  , (-2 ,  4)
  , ( 3 ,  3)
  , ( 0 ,  1)
;

select * from location_1d;

DROP TABLE IF EXISTS location_2d;
CREATE TABLE location_2d (
    x1 integer
  , y1 integer
  , x2 integer
  , y2 integer
);

INSERT INTO location_2d
VALUES
    (0, 0, 2, 2)
  , (3, 5, 1, 2)
  , (5, 3, 2, 1)
;

select * from location_2d;

숫자 데이터의 절댓값, 제곱 평균 제곱근(RMS) 계산하기
- 절대값 계산시 ABS함수(abstract)를 사용
- 제곱 평균 제곱근은 두 값의 차이를 제곱한 뒤 제곱근을 적용해서 나오는 값
  - 제곱시 POWER 함수
  - 제곱근 구할 때 SQRT함수를 사용
    - 값이 일차원인 경우 절댓값과 제곱 평균 제곱근은 같은 결과
```
--- 6-9 일차원 데이터의 절댓값과 제곱 평균 제곱근을 계산하는 쿼리
select 
	abs(x1 - x2) as abs,
	sqrt(power(x1 - x2, 2)) as rms
from location_1d
;
```
xy 평면 위에 있는 두 점의 유클리드 거리 계산하기
- 유클리드 거리는 물리적 공간에서 거리를 구할 때 사용하는 일반적인 방법
  - postgreSQL에는 POINT 자료형이라고 불리는 좌표를 다루는 자료구조 가 있음
    - POINT 자료형 데이터로 변환 후
    - 거리 연산자 <->를 사용하면 됨
```
--- sqrt(power())
select 
	sqrt(power(x1 - x2, 2) + power(y1 - y2, 2)) as dist
from location_2d
;

--- point <-> 사용  
select 
	point(x1,y1) <-> point(x2,y2) as dist
from location_2d
;
```

728x90

'DataBase' 카테고리의 다른 글

2022-07-01-데이터분석을-위한-SQL레시피-3일차 (0)	2022.07.11
2022-06-29-데이터분석을-위한-SQL레시피-2일차 (0)	2022.07.11
2022-06-10-조인(내부조인,외부조인,상호조인등) (0)	2022.07.11
2022-06-09-내장함수,-순위분석-함수,-피벗,-CLOB/BLOB (0)	2022.07.11
2022-06-09-sqlldr사용법 (0)	2022.06.13

쓰리디핏 공작소

2022-06-28-데이터분석을-위한-SQL레시피-1일차

01. 5강 하나의 값 조작하기

01.1 코드 값을 레이블로 변경하기

01.2 URL에서 요소 추출하기

01.3 문자열을 배열로 분해하기

01.4 날짜와 타임스탬프 다루기

01.5 결손 값을 디폴트 값으로 대치하기

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

02.1 문자열 연결하기

02.2 여러 개의 값 비교하기

02.3 2개의 값 비율 계산하기

02.4 두 값의 거리 계산하기

'DataBase' 카테고리의 다른 글

댓글

티스토리툴바

2022-06-28-데이터분석을-위한-SQL레시피-1일차

01. 5강 하나의 값 조작하기

01.1 코드 값을 레이블로 변경하기

01.2 URL에서 요소 추출하기

01.3 문자열을 배열로 분해하기

01.4 날짜와 타임스탬프 다루기

01.5 결손 값을 디폴트 값으로 대치하기

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

02.1 문자열 연결하기

02.2 여러 개의 값 비교하기

02.3 2개의 값 비율 계산하기

02.4 두 값의 거리 계산하기

'DataBase' 카테고리의 다른 글

관련글

댓글

티스토리툴바