SQLD SQL활용 2

5. 윈도우 함수

OVER키워드와 함께 사용되며 역할에 따라 다음과 같이 나눌 수 있다.

순위함수	RANK, DENSE_RANK, ROW_NUMBER
집계함수	SUM, MAX, MIN, AVG, COUNT
행 순서함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율함수	CUME_DIST, PERCENT_RANK, NITLE, RATIO_TO_REPORT

5.1 순위함수

DENSE_RANK - 1,2,2,3,4,4,5
ROW_NUMBER - 1,2,3,4,5,6,7

1.RANK

순위를 매기면서 같은 순위가 존재하면 존재하는 만큼 다음 순위를 건너뛴다.
1,2,2,4,5,5,7

SELECT COUNT(*)
RANK() OVER(ORDER BY COUNT(*) DESC) AS RANK
FROM A
GROUP BY NO;

2.DENSE_RANK

순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다.
DENSE가 사전적의미로 밀집한이라는 뜻을 가지고 있으므로 순위가 밀집되어 있다라고 기억해보자.

SELECT COUNT(*)
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS DENSE_RANK
FROM A
GROUP BY NO;

3.ROW_NUMBER

순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다.

SELECT COUNT(*)
ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS ROW_NUMBER
FROM A
GROUP BY NO;

ROW_NUMBER는 페이징할때 사용했던 것 같다.
잘 기억해두자!!

SELECT COUNT(*)
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COUNT(*) DESC) AS ROW_NUMBER
FROM A
GROUP BY NO;

PARTITION BY을 사용하면 그루핑한 것 처럼 나눌 수 있다.!

5.2 집계함수

1. SUM

데이터의 합계를 구하는 함수
인자값으로는 숫자형만 올 수 있다.

이것은 SQLD시험 성적데이터에 SUM함수를 적용한 것이다.
SELECT SUM(SCORE) AS TOTAL_SCORE FROM SQLD;

위 쿼리는 모든 사람들의 점수를 합한 결과를 처리한다.
개인별 총점수를 구하는 쿼리를 작성해보자

SELECT STUDENT_NAME, SUBJECT, SCORE,
SUM(SCORE) OVER (PARTITION BY STUDENT_NAME) AS TOTAL_SCORE
FROM SQLD;

OVER 절 내에 ORDER BV절을 사용하고 RANGE UNBOUNDED PRECEDING를 사용하면 데이터의 누적값을 구할 수 있다.

SELECT STUDENT_NAME, SUBJECT, SCORE,
SUM(SCORE) OVER (PARTITION BY STUDENT_NAME ORDER BY SUBJECT DESC RANGE UNBOUNDED PRECEDING) AS TOTAL_SCORE
FROM SQLD;

SUM하게 하는 컬럼을 OVER절에서 ORDER BY절에 명시하면 RANGE UNBOUNDED PRECEDING 구문없이도 누적합이 집계된다.

2. MAX

데이터의 최댓값을 구하는 함수

위와 마찬가지로
SELECT MAX(SCORE) FROM SQLD;
이 쿼리를 사용하면 모든 점수를 대상으로 최댓값을 출력한다.

과목별로 최대 점수를 구해보자.
SELECT ST_NAME, SUBJECT, SCORE,
MAX(SCORE) OVER(PARTITION BY SUBJECT) AS MAX_SCORE
FROM SQLD

과목별 최대 점수를 받은 사람만 출력하려면 서브쿼리를 사용하면된다.

SELECT ST_NAME, SUBJECT, SCORE,
FROM (SELECT ST_NAME, SUBJECT, SCORE,
MAX(SCORE) OVER(PARTITION BY SUBJECT) AS MAX_SCORE
FROM SQLD)
WHERE SCORE = MAX_SCORE;

3. MIN

데이터의 최솟값을 구하는 함수이다.

위와 마찬가지로
SELECT MIN(SCORE) FROM SQLD;
이 쿼리를 사용하면 모든 점수를 대상으로 최솟값을 출력한다.

