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

728x90

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

01.1 날짜/시간 계산하기

사전준비
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-02-29 10:00:00', '2000-02-29') , ('U003', '2016-03-01 10:00:00', '2000-02-29') ; select * from mst_users_with_dates;
실습 쿼리

--- 6-11 미래 또는 과거의 날짜/ 시간을 계산하는 쿼리
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 before_1_month
from mst_users_with_dates;

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

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

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

실습쿼리

--- 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-14 연 부분 차이를 계산하는 쿼리
select
	user_id,
	current_date as today,
	register_stamp::date as register_date,
	birth_date::date as birth_date,
	(current_date- birth_date::date)/365 as current_age,
	(register_stamp::date-birth_date::date)/365 as register_age 
from mst_users_with_dates;

--- 6-15 날짜를 정수로 표현해서 나이를 계산하는 함수
select floor((20160228-20000229)/10000) as age;

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

01.2 IP 주소 다루기

일반적인 웹 서비스는 로그 데이터에 사용자 IP 주소로 저장
- 보통 IP주소를 로그로 저장할 때는 문자열로 저장
IP 주소 자료형 활용하기

--- 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;
	
--- 6-18 inet 자료형을 사용해 IP 주소 범위를 다루는 쿼리
select cast('127.0.0.1' as inet) << cast('127.0.0.0/8' as inet) as is_contained;

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

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

정수 자료형으로 변환하면 숫자 대소 비교등이 가능해짐
- 아래는 텍스트 자료형으로 정의된 IP 주소에 있는 4개의 10진수 부분(점으로 구분된 각각의 값)을 정수 자료형으로 추출

--- 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,
	cast(split_part(ip, '.', 3)as integer) as ip_part_3,
	cast(split_part(ip, '.', 4)as integer) as ip_part_4
from
	(select '192.168.0.1' 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 '192.168.0.1' as ip) as t;\

IP 주소를 0으로 메우기
- 위에 처럼 10진수를 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 '192.168.0.1' as ip) as t;

02.7강 하나의 테이블에 대한 조작

데이터 집약
- 레코드의 수를 세주는 함수
- 레코드에 저장된 값의 합계, 평균, 최대, 최소를 계산해주는 함수
- 통계처리를 사용해 통계 지표를 출력해주는 함수
데이터 가공

02.1 그룹의 특징 잡기

집약 함수란?
- 여러 레코드를 기반으로 하나의 값을 리턴하는 함수
  - 예를 들어 모든 레코드의 수를 리턴해주는 COUNT 함수
    - 값의 합계를 리턴해주는 SUM함수 등이 있음

테이블 전체의 특징량 계산하기

대소 비소가 가능한 자료형
- 숫자, 문자열, 타임스탬프 등
사전 준비

DROP TABLE IF EXISTS review;
CREATE TABLE review (
    user_id    varchar(255)
  , product_id varchar(255)
  , score      numeric
);

INSERT INTO review
VALUES
    ('U001', 'A001', 4.0)
  , ('U001', 'A002', 5.0)
  , ('U001', 'A003', 5.0)
  , ('U002', 'A001', 3.0)
  , ('U002', 'A002', 3.0)
  , ('U002', 'A003', 4.0)
  , ('U003', 'A001', 5.0)
  , ('U003', 'A002', 4.0)
  , ('U003', 'A003', 4.0)
;

select * from review;

테이블 전체의 특징량 계산하기

--- 7-1 집약 함수를 사용해서 테이블 전체의 특징량을 계산하는 쿼리
select
	count(*) as total_count,
	count(distinct user_id) as user_count,
	count(distinct product_id) as product_count,
	sum(score) as sum,
	avg(score) as avg,
	max(score) as max,
	min(score) as min
from
	review;

그루핑한 데이터의 특징량 계산하기

데이터를 조금 더 작게 분할하고 싶은 경우 사용

--- 7-2 사용자 기반으로 데이터를 분할하고 집약함수를 적용하는 쿼리
select
	user_id,
	count(*) as total_count,
	count(distinct product_id) as product_count,
	sum(score) as sum,
	avg(score) as avg,
	max(score) as max,
	min(score) as min
from
	review
group by
	user_id;

집약 함수를 적용한 값과 집약 전의 값을 동시에 다루기

--- 7-3 윈도 함수를 사용해 집약 함수의 결과와 원래 값을 동시에 다루는 쿼리
select
	user_id,
	product_id,
	score, -- 개별 리뷰 점수
	avg(score) over() as avg_score, --전체 평균 리뷰 점수
	avg(score) over(partition by user_id)as user_avg_score, -- 사용자 평균 리뷰 점수
	score - avg(score) over(partition by user_id) as user_avg_score_diff -- 개별 리뷰 점수와 사용자 평균 리뷰 점수의 차이
