데이터분석을 위한 SQL레시피 3일차

July 01, 2022

2022-07-01-데이터분석을-위한-SQL레시피-3일차

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

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

02.8강 여러 개의 테이블 조작하기

02.1 여러 개의 테이블을 세로로 결합하기

02.2 여러 개의 테이블을 가로로 정렬하기

02.3 조건 플래그를 0과 1로 표현하기

02.4 계산한 테이블에 이름 붙여 재사용하기

02.5 유사 테이블 만들기

목표범위 : 7강 하나의 테이블에 대한 조작(4.가로기반 데이터를 세로 기반으로 변환하기) ~ 8강 여러 개의 테이블 조작하기

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

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

세로 기반 데이터를 가로로 변경하는 것보다
- 가로기반 데이터를 세로 기반으로 변환하는 것이 어려운 이유
- 이미 데이터가 쉼표로 구분된 열 기반의 형식으로 저장되어서, 이를 분석하기 위해 어쩔 수 없이 변환해야하는경우가 많음

열로 표현된 값을 행으로 변환하기

-- 7-12 일련 번호를 가진 피벗 테이블을 사용해 행으로 변환하는 쿼리
select * from quarterly_sales qs ;
select 
	q.year,
	-- Q1 ~ Q4까지의 레이블 이름 출력하기
	case
		when p.idx = 1 then 'q1'
		when p.idx = 2 then 'q2'
		when p.idx = 3 then 'q3'
		when p.idx = 4 then 'q4'
	end as quarter,
	-- Q1 ~ Q4까지의 매출 출력하기
	case
		when p.idx = 1 then q.q1
		when p.idx = 2 then q.q2
		when p.idx = 3 then q.q3
		when p.idx = 4 then q.q4
	end as sales
from
	quarterly_sales as q
cross join
-- 행으로 전개하고 싶은 열의 수만큼 순번 테이블 만드리
(
			  select 1 as idx 
	union all select 2 as idx
	union all select 3 as idx
	union all select 4 as idx
) as p ;

임의의 길이를 가진 배열을 행으로 전개하기

테이블 함수란
리턴값이 테이블인 함수를 의미
- postgreSQL에서는 unnest 함수가 있음

--- 7-13 임의의 길이를 가진 배열을 행으로 전개하기
select unnest(array['A001', 'A002', 'A003']) as product_id;

--- 7-14 테이블 함수를 사용해 쉼표로 구분된 문자열 데이터를 행으로 전개하는 쿼리
DROP TABLE IF EXISTS purchase_log;
CREATE TABLE purchase_log (
  purchase_id integer
, product_ids varchar(255)
);

INSERT INTO purchase_log
VALUES
  (100001, 'A001,A002,A003')
, (100002, 'D001,D002')
, (100003, 'A001')
;
select 
	purchase_id,
	product_id
from
	purchase_log as p
	-- string_to_array 함수로 문자열을 배열로 변환 후 unnest 함수로 테이블로 변환하기
	cross join unnest(string_to_array(product_ids,',')) as product_id;

--- 7-15 postgreSQL에서 쉼표로 구분된 데이터를 행으로 전개하는 쿼리
select
	purchase_id,
	-- 쉼표로 구분된 문자열을 한 번에 행으로 전개하기
	regexp_split_to_table(product_ids,',') as product_id
from purchase_log;

Redshift에서 문자열을 행으로 전개하기

피벗 테이블을 사용해 문자열을 행으로 전개하기

--- 7-16 일련 번호를 가진 피벗 테이블을 만드는 쿼리
select *
from(
			  select 1 as idx 
	union all select 2 as idx
	union all select 3 as idx
)as pivot;

--- 7-17 split_part 함수의 사용 예
select 
	split_part('A001,A002,A003',',',1) as part_1,
	split_part('A001,A002,A003',',',2) as part_2,
	split_part('A001,A002,A003',',',3) as part_3
;

split_part 함수는
- 문자열을 쉼표 등의 구분자로 분할해 n번째 요소를 추출하는 함수

--- 7-18 문자 수의 차이를 사용해 상품 수를 계산하는 쿼리
select
	purchase_id,
	product_ids,
	-- 상품 ID 문자열을 기반으로 쉼표를 제거하고,
	-- 문자 수의 차이를 계산해서 상품 수 구하기
	1 + char_length(product_ids)
		- char_length(replace(product_ids,',',''))
	as product_num
from 
	purchase_log;

replace를 이용하여 쉼표 제거,
문자 수를 세는 char_length 함수로 원래 문자열과의 차이를 계산하여 상품수 계산

