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

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

sql의 특징은 데이터를 집합으로 다루는 것

#1 데이터 집약

-그룹의 특징 잡기

집약함수:여러 레코드를 기반으로 하나의 값을 리턴하는 함수

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

COUNT:지정한 컬럼의 레코드 수 리턴

DISTINCT:중복 제외

SUM:합계,AVG:평균(컬럼의 자료형이 정수 또는 실수의 숫자 자료형이어야함)

MAX:최댓값,MIN:최솟값(대소 비교가 가능한 자료형(숫자,문자열,타임스탬프등에 적용가능)

>>집약함수를 사용해서 테이블 전체의 특징량 계산하는 쿼리

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

;

 

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

>>사용자 기반으로 데이터를 분할하고 집약 함수를 적용하는 쿼리

GROUP BY 구문 사용한 쿼리에서는 GROUP BY 구문에 지정한 컬럼 또는 집약 함수만 SELECT 구문의 컬럼으로 지정 가능

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

;

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

>>윈도 함수를 사용해 집약 함수의 결과와 원래 값을 동시에 다루는 쿼리

윈도우 함수를 사용하려면 집약 함수 뒤에 OVER 구분을 붙이고 여기에 윈도 함수를 지정

매개변수에 PARTTITION BY <컬럼이름> 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약 함수 적용

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

;

-그룹 내부의 순서

>ORDER BY 구문으로 순서 정의하기

ROW_NUMBER:순서에 유일한 순위 번호를 붙이는 함수

RANK:같은 순위의 레코드 뒤의 순위 번호를 건너뜀

DENSE_RANK:같은 순위의 레코드 뒤의 순위 번호를 건너뛰지 않음

LAG:현재 행을 기준으로 행 앞의 값 추출

LEAD:현재 행을 기준으로 행 뒤의 값 추출

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 구문과 집약 함수 조합하기

select

product_id

,score

 

--점수 순서로 유일한 순위를 붙임

,row_number()over(order by score desc) as row

 

--순위 상위부터의 누계 점수 계산하기

,sum(score)

over(order by score desc) as row

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

;

>윈도 프레임 지정에 대해

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

>>윈도 프레임 지정별 상품 ID를 집약하는 쿼리

윈도우 함수에 프레임 지정하지 않으면 ORDER BY 구문이 없는 경우 모든 행,

ORDER BY 구문이 있는 경우 첫 행에서 현재 행까지가 디폴트 프레임으로 지정된다

select

product_id

,score

 

--점수 순서로 유일한 순위를 붙임

,row_number()over(order by score desc) as row

 

--가장 앞 순위부터 가장 뒷 순위까지의 범위를 대상으로 상품 ID 집약하기

,array_agg(product_id)

, collect_list(product_id)

over(order by score desc)

rows between unbounded preceding and unbounded following)

as whole_agg

 

--가장 앞 순위부터 현재 순위까지의 범위를 대상으로 상품ID 집약하기

,array_agg(pruduct_id)

, collect_list(product_id)

over(order by score desc

rows between unbounded preceding and current row)

as cum_agg

 

--순위 하나 앞과 하나 뒤까지의 범위를 대상으로 상품ID 집약하기

,array_agg(product_id)

, collect_list(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 조합하기

>>윈도 함수를 사용해 카테고리들의 순위를 계산하는 쿼리

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개 추출하기

>>카테고리들의 순위 상위 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

--외부 쿼리에서 순위 활용해 압축하기

where rank<=2

order by category, rank

;

>>카테고리별 순위 최상위 상품을 추출하는 쿼리

--distinct 구문을 사용해 중복 제거하기

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

;

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

>행을 열로 변환하기

미리 열의 종류와 수를 알고 있을 때만 사용 가능

>>행으로 저장된 지표 값을 열로 변환하는 쿼리

select

dt

,max(case when indicator = 'impressions' then val end) as impressisions

,max(case when indicator = 'impressions' then val end) as sessions

,max(case when indicator = 'impressions' then val end) as users

from daily_kpi

group by dt

order by dt

;

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

>>행을 집약해서 쉼표로 구분된 문자열로 변환하기

select

purchase_id

--상품ID를 배열에 집약하고 쉼표로 구분된 문자열로 변환하기

,string_agg(product_id, ',') as products_ids

from purchase_detail_log

group by purchase_id

order by purchase_id

;

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

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

>>일련 번호를 가진 피벗 테이블을 사용해 행으로 변화는 쿼리

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

;

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

>>테이블 함수를 사용해 배열을 행으로 전개하는 쿼리

select unnest(array['A001','A002','A003']) as product_id;

>>테이블 함수를 사용해 쉼표로 구분된 문자열 데이터를 행으로 전개하는 쿼리

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 ;

>>postgreSQL에서 쉼표로 구분된 데이터를 행으로 전개하는 쿼리

select

purchase_id

--쉼표로 구분된 문자열을 한번에 행으로 전개하기

,regexp_split_to_table(product_ids, ',') as product_id

from purchase_log

;

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

select *

from(

select 1 as idx

union all select 2 as idx

union all select 3 as idx

) as pivot

;

>split_part 함수 사용 예

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

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

;

>>문자 수의 차이를 사용해 상품 수를 계산하는 쿼리

select

purchase_id

,product_ids

--상품 id 문자열을 기반으로 쉼표를 제거하고,

--문자 수의 차이를 계산해서 상품 수 구하기

, 1 + char_length(product_ids)

- char_length(replace(product_ids, ',', ''))

as product_num

from

purchase_log

;

>>피벗 테이블을 사용해 문자열을 행으로 전개하는 쿼리

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

;

 

 

 

 

 

<데이터 분석을 위한 sql레시피에서 발췌한 내용입니다>