과목별로 최소 점수를 구해보자.
SELECT ST_NAME, SUBJECT, SCORE,
MIN(SCORE) OVER(PARTITION BY SUBJECT) AS MIN_SCORE
FROM SQLD

과목별 최소 점수를 받은 사람만 출력하려면 서브쿼리를 사용하면된다.

SELECT ST_NAME, SUBJECT, SCORE,
FROM (SELECT ST_NAME, SUBJECT, SCORE,
MIN(SCORE) OVER(PARTITION BY SUBJECT) AS MIN_SCORE
FROM SQLD)
WHERE SCORE = MIN_SCORE;

4. AVG

데이터의 평균값을 구하는 함수

위와 마찬가지로
SELECT AVG(SCORE) FROM SQLD;
이 쿼리를 사용하면 모든 점수를 대상으로 평균값을 출력한다.

과목별로 평균 점수를 구해보자.
SELECT ST_NAME, SUBJECT, SCORE,
ROUND(AVG(SCORE) OVER(PARTITION BY SUBJECT)) AS AVG_SCORE
FROM SQLD

과목별 평균 점수 이상인 사람만 출력하려면 서브쿼리를 사용하면된다.

SELECT ST_NAME, SUBJECT, SCORE,
FROM (SELECT ST_NAME, SUBJECT, SCORE,
ROUND(AVG(SCORE) OVER(PARTITION BY SUBJECT)) AS AVG_SCORE
FROM SQLD)
WHERE SCORE >= AVG_SCORE;

5. COUNT

데이터의 건수를 구하는 함수

위와 마찬가지로
SELECT COUNT(*) FROM SQLD;
이 쿼리를 사용하면 모든 점수를 대상으로 건수를 출력한다.

과목별로 PASS한 건수를 구해보자.
SELECT ST_NAME, SUBJECT, SCORE,
COUNT(*) OVER(PARTITION BY SUBJECT) AS PASS_COUNT
FROM SQLD

COUNT함수는 WINDOWING절을 이용하여 원하는 범위에 해당하는 데이터에 대한 통계값을 구할 수 있다.
과목별로 본인보다 점수가 높거나 같은 건수를 카운트 하는 쿼리는 다음과 같다.

SELECT ST_NAME, SUBJECT, SCORE,
FROM (SELECT ST_NAME, SUBJECT, SCORE,
COUNT(*) OVER(PARTITION BY SUBJECT
ORDER BY SCORE DESC
RANGE UNBOUNDED PRECEDING) AS PASS_COUNT)
WHERE SCORE >= AVG_SCORE;

윈도윙 절

범위	의미
UNBOUNDED PRECEDING	위쪽 끝 행
UNBOUNDED FOLLWING	아래쪽 끝 행
CURRENT ROW	현재행
n PREVEDING	현재행에서 위로 n만큼 이동
n FOLLOWING	현재행에서 아래로 n만큼 이동

기준	의미
ROWS	행자체가 기준이 된다.
RANGE	행이 가지고 있는 데이터 값이 기준이된다.

5.3 행 순서 함수

1. FIRST_VALUE

파티션별 가장 선두에 위치한 데이터를 구하는 함수
MSSQL에서는 지원하지 않는다.

SELECT ST_NAME, SUBJECT, SCORE,
FIRST_VALUE(SCORE) OVER(ORDER BY SCORE) AS FIRST_VALUE
FROM SQLD

위 쿼리는 모든 점수를 대상으로 오름차순 한다음 첫번째 위치하는 점수를 출력한다.
당므은 과목별로 가장 높은 점수를 구하는 쿼리이다.

SELECT ST_NAME, SUBJECT, SCORE,
FIRST_VALUE(SCORE) OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC) AS FIRST_VALUE
FROM SQLD

2. LAST_VALUE

파티션별 가장 끝에 위치한 데이터를 구하는 함수
MSSQL에서는 지원하지 않는다.

SELECT ST_NAME, SUBJECT, SCORE,
LAST_VALUE(SCORE) OVER(ORDER BY SCORE) AS LAST_VALUE
FROM SQLD

