실무에서 자주 쓰는 sql 패턴

4Ls 회고는 뭘까?

Liked : 좋았던 점

Lacked : 아쉬웠던 점

Learned : 배운 점

Longed for : 앞으로 바라는 점

윈도우 함수의 활용

집계 함수

sum(), avg(), count() 등

<예시>

사용 데이터 베이스 : US E-Commerce Records 2020

테이블 : records

Q. 서브 카테고리 별 매출액(sales)을 계산하고 그 매출액이 각 서브 카테고리가 속해있는 카테고리 안에서 비중을 얼마나 차지하는지, 그리고 전체 매출액에서 비중을 얼마나 차지하는지 각각 계산하시오. 결과물은 아래 모양을 참고.

•

category : 카테고리 이름

•

sub_category : 서브 카테고리 이름

•

sales_sub_category : 서브 카테고리 별 매출액 합계

•

sales_category : 카테고리별 매출액 합계

•

sales_total : 전체 매출액

•

pct_in_category : 카테고리 매출 중 해당 서브 카테고리의 매출 비율

•

pct_in_total : 전체 매출 중 해당 서브 카테고리의 매출 비율

select category
      ,sub_category
      ,round(sum_sales, 2) as sales_sub_category
      ,round(sum(sum_sales) over (partition by category), 2) as sales_category -- 카테고리별 매출액 합계
      ,round(sum(sum_sales) over() , 2) as sales_total -- 전 매출액 합계
      ,round(sum_sales / sum(sum_sales) over (partition by category), 2) as pct_in_category
			,round(sum_sales / sum(sum_sales) over(), 2) as pct_in_total
from (
  select category
        ,sub_category
        ,sum(sales) as sum_sales -- 서브 카테고리별 매출액 합계
  from records
  group by category,sub_category
) as sales;
SQL
복사

category	sub_category	sales_sub_category	sales_category	sales_total	pct_in_category	pct_in_total
Furniture	Chairs	95554.35	215387.27	733215.26	0.44	0.13
Furniture	Furnishings	28915.09	215387.27	733215.26	0.13	0.04
Furniture	Tables	60893.54	215387.27	733215.26	0.28	0.08
Furniture	Bookcases	30024.28	215387.27	733215.26	0.14	0.04
Office Supplies	Binders	72788.05	246097.18	733215.26	0.3	0.1
Office Supplies	Storage	69677.62	246097.18	733215.26	0.28	0.1
Office Supplies	Art	8863.07	246097.18	733215.26	0.04	0.01
Office Supplies	Appliances	42926.93	246097.18	733215.26	0.17	0.06
Office Supplies	Labels	3861.22	246097.18	733215.26	0.02	0.01
Office Supplies	Paper	27694.72	246097.18	733215.26	0.11	0.04
Office Supplies	Supplies	16049.41	246097.18	733215.26	0.07	0.02
Office Supplies	Fasteners	857.59	246097.18	733215.26	0	0
Office Supplies	Envelopes	3378.57	246097.18	733215.26	0.01	0
Technology	Copiers	62899.39	271730.81	733215.26	0.23	0.09
Technology	Phones	105340.52	271730.81	733215.26	0.39	0.14
Technology	Accessories	59946.23	271730.81	733215.26	0.22	0.08
Technology	Machines	43544.68	271730.81	733215.26	0.16	0.06

RANK

row_number(), rank(), dense_rank()

→ 비즈니스에서

“성연령별로 인기있는 컨텐츠 10개씩 뽑아주세요. 큰 차이가 있나요? 성연령별로 별 차이가 없다고 하면, 어떤 기준으로 나누면 좀 차이가 있을까요? 추천 컨텐츠 영역을 기획하려고 하는데 기준을 어떻게 잡으면 좋을지 고민입니다”

< 예시 >

리트코드 185번

LEAD, LAG

LEAD(), LAG()

→ 비즈니스에서 : 세션 정의하기 등 연속된 로그 안에서 패턴 찾기

“택배 차량이 배송 중간에 정차하는 것을 최소화하고 싶은데, 일단 그 전에 지금 하루 배송 중에 몇 번 정차하는지 한 번 정차 할 때마다 평균적으로 몇 박스 정도 배송하는지 알고싶습니다.”

