SQL 복수 행 함수는 앞에서 살펴본 단일 행 함수와 달리 한꺼번에 여러 데이터가 함수로 입력된다.
( 속도가 느려질 수 있다. )
1. GROUP 함수의 종류
COUNT 입력되는 데이터들의 건수를 출력
SUM 입력되는 데이터들의 합계 값을 출력
AVG 입력되는 데이터들의 평균 값을 출력
( AVG는 자동으로 NULL값은 제외하기 때문에
AVG(NVL(BONUS,0)) 이런식으로 써주어야 한다. )
MAX 입력되는 데이터들 중 최고 값을 출력
MIN 입력되는 데이터들 중 최소 값을 출력
( MAX, MIN은 시간이 오래 걸리는 함수 중 한가지다. 인덱스를 활용하는 것을 추천한다. )
STDDEV 입력되는 데이터 값들의 표준 편차 값 출력
VARIANCE 입력되는 데이터 값들의 분산 값 출력
ROLLUP 입력되는 데이터들의 소계 값을 자동으로 계산해서 출력
CUBE 입력되는 데이터들의 소계 및 총계를 자동 계산 후 출력
GROUPING 해당 칼럼이 그룹에 사용되었는지 여부를 1 또는 0으로 반환
GROUPINGSET 한번의 질의로 여러 개의 그룹화 가능
LISTAGG
PIVOT
LAG
LEAD
RANK
DENSE_RANK
2. 특정 조건으로 세부적인 그룹화 하기
( GROUP BY 절 사용하기 )
SELECT deptno, AVG(NVL(pay,0)) "평균급여"
FROM professor
GROUP BY deptno, position ;
DEPTNO 평균급여
---------- ----------
201 330
202 310
301 220
301 290
102 250
103 530
103 290
203 500
101 270
202 260
101 350
201 570
101 550
103 330
102 350
102 490
16개의 행이 선택됨
SELECT 절에 사용된 그룹 함수 이외의 칼럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 한다.
GROUP BY 절에는 Alias 는 사용하면 안되고 칼럼명을 써야 한다.
ORACLE 10g R1 버전까지는 GROUP BY 결과는 늘 sort 되어서 출력되었다. 그러나 R2버전부터는 HASH 기반의 NEW in-Memory Sort Algorithm 방식으로 변경되면서 더이상 정렬이 되지 않고 출력된다. 이전 방식으로 돌아가려면 pfile이나 spfile에 "_gby_hash_aggregation_enabled"=FALSE로 설정해주면 되지만 비추천.
3. 조건을 주고 검색하기
( HAVING 절 사용하기 )
그룹 함수를 비교 조건으로 사용하려면 HAVING 절을 사용해야 한다. 그리고 HAVING 절은 반드시 GROUP BY 절 다음에 와야만 한다.
SELECT deptno, AVG(NVL(pay,0))
FROM professor
GROUP BY deptno
HAVING AVG(pay) > 450 ;
DEPTNO AVG(NVL(PAY,0))
---------- ---------------
203 500
4. 자동으로 소계 / 합계를 구해주는 함수
ROLLUP 함수는 주어진 데이터들의 소계를 구해주고 CUBE 함수는 주어진 데이터들의 전체 총계까지 구해주는 함수다.
SELECT deptno, position,
COUNT(*), SUM(pay)
FROM professor
GROUP BY ROLLUP(deptno, position);
DEPTNO POSITION COUNT(*) SUM(PAY)
---------- ---------- ---------- ----------
101 정교수 1 550
101 조교수 2 700
101 전임강사 1 270
101 4 1520
102 정교수 1 490
102 조교수 1 350
102 전임강사 1 250
102 3 1090
103 정교수 1 530
103 조교수 1 330
103 전임강사 1 290
103 3 1150
201 정교수 1 570
201 조교수 1 330
201 2 900
202 조교수 1 310
202 전임강사 1 260
202 2 570
203 정교수 1 500
203 1 500
301 조교수 1 290
301 전임강사 1 220
301 2 510
17 6240
24개의 행이 선택됨
SELECT deptno, position,
COUNT(*), SUM(pay)
FROM professor
GROUP BY CUBE(deptno, position);
DEPTNO POSITION COUNT(*) SUM(PAY)
---------- ---------- ---------- ----------
17 6240
정교수 5 2640
조교수 7 2310
전임강사 5 1290
101 4 1520
101 정교수 1 550
101 조교수 2 700
101 전임강사 1 270
102 3 1090
102 정교수 1 490
102 조교수 1 350
102 전임강사 1 250
103 3 1150
103 정교수 1 530
103 조교수 1 330
103 전임강사 1 290
201 2 900
201 정교수 1 570
201 조교수 1 330
202 2 570
202 조교수 1 310
202 전임강사 1 260
203 1 500
203 정교수 1 500
301 2 510
301 조교수 1 290
301 전임강사 1 220
27개의 행이 선택됨
5. 다른 그룹핑 관련 함수들 살펴보기
(1) GROUPING 함수
ROLLUP 함수와 CUBE 함수와 함께 사용되는 함수로 어떤 칼럼이 해당 grouping 작업에 사용되었는지 아닌지를 구별해주는 역할을 한다. 만약 어떤 칼럼이 grouping 작업에 사용되었으면 0을 반환하고 반대로 사용되지 않았다면 1을 반환한다.
SELECT deptno, SUM(pay),
GROUPING(deptno) g_deptno
FROM professor
GROUP BY ROLLUP(deptno);
DEPTNO SUM(PAY) G_DEPTNO
---------- ---------- ----------
101 1520 0
102 1090 0
103 1150 0
201 900 0
202 570 0
203 500 0
301 510 0
6240 1
8개의 행이 선택됨
(2) GROUPING_ID 함수
GROUPING 함수를 보다 편하게 사용하기 위해 기능을 합쳐 둔 것과 같다.
SELECT deptno, position, SUM(pay),
GROUPING_ID(deptno, position) GDP,
GROUPING_ID(position, deptno) GPD
FROM professor
GROUP BY ROLLUP(deptno, position);
DEPTNO POSITION SUM(PAY) GDP GPD
---------- ---------- ---------- ---------- ----------
101 정교수 550 0 0
101 조교수 700 0 0
101 전임강사 270 0 0
101 1520 1 2
102 정교수 490 0 0
102 조교수 350 0 0
102 전임강사 250 0 0
102 1090 1 2
103 정교수 530 0 0
103 조교수 330 0 0
103 전임강사 290 0 0
103 1150 1 2
201 정교수 570 0 0
201 조교수 330 0 0
201 900 1 2
202 조교수 310 0 0
202 전임강사 260 0 0
202 570 1 2
203 정교수 500 0 0
203 500 1 2
301 조교수 290 0 0
301 전임강사 220 0 0
301 510 1 2
6240 3 3
24개의 행이 선택됨
(3) GROUPING SETS 함수
그루핑 조건이 여러 개일 경우 유용하게 사용할 수 있다.
SELECT grade, deptno1, COUNT(*)
FROM student
GROUP BY GROUPING SETS(grade, deptno1);
GRADE DEPTNO1 COUNT(*)
---------- ---------- ----------
1 5
2 5
4 5
3 5
102 4
201 6
301 2
101 4
202 2
103 2
10개의 행이 선택됨
(4) LISTAGG 함수 ( 11g에서 추가됨 )
SELECT deptno,
LISTAGG(name, '**') WITHIN GROUP(ORDER BY hiredate) "LISTAGG"
FROM professor
GROUP BY deptno;
DEPTNO LISTAGG
---------- ------------------------------
101 조인형**박승곤**송도권**나교수
102 주승재**김영조**양선희
103 김도형**나한열**김현정
201 심슨**최슬기
202 박원범**차범철
203 바비
301 허은**전민
7개의 행이 선택됨
(5) PIVOT 함수 ( 11g에서 추가됨 )
PIVOT 함수는 row 단위를 column 단위로 변경해주고 UNPIVOT 함수는 반대로 column 단위를 row 단위로 변경해 주는 기능을 한다.
PIVOT 기능을 사용하지 않고 DECODE 함수를 사용하여 달력 만들기
SELECT MAX(DECODE(day, '일', num_day)) AS SUN,
MAX(DECODE(day, '월', num_day)) AS MON,
MAX(DECODE(day, '화', num_day)) AS TUE,
MAX(DECODE(day, '수', num_day)) AS WED,
MAX(DECODE(day, '목', num_day)) AS THU,
MAX(DECODE(day, '금', num_day)) AS FRI,
MAX(DECODE(day, '토', num_day)) AS SAT
FROM cal
GROUP BY week
ORDER BY week
SUN MON TUE WED THU FRI SAT
--- --- --- --- --- --- ---
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
PIVOT 기능을 사용하여 달력만들기
SELECT *
FROM (
SELECT week "주", day, num_day
FROM cal
)
PIVOT (
MAX(num_day)
FOR day IN('일' as "일",
'월' as "월",
'화' as "화",
'수' as "수",
'목' as "목",
'금' as "금",
'토' as "토")
)
ORDER BY "주" ;
주 일 월 화 수 목 금 토
- -- -- -- -- -- -- --
1 1 2 3 4 5 6 7
2 8 9 10 11 12 13 14
3 15 16 17 18 19 20 21
4 22 23 24 25 26 27 28
5 29 30 31
(6) UNPIVOT
테스트로 테이블을 생성
CREATE TABLE t_unpivot AS
(
SELECT *
FROM (
SELECT deptno, job, empno
FROM emp
)
PIVOT (
COUNT(empno)
FOR job IN ( 'CLERK' as "CLERK",
'MANAGER' as "MANAGER",
'PRESIDENT' as "PRESIDENT",
'ANALYST' as "ANALYST",
'SALESMAN' as "SALESMAN"
)
)
) ;
UNPIVOT 함수로 합쳐진 결과를 푼다.
SELECT *
FROM t_unpivot
UNPIVOT (
empno FOR job IN (CLERK, MANAGER, PRESIDENT, ANALYST, SALESMAN)
) ;
DEPTNO JOB EMPNO
---------- --------- ----------
30 CLERK 1
30 MANAGER 1
30 PRESIDENT 0
30 ANALYST 0
30 SALESMAN 4
20 CLERK 2
20 MANAGER 1
20 PRESIDENT 0
20 ANALYST 2
20 SALESMAN 0
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
10 ANALYST 0
10 SALESMAN 0
15개의 행이 선택됨
6. 그룹 함수 연습문제
교수 중에서 pay 와 bonus를 합친 금액이 가장 많은 경우와 가장 적은 경우와 평균 금액을 구하라.
단, 보너스가 없을 경우는 보너스를 0으로 계산하고 출력 금액은 모두 소수점 첫째자리까지만 나오게 할 것.
SELECT MAX(pay+NVL(bonus,0)) "MAX",
MIN(pay+NVL(bonus,0)) "MIN",
ROUND(AVG(pay+NVL(bonus,0)),1) "AVG"
FROM professor ;
MAX MIN AVG
---------- ---------- ----------
700 220 412.9
교수 중에서 pay 와 bonus를 합친 금액이 가장 많은 경우와 가장 적은 경우와 평균 금액을 구하라.
단, 보너스가 없을 경우는 급여를 0으로 계산하고 출력 금액은 모두 소수점 첫째자리까지만 나오게 할 것.
SELECT MAX(NVL2(bonus,pay+bonus,pay*0)) "MAX",
MIN(NVL2(bonus,pay+bonus,pay*0)) "MIN",
ROUND(AVG(NVL2(bonus,pay+bonus,pay*0)),1) "AVG"
FROM professor ;
MAX MIN AVG
---------- ---------- ----------
700 0 298.8
월별로 태어난 인원수를 출력하라.
SELECT COUNT(*) "합계",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'01',0)) "1월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'02',0)) "2월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'03',0)) "3월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'04',0)) "4월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'05',0)) "5월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'06',0)) "6월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'07',0)) "7월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'08',0)) "8월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'09',0)) "9월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'10',0)) "10월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'11',0)) "11월",
COUNT(DECODE(TO_CHAR(birthday,'MM'),'12',0)) "12월"
FROM student;
합계 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
20 3 3 2 2 0 1 0 2 2 2 1 2
지역별 인원수를 출력하라. 02는 서울, 031은 경기, 051은 부산, 052는 울산, 053은 대구, 055는 경남
SELECT COUNT(*) "합계",
COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1), 02, 0)) "서울",
COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1), 031, 0)) "경기",
COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1), 051, 0)) "부산",
COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1), 052, 0)) "울산",
COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1), 053, 0)) "대구",
COUNT(DECODE(SUBSTR(tel,1,INSTR(tel,')')-1), 055, 0)) "경남"
FROM student;
합계 서울 경기 부산 울산 대구 경남
---------- ---------- ---------- ---------- ---------- ---------- ----------
20 6 2 4 0 2 6
부서별 직급별 급여 합계 결과 출력. 먼저 아래 두 건의 데이터 입력 후 작업.
insert into emp(empno,deptno,ename,sal) values (1000,10,'홍길동',3600);
insert into emp(empno,deptno,ename,sal) values (2000,30,'일지매',3000);
commit ;
SELECT deptno,
SUM(DECODE(job,'CLERK',sal,0)) "CLERK",
SUM(DECODE(job,'MANAGER',sal,0)) "MANAGER",
SUM(DECODE(job,'PRESIDENT',sal,0)) "PRESIDENT",
SUM(DECODE(job,'ANALYST',sal,0)) "ANALYST",
SUM(DECODE(job,'SALESMAN',sal,0)) "SALESMAN",
SUM(NVL2(job,sal,0)) "합계"
FROM emp
GROUP BY ROLLUP(deptno) ;
DEPTNO CLERK MANAGER PRESIDENT ANALYST SALESMAN 합계
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 0 2450 5000 0 0 7450
20 10975 0 0 0 0 10975
30 0 2850 0 0 0 2850
10975 5300 5000 0 0 21275
교수번호와 이름을 한 행당 3건씩 출력하라
SELECT CEIL(ROWNUM/3) no,
MAX(DECODE(MOD(ROWNUM,3),1,profno,NULL)) "사번1",
MAX(DECODE(MOD(ROWNUM,3),1,name,NULL)) "이름1",
MAX(DECODE(MOD(ROWNUM,3),2,profno,NULL)) "사번2",
MAX(DECODE(MOD(ROWNUM,3),2,name,NULL)) "이름2",
MAX(DECODE(MOD(ROWNUM,3),0,profno,NULL)) "사번3",
MAX(DECODE(MOD(ROWNUM,3),0,name,NULL)) "이름3"
FROM professor
GROUP BY CEIL(ROWNUM/3)
ORDER BY 1 ;
NO 사번1 이름1 사번2 이름2 사번3 이름3
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1001 조인형 1002 박승곤 1003 송도권
2 2001 양선희 2002 김영조 2003 주승재
3 3001 김도형 3002 나한열 3003 김현정
4 4001 심슨 4002 최슬기 4003 박원범
5 4004 차범철 4005 바비 4006 전민
6 4007 허은 5000 나교수
6개의 행이 선택됨
7. 그 외 주요 그룹함수
(1) LAG 함수
이전 행 값을 가져올 때 사용하는 함수
문법
LAG(출력할 칼럼명, OFFSET, 기본 출력값) OVER(Query_partition 구문, ORDER BY 정렬할 칼럼)
SELECT name, hiredate, pay,
LAG(pay, 1, 0) OVER(ORDER BY hiredate) "LAG"
FROM professor ;
NAME HIREDATE PAY LAG
---------- -------- ---------- ----------
조인형 80/06/23 550 0
김도형 81/10/23 530 550
심슨 81/10/23 570 530
주승재 82/04/29 490 570
바비 85/09/18 500 490
김영조 85/11/30 350 500
박승곤 87/01/30 380 350
나한열 97/07/01 330 380
송도권 98/03/22 270 330
박원범 99/12/01 310 270
허은 01/05/23 290 310
양선희 01/09/01 250 290
김현정 02/02/24 290 250
차범철 09/01/28 260 290
최슬기 09/08/30 330 260
전민 10/06/28 220 330
나교수 13/11/12 320 220
17개의 행이 선택됨
(2) LEAD 함수
이후의 행 값을 가져오는 함수로 문법이나 사용법은 LAG함수와 동일하다. 다만 OFFSET 값이 가장 마지막에 보인다.
SELECT name, hiredate, pay,
LEAD(pay, 1, 0) OVER(ORDER BY hiredate) "LEAD"
FROM professor ;
NAME HIREDATE PAY LEAD
---------- -------- ---------- ----------
조인형 80/06/23 550 530
김도형 81/10/23 530 570
심슨 81/10/23 570 490
주승재 82/04/29 490 500
바비 85/09/18 500 350
김영조 85/11/30 350 380
박승곤 87/01/30 380 330
나한열 97/07/01 330 270
송도권 98/03/22 270 310
박원범 99/12/01 310 290
허은 01/05/23 290 250
양선희 01/09/01 250 290
김현정 02/02/24 290 260
차범철 09/01/28 260 330
최슬기 09/08/30 330 220
전민 10/06/28 220 320
나교수 13/11/12 320 0
17개의 행이 선택됨
(3) RANK 함수 - 순위 출력 함수
RANK 함수는 주어진 칼럼값의 그룹에서 값의 순위를 계산한 후 순위를 출력해준다.
집계용 문법
RANK(조건값) WITHIN GROUP (ORDER BY 조건값 칼럼명 [ASC | DESC])
예1. 이름이 '송도권'인 교수의 순위 조회하기
SELECT RANK('송도권') WITHIN GROUP (ORDER BY name) "RANK"
FROM professor ;
분석용 문법
RANK() (ORDER BY 조건칼럼명 [ASC | DESC])
예1. 10번 부서의 급여순위 구하기
SELECT empno, ename, sal,
RANK() OVER (ORDER BY sal DESC) "순위"
FROM emp
WHERE deptno = 10 ;
EMPNO ENAME SAL 순위
---------- ---------- ---------- ----------
7839 KING 5000 1
1000 홍길동 3600 2
7782 CLARK 2450 3
예2. 부서별 급여 순위 구하기
SELECT empno, ename, sal, deptno,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) "RANK"
FROM emp ;
EMPNO ENAME SAL DEPTNO RANK
---------- ---------- ---------- ---------- ----------
7839 KING 5000 10 1
1000 홍길동 3600 10 2
7782 CLARK 2450 10 3
7902 FORD 5000 20 1
7788 SCOTT 3000 20 2
7566 JONES 2975 20 3
2000 일지매 3000 30 1
7698 BLAKE 2850 30 2
8개의 행이 선택됨
PARTITION BY 구문은 그룹핑을 위해 사용
(4) 누적합계 구하기
예1. panmae테이블을 사용하여 1000 번 대리점의 판매내역을 출력하되 판매일자, 제품코드, 판매량, 누적 판매금액을 출력하세요.
SELECT p_date "판매일자", p_code "제품코드", p_qty "판매량", p_total "판매금액",
SUM(p_total) OVER(ORDER BY p_total) "누적판매금액"
FROM panmae
WHERE p_store = '1000' ;
판매일자 제품코드 판매량 판매금액 누적판매금액
-------- ---------- ---------- ---------- ----------
20110103 100 2 1600 1600
20110102 102 2 2000 3600
20110101 100 3 2400 6000
20110102 105 2 3000 9000
예2. 제품코드별로 분류
SELECT p_date "판매일자", p_code "제품코드", p_qty "판매량", p_total "판매금액",
SUM(p_total) OVER(PARTITION BY p_code ORDER BY p_total) "누적판매금액"
FROM panmae
WHERE p_store = '1000' ;
판매일자 제품코드 판매량 판매금액 누적판매금액
-------- ---------- ---------- ---------- ----------
20110103 100 2 1600 1600
20110101 100 3 2400 4000
20110102 102 2 2000 2000
20110102 105 2 3000 3000
예3. 제품코드와 판매점 별로 누적합계 구하기
SELECT p_date "판매일자", p_code "제품코드", p_qty "판매량", p_total "판매금액",
SUM(p_total) OVER(PARTITION BY p_code, p_store ORDER BY p_date) "누적판매금액"
FROM panmae
WHERE p_store = '1000' ;
판매일자 제품코드 판매량 판매금액 누적판매금액
-------- ---------- ---------- ---------- ----------
20110101 100 3 2400 2400
20110103 100 2 1600 4000
20110102 102 2 2000 2000
20110102 105 2 3000 3000
예4. 각 판매점 별 판매량과 판매금액을 구한 후 수량대비비중, 금액대비비중 구하기
SELECT p_code "제품코드",
SUM(SUM(p_qty)) OVER() "총판매수량",
SUM(SUM(p_total)) OVER() "총판매금액",
p_store "판매점",
p_qty "판매량",
p_total "판매금액",
ROUND((RATIO_TO_REPORT(SUM(p_qty)) OVER())*100,2) "수량대비비중",
ROUND((RATIO_TO_REPORT(SUM(p_total)) OVER())*100,2) "금액대비비중"
FROM panmae
WHERE p_code = 100
GROUP BY p_code, p_store, p_qty, p_total ;
제품코드 총판매수량 총판매금액 판매점 판매량 판매금액 수량대비비중 금액대비비중
---------- ---------- ---------- ----- ---------- ---------- ---------- ----------
100 29 23200 1002 2 1600 6.9 6.9
100 29 23200 1001 3 2400 10.34 10.34
100 29 23200 1000 3 2400 10.34 10.34
100 29 23200 1000 2 1600 6.9 6.9
100 29 23200 1004 5 4000 17.24 17.24
100 29 23200 1004 10 8000 34.48 34.48
100 29 23200 1003 4 3200 13.79 13.79
7개의 행이 선택됨
(5) 판매비율 구하기
예1. 교수들의 급여를 구하고, 전체급여합계에서 차지하는 비율 구하기
SELECT deptno "부서번호",
SUM(SUM(pay)) OVER() "총급여",
name "교수명", pay "급여",
ROUND((RATIO_TO_REPORT(SUM(pay)) OVER())*100,2) "급여비중%"
FROM professor
GROUP BY deptno, name, pay
ORDER BY 1 ;
부서번호 총급여 교수명 급여 급여비중%
---------- ---------- ---------- ---------- ----------
101 6240 나교수 320 5.13
101 6240 박승곤 380 6.09
101 6240 송도권 270 4.33
101 6240 조인형 550 8.81
102 6240 김영조 350 5.61
102 6240 양선희 250 4.01
102 6240 주승재 490 7.85
103 6240 김도형 530 8.49
103 6240 김현정 290 4.65
103 6240 나한열 330 5.29
201 6240 심슨 570 9.13
201 6240 최슬기 330 5.29
202 6240 박원범 310 4.97
202 6240 차범철 260 4.17
203 6240 바비 500 8.01
301 6240 전민 220 3.53
301 6240 허은 290 4.65
17개의 행이 선택됨
예2. 학과별 급여합계에서 차지하는 비율 구하기
SELECT deptno "부서번호",
SUM(SUM(pay)) OVER(PARTITION BY deptno) "총급여",
name "교수명", pay "급여",
ROUND((RATIO_TO_REPORT(SUM(pay)) OVER())*100,2) "급여비중%"
FROM professor
GROUP BY deptno, name, pay
ORDER BY 1 ;
부서번호 총급여 교수명 급여 급여비중%
---------- ---------- ---------- ---------- ----------
101 1520 나교수 320 5.13
101 1520 박승곤 380 6.09
101 1520 송도권 270 4.33
101 1520 조인형 550 8.81
102 1090 김영조 350 5.61
102 1090 양선희 250 4.01
102 1090 주승재 490 7.85
103 1150 김도형 530 8.49
103 1150 김현정 290 4.65
103 1150 나한열 330 5.29
201 900 심슨 570 9.13
201 900 최슬기 330 5.29
202 570 박원범 310 4.97
202 570 차범철 260 4.17
203 500 바비 500 8.01
301 510 전민 220 3.53
301 510 허은 290 4.65
17개의 행이 선택됨
(6) LAG 함수를 활용한 차이 구하기
예1. 100번 판매점의 일자별 판매내역과 금액과 전일 판매수량과 금액 차이 출력하기
SELECT p_store "판매점",
p_date "판매날짜",
p_code "제품코드",
p_qty "당일판매수량",
LAG(p_qty, 1, 0) OVER(ORDER BY p_date) "전일판매수량",
p_qty - LAG(p_qty, 1, 0) OVER(ORDER BY p_date) "수량차이",
p_total "당일판매금액",
LAG(p_total, 1, 0) OVER(ORDER BY p_date) "전일판매금액",
p_total - LAG(p_total, 1, 0) OVER(ORDER BY p_date) "금액차이"
FROM panmae
WHERE p_store = 1000 ;
판매점 판매날짜 제품코드 당일판매수량 전일판매수량 수량차이 당일판매금액 전일판매금액 금액차이
----- -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1000 20110101 100 3 0 3 2400 0 2400
1000 20110102 102 2 3 -1 2000 2400 -400
1000 20110102 105 2 2 0 3000 2000 1000
1000 20110103 100 2 2 0 1600 3000 -1400
예2. 모든 판매점을 판매점별로 구분해서 출력하기
SELECT p_store "판매점",
p_date "판매날짜",
p_code "제품코드",
p_qty "당일판매수량",
LAG(p_qty, 1, 0) OVER(PARTITION BY p_store ORDER BY p_date) "전일판매수량",
p_qty - LAG(p_qty, 1, 0) OVER(ORDER BY p_date) "수량차이",
p_total "당일판매금액",
LAG(p_total, 1, 0) OVER(PARTITION BY p_store ORDER BY p_date) "전일판매금액",
p_total - LAG(p_total, 1, 0) OVER(ORDER BY p_date) "금액차이"
FROM panmae ;
판매점 판매날짜 제품코드 당일판매수량 전일판매수량 수량차이 당일판매금액 전일판매금액 금액차이
----- -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1000 20110101 100 3 0 3 2400 0 2400
1001 20110101 101 5 0 2 4500 0 2100
1004 20110101 103 6 0 1 5400 0 900
1003 20110101 102 2 0 -4 2000 0 -3400
1002 20110102 103 5 3 3 4500 2400 2500
1002 20110102 104 3 0 -2 2400 0 -2100
1000 20110102 102 2 2 -1 2000 3000 -400
1000 20110102 105 2 3 0 3000 2400 1000
1001 20110103 100 3 5 1 2400 4500 -600
1000 20110103 100 2 2 -1 1600 2000 -800
1004 20110103 100 10 6 8 8000 5400 6400
1003 20110103 101 4 2 -6 3600 2000 -4400
1002 20110104 102 2 2 -2 2000 1600 -1600
1001 20110104 102 4 3 2 4000 2700 2000
1004 20110104 100 5 10 1 4000 8000 0
1003 20110104 103 2 4 -3 1800 3600 -2200
1002 20110104 101 4 5 2 3600 4500 1800
1003 20110104 101 3 4 -1 2700 3200 -900
1001 20110104 101 3 3 0 2700 2400 0
1002 20110104 100 2 4 -1 1600 3600 -1100
1003 20110104 100 4 2 2 3200 1800 1600
21개의 행이 선택됨
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저