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

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-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, ',', '')));

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

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

• 실습 쿼리
  • 마스터 테이블의 행 수를 변경하지 않고 데이터를 가로 정렬하려면
    • 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;

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

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

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

02.5 유사 테이블 만들기

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

  --- 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의 경우 처리가 비교적 무거우므로 레코드 수가 많아지면 성능 문제가 발생할 수 있음

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

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

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