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';