ROLL UP 및 RANK

ROLL UP이나 RANK는 오라클에서도 쓰이고, 시험에도 많이 나오므로 중요하다.

📢ROLL UP

WITH ROLLUP : 그룹별로 합계를 한번에 구할 때 사용
GROUP BY 그룹명 WITH ROLLUP

📢ROLL UP 실습

--1. 과목별(co_name) 성적(중간, 기말, 출석, 과제) 합계  sum, group by
--과목		     중 기 출 과
--프로그래밍일반 	65 68 30 40
--디자인기초 	  50 50 10 10
--합계            00  00  00  00
--co_name => course c
--성적 => attend a

select ifnull(c.co_name, '합계') as '과목명',
sum(a.at_mid) as '중간', 
sum(a.at_final) as '기말',
sum(a.at_attend) as '출석',
sum(a.at_hw) as '과제'
from attend a
join course c 
on a.at_co_code = c.co_code
group by c.co_name WITH ROLLUP
order by GROUPING(c.co_name) ASC;

-- ifnull(값1, 값2) : 값1이 null이면 값2를 표시
-- if ( c.co_name is null, '합계', c.co_name)
-- ifnull(c.co_name, '합계')

+----------------+------+------+------+------+
| 과목명         | 중간 | 기말 | 출석 | 과제 |
+----------------+------+------+------+------+
| 기초전기       |   30 |   30 |    8 |    7 |
| 대학수학기초   |   70 |   78 |   18 |   19 |
| 디자인기초     |   39 |   39 |    9 |    8 |
| 색채이론       |   37 |   20 |    9 |    1 |
| 컴퓨터개론     |   46 |   43 |    9 |   14 |
| 프로그래밍일반 |   32 |   26 |    3 |    5 |
| 화학이론       |   54 |   50 |   13 |   13 |
| 합계           |  308 |  286 |   69 |   67 |
+----------------+------+------+------+------+

--2. 학점별 중간, 기말, 출석, 과제 평균 	avg, group by
select ifnull(a.at_score, '평균')as '학점',
round(avg(a.at_mid),2) as '중간', 
round(avg(a.at_final),2) as '기말',
round(avg(a.at_attend),2) as '출석',
round(avg(a.at_hw),2) as '과제'
from attend a
where a.at_score is not null
group by a.at_score WITH ROLLUP
--having a.at_score is not null
order by GROUPING(a.at_score);

--having a.at_score is not null
--with rollup은 having 조건을 인식못함.

+------+-------+-------+------+------+
| 학점 | 중간  | 기말  | 출석 | 과제 |
+------+-------+-------+------+------+
| A    | 39.50 | 39.50 | 9.50 | 9.00 |
| B    | 30.00 | 38.00 | 8.00 | 9.00 |
| C    | 30.00 | 30.00 | 8.00 | 7.00 |
| D    | 28.00 | 26.25 | 6.50 | 4.75 |
| F    | 19.00 | 11.33 | 2.67 | 4.67 |
| 평균 | 28.00 | 26.00 | 6.27 | 6.09 |
+------+-------+-------+------+------+

 

📢RANK

RANK는 SQL 시험에 굉장히 자주 나옴.

rank() over (order by 기준컬럼 desc) as 컬럼명

     👉 값이 같다면 같은 순위를 가짐 다음 순위는 건너 뜀

dense_rank() over (order by 기준컬럼 desc) as 컬럼명

     👉 값이 같다면 같은 순위를 가짐 다음 순위를 이어감

📢RANK 실습

--at_final을 기준으로 순위를 작성
--학번 at_final ranking

select at_std_num, at_final, 
rank() over (order by at_final desc) as ranking 
from attend 
where at_term = 1;

+------------+----------+---------+
| at_std_num | at_final | ranking |
+------------+----------+---------+
| 2020160002 |       40 |       1 |
| 2023160002 |       39 |       2 |
| 2020160001 |       38 |       3 |
| 2020123020 |       35 |       4 |
| 2019456001 |       30 |       5 |
| 2020123020 |       26 |       6 |
| 2020123001 |       24 |       7 |
| 2023160002 |       20 |       8 |
| 2019456001 |       15 |       9 |
| 2019160123 |       11 |      10 |
| 2020123001 |        8 |      11 |
+------------+----------+---------+

-- 기말 성적을 기준으로 5등 미만까지만 장학금대상 명단
select * from 
(select at_std_num, at_final, 
rank() over (order by at_final desc) as ranking 
from attend 
where at_term = 1) as r
where r.ranking < 5;

+------------+----------+---------+
| at_std_num | at_final | ranking |
+------------+----------+---------+
| 2020160002 |       40 |       1 |
| 2023160002 |       39 |       2 |
| 2020160001 |       38 |       3 |
| 2020123020 |       35 |       4 |
+------------+----------+---------+