[250124] 사전캠프 10일차 SQL(Window 함수, OVER, PARTITION BY, RANK, SUM, DATE_FORMAT)

이효원·2025년 1월 24일
sql내일배움캠프사전캠프

내일배움캠프 사전캠프

목록 보기
11/22

SQL

Window 함수

기본 구조

Window_function(Argument:인수) 
OVER (PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)
  • Window_function: 함수 이름 (RANK, SUM 등)
  • Argument(인수) : 함수에 따라 작성하거나 생략
  • OVER : 윈도우 함수가 어느 범위에서 계산을 수행해야 하는지 지정
    • PARTITION BY
      • 데이터를 그룹화하기 위한 기준을 정의함.
      • GROUP BY와 유사하지만, GROUP BY는 그룹별로 하나의 결과만 반환하고, PARTITION BY는 그룹별로 윈도우를 나누면서 각 행을 유지
    • ORDER BY
      • 윈도우 내에서 데이터를 정렬하는 기준을 정의.
      • 특정 순서대로 값을 계산하거나, 누적 계산 등을 수행할 때 필요

각 행의 관계를 정의하기 위한 함수

집계 연산을 수행하면서도 개별 행의 데이터도 유지하는 기능
특정 범위(윈도우)에 대해 계산한 값을 각 행에 반환
-> 계산 함수는 원래 테이블이 압축되어 결과 테이블이 달라지는 반면, 윈도우 함수는 기존 테이블은 그대로 유지하면서, 각 행에 계산 결과를 추가

순위 매기기 RANK

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

# 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
SELECT 
	  cuisine_type
	, restaurant_name
	, count_order
	, ranking
FROM 
(# RANK 사용해서 ranking 컬럼 추가하기
SELECT 
	  cuisine_type
	, restaurant_name
	, count_order
	# RANK 함수 적용
	,  RANK () OVER (PARTITION BY cuisine_type
	ORDER BY
		count_order DESC) ranking
FROM
	(# 음식 타입별, 음식점 별 주 건수 집계하기
	SELECT
		  cuisine_type
		, restaurant_name
		, COUNT(*) count_order
	FROM
		food_orders fo
	GROUP BY
		  1
		, 2
	) food_data
) ranking_data
WHERE ranking <=3

그런데 굳이 서브쿼리를 두개 쓰지 않고 RANK를 사용해 만든 ranking을 바로 WHERE 로 필터링하고 싶었는데 불가능하다고 한다.
그 이유는 SQL의 처리 순서 때문! 완벽하게 이해는 안되지만 일단 그렇구나 하고 넘어가자.

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY
즉, RANK는 SELECT 단계에서 만들어지므로, WHERE에서는 이 값을 바로 사용할 수 없습니다.

누적합 SUM

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

SELECT 
	restaurant_name
	, cuisine_type
	, count_order
    #  음식 타입별 전체 주문 건수 합계
	, SUM(count_order) 
    	OVER (PARTITION BY cuisine_type) sum_order
    # 음식 타입별로 주문 건수를 낮은 순서로 정렬했을 때 누적 합
	, SUM(count_order) 
    	OVER (PARTITION BY cuisine_type 
        		ORDER BY count_order) cum_order
FROM 
( # 음식점 이름과 음식 타입을 기준으로 주문 건수 계산
SELECT 
	restaurant_name 
	, cuisine_type 
	, COUNT(*) count_order
FROM food_orders fo 
GROUP BY 1, 2
) order_data

음식 타입별 전체 주문 건수 합계
SUM(count_order) OVER (PARTITION BY cuisine_type)

음식 타입별로 주문 건수를 낮은 순서로 정렬했을 때 누적 합
SUM(count_order) OVER (PARTITION BY cuisine_type ORDER BY count_order)

윈도우 함수의 장점을 극대화하는 순차적 계산 기능은 ORDER BY 에서 결정!
만약 그냥 PARTITION BY 만 있으면, SUM 하고 GROUP BY랑 다를게 없다

윈도우 함수 뭔가 완벽하게 이해가 되는 느낌이 아니라서,
문제를 좀 많이 풀면서 익혀야겠다..

날짜 포맷과 조건

  • DATE() DATE 타입으로 변경
  • DATE_FORMAT(컬럼명, 포맷)

년도 별로 3월의 주문 건수 구하기

SELECT 
	  DATE_FORMAT(DATE(p.date), '%Y') Year
	, DATE_FORMAT(DATE(p.date), '%m') Month
	, COUNT(quantity) month_order
FROM
	food_orders fo
	INNER JOIN payments p 
	ON fo.order_id = p.order_id
WHERE DATE_FORMAT(DATE(p.date), '%m') = '03'
GROUP BY Year, Month
ORDER BY Year

