01. Window Fuction

1) window fuction

• 정의 : 행과 행 간의 관계 정의를 위해 제공되는 함수
• 역할 : 순위, 합계 평균, 행 위치 등의 조작 가능, 모든 컬럼을 잃고 싶지 않을 때 사용
• 특징 : GROUP BY와 병행 사용 불가
• 종류 (총 4가지)
• 순위 : RANK, DENSE_RANK, ROW_NUMBER
• 집계 : SUM, MAX, MIN, AVG, COUNT
• ⭐순서 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• ⭐비율 : RATIO_TO_REPROT, PERCENT_RANK, CUME_DIST, NTILE
• 문법
  SELECT절에서 사용된다

 # 윈도우 함수 기본문법 
SELECT WINDOW_FUNCTION() OVER([PARTITION BY 컬럼] [ORDER BY 컬럼])
FROM 테이블명

• WINDOW 함수 사용 예시 1
  사용자별 채팅 텍스트 데이터의 에시
  유저 기준으로, 가장 마지막 일자의 CHAT_TEXT 추출

▶️ 집계함수를 사용하게 되면, 우리는 CHAT_TEXT를 조회 할 수 없음!

CHAT_TEXT도 같이 보고 싶다면 윈도우 함수 사용

ROW_NUMBER() OVER(partition by USER_ID order by REVIEW_DATE) AS rown

• WINDOW 함수 사용 예시 2
  1) 순위를 매기는 함수
  RANK
  특정 컬럼의 순위를 구하는 함수
  동일한 값에 대해서는 같은 순위를 부여하며, 중간 순위는 비운 값으로 출력

DENSE_RANK
RANK와 동일하나, 동일한 값은 같은 순위 부여하고 중간 순위를 비우지 않는다.

⭐ ROW_NUMBER (가장 많이 사용!)
동일한 값이어도 고유한 순위를 부여함. (MySQL SYSTEM 마음대로)
(순위가 중간에 비면 안되는 경우에)

2) 순서를 정하는 함수
FIRST_VALUE
파티션별 가장 먼저 나온 값을 구하여 출력
처음 나온 행만 가져오며 MIN과 결과가 동일

LAST_VALUE
파티션별 가장 마지막에 나온 값을 구하여 출력
마지막에 나온 행만 가져오며 MAX와 결과가 동일

⭐LAG
이전 N번째 행을 가져오는 함수
별도 명시가 없으면 기본값은 1이다
NVL, ISNULL과 기능이 동일함

# 윈도우 함수 - LAG 함수 예제
select *
, LAG(SALARY) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1 

# 윈도우 함수 - LAG 함수 예제2: 2번째 전 값 구하기 
select *
, LAG(SALARY,2) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1 

⭐ LEAD
이후 N행의 값을 가져오는 함수
별도 명시가 없으면 기본값은 1이다

# 윈도우 함수 - LEAD 함수 예제
select *
, LEAD(SALARY) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1 

# 윈도우 함수 - LEAD 함수 예제2: 2번째 후 값 구하기 
select *
, LEAD(SALARY,2) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1 

3) 비율을 구하는 함수
RATIO_TO_REPORT (MySQL 지원 안함)
파티션 내 전체 SUM값에 대한 행별 백분율을 소수점으로 출력
결과값은 0~1사이, 비율의 합은 1

⭐PERCENT_RANK
파티션 별 가장 먼저 나오는 값을 0, 가장 마지막에 나오는 값이 1로 행 순서별 백분율을 출력
구간을 나누어 백분율로 출력 (상위 N%를 구함)

# 윈도우 함수 - PERCENT_RANK 함수 예제
select *
, PERCENT_RANK() OVER (partition by JOB order BY SALARY)
from basic.window1 

✅ 계산방식: (파티션 내 순위 - 1) / (파티션 내 전체 행 개수 - 1)

• 이때 순위는 RANK 함수 결과의 순위

CUME_DIST
파티션 별 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 출력

# 윈도우 함수 - CUME_DIST 함수 예제
select *
, cume_dist() OVER (partition by JOB order BY SALARY)
from basic.window1 

NTILE
파티션 별 전체 건수를 계산한 값으로 N등분한 결과 출력

# 윈도우 함수 - NTILE 함수 예제
select *
, NTILE(4) OVER (PARTITION BY JOB ORDER BY SALARY)
from basic.window1 

02. WITH

• 정의 : SQL구문에서 사용되는 임시(가상) 테이블

• 사용 이유 : 쿼리 가독성 및 쿼리 성능 향상