--- 7-19 피벗 테이블을 사용해 문자열을 행으로 전개하는 쿼리
select
	l.purchase_id,
	l.product_ids,
	-- 상품 수만큼 순번 붙이기
	p.idx,
	-- 문자열을 쉼표로 구분해서 분할하고, idx번째 요소 추출하기
	split_part(l.product_ids,',',p.idx) as product_id
from
	purchase_log as l
	join
		(
					select 1 as idx
		 union all	select 2 as idx
		 union all	select 3 as idx
		)as p
	-- 피벗 테이블의 id가 상품 수 이하의 경우 결합하기
	on p.idx <=
		(1 + char_length(l.product_ids)
			- char_length(replace(l.product_ids, ',', '')));

02.`8강` 여러 개의 테이블 조작하기

업무 데이터를 사용하는 경우
- 관계형 구조가 아닌경우 여러 개의 테이블에 데이터를 저장함
- 이런 데이터를 분석하기 위해 테이블을 하나로 합쳐야하는 경우가 많음
- 예를 들어 sns 사이트라면 , 댓글, 좋아요, 팔로우라는 각각의 테이블에 저장된 정보를 기반으로 사용자가 어떤 행동을 하는가를 분석하고 싶으면 이런 테이블을 합쳐서 다뤄어야함
로그 데이터를 사용하는 경우

02.1 여러 개의 테이블을 세로로 결합하기

사전 준비

DROP TABLE IF EXISTS app1_mst_users;
CREATE TABLE app1_mst_users (
  user_id varchar(255)
, name    varchar(255)
, email   varchar(255)
);

INSERT INTO app1_mst_users
VALUES
  ('U001', 'Sato'  , 'sato@example.com'  )
, ('U002', 'Suzuki', 'suzuki@example.com')
;

DROP TABLE IF EXISTS app2_mst_users;
CREATE TABLE app2_mst_users (
  user_id varchar(255)
, name    varchar(255)
, phone   varchar(255)
);

INSERT INTO app2_mst_users
VALUES
  ('U001', 'Ito'   , '080-xxxx-xxxx')
, ('U002', 'Tanaka', '070-xxxx-xxxx')
;

실습 쿼리

--- 8-1 UNION ALL 구문을 사용해 테이블을 세로로 결합하는 쿼리
select 'app1' as app_name, user_id, name, email from app1_mst_users
union all
select 'app2' as app_name, user_id, name, null as email from app2_mst_users;

02.2 여러 개의 테이블을 가로로 정렬하기

사전 준비

DROP TABLE IF EXISTS mst_categories;
CREATE TABLE mst_categories (
  category_id integer
, name        varchar(255)
);

INSERT INTO mst_categories
VALUES
  (1, 'dvd' )
, (2, 'cd'  )
, (3, 'book')
;

DROP TABLE IF EXISTS category_sales;
CREATE TABLE category_sales (
  category_id integer
, sales       integer
);

INSERT INTO category_sales
VALUES
  (1, 850000)
, (2, 500000)
;

DROP TABLE IF EXISTS product_sale_ranking;
CREATE TABLE product_sale_ranking (
  category_id integer
, rank        integer
, product_id  varchar(255)
, sales       integer
);

INSERT INTO product_sale_ranking
VALUES
  (1, 1, 'D001', 50000)
, (1, 2, 'D002', 20000)
, (1, 3, 'D003', 10000)
, (2, 1, 'C001', 30000)
, (2, 2, 'C002', 20000)
, (2, 3, 'C003', 10000)
;

실습 쿼리

--- 8-2 여러 개의 테이블을 결합해서 가로로 정렬하는 쿼리

select 
	m.category_id,
	m.name,
	s.sales,
	r.product_id as sale_product
from
	mst_categories as m
	join
	-- 카테고리별로 매출액 결합하기
	category_sales as s
	on m.category_id = s.category_id
	join
	-- 카테고리별로 상품 결합하기
	product_sale_ranking as r
	on m.category_id = r.category_id;

마스터 테이블의 행 수를 변경하지 않고 데이터를 가로 정렬하려면
LEFT JOIN을 사용해 결합하지 못한 레코드를 유지한 상태로
결합할 레코드가 반드시 1개 이하가 되게 하는 조건을 사용해야 함

--- 8-3 마스터 테이블의 행 수를 변경하기 않고 여러 개의 테이블을 가로로 정렬하는 쿼리
select 
	m.category_id,
	m.name,
	s.sales,
	r.product_id as top_sale_product
from
	mst_categories as m 
	-- left join을 사용해서 결합한 레코드를 남기
	left join
	-- 카테고리별 매출액 결합하기
	category_sales as s
	on m.category_id = s.category_id
	-- left join을 사용해서 결합하지 못한 레코드를 남김
	left join
	-- 카테고리별 최고 매출 상품 하나만 추출해서 결합하기
	product_sale_ranking as r
	on m.category_id = r.category_id
	and r.rank =1;

