[DAY 28] SQL 데이터 집계하기 - ROLLUP, WINDOW FUNCTION

JOIN, 서브쿼리, WITH와 CTE 등에 대한 내용을 복습하고 강의가 시작되었다.

ROLLUP, WINDOW FUNCTION 등 데이터 집계 함수에 대해 공부하였다.

ROLLUP

그룹별 소계, 총계를 구하기 위해 사용한다

ROLLUP 함수는 n개의 그룹을 지정하면 n+1개 조합이 출력되며, 그룹화 순서가 중요하다
예시 : ROLLUP(A, B, C)
1. A그룹별 B그룹별 C그룹에 대한 결과
2. A그룹별 B그룹에 대한 결과
3. A그룹에 대한 결과
4. 모든 데이터에 대한 결과

# 국가별, 성별 유저 수
select
  country,
  gender,
  count(id) as count_user
from `thelook_ecommerce.users`
group by rollup(country, gender)

country, gender 순서로 rollup 함수를 사용했다.

rollup 적용

2개의 그룹을 사용한 결과 3개의 결과를 얻었다.
1. 국가별, 성별 유저 수
2. 국가별 유저 수
3. 전체 유저 수

### SQL 연습문제 11-1
회원(users) 테이블에서 연령대 별 성별의 소계 및 합계를 조회하세요.

select 
  trunc(age, -1) || '대' as age_group,
  gender,
  count(id) as user_count
from `thelook_ecommerce.users`
group by rollup(age_group, gender)
order by age_group, gender

나이에서 1의자리를 버리고 연령대로 만들었다.

2개의 그룹을 사용한 결과 3개의 결과를 얻었다.
1. 연령대별, 성별 유저 수
2. 연령대별 유저 수
3. 전체 유저 수

Window Function(윈도우 함수)란?

함수이므로 동작 방식은 input -> 처리 -> output이다.
over절을 포함하며 현재 행과 관련이 있는 테이블 행들에 대해 계산을 수행한다.

분석 함수라고도 부르는 윈도우 함수는 
행 그룹의 값을 계산하고 각 행마다 하나의 결과를 반환한다.
행 그룹에 대해 하나의 결과를 반환하는 집계 함수와는 차이가 있다.

윈도우 함수 구조

WINDOW_FUNCTION_NAME(expression)
OVER(
  [PARTITION BY 컬럼]
  [ORDER BY 컬럼]
  [WINDOWING 절]
)

윈도우 함수의 분류
그룹 내 순위 관련 함수
- RANK, DENSE_RANK, ROW_NUMBER
그룹 내 행 순서 관련 함수
- LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
그룹 내 비율 관련 함수
- CUME_DIST, PERCENT_RANK, NTILE
그룹 내 집계 관련 함수
- SUM, MAX, MIN, AVG, COUNT

순위 윈도우 함수

RANK : 기준 값이 같다면 공동 순위가 부여되고 다음 순위는 동일값의 수만큼 건너뛰어 부여된다.

예시1

select 
  id,
  last_name,
  age,
  RANK() OVER (ORDER BY age) AS rank_number_in_all,
from `thelook_ecommerce.users`
where id between 1 and 10
order by age

아래 결과를 이해해보자.

rank 결과

id4의 Bailey와 id2의 Johnson은 둘 다 age가 33이므로 같은 4위가 되며 그 다음 순위는 6위이다.

예시2

select 
  id,
  last_name,
  country,
  age,
  RANK() OVER (partition by country ORDER BY age) AS rank_number_in_all,
from `thelook_ecommerce.users`
where id between 1 and 10
order by country

이번에는 partition by를 지정했다.

국가별로 rank() 함수가 적용된 것을 확인할 수 있다.

DENSE_RANK() : 동일값인 경우 동일 순위가 부여되는 것은 RANK()와 같지만, 다음 값을 건너뛰지 않고 순차적으로 부여한다.

예시

select 
  id,
  last_name,
  age,
  DENSE_RANK() OVER (ORDER BY age) AS rank_number_in_all,
from `thelook_ecommerce.users`
where id between 1 and 10
order by age

RANK()와의 차이를 중점으로 보자.

10개 데이터가 9위까지 표시되었다.

ROW_NUMBER() : 1부터 순차적으로 증가하는 번호를 부여한다. 동일값이어도 같은 번호를 부여하지 않는다.

예시

select 
  id,
  last_name,
  age,
  ROW_NUMBER() OVER (ORDER BY age) AS rank_number_in_all,
from `thelook_ecommerce.users`
where id between 1 and 10
order by age

 

1부터 10까지 같은 번호를 부여했다. age가 같아도 다른 번호가 된다.

