SQL Review 6th (Rollup, Window)

trunc(age, -1)  || '대' 

여기에서 왜 합집합 연산이 두개 필요할까 해서 

| 을 1개만 사용해서 오류를 내보았다.

이런 오류 발생

| 연산자 사용하려면 형태가 같아야 하는데 숫자와 string 인것 같아서 그렇다.

근데 왜 두개하면 해결되는거지..? 

 

 

 

💡비트와이즈(비트)연산 ) : 비트 연산(bitwise operation)은 한 개 or 두 개의 이진수에 대해 비트단위로 적용되는 연산. 

( ex)Not, Or, And  )

 

 

 

윈도우 함수 

▼ 윈도우 함수의 분류

• 그룹 내 순위 관련 함수(RANKING FAMILY)
  • RANK, DENSE_RANK, ROW_NUMBER
• 그룹 내 집계 관련 함수(WINDOW AGGREGATE FAMILY)
  • SUM, MAX, MIN, AVG, COUNT
• 그룹 내 행 순서 관련 함수
  • LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
• 그룹 내 비율 관련 함수
  • CUME_DIST, PERCENT_RANK, NT

 

▼ 윈도우 함수의 문법

- 함수 이름(컬럼, OFFSET) OVER (PARTITION BY 파티션_컬럼 ORDER BY 정렬_컬럼)
- OFFSET : 값을 가져올 행의 위치. 기본 값은 1이고 생략 가능

# 필요에 따라 PARTITION BY는 생략 가능

 

 

순위 윈도우 함수

• RANK() : 파티션 내에서 현재 행의 순위를 부여. 동일 값인 경우 동일 순위가 부여되고, 다음 순위는 동일값의 수만큼 건너뛰어 부여

나이순 

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

 

국가내 나이순

select id,
first_name,
last_name,
country,
age,
rank() over (partition by country order by age) as rank_number_in_country
from `thelook_ecommerce.users`
where id between 1 and 20
order by country, age

국가 내에서 유저의 나이순으로 랭킹을 매기고 , 

그다음 국가에서도 나이순으로 랭킹 매긴다.

partition : rank() 로 순위매길 구역? 범위 ? 라고 생각하면 될 것 같다

 

 

• Dense Rank() : 파티션 내에서 현재 행의 순위를 부여. 동일 값인 경우 동일 순위가 부여되고, 다음 순위는 건너뛰지 않고 순차 번호로 부여

Rank()와의 차이점 ❗

Rank() 는 동일 값에 동일 순위 부여한 뒤에 다음 순위는 동일 값 수만큼 빼고 부여

Dense Rank()는 동일 값에 동일 순위 부여한 뒤에 순차적으로 부여

# dense_rank_number_in_country : 유저의 국가내 나이순 랭킹 
select 
  id,
  first_name,
  last_name,
  country,
  DENSE_RANK() OVER ( PARTITION BY country ORDER BY age ) AS dense_rank_number_in_country,
from `thelook_ecommerce.users`
where id between 1 and 20
order by country, age

 

 

ROW_NUMBER() : 

• 파티션 내에서 1부터 순차적으로 하나씩 증가하는 번호를 생성

Rank() 함수와의 차이점❗

1) 동일한 값이 있든 없든 순차적으로 숫자 부여

2) select 에 기준이 되는(여기선 created_at : 회원가입 시기) 컬럼을 언급하지 않아도됨

 

회원가입순으로  번호 조회

select 
  id,
  first_name,
  last_name,
  country,
  created_at,
  ROW_NUMBER() OVER ( ORDER BY created_at ) AS order_number,
from `thelook_ecommerce.users`
where id between 1 and 20

 

국가 내 나이순 (나이 동일한 경우 순차적으로 번호 부여)

# order_number_in_country : 유저의 국가내에서 나이순 번호(나이가 같은 경우 순차적으로 번호 부여)
select 
  id,
  first_name,
  last_name,
  country,
  ROW_NUMBER() OVER ( PARTITION BY country ORDER BY age ) AS order_number_in_country,
from `thelook_ecommerce.users`
where id between 1 and 20

 

탐색 윈도우 함수 (그룹 내 행 순서)

 

• LAG, LEAD : LAG는 이전 행의 필드를 읽고, LEAD는 다음 행의 필드를 읽음

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

 

 

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

 

다음 이벤트 테이블에서 얼마만의 재방문인지 확인

 

select 
  id,
  user_id,
  created_at,
  lag(created_at) over(order by created_at) as created_at_prev,
  datetime_diff(DATETIME(created_at), DATETIME(lag(created_at) over(order by created_at)), second) as prev_visit_second
 from `thelook_ecommerce.events`
 where user_id = 5782
 order by created_at

 

코비드 데이터 세트에서 2020년 5월 1일~10일의 국가별로 전날 확진자수와 다음날 확진자수를 표시

select country_name,
state_name,
refresh_date,
confirmed,
(confirmed - lag(confirmed) over(partition by country_name, state_name order by refresh_date)) as diff_confirmed_prior_day,
(deaths - lag(deaths) over (partition by country_name, state_name order by refresh_date)) as diff_deaths_prior_day
from `dataflix_covid_dataset.world_covid`
order by country_name, state_name, refresh_date;

따로 lag(confirmed)를 변수 지정해주지 않고 바로 계산한 뒤에 as로 별칭 지정해주어서 해줘도 가능

 

 

+ SQL 자체 연습문제 

 

dataflix_covid_dataset 데이터 세트, 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 `dataflix_covid_dataset.world_covid`
where refresh_date = '2020-06-01'
order by deaths desc

❗동기님 질문❗

death_ranks 는 사망자수가 많은 순이니 지역별로 구분해줘야되지 않나요 ? (partition by country_name)

=> world_covid 의 combine_key 자체가 각 나라 별 수집 데이터이기 때문에 별도로 구분 필요 X

     구분해주게 되면 모든 결과값에 1이뜸

 

 

오전에 병원 갔다와서 오후부터 들었는데... 하나도 못알아듣겠어서 

강의 시간에 그냥 강의 첫내용 부터 시작해서 정리해봤다

SQL은 아무래도 별도의 개념교재를 사서 개념을 좀 잡아봐야 될 것 같다