-- 8-4 상관 서브쿼리로 여러 개의 테이블을 가로로 정렬하는 쿼리
select
	m.category_id,
	m.name,
	-- 상관 서브쿼리를 사용해 카테고리별로 매출액 추출하기
	(
		select s.sales as s
		from category_sales as s
		where m.category_id = s.category_id
	)as sales,
	--  상관 서브쿼리를 사용해 카테고리별로 최고 매출 상품을
	-- 하나 추출하기(순위로 따로 압축하지 않아도 됨)
	(
		select r.product_id
		from product_sale_ranking as r
		where m.category_id = r.category_id
		order by sales desc
		limit 1
	)as top_sale_product
from 
	mst_categories as m;

02.3 조건 플래그를 0과 1로 표현하기

사전 준비

DROP TABLE IF EXISTS mst_users_with_card_number;
CREATE TABLE mst_users_with_card_number (
  user_id     varchar(255)
, card_number varchar(255)
);

INSERT INTO mst_users_with_card_number
VALUES
  ('U001', '1234-xxxx-xxxx-xxxx')
, ('U002', NULL                 )
, ('U003', '5678-xxxx-xxxx-xxxx')
;

DROP TABLE IF EXISTS purchase_log;
CREATE TABLE purchase_log (
  purchase_id integer
, user_id     varchar(255)
, amount      integer
, stamp       varchar(255)
);

INSERT INTO purchase_log
VALUES
  (10001, 'U001', 200, '2017-01-30 10:00:00')
, (10002, 'U001', 500, '2017-02-10 10:00:00')
, (10003, 'U001', 200, '2017-02-12 10:00:00')
, (10004, 'U002', 800, '2017-03-01 10:00:00')
, (10005, 'U002', 400, '2017-03-02 10:00:00')
;

실습 쿼리

--- 8-5 신용카드 등록과 구매 이력 유무를 0과 1이라는 플래그로 나타내는 쿼리
select 
	m.user_id,
	m.card_number,
	count(p.user_id) as purchase_count,
	-- 신용 카드 번호를 등록한 경우 1, 등록하지 않은 경우 0으로 표현하기
	case when m.card_number is not null then 1 else 0 end as has_card,
	-- 구매 이력이 있는 경우 1, 없는 경우 0으로 표현하기
	sign(count(p.user_id)) as has_purchased
from
	mst_users_with_card_number as m
	left join 
		purchase_log as p 
		on m.user_id = p.user_id
	group by m.user_id, m.card_number;

02.4 계산한 테이블에 이름 붙여 재사용하기

사전 준비

DROP TABLE IF EXISTS product_sales;
CREATE TABLE product_sales (
  category_name varchar(255)
, product_id    varchar(255)
, sales         integer
);

INSERT INTO product_sales
VALUES
  ('dvd' , 'D001', 50000)
, ('dvd' , 'D002', 20000)
, ('dvd' , 'D003', 10000)
, ('cd'  , 'C001', 30000)
, ('cd'  , 'C002', 20000)
, ('cd'  , 'C003', 10000)
, ('book', 'B001', 20000)
, ('book', 'B002', 15000)
, ('book', 'B003', 10000)
, ('book', 'B004',  5000)
;

실습 쿼리

--- 8-6 카테고리별 순위를 추가한 테이블에 이름 붙이기
with
product_sale_ranking as(
	select
		category_name,
		product_id,
		sales,
		row_number() over(partition by category_name order by sales desc) as rank
	from
		product_sales
)
select * from product_sale_ranking;

--- 8-7 카테고리들의 순위에서 유니크한 순위 목록을 계산하는 쿼리
with
product_sales_ranking as(
		select
		category_name,
		product_id,
		sales,
		row_number() over(partition by category_name order by sales desc) as rank
	from
		product_sales
),
mst_rank as(
	select distinct rank
	from product_sale_ranking
)
select * from mst_rank;

--- 8-8 카테고리들의 순위를 횡단적으로 출력하는 쿼리
with
product_sale_ranking as(
		select
		category_name,
		product_id,
		sales,
		row_number() over(partition by category_name order by sales desc) as rank
	from
		product_sales
),
mst_rank as(
	select distinct rank
	from product_sale_ranking
)
select 
	m.rank,
	r1.product_id 	as dvd,
	r1.sales 		as dvd_sales
	,
	r2.product_id	as cd,
	r2.sales 		as cd_sales,
	r3.product_id	as book,
	r3.sales 		as book_sales
