(SQL의 기본과 활용) 2-2. SQL 활용 (2)

---

---

05. 윈도우 함수

• OVER 키워드로 사용/랭크, 집계, 행 순서, 비율 함수로 나눌 수 있습니다.
  
  
• 랭크 함수
  • RANK : 순위를 매기면서 같은 순서가 존재하면 존재하는 수만큼 다음 순위 건너 뛰기 / 1, 2, 2, 4, …
  • DENSE_RANK : 순위 지정 중에 동일한 순위가 존재하더라도 다음 순위로 건너 뛰지 않고 계속됩니다 / 1, 2, 2, 3, 4, 4, …
  • ROW_NUMBER : 순위를 매기는 동안 동일한 값이지만 다른 순위 / 1, 2, 3, 4, 5, …

-- RANK
SELECT ORDER_DT, COUNT(*),
	RANK() OVER(ORDER BY COUNT(*) DESC) AS RANK
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT;

-- DENSE_RANK
SELECT ORDER_DT, COUNT(*),
	DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS DENSE_RANK
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT;

-- ROW_NUMBER
SELECT ORDER_DT, COUNT(*),
	ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS ROW_NUMBER
FROM STARBUCKS_ORDER
GROUP BY ORDER_DT;

• 집계 함수
  • SUM: 데이터 합계/숫자 유형만 가능
  • MAX/MIN : 최대 코멘트/최소값
  • AVG: 평균
  • COUNT : 데이터 수

-- SUM
SELECT STUDENT_NAME, SUBJECT, SCORE,
	SUM(SCORE) OVER(PARTITION BY STUDENT_NAME) AS TOTAL_SCORE
FROM SQLD;
-- 오라클의 경우, OVER절 내에 ORDER BY 사용해서 데이터 누적값 구할 수 있음
SELECT STUDENT_NAME, SUBJECT, SCORE,
	SUM(SCORE) OVER(PARTITION BY STUDENT_NAME ORDER BY SUBJECT DESC RANGE UNBOUNDED PRECEDING)
    AS TOTAL_SCORE
FROM SQLD;

-- MAX
SELECT MAX(SCORE) AS MAX_SCORE FROM SQLD;
-- MIN
SELECT MIN(SCORE) AS MIN_SCORE FROM SQLD;

-- AVG
SELECT AVG(SCORE) AS AVG_SCORE FROM SQLD;

-- COUNT
SELECT COUNT(*) AS SCORE_COUNT FROM SQLD;

• 행 순서 함수(4개 모두 MSSQL에서는 지원 X)
  • FRIST_VALUE : 파티션 당 가장 맨 위에 위치한 데이터를 얻는 함수
  • LAST_VALUE : 파티션 당 가장 마지막에 위치한 데이터를 얻는 함수
  • LAG : 파티션 당 특정 수만큼 선행하는 데이터를 얻는 함수
  • LEAD: 파티션당 특정 수만큼 뒤에 있는 데이터를 검색하는 함수

-- FIRST_VALUE
SELECT STUDENT_NAME, SUBJECT, SCORE,
	FIRST_VALUE(SCORE) OVER(ORDER BY SCORE) AS FIRST_VALUE
FROM SQLD;

-- LAST_VALUE
SELECT STUDENT_NAME, SUBJECT, SCORE,
	LAST_VALUE(SCORE) OVER(ORDER BY SCORE) AS LAST_VALUE
FROM SQLD;
/* 이때 LAST_VALUE가 ROW의 SCORE 값과 동일 값 출력...
Why? WINDOWING절의 디폴트가 RANGE UNBOUNDED PRECEDING이어서
파티션의 범위가 맨 위 끝 행 ~ 현재 행가지 지정되어 있기 때문!

의도한 결과대로 하려면 WINDOWING절 명시 */
SELECT STUDENT_NAME, SUBJECT, SCORE,
	LAST_VALUE(SCORE) OVER(ORDER BY SCORE
    RANGE BETWEEN UNBOUNDED PRESEDING
    AND UNBOUNDED FOLLOWING) AS LAST_VALUE
FROM SQLD;

-- LAG : 두번째 인자값 생략시 기본값은 1
SELECT STUDENT_NAME, SUBJECT, SCORE,
	LAG(SCORE, 3) OVER(ORDER BY SCORE) AS LAG
FROM SQLD;

-- LEAD : 두번째 인자값 생략시 기본값은 1
SELECT STUDENT_NAME, SUBJECT, SCORE,
	LEAD(SCORE, 3) OVER(ORDER BY SCORE) AS LEAD
FROM SQLD;

---

06. Top-N 쿼리

---

07. 셀프 조인(Self Join)

---

08. 계층 쿼리

---