위 쿼리는 오름차순을 한다음 마지막에 위치하는 점수를 출력해야하나
ORDER BY한 것이랑 집계한 컬럼이 같아
기본값이 RANGE UNBOUNDED PRECEDING이어서 파티션의 범위가 맨위 끝행부터 현재행까지로 지정되었기 때문에 각 값들이 그대로 나온다.
의도한 바로 나오게 하기 위해서는 맨위 행부터 맨아래행까지로 지정을 해야한다.

SELECT ST_NAME, SUBJECT, SCORE,
FIRST_VALUE(SCORE) OVER(PARTITION BY SUBJECT ORDER BY SCORE
RANGE BETWEEN UNBONDED PRECEDING AND UNBOUNDED FOLOOWING) AS FIRST_VALUE
FROM SQLD

3.LAG

파티션별로 특정 수 만큼 앞선 데이터를 구하는 함수
MSSQL에서는 지원하지 않는다.

SELECT ST_NAME, SUBJECT, SCORE,
LAG(SCORE, 3) OVER(ORDER BY SCORE) AS LAG
FROM SQLD

LAG함수의 두번째 인자값을 생략하면 기본값은 1이다.

4. LEAD

파티션별로 특정 수만큼 뒤에 있는 데이터를 구하는 함수
MSSQL에서는 지원하지 않는다.

SELECT ST_NAME, SUBJECT, SCORE,
LEAD(SCORE, 3) OVER(ORDER BY SCORE) AS LEAD
FROM SQLD

LEAD함수의 두번째 인자값을 생략하면 기본값은 1이다.

5.4 비율함수

1. RATIO_TO_REPORT

파티션별 합계에서 차지하는 비율을 구하는 함수이다.
MSSQL에서는 지원하지 않는다.

SELECT STUDENT_NAME, SUBJECT, SCORE,
SUM(SCORE) OVER (PARTITION BY SUBJECT) AS SUM,
SCORE/SUM(SCORE) OVER(PARTITION BY SUBJECT) AS "SCORE/SUM",
RATIO_TO_REPORT(SCORE) OVER(PARTITION BY SUBJECT) AS RATIO_TO_REPORT
FROM SQLD;

과목별로 파티션을 나누고 총합을 구하고 과목별 합에서 SCORE가 차지하는 비율을 구한것이다.

2. PERCENT_RANK

해당 파티션의 맨위 끝행을 0 맨아래끝행을 1로 놓고 현재 행이 위치하는 백분위 순위값을 구하는 함수이다.
MSSQL에서는 지워하지 않는다.

SELECT STUDENT_NAME, SUBJECT, SCORE,
RANK() OVER (ORDER BY SCORE) AS SUM,
COUNT(*) OVER AS COUNT,
(RANK() OVER(ORDER BY SCORE) - 1 / COUNT(*) OVER() -1) AS "RANKE(-1) / COUNT(-1)"
PERCENT_RANK() OVER(ORDER BY SCORE) AS PERCENT_RANK
FROM SQLD;

랭크순위값에서 1을 뺀 값을 총 COUNT에서 1을 뺀값으로 나눈값과 동일하다

3. CUME_DIST

해당 파티션에서의 누적 백분율을 구하는 함수이다.
결과값은 0보다크고 1보자 작거나 같은 값을 가진다.
MSSQL에서는 지원하지 않는다.

형재행까지의 누적건수에서 전체건수로 나눈값과 동일하다.

4. NTILE

주어진 수 만큼 행들을 N등분한 후 현재행이 해당하는 등급을 구하는 함수이다.
만약 행이 10개인데 3등분한다면
1.3개씩 그룹을 부여한다.
2.남는행은 앞부터 채운다.
그래서 1그룹 4개 2그룹3 3그룹 3이된다.

6. 셀프조인

셀프조인은 말그대로 나자신과의 조인이다.
from절에 같으 테이블이 두번이상 등자하기 때문에 혼란을 막기위해 별명을 꼭 표기해주어야한다.