“사람들이 한 세션 안에 평균적으로 몇 번 클릭을 하는지, 특정 기능을 몇 번 사용하는지 알고 싶습니다. 그런데 세션 id는 db에 없습니다..”

유저 8번의 session 정의하기

step 1. 유저 8번의 행동 로그 추출

select user_id
		,event_type
		,event_name
		,occurred_at
		,lag(occurred_at, 1) over(partition by user_id order by occurred_at) as last_event
		,lead(occurred_at, 1) over(partition by user_id order by occurred_at) as next_event
		,row_number() over () as id
from tutorial.yammer_events e
where e.event_type = 'engagement'
and user_id = 8
order by user_id, occurred_at;
SQL
복사

step 2. 로그들 간 시간 간격 계산

select user_id
		,event_type
		,event_name
		,occurred_at
		,occurred_at - lag(occurred_at, 1) over(partition by user_id order by occurred_at) as last_event_term
		,lead(occurred_at, 1) over(partition by user_id order by occurred_at) - occurred_at as next_event_term
		,row_number() over () as id
from tutorial.yammer_events e
where e.event_type = 'engagement'
and user_id = 8
order by user_id, occurred_at
SQL
복사

step 3. 세션 시작 지점과 끝 지점 로그만 필터링하고, 같은 세션끼리 같은 id 부여

•

last_event 가 없음 , last_event 가 10분 이상임

•

next_event 가 10분 이상임, next_event 가 없음

select bounds.*
		,CASE WHEN last_event_term >= interval '10 MINUTE' THEN id
			WHEN last_event IS NULL THEN id-- 시작 세션이면 같은 id 부여
			ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY id) -- 끝 세션이면 그 전의 id를 받아옴
		 END as session
 from(
	select user_id
			,event_type
			,event_name
			,occurred_at
			,timestampdiff(second, lag(occurred_at) over(partition by user_id order by occurred_at), occurred_at) as last_event_term
			,timestampdiff(second, occurred_at, lead(occurred_at, 1) over(partition by user_id order by occurred_at)) as next_event_term
			,row_number() over () as id
	from tutorial.yammer_events e
	where e.event_type = 'engagement'
	and user_id = 8
	order by user_id, occurred_at
) bounds
where last_event_term >= interval '10 MINUTE'
or last_event_term IS NULL
or next_event_term >= interval '10 MINUTE'
or next_event_term IS NULL
SQL
복사

step 4. 세션 아이디별로 session_start, session_end 값을 가질 수 있도록 데이터 가공

select user_id
		,session
		,MIN(occurred_at) as session_start -- 세션의 시작 
		,MAX(occurred_at) as session_end -- 세션의 끝
from(
	select bounds.*
			,CASE WHEN last_event >= interval '10 MINUTE' THEN id
				WHEN last_event IS NULL THEN id-- 시작 세션이면 같은 id 부여
				ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY id) -- 끝 세션이면 그 전의 id를 받아옴
			 END as session
	 from(
		select user_id
				,event_type
				,event_name
				,occurred_at
				,occurred_at - lag(occurred_at, 1) over(partition by user_id order by occurred_at) as last_event_term
				,lead(occurred_at, 1) over(partition by user_id order by occurred_at) - occurred_at as next_event_term
				,row_number() over () as id
		from tutorial.yammer_events e
		where e.event_type = 'engagement'
		and user_id = 8
		order by user_id, occurred_at
	) bounds
	where last_event >= interval '10 MINUTE'
	or last_event IS NULL
	or next_event >= interval '10 MINUTE'
	or next_event IS NULL
) final
group by 1,2
SQL
복사

길고 복잡한 쿼리 핵심 로직 이해하기 꿀팁

일부 데이터만 본다. 한 번에 모든 데이터를 다 보면 핵심 로직 보기전에 헷갈려서 포기하게 된다. (user_id = 8)

쿼리를 분해하고, 로직의 가장 안쪽에 있는 서브쿼리부터 이해한다.

쿼리 결과를 스프레드 시트로 정리하며 스텝 별 데이터와 쿼리의 논리를 눈으로 확인한다.

포기하지 말고 정확하고 명확하게 이해될 때까지 끈질기게 본다.