[데이터 분석을 위한 SQL 레시피] 7강 - 하나의 테이블에 대한 조작

 

5,6 강에서는 레코드 하나하나를 조작했지만 실무에서는 대량의 레코드를 처리해야함.

대량의 데이터 집계가 필요하고, 지표를 사용해 데이터 전체의 특징을 파악해야함

 

테이블의 데이터를 집약하고, 테이블의 형식이 집계에 적합하지 않은 경우 테이블을 가공해야함

 

윈도우 함수

---

1. 그룹의 특징 잡기

review

SELECT
	COUNT(*) AS total_count,
	COUNT(DISTINCT user_id) as user_count,
	sum(score) as sum,
	avg(score) as avg,
	max(score) as max
from
	review;

 

 

group by user_id

 

count, sum, avg, max, min 등 다양한 집계 함수로 컬럼( or 그룹)의 특징을 잡을 수 있음

distinct : nunique

 

group by 사용시 주의 사항

: select 구문의 컬럼으로 집약 함수 혹은 group by 구문에 지정한 컬럼만 사용 가능

 

일반적으로 집약함수를 적용한 값과 집약 전의 값은 동시에 사용할 수 없음

-> 윈도우 함수를 사용하면 가능

 

 

윈도우 함수(Window Function)

SELECT
	WINDOW_FUNCTION(ARGUMENTS) OVER([PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절])
FROM
	테이블명;

윈도우 함수는 행들간의 관계를 정의하기 위한 함수

 

WINDOW_FUNCTION : 윈도우 함수(아래 표 참고)

ARGUMENTS : 인수, 함수에 따라 0~n개

OVER : 윈도우 함수 사용시 필수적으로 작성해줘야하는 부분

PARTITION BY : 해당 컬럼을 기반으로 그룹화 함, GROUP BY와 비슷

ORDER BY : 정렬

WINDOWING : 함수의 대상이 되는 행의 범위를 지정해줌.

 

+ sql server 라는 게 특별한 개념이 아니라 MySQL, ORACLE 와 같은 DBMS의 한 종류 

 

 

윈도우 함수로 일반 집계 함수를 활용할 때는 아래와 같이 활용하면 된다!

SELECT
	user_id,
	product_id,
	score,
	avg(score) over() as avg_score1,
	avg(score) over(partition by user_id) as avg_score2,
	score - avg(score) over(partition by user_id) as diff
from
	review;

 

 

 

COUNT(*)  vs  COUNT(열이름)

COUNT(*) : NULL 여부 관계 없이 검색된 행 수의 카운트

+ count(*) 이랑 count(1)은 같음. count 함수 매개변수에 어떤 숫자를 넣어줘도 같은 기능임.
COUNT(열이름) : NULL 제외하고 검색된 행 수의 카운트. 속도 느림

---

2. 그룹 내부의 순서

윈도우 함수를 활용하면 SQL로 순서를 다룰 수 있음

 

row_number() : 점수 순서로 유일한 순위 매핑

rank() : 같은 순위를 허용해서 순위 매핑

dense_rank() : 같은 순위가 있을 때 다음 순위를 건너 뛰고 이어서 순위 매핑

 

lag() : 현재 행보다 앞에 있는 행의 값 추출, 몇 칸 앞인지 지정 가능

lead() : 현재 행보다 뒤에 있는 행의 값 추출, 몇 칸 뒤인지 지정 가능

 

over() 안에는 order by 혹은 partition by를 꼭 해줘야함.

DBMS 에 따라 오류 나거나, 모든 행을 하나의 그룹으로 생각해서 각 행에 동일한 순위를 할당할 수도 있음

 

select
	product_id,
	score,
	row_number() over(order by score desc) as row
from
	popular_products;

 

row는 score를 내림차순으로 정렬한 뒤, 순서대로 순위가 매핑된 것

 

select
	product_id,
	score,
	row_number() over(order by score desc) as row,
	rank() over(order by score) as rank
from
	popular_products;

 

order by score 라고 하면?

rank 칼럼에서는 score를 기준으로 오름차순 정렬이 되면서 그에 맞는 순위가 매핑 됨

score값이 새로 정렬 됨에 따라 row 컬럼도 함께 정렬됨. row 컬럼은 기존에 매핑되었던 순위가 유지되기 때문

 

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;

 