from review;

02.2 그룹 내부의 순서

ORDER BY 구문으로 순서 정의하기
- 윈도함수에서는 OVER구문 내부에 ORER BY구문을 사용할 수 있음
- ROW_NUMBER 함수는
  - 유일한 순위 번호를 붙이는 함수
- RANK, DENSE_RANK 함수는
  - 같은 순위의 레코드가 있을 때 순위 번호를 같게 붙임
  - RANK의 경우 같은 순위의 레코드 뒤의 순위 번호를 건너뜀
  - DENSE_RANK함수의 경우 순위 번호를 건너뛰지 않음
- LAG함수, LEAD함수는
  - 현재 행을 기준으로 앞의 행 또는 뒤의 행의 값을 추출하는 함수
사전 준비

DROP TABLE IF EXISTS popular_products;
CREATE TABLE popular_products (
    product_id varchar(255)
  , category   varchar(255)
  , score      numeric
);

INSERT INTO popular_products
VALUES
    ('A001', 'action', 94)
  , ('A002', 'action', 81)
  , ('A003', 'action', 78)
  , ('A004', 'action', 64)
  , ('D001', 'drama' , 90)
  , ('D002', 'drama' , 82)
  , ('D003', 'drama' , 78)
  , ('D004', 'drama' , 58)
;

실습 쿼리

--- 7-3 윈도 함수의 ORDER BY 구문을 사용해 테이블 내부의 순서를 다루는 쿼리
select
	product_id,
	score,
	row_number() over(order by score desc) as row, --점수로 유일한 순위를 붙임
	rank() over(order by score desc) as rank, --같은 순위를 허용해서 순위를 붙임
	dense_rank() over(order by score desc) as dense_rank, --같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 뛰고 순위를 붙임
	lag(product_id) over(order by score desc) as lag1,-- 현재행 보다 앞에 있는 행의 값 추출하기
	lag(product_id,2) over(order by score desc) as lag2,
	lead(product_id) over(order by score desc) as lead1,-- 현재행 보다 뒤에 있는 행의 값 추출하기
	lead(product_id,2) over(order by score desc) as lead2
from popular_products 
order by row;

order by 구문과 집약 함수 조합하기

--- 7-5 order by 구문과 집약 함수를 조합해서 계산하는 쿼리
select
	product_id,
	score,
	--점수 순서로 유일한 순서를 붙임
	row_number() over(order by score desc) as row,
	-- 순위 상위부터의 누계 점수 계산하기
	sum(score) over(order by score desc rows between unbounded preceding and current row)
	as cum_score,
	-- 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산하기
	avg(score) over(order by score desc rows between 1 preceding and 1 following)
	as local_avg,
	--순위가 높은 상품 ID 추출하기 
	first_value(product_id) over(order by score desc rows between unbounded preceding and unbounded following)
	as first_value,
	-- 순위가 낮은 상품 ID 추출하기
	last_value(product_id) over(order by score desc rows between unbounded preceding and unbounded following)
	as last_value
from popular_products
order by row;

윈도 프레임 지정에 대해서

프레임 지정이란
- 현재 레코드 위치를 기반으로 상대적인 윈도를 정의하는 구문

프레임 지정 구문

ROWS BETWEEN start AND end
- start와 end에는
  - CURRENT ROW(현재의 행)
  - n PRECEDING(n행 앞)
  - n FOLLOWING(n행 뒤)
  - UNBOUNDED PRECEDING(이전 행 전부)
  - UNBOUNDED FOLLOWING(이후 행 전부)

--- 7-6 윈도 프레임 지정별 상품 ID를 집약하는 쿼리
select
	product_id,
	--점수 순서로 유일한 순위를 붙임
	row_number() over(order by score desc) as row,
	-- 가장 앞 순위부터 가장 뒷 순위까지의 범위를 대상으로 상품 ID집약하기
	array_agg(product_id) over(order by score desc rows between unbounded preceding and unbounded following)
	as whole_agg,
	-- 가장 앞 순위부터 현재 순위 까지의 범위를 대상으로 상품 ID집약하기
	array_agg(product_id) over(order by score desc rows between unbounded preceding and current row)
	as cum_agg,
	-- 순위 하나 앞과 하나 뒤까지의 범위를 대상으로 상품 ID집약하기
	array_agg(product_id) over(order by score desc rows between 1 preceding and 1 following)
	as local_agg
from popular_products
where category = 'action'
order by row;

partition by와 order by 조합하기