• 특징 및 장점 :
  1️⃣임시 테이블의 개념으로, 작성한 쿼리 내에서만 실행됨
  2️⃣하나의 SQL 구문에서 여러개의 WITH 선언 가능
  3️⃣하나의 테이블에 대한 여러 조회가 필요한 경우, WITH절 사용으로 1회 조회 및 선언하게 되어 가독성 및 쿼리 성능이 좋아짐
  4️⃣복잡한 연산을 보다 효율적으로 처리(JOIN, UNION 등의 결과를 WITH에 저장)

• 문법 :
  WITH 임시테이블이름 AS
  ( SELECT 컬럼1, 컬럼2, ...
  FROM 테이블 명
  )
  SELECT 임시테이블에서 불러온 컬럼 중 필요한 컬럼
  FROM 임시테이블명

• 예시

# with 구문 활용 예시1 
with soso as  # with 뒤쪽에 임시테이블명 지정
(	select etc_str2, etc_str1, count(distinct game_actor_id)as actor_cnt 
	from basic.users
	group by etc_str2, etc_str1 # 임시테이블을 만들 때 활용할 테이블 
)
select *
from soso # WITH절에서 지정한 임시테이블명 

;

#####################################################################
# with 구문 활용 예시2 - 경험치가 가장 많은 캐릭터 정보 조회하기 
with dodo as # with 뒤쪽에 임시테이블명 지정
(	select * 
	from basic.users
)
select *
from(	select max(exp)as maxexp
		from dodo
	)as a 
inner join
	(	select *
		from dodo
	)as b 
on a.maxexp=b.exp
;

#####################################################################
# with 구문 활용 예시3 - 다중 with 구문  
with gogo as # 첫번째 with 절
(	select game_account_id, exp
	from basic.users 
	where `level` >50
),
hoho as # 두번째 with 절, with 구문은 처음 한번만 작성합니다. 
(	select distinct game_account_id, pay_amount, approved_at
	from basic.payment 
	where pay_type='CARD'
) # 이 부분에서 with 구문이 종료됩니다. 
select case when b.game_account_id is null then '결제x' else '결제o' end as gb
, count(distinct a.game_account_id)as accnt 
from gogo as a
left join hoho as b
on a.game_account_id=b.game_account_id
group by case when b.game_account_id is null then '결제x' else '결제o' end
;

03. 그 외 중요한 함수

• STRING 관련 함수
  CONCAT 문자열끼리 병합할 때 사용
  SUBSTRING 문자열을 특정 위치에서 어느 위치까지 자를지
  ⭐SUBSTRING_INDEX 문자열 특정 구분기호를 통해 출력할 때 사용
  REVERSE 문자열 뒤집을 때 사용
  LEFT, RIGHT 문자열 기준으로 어떤 방향에서 N개 출력

• MATH 관련 함수
  ⭐ABS 절대값 출력
  ⭐ROUND 숫자를 소수점 이하 자릿수에서 올림
  CEILING 소수점 올림 출력
  FLOOR 소수점 내림 출력
  TRUNCATE 소수점 이하 자릿수에서 버림 출력 TRUNCATE(2.77,1) = 2.7
  RAND 지정 숫자 범위 중 하나 랜덤 출력

• 날짜 관련 함수
  NOW, SYSDATE, CURRENT_TIMESTAMP 현재 날짜, 시간 출력
  DATE_ADD 날짜에서 기준값만큼 덧셈하여 출력
  DATE_SUB 날짜에서 기준값만큼 뺄셈하여 출력
  DATEDIFF 두 날짜에서 뺄셈하여 일수를 출력
  DATE_FORMAT 날짜를 형식에 맞게 출력
  UNIX_TIMESTAMP 현재 시간을 UNIXTIME으로 출력
  CURDATE, CURRENT_DATE 현재의 날짜를 출력
  CURTIME, CURRENT_TIME 현재의 시간을 출력
  YEAR, MONTH, DAY 날짜의 연도, 월, 일 출력

---

오늘은 드디어 새로운 개념인 WINDOW 함수를 배웠다!
아마 사전캠프 때 가볍게 RANK()에 대해서만 짚고 넘어갔었던 것으로 기억하는데,
더 다양한 WINDOW 함수와 그 쓰임새를 알았다.
여태까지 풀었던 코드카타 문제들 중에서도 이 WINDOW 함수로 더 효율적으로 작성할 수 있는 문제들이 있었을 것 같아서,
주말에는 한번 QCC 연습 겸 SQL 실력을 쌓아보고자 한번 다시 풀어볼 계획이다.