invalid-file- 실습용 예제 파일의 테이블 생성 (table_exam-eizizie 파일 다운)
@d:\down\table_exam.sql;
=> 예제파일이 저장된 경로를 입력하고 sql파일 이름 입력
(파일을 클릭하면 메모장으로 테이블 구조와 데이터 확인 가능)
SELECT * FROM tab;
=> 생성된 테이블 확인
SELECT * FROM 테이블_이름;
=> 테이블에 있는 모든 레코드 검색
(주의 : 레코드가 방대한 양일 경우 *를 쓰면 레코드가 모두 출력, 감당 어려움)
DESC 테이블_이름;
=> 테이블 구조 확인
- RANK : 특정 칼럼 값에 의해 정렬된 결과에 순위 부여 (DENSE_RANK : 동일순위 무시)
RANK() OVER(
[PARTITION BY 칼럼명]
ORDER BY 데이터정렬_기준칼럼)
예) 학생을 키가 큰 순서로 순위를 부여해 출력
SELECT studno, name, height,
RANK() OVER(ORDER BY height DESC) AS rank
FROM student;
STUDNO NAME HEIGHT RANK
---------- -------------------- ---------- ----------
10106 서재진 186 1
20104 조명훈 184 2
20102 박동진 182 3
10202 오유석 177 4
10101 전인하 176 5
10107 이광훈 175 6
20101 이동훈 172 7
10105 임유진 171 8
10204 윤진욱 171 8
10103 김영균 170 10
10102 박미경 168 11
20103 김진경 166 12
10201 김진영 164 13
10108 류민정 162 14
10104 지은경 161 15
10203 하나리 160 16
=> SELECT 문에서 studno, name, height, rank 를 검색
예) 전체 학생을 학과별로 그룹화하여 학과별로 키가 큰 사람부터 순위를 부여하여 출력
SELECT name, deptno, height, RANK() OVER
(PARTITION BY deptno ORDER BY height DESC) length_rank
FROM student;
NAME DEPTNO HEIGHT LENGTH_RANK
-------------------- ---------- ---------- -----------
서재진 101 186 1
전인하 101 176 2
이광훈 101 175 3
임유진 101 171 4
김영균 101 170 5
박미경 101 168 6
류민정 101 162 7
지은경 101 161 8
오유석 102 177 1
윤진욱 102 171 2
김진영 102 164 3
하나리 102 160 4
조명훈 201 184 1
박동진 201 182 2
이동훈 201 172 3
김진경 201 166 4
=> PARTITION문을 이용하여 그룹화된 데이터의 순위 확인 가능
- Top-N : 큰 값이나 작은 값 순으로 상위 N개 출력
SELECT [칼럼목록], ROWNUM_순위할당
FROM (SELECT [칼럼목록] FROM 테이블명
ORDER BY 정렬기준_칼럼)
WHERE ROWNUM <= N
예) 전체 학생 중 키가 가장 큰 상위 3명의 학번, 이름, 키 출력
SELECT studno, name, height, rank
FROM (SELECT studno, name, height, RANK() OVER(ORDER BY height DESC) AS rank FROM student)
WHERE rank <= 3;
STUDNO NAME HEIGHT RANK
---------- -------------------- ---------- ----------
10106 서재진 186 1
20104 조명훈 184 2
20102 박동진 182 3
=> studno, name, height, rank를 검색 후 rank<=3인 레코드 출력
처음 SELECT 문에 RANK함수를 이용할 경우 오류 발생
SELECT studno, name, height, RANK() OVER
(ORDER BY height DESC) AS rank FROM student
WHERE rank <= 3;
-----> WHERE rank <= 3
*
3행에 오류:
ORA-00904: 열명이 부적합합니다예) 학년별로 몸무게가 가장 많이 나가는 상위 2명의 이름과 몸무게 출력
SELECT name, weight, grade, rank
FROM (SELECT name, weight, grade, DENSE_RANK() OVER(PARTITION BY grade ORDER BY weight DESC) rank
FROM student)
WHERE rank<3
ORDER BY grade;
NAME WEIGHT GR RANK
-------------------- ---------- -- ----------
서재진 72 1 1
박동진 70 1 2
류민정 72 2 1
임유진 54 2 2
김영균 88 3 1
윤진욱 70 3 2
오유석 92 4 1
이광훈 92 4 1
전인하 72 4 2
=> name, weight, grade, rank를 검색하고, rank<3 검색
PARTITION문을 이용하여 학년별로 나누고, 가장 무거운 무게 2순위까지 출력
예) 학년별로 평균 키를 구하여 평균 키가 가장 작은 2개 학년과 평균 키 출력
SELECT grade, avg_height, rank
FROM (SELECT grade, avg(height) avg_height,RANK() OVER(ORDER BY avg(height)) rank
FROM student GROUP BY grade)
WHERE rank<3;
GR AVG_HEIGHT RANK
-- ---------- ----------
2 164.8 1
3 170.5 2
=> 학년을 그룹화하고 그 평균의 순위 검색,
평균을 구해야 하기 때문에 PARTITION문이 아닌 GROUP문 이용
[출처] [SQL 오라클] RANK 분석 함수|작성자 이히야호
