3DPIT Study Blog

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

June 29, 2022

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

목차

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

01.1 날짜/시간 계산하기

01.2 IP 주소 다루기

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

02.1 그룹의 특징 잡기

02.2 그룹 내부의 순서

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

  • 목표범위 : 03.6강 여러 개의 값에 대한 조작(5.날짜/시간 계산하기) ~ 03.7강 하나의 테이블에 대한 조작(3.세로기반 데이터를 가로 기반으로 변환하기까지)

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으로 메우기
    --- 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;
    • 위에 처럼 10진수를 0으로 메워서 고정 길이 문자열을 만들면, 문자열 상태로 대소 비교 등을 할 수 있음

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;
Written by@[KyeongMinPark]
Docker, C++, C#, Java, Golang으로 개발 모니터링운영 및 개발, 자원수집기 Beat & Exporter 개발 Gitlab Runner CI/CD & Hugo 연동과 테스트코드및 등을 공부와 개발중 ORM, TDD, BDD, DDD, DesignPattern, WebAssembly Studying

GitHub