--- 7-7 윈도 함수를 사용해 카테고리들의 순위를 계산하는 함수
select
	category,
	product_id,
	score,
	--카테고리별로 점수 순서로 정렬하고 유일한 순위를 붙임
	row_number() over(partition by category order by score desc) as row,
	--카테고리별로 같은 순위를 허가하고 순위를 붙임
	rank() over(partition by category order by score desc) as rank,
	-- 카테고리별로 같은 순위가 있을 때
	-- 같은 순위 다음에 있는 순위를 건너 뛰고 순위를 붙임
	dense_rank() over(partition by category order by score desc)
	as dense_rank 
from popular_products
order by category, row;

각 카테고리의 상위 n개 추출하기

--- 7-8 카테고리들의 순위 상위 2개까지의 상품을 추출하는 쿼리
select *
from
-- 서브 쿼리 내부에서 순위 계산
(
select
	category,
	product_id,
	score,
	--카테고리별로 점수 순서로 유일한 순위를 붙임
	row_number() over(partition by category order by score desc) as rank 
from popular_products
)as popular_products_with_rank
-- 외부 쿼리에서 순위 활용해 압축하기
where rank <=2
order by category, rank;


-- 7-9 카테고리별 순위 최상위 상품을 추출하는 쿼리
select distinct 
	category,
	--카테고리별로 순위 최상위 상품ID 추출하기
	first_value(product_id) over(partition by category order by score desc
	rows between unbounded preceding and unbounded following)
	as product_id
from popular_products;

02.3 세로 기반 데이터를 가로 기반으로 변환하기

행을 열로 변환하기
- GROUP BY dt
- MAX(CASE ~)구문 사용
  - 해당 쿼리는 날짜별로 지표들이 하나씩 존재하므로 표현식의 조건 true 하나뿐
    - 그래서 그 하나를 MAX함수로 추출
사전 준비

DROP TABLE IF EXISTS daily_kpi;
CREATE TABLE daily_kpi (
    dt        varchar(255)
  , indicator varchar(255)
  , val       integer
);

INSERT INTO daily_kpi
VALUES
    ('2017-01-01', 'impressions', 1800)
  , ('2017-01-01', 'sessions'   ,  500)
  , ('2017-01-01', 'users'      ,  200)
  , ('2017-01-02', 'impressions', 2000)
  , ('2017-01-02', 'sessions'   ,  700)
  , ('2017-01-02', 'users'      ,  250)
;

select * from daily_kpi;

DROP TABLE IF EXISTS purchase_detail_log;
CREATE TABLE purchase_detail_log (
    purchase_id integer
  , product_id  varchar(255)
  , price       integer
);

INSERT INTO purchase_detail_log
VALUES
    (100001, 'A001', 3000)
  , (100001, 'A002', 4000)
  , (100001, 'A003', 2000)
  , (100002, 'D001', 5000)
  , (100002, 'D002', 3000)
  , (100003, 'A001', 3000)
;

select * from purchase_detail_log;

행을 열로 변환하기

--- 7-10 행으로 저장된 지표 값을 열로 변환하는 쿼리
select
	dt,
	max(case when indicator = 'impressions' then val end) as impressions,
	max(case when indicator = 'sessions' then val end) as sessions,
	max(case when indicator = 'users' then val end) as users
from daily_kpi
group by dt
order by dt;

행을 쉼표로 구분한 문자열로 집약하기

--- 7-11 행을 집약해서 쉼표로 구분된 문자열로 변환하기
select
	purchase_id,
	--상품 ID를 배열에 집약하고 쉼표로 구분된 문자열로 변환하기
	string_agg(product_id, ',') as product_idx,
	sum(price) as amount
from purchase_detail_log 
group by purchase_id
order by purchase_id;

728x90

'DataBase' 카테고리의 다른 글

2022-07-01-데이터분석을-위한-SQL레시피-3일차 (0)	2022.07.11
2022-06-28-데이터분석을-위한-SQL레시피-1일차 (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-29-데이터분석을-위한-SQL레시피-2일차

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

01.1 날짜/시간 계산하기

01.2 IP 주소 다루기

02.7강 하나의 테이블에 대한 조작

02.1 그룹의 특징 잡기

02.2 그룹 내부의 순서

02.3 세로 기반 데이터를 가로 기반으로 변환하기

'DataBase' 카테고리의 다른 글

댓글

티스토리툴바

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

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

01.1 날짜/시간 계산하기

01.2 IP 주소 다루기

02.7강 하나의 테이블에 대한 조작

02.1 그룹의 특징 잡기

02.2 그룹 내부의 순서

02.3 세로 기반 데이터를 가로 기반으로 변환하기

'DataBase' 카테고리의 다른 글

관련글

댓글

티스토리툴바