[TIL] SQL_10일차(Window Function,Rank,Sum, date type)

이전 시간에 이어서 이번 시간에는 아래와 같은 내용에 대해 공부합니다.

✍🏻배운내용🌞

1. Window Function 이란?

2. SQL 의 날짜 데이터 형식(포맷 fomat)과 조건

---

1. Window Function 이란?

1) Window Function 은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줍니다.

 

2) 기본 SQL 구조로 해결하기 위해서는 복잡하게 Subquery 문을 이용하거나, 여러번의 연산을 수행해줘야 하지만, 자체적으로 제공해 주는 기능( Window Function )을 이용하면 조금 더 편리합니다.

 

3) 여기에서는 많은 Window Function 중에 누적합, 순위 Rank 를 구하는 것만 학습합니다.

⇒ Rank() 는 ‘특정 기준으로 순위를 매겨주는’ 함수입니다.

⇒ Sum() 은 앞서 배운 합계를 구하는 기능과 동일합니다. 특히, 누적합이 필요하거나 카테고리별 합계컬럼과 원본 컬럼을 함께 이용할 때 유용하게 사용할 수 있습니다.

 

[ Window Function 의 기본 구조 ]

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

 

• window_function : 기능 명을 사용해줍니다. (sum, rank, avg 와 같이 기능명이 있습니다)

• argument : 함수에 따라 작성하거나 생략합니다.

• partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하다고 생각해주시면 됩니다.

• order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어줍니다.

 

 

[실습1] N 번째까지의 대상을 조회하고 싶을 때, Rank()

1. 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

1-1) 음식 타입별, 음식점별 주문 건수 집계하기

SELECT cuisine_type, restaurant_name, count(1) cnt_order FROM food_orders group by 1,2

1-2) Rank 함수 적용하기

SELECT cuisine_type, restaurant_name, cnt_order,                rank() over (partition by cuisine_type order by cnt_order desc) ranking FROM ( SELECT cuisine_type, restaurant_name, count(1) cnt_order FROM food_orders group by 1,2 ) a

1-3) 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기

SELECT cuisine_type, restaurant_name, cnt_order, ranking FROM ( SELECT cuisine_type, restaurant_name, cnt_order, rank() over (partition by cuisine_type order by cnt_order desc) ranking FROM (SELECT cuisine_type, restaurant_name, count(1) cnt_order FROM food_orders group by 1,2) a ) b WHERE ranking<= 3 # where은 가장 아래에 적어줘야 합니다.

 

 

 

[실습2] 전체에서 차지하는 비율, 누적합을 구할 때, Sum()

2. 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

2-1) 음식 타입별, 음식점별 주문 건수 집계하기

select customer_id, restaurant_name, count(1) cnt_order from food_orders group by 1,2

 

2-2) 주문건 카테고리별 합, 주문건이 낮은 순으로  카테고리별 누적합 구하기

select cuisine_type, restaurant_name, cnt_order, sum(cnt_order) over (partition by cuisine_type) sum_cuisine, # 음식 타입별 주문건수의 합 sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine # 음식 타입별 cnt_order별 누적합 from ( select cuisine_type, restaurant_name, count(1) cnt_order from food_orders group by 1,2 ) a order by cuisine_type,cnt_order

 

 

2. SQL 의 날짜 데이터 형식(포맷 fomat)과 조건

1) 문자타입, 숫자타입과 같이 '날짜 데이터'도 특정한 타입 즉 데이터 타입을 가지고 있습니다.

 

2) 년, 월, 일, 시, 분, 초의 값을 모두 갖고 있으며 '년', '월', '일' 등으로 형식(포맷)을 변경할 수도 있습니다.

 

3) date type 을 date_format 을 이용하여 조희합니다. date_format(date(date), '%-') 

 

[실습1] 날짜 데이터의 여러 포맷

1. yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

# 처음 date는 함수명(시계모양)이고 두번째 date는 컬럼명입니다. select date(date) date_chage, date from payments

 

2. date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기

1) 년 : Y (4자리), y(2자리) 

2) 월 : M, m

3) 일 : d, e11111

4) 요일 : w

select date(date) date_type, date_format(date(date), '%Y') "년", date_format(date(date), '%m') "월", date_format(date(date), '%d') "일", date_format(date(date), '%w') "요일" from payments

 

3-1. 년도, 월을 포함하여 데이터 가공하여 주문건수 구하기

select date_format(date(date), '%Y') "년", date_format(date(date), '%m') "월", date_format(date(date), '%Y%m') "년월", count(f.order_id) "주문건수" # count(1) from food_orders f inner join payments p on f.order_id = p.order_id group by 1, 2, 3

 

3-2. 년도, 월을 포함하여 데이터 가공하여 주문건수 구하기,년도로 오름차순,식당 이름 포함

select restaurant_name, date_format(date(date), '%Y') "년", date_format(date(date), '%m') "월", date_format(date(date), '%Y,%m') "년월", count(f.order_id) "주문건수" # count(1) from food_orders f inner join payments p on f.order_id = p.order_id group by 2, 3, 4 order by 2

 

 

4. 3월 조건으로 지정하고, 년도별로 정렬하기

select restaurant_name, date_format(date(date), '%Y') "년", date_format(date(date), '%m') "월", date_format(date(date), '%Y,%m') "년월", count(f.order_id) "주문건수" # count(1) from food_orders f inner join payments p on f.order_id = p.order_id where date_format(date(date), '%m') = '03' group by 2, 3, 4 order by 2

 

 

---

💡[ Tip ] SQL생각 흐름 🧠 

1. 기본구조를 적기 : select from where

2. 어떤 데이터를 가져올 테이블을 적기 : from 테이블

 → 만약 조건이 잘 파악이 안 되면 (전체) 테이블만 우선 실행하여 결과를 보고 조건 찾기

3. 어떤 컬럼을 이용할 것인지 선택 : select 컬럼

4. 어떤 조건을 지정할 지 적기 : where 조건, group by / order by

5. 어떤 함수(수식) 을 이용해야 하는가 → 갯수 구하는 수식

 

---

ADsP 시험 정보가 궁금하신 분들은 여기를 클릭 💨

이전 내용이 궁금하다면 여기를 클릭 💨

[🔎복습 ] replace, substr, concat, if/case에 대해 더 알고싶다면 여기를 클릭🔥

 

🌸 오늘 하루도 즐거운 하루 되세요🌄