from 
	mst_rank as m
	left join 
	product_sale_ranking r1
			on m.rank = r1.rank
	and r1.category_name = 'dvd'
	left join 
		product_sale_ranking as r2
	on m.rank = r2.rank
	and r2.category_name = 'cd'
	left join 
		product_sale_ranking as r3
	on m.rank = r3.rank
	and r3.category_name = 'book'
order by m.rank;

02.5 유사 테이블 만들기

임의의 레코드를 가진 유사 테이블 만들기

--- 8-9 디바이스 ID와 이름의 마스터 테이블을 만드는 쿼리
with
mst_devices as(
				select 1 as device_id, 'PC' as device_name
	union all 	select 2 as device_id, 'SP' as device_name
	union all 	select 3 as device_id, '애플리케이션' as device_name
)
select * from mst_devices;

--- 8-10 의사 테이블을 사용해 코드를 레이블로 변환하는 쿼리
with
mst_devices as(
				select 1 as device_id, 'PC' as device_name
	union all 	select 2 as device_id, 'SP' as device_name
	union all 	select 3 as device_id, '애플리케이션' as device_name
)
select 
	u.user_id,
	d.device_name
from
	mst_users as u
	left join
		mst_devices as d 
	on u.register_device = d.device_id;

union all의 경우 처리가 비교적 무거우므로 레코드 수가 많아지면 성능 문제가 발생할 수 있음

VALUES 구문을 사용한 유사 테이블 만들기

PostgreSQL에서는 INSERT 구문 이외에도 VALUES구문을 사용해 레코드를 만들 수 있음
성능적으로 좋을 뿐만 아니라 코드도 굉장히 간단해짐

--- 8-11 VALUES 구문을 사용해 동적으로 테이블 만드는 쿼리
with
mst_devices(device_id, device_name)as(
	values
		(1, 'PC'),
		(2, 'SP'),
		(3, '애플리케이션')
)
select * from mst_devices;

배열형 테이블 함수를 사용한 유사 테이블 만들기

postgreSQL의 경우 안됨 | hive, sparkSQL만 가능

--- 8-12 배열과 explode 함수를 사용해 동적으로 테이블을 만드는 쿼리
with
mst_devices as(
	select
		-- 배열을 열로 전개하기
		d[0] as device_id
		d[1] as device_name
from 
	-- 배열을 테이블로 전개하기
	(
		select explode(
		array(
			array('1', 'PC'),
			array('1', 'SP'),
			array('1', '애플리케이션')
		))d	
	)as t
)select * from mst_devices;

--- 8-13 map 자료형과 explode함수를 사용해 통적으로 테이블을 작성하는 쿼리
with
mst_devices as(
	select
  	--map 자료형의 데이터를 열로 전개하기
  	d['device_id'] as device_id,
  	d['device_name'] as device_name
  from
  	-- 배열을 테이블로 전개하기
  (
  	select explode(
      	array(
              map('device_id','1','device_name','PC')
              map('device_id','2','device_name','SP')
              map('device_id','3','device_name','애플리케이션')
          ))d
  ) as t
)
select * from mst_devices;

순번을 사용해 테이블 작성하기

--- 8-14 순번을 가진 유사 테이블 작성하는 쿼리
with
series as(
	-- 1부터 5까지 순번 생성하기
	select generate_series(1,5) as idx
)
select * from series;

--- 8-15 repeat 함수를 응용해서 순번을 작성하는 쿼리 
-- postgreSQL안됨 | hive, sparkSQL만 됨
select 
	row_number() over(order by x) as idx
from
	-- repeat함수와 split 함수를 조합해서 임의의 길이를 가진 배열을 생성하고
	-- explode로 전개하기
	(select explode(split(repeat('x',5 - 1),'x'))as x)as t;

Written by@[KyeongMinPark]

Docker, C++, C#, Java, Golang으로 개발 모니터링운영 및 개발, 자원수집기 Beat & Exporter 개발 Gitlab Runner CI/CD & Hugo 연동과 테스트코드및 등을 공부와 개발중 ORM, TDD, BDD, DDD, DesignPattern, WebAssembly Studying

GitHub

데이터분석을 위한 SQL레시피 3일차

2022-07-01-데이터분석을-위한-SQL레시피-3일차

목차

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

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

02.8강 여러 개의 테이블 조작하기

02.1 여러 개의 테이블을 세로로 결합하기

02.2 여러 개의 테이블을 가로로 정렬하기

02.3 조건 플래그를 0과 1로 표현하기

02.4 계산한 테이블에 이름 붙여 재사용하기

02.5 유사 테이블 만들기

02.`8강` 여러 개의 테이블 조작하기