day06_1 - 오라클 (group by, rownum, rollup 함수, cube 함수, listagg 함수, lag 함수, lead 함수)
[group by 예제]
select deptno count(pay), to_char(round(avg(pay), 2),'999,999.99') "avg",
max(pay) "max",
min(pay) "min"
from processor
group by deptno
having round(avg(pay)) > 300;
학생의 월별 생일자가 1명인 월을 구하시오.
select to_char(birthday, 'mm') 월, count(birthday) 인원수
from student
group by to_char(birthday, 'mm')
having count(birthday) = 1
order by 월 asc;
[rownum]
-> where 절에 의해 정해진 순차적인 번호
-> rownum은 메모리에서 사용자가 요구하는 만큼 뽑는 순간 순서를 매겨서 찍는다
-> rownum은 where 조건절에 맞는 row를 가져올 때 순서가 매겨져서 가져온다.
-> where 조건절이 없다면 전부 순서를 매겨 가져온다.
-> order by를 통해서는 rownum로 매긴 순서에 영향을 주지 못한다.
아래에서 1번은 가능하지만 2번은 불가능하다.
-> 가장 처음 rownum 번호를 매기는 시점은 0이기 때문에 5보다 작다.
-> 그래서 1번은 초기가 0? 이라 가능하지만 2번은 가장 처음 시점에서 0이고 where 조건절에 오류가 난다. 계속 오류가 나기 때문에 0에서 카운트가 늘어나지 않아서 아무것도 출력이 안된다.
--1. 가능
select empno, enaem, from emp
where rownum<5;
--2. 불가능
select empno, enaem, from emp
where rownum > 5;
[rollup 함수]
-> 자동으로 소계 값을 구해주는 함수
--rollup
select deptno, position,
count(*), sum(pay)
from professor
group by rollup(deptno, position);
-> position의 널 부분이 소계값이다.
[cube]
-> rollup 함수와 같이 각 소계도 출력 하면서, 전체 총계까지 출력한다.
--cube
select deptno, position,
count(*), sum(pay)
from professor
group by cube(deptno, position);
[grouping 함수 관련 연습 문제]
select count(birthday) 합계,
count(decode(to_char(birthday, 'mm'), 01, 1)) "1월",
count(decode(to_char(birthday, 'mm'), 02, 1)) "2월",
count(decode(to_char(birthday, 'mm'), 03, 1)) "3월",
count(decode(to_char(birthday, 'mm'), 04, 1)) "4월",
count(decode(to_char(birthday, 'mm'), 05, 1)) "5월",
count(decode(to_char(birthday, 'mm'), 06, 1)) "6월",
count(decode(to_char(birthday, 'mm'), 07, 1)) "7월",
count(decode(to_char(birthday, 'mm'), 08, 1)) "8월",
count(decode(to_char(birthday, 'mm'), 09, 1)) "9월",
count(decode(to_char(birthday, 'mm'), 10, 1)) "10월",
count(decode(to_char(birthday, 'mm'), 11, 1)) "11월",
count(decode(to_char(birthday, 'mm'), 12, 1)) "12월"
from student;
[listagg 함수]
select deptno, listagg(name) within group(order by kiredate) listagg
from professor
group y deptno;
[lag 함수]
-> 이전 행 값을 가져올 때 사용하는 함수
--lag
select name, hiredate, pay,
lag(pay, 1, 0) over (order by hiredate) lag
from professor;
[lead 함수]
-> lag 함수와는 반대로 이후의 값을 가져오는 함수
--lead
select name, hiredate, pay,
lead(pay,1,0)
over(order by hiredate) lead
from professor;
[rank 함수]
-> ~~ 순으로 해당 값이 몇위에 랭크되어 있는지 출력
--rank
select rank('아이유') within group(order by name) rank
from professor;
[누적 합계 구하기]
select p_date, p_code, p_qty, p_total,
sum(p_total) over(order by p_date) 누계
from panmae
where p_store='1000';