순위 윈도우 함수 연습문제

### SQL 연습문제 11-2
상품정보(products) 테이블에서 각 브랜드 내에서 상품 가격별 순위(brand_rank)를 조회하세요.
- 조회 항목 : id, 브랜드(brand), 상품가격(cost), 각 브랜드 내 상품 가격이 높은 순위(brand_rank)
- 정렬 조건 : 브랜드 오름차순, 상품가격(cost) 내림차순
- RANK 함수 이용

select 
     id,
     brand,
     cost,
     rank() over(partition by brand order by cost desc) as rank
from `thelook_ecommerce.products`
where brand is not null
order by brand, rank

브랜드와 가격, 순위를 확인하면

같은 브랜드별로 가격 순위가 잘 계산되었다.

### SQL 연습문제 11-2
covid 데이터 세트, world_covid 테이블에서 
2020년 6월 1일 데이터 중에서 사망자수가 높은 지역(combined_key)과 순위를 조회하세요.
이때, 동일 값인 경우 동일 순위가 부여되고, 다음 순위는 건너뛰지 않고 순차 번호로 부여합니다.
- 조회 항목 : 발생날짜(refresh_date), 지역명(combined_key), deaths(사망자 수), 지역별 사망자 수 순위(deaths_rank)
- 정렬 조건 : 사망자 수(deaths)가 높은 순

select 
  refresh_date,
  combined_key,
  deaths,
  dense_rank() over(order by deaths desc) as deaths_rank
from `covid.world_covid`
where refresh_date = '2020-06-01'
order by deaths desc

 

문제의 순위 조건에 따라 dense_rank()를 사용하였다.

 

탐색 함수(행 순서 관련 윈도우 함수)

LAG : 이전 행의 값 가져오기
LEAD : 다음 행의 값 가져오기

LAG(expression [,offset] [,default])
LEAD(expression [,offset] [,default])

offset : 값을 가져올 행의 위치 (현재 행 대비 몇 행 차이)
default : 가져올 값이 없을 경우 기본값

예시

select 
  id,
  first_name,
  last_name,
  lag(id) over(order by id) as id_prev,
  lead(id, 2) over(order by id) as id_next,
 from `thelook_ecommerce.users`
 where id in (1,2,3,4,5)
 order by id

이전행 id, 다음행 id 가져오기

lead에는 offset을 지정하여 2개 뒤 행의 id를 가져왔다.

응용 : 코로나 확진자 수 확인

select 
  country_name,
  refresh_date, 
  confirmed,
  LAG(confirmed) OVER (PARTITION BY country_name ORDER BY refresh_date ) as prior_day,
  LEAD(confirmed) OVER (PARTITION BY country_name ORDER BY refresh_date) as next_day,
from `covid.world_covid`
where refresh_date between '2020-05-01' and '2020-05-10'
ORDER BY country_name, refresh_date

전, 후일 대비 비교를 할 수 있다.

FIRST_VALUE : 그룹 내의 첫 값을 구하기
LAST_VALUE : 그룹 내의 마지막 값을 구하기
주의할 점은, LAST_VALUE는 지금까지 읽은 행의 집합을 의미하기 때문에 항상 자기 자신이다.
전체 그룹에 대한 마지막 값을 구하려면 옵션을 주어야 한다.

예시

select 
  id,
  last_name,
  created_at,
  FIRST_VALUE(id) OVER ( ORDER BY id ) as first_id, 
  LAST_VALUE(id) OVER ( ORDER BY id ) as last_id
from `thelook_ecommerce.users`
where id between 1 and 10

첫 값, 마지막 값을 구한 결과

LAST_VALUE 값은 10만 나와야 할 것 같지만 현재 윈도우에서 끝 값을 의미하기 때문에 1~10이 나온다

전체 범위에서 끝 값을 출력하고 싶다.

