[SQLD] 순위함수 RANK(), DENSE_RANK(), ROW_NUMBER, LAG/LEAD, OVER PARTITION BY
[SQLD] 순위함수 RANK(), DENSE_RANK(), ROW_NUMBER, LAG/LEAD, OVER PARTITION BY — #SQLD #개발자의도구들 #SQLRANK #SQLDENSE_RANK #SQLROW_NUMBER #S...
#SQLD#Naver Blog
#SQLD #개발자의도구들 #SQLRANK #SQLDENSE\_RANK #SQLROW\_NUMBER #SQLLAG #SQL순위함수
24년도 SQLD 제 55회 시험을 대비하며 정리한 글입니다.
\* 본글은 PC버전에 최적화 되어있습니다.
**\* **전체 목차는 여기를 참고해주세요.
윈도우함수-순위함수
SQL자격검정 실전문제
윈도우 함수는 테이블의 데이터를 특정 창을 통해 볼 수 있도록 만드는 함수입니다. 흔히 사용하는 집계함수나, 이번 글을 통해 배울 순위함수가 대표적인 예시입니다.
RANK() / DENSE\_RANK() / ROW\_NUMBER()
RANK()는 데이터의 결과에 순위를 매길 수 있는 함수 입니다.
-- 테이블 생성
CREATE TABLE student_scores (
student_id NUMBER,
student_name VARCHAR2(50),
subject VARCHAR2(50),
score NUMBER
);
-- 데이터 삽입
INSERT ALL
INTO student_scores (student_id, student_name, subject, score) VALUES (1, '김철수', '수학', 85)
INTO student_scores (student_id, student_name, subject, score) VALUES (1, '김철수', '영어', 92)
INTO student_scores (student_id, student_name, subject, score) VALUES (1, '김철수', '과학', 85)
INTO student_scores (student_id, student_name, subject, score) VALUES (2, '이영희', '수학', 92)
INTO student_scores (student_id, student_name, subject, score) VALUES (2, '이영희', '영어', 88)
INTO student_scores (student_id, student_name, subject, score) VALUES (2, '이영희', '과학', 95)
INTO student_scores (student_id, student_name, subject, score) VALUES (3, '박민수', '수학', 78)
INTO student_scores (student_id, student_name, subject, score) VALUES (3, '박민수', '영어', 85)
INTO student_scores (student_id, student_name, subject, score) VALUES (3, '박민수', '과학', 88)
INTO student_scores (student_id, student_name, subject, score) VALUES (4, '정지원', '수학', 95)
INTO student_scores (student_id, student_name, subject, score) VALUES (4, '정지원', '영어', 92)
INTO student_scores (student_id, student_name, subject, score) VALUES (4, '정지원', '과학', 90)
INTO student_scores (student_id, student_name, subject, score) VALUES (5, '홍길동', '수학', 88)
INTO student_scores (student_id, student_name, subject, score) VALUES (5, '홍길동', '영어', 78)
INTO student_scores (student_id, student_name, subject, score) VALUES (5, '홍길동', '과학', 85)
select * from dual;
정규화 되어 있지는 않지만, 실습으로는 좋은 테이블 구조입니다. 현재 이 테이블에서 과목별로, 점수 순위를 매겨보겠습니다.
SELECT student_name, subject, score,
RANK() OVER(PARTITION BY subject ORDER BY score desc) as rank
from student_scores;
여기서 포인트는 RANK()의 경우 같은 과목에 대해서 같은 등수 처리 시행후, 그 다음 점수에 대해서 윗 등수의 갯수 이후부터 RANK를 적용합니다. 쉽게 설명하자만 올림픽의 등수 방법과 같은데, 만약 올림픽에서 2등이 2명 생기면 동메달을 획득하는 선수가 없는 것과 같습니다.
SELECT student_name, subject, score,
DENSE_rank() OVER(PARTITION BY subject ORDER BY score desc) as rank
from student_scores;
DENSE\_RANK()의 경우 같은 등수가 발생해도, 다음 등수는 그 수에 상관없이 다음 RANK가 됩니다.
SELECT student_name, subject, score,
ROW_NUMBER() over(PARTITION BY subject ORDER BY score desc) as rank
FROM student_scores;
ROW\_NUMBER()은 같은 행에 대해서도 같은 RANK로 부여하지않고, 그냥 행의 순서에 따라 RANK를 부여합니다.
| 👉 핵심정리RANK()는 같은 점수에 대해 동일 RANK부여, 이후 등수에 대해 윗 RANK 수만큼 차감 후 부여한다.DENSE\_RANK()는 같은 점수에 대해 동일 RANK부여, 이후 등수는 차감 없이 다음 숫자를 부여한다ROW\_NUMBER()은 같은 등수 상관없이 행의 순서대로 RANK를 부여한다.그룹별로 RANK를 보고 싶다면 OVER절 안에 PARTITION BY를 사용하면됩니다.GROUP BY와 의미적으로 동일합니다.PRATIION BY절이 없다면, 전체 집합을 하나의 PARTIION으로 보는 것과 같습니다.윈도우 적용 범위는 PARTITION을 넘을 수 없습니다. OVER : 윈도우 함수를 사용할 때, 데이터를 파티션으로 나누고 정렬하여 연산을 수행하도록 돕는다.PARTITION BY 결과 집합을 파티션으로 나누기ORDER BY 각 파티션 내의 행을 정렬OPTIONALROWS : 물리적인 행의 수를 기준으로 윈도우 프레임 정의정확한 행 수를 지정중복된 값을 개별행으로 취급ROWS BETWEEN 2 PRECEDING AND CURRENT ROW현재 행을 포함하여 이전 2개의 행까지 윈도우 프레임으로 정의RANGE: 논리적인 값의 범위를 기준으로 윈도우 프레임 정의ORDER BY 절에 지정된 열의 값을 기준으로 윈도우 경계 설정동일 값을 가진 행을 하나의 그룹 취급RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW파티션 시작 부터 현재 항과 동일한 값을 가진 모든 행까지를 윈도우 프레임으로 |
|---|
오답노트
Q. 아래 SQL에 대한 설명으로 가장 적절한 것은?
SELECT 상푼분류코드, AVG(상품가격) AS 상품가격,
COUNT(*) OVER(ORDER BY AVG(상품가격)
RANGE BETWEEN 10000 PRECEDING
AND 10000 FOLLOWING) AS 유사개수
FROM 상품
GROUP BY 상품분류코드
1. WINDOW FUNCTION을 GROUP BY 절과 함께 사용하였으므로 위의 SQL은 오류이다.
2. WINDOW FUNCTION을 ORDER BY 절에 AVG 집계함수를 사용하였으므로 SQL 오류이다.
3. 유사개수 칼럼은 상품분류코드별 평군 상품가격을 서로 비교하여 -10000 ~ +10000 사이에
존재하는 상품분류코드의 개수를 구한 것이다.
4. 유사개수 칼럼은 상품전체의 평균상품가격을 서로 비교하여 -10000~ +10000 사이에 존재하는
상품의 개수를 구한 것이다.
A. 3
💡 WINDOW FUNCTION은 GROUP BY와 함께 사용해도 문제 없다.
OVER은 WINDOW FUNCTION과 함께사용되는데, 집계함수도 WINDOW FUNCTION의 한 종류이다.
RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING: 현재 행의 평균 가격을 기준으로
10,000 단위 위아래로 범위를 설정합니다
LAG/LEAD
| 👉 핵심정리LAG: 현재 행보다 이전 행의 값을 반환LEAD: 현재 행보다 이후 행의 값을 반환두 함수는 거의 동일하며 작동방향만 다르다. |
|---|
SELECT
date,
value,
LAG(value) OVER (ORDER BY date) AS previous_value,
LEAD(value) OVER (ORDER BY date) AS next_value
FROM time_series_data;
각 날짜의 값과 함께, 이전 날짜와 다음날짜를 함께 보여준다.