데이터분석을 위한 SQL레시피 1일차
June 28, 2022
2022-06-28-데이터분석을-위한-SQL레시피-1일차
목차
1.
5강하나의 값 조작하기01.1 코드 값을 레이블로 변경하기
01.2 URL에서 요소 추출하기
01.3 문자열을 배열로 분해하기
01.4 날짜와 타임스탬프 다루기
01.5 결손 값을 디폴트 값으로 대치하기
2.
6강여러 개의 값에 대한 조작02.1 문자열 연결하기
02.2 여러 개의 값 비교하기
02.3 2개의 값 비율 계산하기
02.4 두 값의 거리 계산하기
- 목표 범위: 03.5강 하나의 값 조작하기 ~ 03.6강 여러 개의 값에 대한 조작 (4.두 값의 거리 계산하기 까지)
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 ;
![[KyeongMinPark]](/static/a60cd93918cf177a4b750983d03b54b1/a12fc/profile.png)