[SQL기본및활용] 2-2. SQL활용 (2)

졸~~~ 려~~~~!!!!

---

목차

05. 윈도우 함수 06. Top-N 쿼리 07. 셀프 조인(Self Join) 08. 계층 쿼리

---

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. 계층 쿼리

 

---