LAST_VALUE(id) OVER (
               ORDER BY id
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
               as last_id

이렇게 WINDOWING을 지정하면 된다.

window 이해

- ROWS : 부분집합인 윈도우 크기를 물리적인 단위로 행 집합 지정
- UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫번째 ROW
- UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 ROW

그 결과

원하는대로 10이 잘 출력된다.

NTH_VALUE : 현재 윈도우 프레임에 있는 N번째 행을 반환하고 이 행이 없으면 NULL을 반환

예시

select 
  id,
  last_name,
  created_at,
  NTH_VALUE(id, 5) OVER ( ORDER BY id ) as second_signup_user_id, 
  NTH_VALUE(id, 5) OVER ( 
    ORDER BY id 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as second_signup_user_id
from `thelook_ecommerce.users`
where id between 1 and 20
order by id

ROWS를 지정하지 않고 현재 윈도우 기준 5번째 행을 구해보고, 모든 범위로 지정하고 구해봤다.

현재윈도우 기준이면 1,2,3,4행에서는 5번째 행의 id를 구할 수 없어서 NULL이 된다
전체 범위로 지정하면 첫행부터 5번째 행의 id를 구할 수 있다.

그룹 내 비율 관련 함수

PERCENT_RANK() : 현재 행의 상대적 순위를 계산한다.
0과 1 사이의 범위에서 행의 백분율 순위가 계산된다.

예시

select 
  brand,
  cost,
  trunc(PERCENT_RANK() OVER (PARTITION BY brand ORDER BY cost ), 2)
from `thelook_ecommerce.products`
where brand is not null
order by brand, cost

각 판매 브랜드의 가격에 대한 백분율 순위 계산

cost가 제일 작은 값은 percent_rank가 0, cost가 제일 큰 값은 percent_rank가 1이 된다

CUME_DIST() : 누적 분포
- 0보다 크고 1보다 작거나 같은 값이 나온다.
- n보다 값이 작거나 같은 행 개수 / 현재 윈도우 또는 파티션의 row 개수

예시

select value,
  cume_dist() over( 
    order by value 
  ) cumulative_distribution
from `weniv.cumedist`;

percent_rank와 달리 시작 값이 0보다 크다

1행 : 1 / 5
2행, 3행 : 3 / 5
4행 : 4 / 5
5행 : 5 / 5

### SQL 연습문제 11-5 
world_covid 테이블에서 
2022년 9월 3일의  확진자 순위에 따른 누적 분포 비율을 조회하세요
- 조회 항목 : 지역명(combined_key), 확진자 수(confirmed), 확진자 순위에 따른 누적 분포 비율(cume_dist)
- 정렬 조건 : 확진자 수(confirmed)가 높은 순
select 
  combined_key,
  confirmed,
  cume_dist() over ( order by confirmed desc ) as cume_dist_confirmed,
from `covid.world_covid`
where refresh_date = '2022-09-03'
order by confirmed desc

화면에 다 담을 수는 없지만 행의 개수가 4000개가 넘어서 누적 분포 비율이 굉장히 작다.

NTILE(n) : 레코드의 집합을 n개의 영역으로 구분하고 소속 영역을 구한다.

예시

select 
  id,
  first_name,
  last_name,
  age,
  ntile(4) over (order by age) as tile_number
from `thelook_ecommerce.users`
where id between 1 and 7
order by age

7명의 사람을 나이 오름차순으로 4개 그룹으로 나눈다.

7명은 4개 그룹으로 나누므로 모든 그룹 인원수가 같을 수 없다.
1, 2, 3은 2명씩, 4는 1명이 되었다.

예시처럼 숫자가 나누어 떨어지지 않는 경우 어떻게 될까?

위 사진은 10개의 데이터를 4개 그룹으로 구분하는 예시다.
이처럼 나누어 떨어지지 않는 경우 그 나머지는 순서대로 하나씩 할당된다.
결론적으로 3개, 3개, 2개, 2개가 되는 것이다.

집계 분석 함수(그룹 내 집계 관련 함수)

집계 함수 종류 : COUNT(개수),  SUM(합계), AVG(평균), MAX(최대), MIN(최소)

가격 누적 합계를 확인하고 싶은 경우

select name, cost, sum(cost)
from product

위처럼 작성하면 에러가 발생한다. group by를 사용하지 않고 집계함수를 사용하였기 때문이다.

이런 경우 Window Function을 사용해야 하는 것이다.

select
    name,
    cost,
    sum(cost) over() as total
from product

하지만 over절을 제대로 작성하지 않으면

합계의 대상 범위가 전체 범위가 되어 모든 행에 전체 합계가 출력된다.

원하는 누적 합계를 얻기 위해서는 아래와 같이 작성해야한다.

select
    name,
    cost,
    sum(cost) over(order by cost) as total
from product

over에 order by cost를 사용하여 비용의 누적 합계를 얻는다.

위에서 언급했듯 Window 범위를 정확히 지정해야 원하는 값을 얻을 수 있다.
다른 집계함수 COUNT, AVG, MAX, MIN 또한 같은 방법으로 사용할 수 있다.


활용도 높은 유용한 기능을 배운 것 같다.

데이터분석 과정이어서 그동안 계속 select에 초점을 맞춰 사용했었는데
오늘 데이터 insert, update, delete(추가, 수정, 삭제)와 테이블 create, alter, drop(생성, 수정, 삭제)에 대한 내용을 공부했다.