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

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