이번에는 서브쿼리를 쓰지 않아도 되는데 써서 풀었다.
서브쿼리를 정확히 언제 쓰고 쓰지 않아도 되는지 헷갈려서 검색했다.

5주차 과제

음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

SELECT
	# 세로축 음식타입별
	cuisine_type
    # 가로축 연령별
	, MAX(IF(age_range = '10s', count_order, 0)) "10대"
	, MAX(IF(age_range = '20s', count_order, 0)) "20대"
	, MAX(IF(age_range = '30s', count_order, 0)) "30대"
	, MAX(IF(age_range = '40s', count_order, 0)) "40대"
	, MAX(IF(age_range = '50s', count_order, 0)) "50대"
FROM 
(	# 주문 계산 해놓기
SELECT 
		# 전체 주문 계산
	 COUNT(*) count_order
	, fo.cuisine_type 
    	#10대부터 50대까지 분류
	, CASE 
		WHEN c.age BETWEEN 10 AND 19 THEN '10s'
		WHEN c.age BETWEEN 20 AND 29 THEN '20s'
		WHEN c.age BETWEEN 30 AND 39 THEN '30s'
		WHEN c.age BETWEEN 40 AND 49 THEN '40s'
		WHEN c.age BETWEEN 50 AND 59 THEN '50s'
	END age_range
    # 두 테이블 병합
FROM food_orders fo 
INNER JOIN customers c 
ON fo.customer_id = c.customer_id 
# 10~ 59 연령 설정
WHERE age BETWEEN 10 AND 59
GROUP BY 2,3
) data_setting
GROUP BY 1;

걷기반 문제 4. 이제 놀만큼 놀았으니 다시 공부해봅시다!

1) sparta_students 테이블에서 모든 학생의 이름(name)과 트랙(track)을 선택하는 쿼리를 작성해주세요!

SELECT 
	  name
	, track
FROM sparta_students

2) sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요!

SELECT *
FROM sparta_students
# <> ~가 아닌
WHERE track <> 'Unity'

3) sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요!

SELECT *
FROM sparta_students
# IN(여러 조건들)
WHERE enrollment_year IN(2021, 2023)

4)sparta_students 테이블에서 Node.js 트랙 소속이고 학점이 ‘A’인 학생의 입학년도를 선택하는 쿼리를 작성해주세요!

SELECT enrollment_year
FROM sparta_students
WHERE track = 'Node.js' AND grade = 'A'

기본기 복습한다는 생각으로 풀었다.

걷기반 문제 5. 공부하다보니 팀 프로젝트 시간이 왔어요!

1) team_projects 테이블에서 AWS 예산(aws_cost)이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리를 작성해주세요!

SELECT *
FROM team_projects
WHERE aws_cost >= 40000;

2) team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!

SELECT *
FROM team_projects
# 1. DATE_FORMAT 활용
WHERE DATE_FORMAT(start_date, '%Y') = 2022;
# 2. YEAR() 활용
WHERE YEAR(start_date) = 2022

3) team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!

SELECT *
FROM team_projects
# 현재 날짜를 나타내는 함수 CURDATE()
WHERE CURDATE() BETWEEN start_date AND end_date;

4) team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!

# 1. DATE_FORMAT 활용해서 날짜만 빼기 
SELECT 
	DATE_FORMAT(end_date, '%d') 
	- DATE_FORMAT(start_date, '%d') `지속 기간`
FROM team_projects
# 2. DATEDIFF(끝나는 날, 시작하는 날) 날짜 차이 구하기
SELECT 
	  name
    , DATEDIFF(end_date, start_date) AS working_days 
FROM team_projects;

전체적으로 날짜를 활용하는 법을 배웠다.
오늘 배운 포맷 방식 말고도 다양한 함수가 있는데,
전부 외우기는 어려우니까 필요할 때마다 검색해서 공부해야겠다.

느낀점

SQL 기초 강의 끝냈다. 2주만에 해내서 뿌듯~
다른 자격증 공부 때문에 사전캠프 시간 아니면 공부하기가 쉽지 않다.
최대한 사전캠프 시간을 잘 활용해야겠다.
다음주는 31일 하루만 하니까 이때 걷기반 문제랑 파이썬 정리 하고
2월부터 파이썬 정리 끝내고 문제 풀이 + SQLD 같이 할 수 있으면 좋겠다.
어쨌든 2주 전보다 훨씬 나은 사람이 되었다.

profile
이효원
이전 포스트

[250123] 사전캠프 9일차 아티클 스터디 + SQL (NULL값 제외, COALESCE, Pivot Table)

다음 포스트

[250131] 사전캠프 11일차 SQL 문제 (LIMIT, 시간 차이 계산 : TIMESTAMPDIFF &DATE_SUB)

0개의 댓글