SQL 활용
-오라클
-순위 함수
- rank over
- dense rank over
- low number over (제일 많이 쓰임)
[Oracle] RANK() OVER vs ROW_NUMBER() OVER
by 스뎅(thDeng) on July 20, 2017
https://blog.leocat.kr/notes/2017/07/20/oracle-rank-over-vs-row-number-over
레코드를 특정 column의 값을 기준으로 정렬하여 순서를 매길 수 있다. RANK() OVER 또는 ROW_NUMBER() OVER를 사용하면 된다. 간단히 아래와 같은 테이블을 생각하자.
SQL> SELECT * FROM TMP_TABLE;
USERID | SCORE
----------|----------
aaa | 10
aaa | 30
aaa | 50
aaa | 90
bbb | 80
bbb | 50
bbb | 20
bbb | 40
aaa | 50
9 rows selected.
SQL> SELECT
USERID,
SCORE,
RANK() OVER (ORDER BY SCORE DESC) RANK
FROM TMP_TABLE;
USERID | SCORE| RANK
----------|----------|----------
aaa | 90| 1
bbb | 80| 2
aaa | 50| 3
aaa | 50| 3
bbb | 50| 3
bbb | 40| 6
aaa | 30| 7
bbb | 20| 8
aaa | 10| 9
9 rows selected.SQL> SELECT USERID,
SCORE,
ROW_NUMBER() OVER (ORDER BY SCORE DESC) RANK
FROM TMP_TABLE;
USERID | SCORE| RANK
----------|----------|----------
aaa | 90| 1
bbb | 80| 2
aaa | 50| 3
aaa | 50| 4
bbb | 50| 5
bbb | 40| 6
aaa | 30| 7
bbb | 20| 8
aaa | 10| 9
9 rows selected.위의 RANK()와 ROW_NUMBER()의 차이는 결과를 자세히 보면 알 수 있다. RANK()의 결과는 3, 4, 5번째 레코드의 점수가 50점으로 동일하기 때문에 때문에 같은 순서인 3번을 매겼다. 하지만 ROW_NUMBER()의 결과는 점수가 같더라도 레코드가 달라지면 다른 순서를 매긴다.
RANK, DENSE_RANK, ROW_NUMBER (순위를 반환하는 함수)
게시자: MyungJong Kim, 2011. 12. 11. 오후 9:09
--- RANK() : 중복 순위 다음은 해당 개수만큼 건너뛰고 반환
--- DENSE_RANK() : 중복 순위 상관없이 순차적으로 반환
--- ROW_NUMBER() : 중복과 관계 없이 무조건 순서대로 반환
select id
, salary
-- 분석 함수의 order by 절을 사용하여 내림차순 정렬한 뒤 rank() 함수가 순위를 반환
, RANK() OVER (ORDER BY salary DESC) Rank
, DENSE_RANK() OVER (ORDER BY salary DESC) DenseRank
, ROW_NUMBER() OVER (ORDER BY salary DESC) RowRank
from (
select 100 id, 39000 salary from dual union all
select 101 id, 30000 salary from dual union all
select 102 id, 9000 salary from dual union all
select 103 id, 17000 salary from dual union all -- 중복
select 104 id, 60000 salary from dual union all
select 105 id, 9500 salary from dual union all
select 106 id, 17000 salary from dual union all -- 중복
select 107 id, 37500 salary from dual union all
select 108 id, 25000 salary from dual union all
select 109 id, 5000 salary from dual union all
select 110 id, 17000 salary from dual -- 중복
) ;
-- - 부서별로 월급이 높은 순위로 2명씩 뽑아보세요~
select a.*
from (select dept
, id
, salary
, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) ranking
-- 부서별로 그룹을 나누고 order by 절을 사용하여 내림차순 정렬한 뒤
-- row_number() 함수가 순위를 반환
from (
select 10 dept, 100 id, 39000 salary from dual union all
select 20 dept, 101 id, 30000 salary from dual union all
select 20 dept, 102 id, 9000 salary from dual union all
select 30 dept, 103 id, 17000 salary from dual union all
select 40 dept, 104 id, 60000 salary from dual union all
select 20 dept, 105 id, 9500 salary from dual union all
select 30 dept, 106 id, 17000 salary from dual union all
select 40 dept, 107 id, 37500 salary from dual union all
select 40 dept, 108 id, 25000 salary from dual union all
select 30 dept, 109 id, 5000 salary from dual union all
select 40 dept, 110 id, 17000 salary from dual
)
) a
where a.ranking < 3 ; -- 2순위까지 조회
최종종합문제
workbook_script_v2.0.sql
0.92MB
워크북_v2.0.pdf
0.31MB
-- [Basic SELECT]
-- 1.춘 기술대학교의 학과 이름과 계열을 표시하시오.
-- 단, 출력 헤더는 "학과 명", "계열" 으로 표시하도록 한다.
SELECT
department_name "학과 명",
category "계열"
FROM
TB_DEPARTMENT;-- 2. 학과의 학과 정원을 다음과 같은 형태로 화면에 출력한다.
select
department_name||'의 정원은 '||capacity||'명 입니다.' "학과 별 정원"
FROM
TB_DEPARTMENT;-- 3."국어국문학과" 에 다니는 여학생 중
-- 현재 휴학중인 여학생을 찾아달라는 요청이 들어왔다. 누구인가?
-- (국문학과의 '학과코드'는 학과 테이블(TB_DEPARTMENT)을 조회해서 찾아 내도록 하자)
select
student_name
from TB_Student
where
department_no ='001' and
substr(student_ssn,8,1) in( '2','4') and
absence_yn = 'Y';-- 4. 도서관에서 대출 도서 장기 연체자 들을 찾아 이름을 게시하고자 한다.
--그 대상자들의 학번이 다음과 같을 때 대상자들을 찾는 적절한 SQL 구문을 작성하시오.
-- A513079, A513090, A513091, A513110, A513119
select
student_name
from TB_Student
where
student_no in ('A513079', 'A513090', 'A513091', 'A513110', 'A513119');-- 5. 입학정원이 20 명 이상 30 명 이하인 학과들의 학과 이름과 계열을 출력하시오.
select
department_name,
category
from TB_DEPARTMENT
where
capacity between 20 and 30;-- 6. 춘 기술대학교는 총장을 제외하고 모든 교수들이 소속 학과를 가지고 있다.
--그럼 춘 기술대학교 총장의 이름을 알아낼 수 있는 SQL 문장을 작성하시오.
select professor_name
from TB_professor
where department_no is null;-- 7. 혹시 젂산상의 착오로 학과가 지정되어 있지 않은 학생이 있는지 확인하고자 한다.
--어떠한 SQL 문장을 사용하면 될 것인지 작성하시오.
select student_name
from TB_Student
where department_no is null;-- 8. 수강신청을 하려고 한다. 선수과목 여부를 확인해야 하는데,
--선수과목이 존재하는 과목들은 어떤 과목인지 과목번호를 조회해보시오.
select class_no
from TB_class
where preattending_class_no is null;-- 9. 춘 대학에는 어떤 계열(CATEGORY)들이 있는지 조회해보시오.
select category
from TB_DEPARTMENT
group by category
order by 1;-- 10. 02 학번 전주 거주자들의 모임을 만들려고 한다.
-- 휴학한 사람들은 제외한 재학중인 학생들의
-- 학번, 이름, 주민번호를 출력하는 구문을 작성하시오.
select
student_no,
student_name,
student_ssn
from TB_Student
where
extract(year from entrance_date) like '2002' and
student_address like '%전주%' and
absence_yn = 'N';-- [Additional SELECT - 함수]
-- 1. 영어영문학과(학과코드 002) 학생들의 학번과 이름, 입학 년도를
-- 입학 년도가 빠른순으로 표시하는 SQL 문장을 작성하시오.
-- ( 단, 헤더는 "학번", "이름", "입학년도" 가 표시되도록 한다.)
select
student_no 학번,
student_name 이름,
to_char(entrance_date,'YYYY-MM-DD') 입학년도
from TB_Student
where
department_no = '002'
order by 3;-- 2.춘 기술대학교의 교수 중 이름이 세 글자가 아닌 교수가 한 명 있다고 한다.
--그 교수의 이름과 주민번호를 화면에 출력하는 SQL 문장을 작성해 보자.
--(* 이때 올바르게 작성한 SQL 문장의 결과 값이 예상과 다르게 나올 수 있다.
--원인이 무엇일지 생각해볼 것)
select
professor_name,
professor_ssn
from TB_professor
where
professor_name not like '___';-- 3. 춘 기술대학교의 남자 교수들의 이름과 나이를 출력하는 SQL 문장을 작성하시오.
--단, 이때 나이가 적은 사람에서 많은 사람 순서로 화면에 출력되도록 만드시오.
--(단, 교수 중 2000 년 이후 출생자는 없으며 출력 헤더는 "교수이름", "나이"로 한다.
--나이는 ‘만’으로 계산한다.)
select
professor_name 교수이름,
extract(year from sysdate)-1900 - substr(professor_ssn,1,2)나이
from TB_professor
order by 2;-- 4. 교수들의 이름 중 성을 제외핚 이름맊 출력하는 SQL 문장을 작성하시오.
--출력 헤더는‚ "이름" 이 찍히도록 한다.
--(성이 2 자인 경우는 교수는 없다고 가정하시오)
select
substr(professor_name,2,length(professor_name)-1)
from TB_professor;-- 5. 춘 기술대학교의 재수생 입학자를 구하려고 핚다. 어떻게 찾아낼 것인가?
-- 이때, 19 살에 입학하면 재수를 하지 않은 것으로 간주한다.
select
student_no,
student_name
from TB_Student
where extract(year from entrance_date)-(substr(student_ssn,1,2)+1900) > 19;
-- 6. 2020년 크리스마스는 무슨 요일인가?
select to_char(to_date('20201225'),'day') from dual;-- 7. TO_DATE('99/10/11','YY/MM/DD'), TO_DATE('49/10/11','YY/MM/DD') 은
--각각 몇 년 몇 월 몇 일을 의미할까?
--또 TO_DATE('99/10/11','RR/MM/DD'),TO_DATE('49/10/11','RR/MM/DD')은
--각각 몇 년 몇 월 몇 일을 의미할까?
select
extract(year from TO_DATE('99/10/11','YY/MM/DD'))||'년'||
extract(month from TO_DATE('99/10/11','YY/MM/DD'))||'월'||
extract(day from TO_DATE('99/10/11','YY/MM/DD'))||'일' "TO_DATE('99/10/11','YY/MM/DD')",
extract(year from TO_DATE('49/10/11','YY/MM/DD'))||'년'||
extract(month from TO_DATE('49/10/11','YY/MM/DD'))||'월'||
extract(day from TO_DATE('49/10/11','YY/MM/DD'))||'일' "TO_DATE('49/10/11','YY/MM/DD')",
extract(year from TO_DATE('99/10/11','RR/MM/DD'))||'년'||
extract(month from TO_DATE('99/10/11','RR/MM/DD'))||'월'||
extract(day from TO_DATE('99/10/11','RR/MM/DD'))||'일' "TO_DATE('99/10/11','RR/MM/DD')",
extract(year from TO_DATE('49/10/11','RR/MM/DD'))||'년'||
extract(month from TO_DATE('49/10/11','RR/MM/DD'))||'월'||
extract(day from TO_DATE('49/10/11','RR/MM/DD'))||'일' "TO_DATE('49/10/11','RR/MM/DD')"
from dual;-- 8. 춘 기술대학교의 2000 년도 이후 입학자들은 학번이 A 로 시작하게 되어있다.
--2000 년도 이전 학번을 받은 학생들의 학번과 이름을 보여주는 SQL 문장을 작성하시오.
select
student_no,
student_name
from TB_Student
where extract(year from entrance_date)<2000;-- 9. 학번이 A517178 인 한아름 학생의 학점 총 평점을 구하는 SQL 문을 작성하시오.
-- 단, 이때 출력 화면의 헤더는 "평점" 이라고 찍히게 하고,
-- 점수는 반올림하여 소수점 이하 한자리까지만 표시한다.
select round(avg(point),1) "평점"
from TB_grade
where student_no = 'A517178';-- 10. 학과별 학생수를 구하여 "학과번호", "학생수(명)" 의 형태로 헤더를 만들어
--결과값이 출력되도록 하시오.
select
department_no "학과번호",
count(*) "학생수(명)"
from TB_Student
group by department_no
order by 1;-- 11. 지도 교수를 배정받지 못핚 학생의 수는 몇 명 정도 되는 알아내는 SQL 문을
--작성하시오.
select
count(*) "count(*)"
from TB_Student
where coach_professor_no is null;-- 12. 학번이 A112113 인 김고운 학생의 년도 별 평점을 구하는 SQL 문을 작성하시오.
-- 단, 이때 출력 화면의 헤더는 "년도", "년도 별 평점" 이라고 찍히게 하고,
-- 점수는 반올림하여 소수점 이하 한 자리까지만 표시한다.
select
substr(term_no,1,4) "년도",
avg(point) "년도 별 평점"
from TB_grade
where student_no ='A112113'
group by substr(term_no,1,4);