교재의 코드를 모두 실행해보면 위의 그림과 같다

 

+ 윈도우 함수의 over 구문 내에서 뿐만아니라, from 절 다음에 order by를 사용할 수도 있음

order by score 라고 하면 모든 결과가 뒤집힘

 

윈도우 함수 WINDOWING 절

ROWS - 행을 기준으로 범위 지정

RANGE - 행이 가지고 있는값을 기준으로 범위 지정

 

ROWS BETWEEN start AND end

 

CURRENT ROW : 현재 행

n PRECEDING : n 행 앞

n FOLLOWING : n 행 뒤

UNBOUNDED PRECEDING : 이전 행 전부

UNBOUNDED FOLLOWING : 이후 행 전부

 

+ 

FIRST_VALUE : 윈도우 내부의 가장 첫 번째 레코드 추출LAST_VALUE : 윈도우 내부의 가장 마지막 레코드 추출-> 최대, 최소랑은 다름

 

array_agg(컬럼명) : 행을 집약해서 {a,b,c} 형태로 반환string_agg(컬럼명, 문자열) : 행을 집약해서 특정 문자열로 구분되는 문자열 반환  

 

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
	,FIRST_VALUE(product_id) over(order by score desc
		rows between unbounded preceding and unbounded following) as first_value
	,array_agg(product_id) over(order by score desc
		rows unbounded preceding)
from
	popular_products;

 

rows unbounded preceding = rows between unbouned preceding and current row

: between 안쓰면 자동으로 현재 행 기준으로 계산하기 때문에 이 둘은 같은 기능

 

 

응용하기 - 카테고리 별 순위 최상위 상품 추출

select distinct 구문으로 중복 제거

select distinct
	category
	,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;

---

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

세로 기반 데이터 : 행 기반 데이터

가로 기반 데이터 : 열 기반 데이터

 

sql은 행(레코드) 기반으로 처리가 기본

하지만 출력에서는 데이터를 열로 전개해야 가독성이 높은 경우가 있음

 

daily_kpi

select
	dt
	, max(case when indicator = 'impressions' then val end) as impressions
	, max(case when indicator = 'sessions' then val end) as impressions
	, max(case when indicator = 'users' then val end) as impressions
from 
	daily_kpi
group by 
	dt;

 

-> 이렇게 테이블의 구조를 새롭게 바꿀 수 있음,

이렇게 바꿀 필요가 있나? 싶지만 그냥 테이블을 여러 형태로 바꿀 수 있고 볼 수 있는 능력이 필요한 것 같음

case 구문 중요!

 

 

+ 앞에서 언급한 string_add 사용하기

select
	purchase_id
	, string_agg(product_id, ',') as product_ids
from
	purchase_detail_log
group by
	purchase_id;

 

string_agg, array_agg는 함수 안에 order by 사용이 가능하다!

 

select
	purchase_id
	, string_agg(product_id, ',' order by product_id desc) as product_ids
from
	purchase_detail_log
group by
	purchase_id;

 

어떠한 순서, 컬럼으로든 정렬 구문이 들어가면 문자열 길이를 기준으로 내림차순 정렬이 됨

문자열 안의 요소들을 정렬하고 싶을 땐, 그 컬럼을 기준으로 정렬하면 됨

---

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

 

quarterly_sales

select 
	q.year
	, 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
	, 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;

 

cross join을 활용해서 테이블을 변환할 수 있음

-> 이렇게 까지 해서 변환해야할 일이 실제로 있을까? 하는 생각이 듦

 

q와 p를 cross join 한 테이블

 

 

테이블 함수

- 함수의 리턴값이 테이블인 함수

- unnest() : 배열을 레코드로 전개할 수 있음.

 

select
	*
from
	unnest(array['a','b','c']);

 

 

미들웨어에 따라서 select 구문 내부에 스칼라 값과 테이블을 동시에 다룰 수 없기도함

-> 이럴 때 cross join을 사용함!

-> Postgresql은 동시에 다룰 수 있음!

 

select
	purchase_id
	,regexp_split_to_table(product_ids, ',') as product_id
from
	purchase_log;

regexp_split_to_table 은 ',' 로 구분된 문자열들을 나눠서 행으로 전개하여 테이블을 반환함