복수행 함수
단일 행 함수와 달리 한꺼번에 여러 건의 데이터가 함수로 입력됨.
GROUP 함수종류
대부분의 그룹함수는 함수에 * 를 사용하면 Null 을 포함하고 해당 컬럼에 데이터가 있는 경우만 ( 즉 NULL 값을 제외하고 ) 작업을 해서 출력함.
함수명 |
|
COUNT |
입력되는 데이터들이 총 건수를 출력 |
SUM |
합계 |
AVG |
평균값 |
MAX |
최대값 |
MIN |
최소값 |
STDDEV |
표준편차 |
VARIANCE |
분산 값 |
ROLLUP |
소계값 |
CUBE |
소계 및 전체 총계 |
| GROUPINGSET | 한번의 쿼리로 여러 개의 함수들을 그룹으로 수행 가능 |
COUNT() : 입력되는 데이터의 총 건수를 반환SCOTT>SELECT COUNT(*), COUNT(comm)
2 FROM emp;
COUNT(*) COUNT(COMM)
---------- -----------
13 4
SUM() : 합계값을 반환SCOTT>SET null --null-- ; SCOTT>SELECT ename, comm 2 FROM emp; ENAME COMM ---------- ---------- SMITH --null-- ALLEN 300 WARD 500 JONES --null-- MARTIN 1400 BLAKE --null-- CLARK --null-- SCOTT --null-- KING --null-- TURNER 0 JAMES --null-- FORD --null-- MILLER --null--
SCOTT>SELECT COUNT(comm), SUM(comm) 2 FROM emp; COUNT(COMM) SUM(COMM) ----------- ---------- 4 2200
AVG() : 평균값 변환주의해야할 사항은 모든 그룹함수를 사용할 때, null값이 존재할 경우 자동으로 null값은 제외하고 계산하기 때문에 의도치 않는 오류가 발생할 수 있다는 점이다. 그래서 NVL함수로 null값을 처리해줘야 정확한 계산이 나온다.
SCOTT>SELECT COUNT(comm), SUM(comm), AVG(comm)
2 FROM emp;
COUNT(COMM) SUM(COMM) AVG(COMM)
----------- ---------- ----------
4 2200 550SCOTT>SELECT COUNT(*), SUM(comm), AVG(NVL(comm,0))
2 FROM emp;
COUNT(*) SUM(COMM) AVG(NVL(COMM,0))
---------- ---------- ----------------
13 2200 169.230769
MAX() / MIN() : 최대 / 최소값 반환여러 건의 데이터를 입력 받아서 순서대로 정렬을 하고 그중 최대값/최소값을 추출하기 때문에 시간이 오래 걸리는 함수 중 한가지이며 그렇기에 사용할 때 주의해야함. 최대값/ 최소값을 구할 때는 MAX / MIN 함수 대신 인덱스 권장함.
SCOTT>SELECT MAX(sal), MIN(sal)
2 FROM emp;
MAX(SAL) MIN(SAL)
---------- ----------
5000 800SCOTT>SELECT MAX(hiredate) "MAX", 2 MIN(hiredate) "MIN" 3 FROM emp; MAX MIN ------------ ------------ 19-APR-87 17-DEC-80
STDDEV() / VARIANCE() : 표준편차 / 분산값을 반환GROUP BY 절 사용하기
전체 값을 조회하는 것이 아니라 특정 조건을 주고 해당 조건에 맞는 결과를 출력.
SCOTT>SELECT deptno, AVG(NVL(sal,0)) "AVG"
2 FROM emp
3 GROUP BY deptno; //부서별로 평균 급여 출력
DEPTNO AVG
---------- ----------
30 1566.66667
20 2443.75
10 2916.66667SCOTT>SELECT deptno, job, AVG(NVL(sal,0)) "AVG_SAL"
2 FROM emp
3 GROUP BY deptno, job //부서별로 먼저 그룹핑후 같은 학과일 경우 직급별로 한번더 그룹핑
4 ORDER BY 1,2; //정렬
DEPTNO JOB AVG_SAL
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 CLERK 800
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
GROUP BY 절 사용시 주의사항:
1. SELECT 절에 사용된 그룹함수 이외의 칼럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 함.
2. GROUP BY 절에 사용된 컬럼이라도 SELECT 절에는 사용되지 않아도 됨.
3. 반드시 컬럼명이 사용되어야 함 (Alias 사용불가)
HAVING 절 사용하기 (그룹핑한 조건으로 검색)
WHERE 절은 그룹함수를 비교조건으로 쓸 수가 없음. 만약, 그룹함수를 조건으로 사용하고 싶을 경우에는 WHERE 대신에 HAVING 절을 사용하면 해결됨.
SCOTT>SELECT deptno, AVG(NVL(sal,0))
2 FROM emp
3 WHERE deptno > 10
4 GROUP BY deptno
5 HAVING AVG(NVL(sal,0)) > 2000;
DEPTNO AVG(NVL(SAL,0))
---------- ---------------
20 2443.75
기본적인것 이외의 GROUP 함수들
ROLLUP(): 소계값 / CUBE() : 전체총계SCOTT>SELECT deptno, position, COUNT(*), SUM(PAY) 2 FROM professor 3 GROUP BY ROLLUP(deptno, position);
DEPTNO POSITION COUNT(*) SUM(PAY) ---------- ------------------------------ ---------- ---------- 101 instructor 1 270 101 a full professor 1 550 101 assistant professor 1 380 101 3 1200 102 instructor 1 250 102 a full professor 1 490 102 assistant professor 1 350 102 3 1090 103 instructor 1 290 103 a full professor 1 530 103 assistant professor 1 330 103 3 1150
실습을 위해 professor 테이블 복사 후 101번 학과에 중복된 직급 데이터를 추가로 입력.
SCOTT>CREATE TABLE professor2 2 AS SELECT deptno, position, pay 3 FROM professor;
Table created. SCOTT>SELECT * FROM professor2; DEPTNO POSITION PAY ---------- ------------------------------ ---------- 101 a full professor 550 101 assistant professor 380 101 instructor 270 102 instructor 250 102 assistant professor 350 102 a full professor 490 103 a full professor 530 103 assistant professor 330 103 instructor 290 SCOTT>INSERT INTO professor2 VALUES(101,'instructor', 100); 1 row created. SCOTT>INSERT INTO professor2 VALUES(101,'a full professor', 100); 1 row created. SCOTT>INSERT INTO professor2 VALUES(101,'assistant professor', 100); 1 row created. SCOTT>commit; Commit complete.SCOTT>SELECT * FROM professor2 2 ORDER BY deptno, position; D EPTNO POSITION PAY ---------- ------------------------------ ---------- 101 a full professor 100 101 a full professor 550 101 assistant professor 100 101 assistant professor 380 101 instructor 270 101 instructor 100 102 a full professor 490 102 assistant professor 350 102 instructor 250 103 a full professor 530 103 assistant professor 330 103 instructor 290
아래 예시에서 ROLLUP 함수는 GROUP BY 절에 주어진 주건으로 소계값을 구해줌. BUT, 각 부서별로 소계는 알수 있지만 교수가 몇 명 인지는 나오지 않음(즉 총 합계는 출력안됨) 그래서 전체 총 합계까지 출력하고 싶다면 ROLLUP 대신 CUBE 함수를 사용하면 됨
SCOTT>SELECT deptno, position, SUM(pay)
2 FROM professor2
3 GROUP BY deptno, ROLLUP(position);
DEPTNO POSITION SUM(PAY)
---------- ------------------------------ ----------
101 instructor 370
101 a full professor 650
101 assistant professor 480
101 1500
102 instructor 250
102 a full professor 490
102 assistant professor 350
102 1090
103 instructor 290
103 a full professor 530
103 assistant professor 330
103 1150
201 a full professor 570
201 assistant professor 330
201 900
202 instructor 260
202 assistant professor 310
202 570
203 a full professor 500
203 500
301 instructor 220
301 assistant professor 290
301 510SCOTT>SELECT deptno, position,
2 COUNT(*), SUM(pay)
3 FROM professor
4 GROUP BY CUBE(deptno, position);
DEPTNO POSITION COUNT(*) SUM(PAY)
---------- ------------------------------ ---------- ----------
16 5920
instructor 5 1290
a full professor 5 2640
assistant professor 6 1990
101 3 1200
101 instructor 1 270
101 a full professor 1 550
101 assistant professor 1 380
102 3 1090
102 instructor 1 250
102 a full professor 1 490
102 assistant professor 1 350
103 3 1150
103 instructor 1 290
103 a full professor 1 530
103 assistant professor 1 330
201 2 900
201 a full professor 1 570
201 assistant professor 1 330
202 2 570
202 instructor 1 260
202 assistant professor 1 310
203 1 500
203 a full professor 1 500
301 2 510
301 instructor 1 220
301 assistant professor 1 290
GROUPING SETS() : 그룹핑 조건이 여러 개 일 경우 사용.
하나의 테이블에 대한 여러가지 그룹 함수가 사용이 될 때 활용.
ex1) 학년별 인원수 합계와 학과별 인원수 합계를 구해야 하는 경우
SCOTT>SELECT grade, deptno1, COUNT(*) 2 FROM student 3 GROUP BY GROUPING SETS(grade,deptno1); GRADE DEPTNO1 COUNT(*) ---------- ---------- ---------- 102 4 201 6 301 2 101 4 202 2 103 2 1 5 2 5 4 5 3 5
ex2) 학년별로 학과별로 인원수와 키의 합계, 몸무게의 합계를 동시에 출력
SCOTT>SELECT grade, deptno1, 2 COUNT(*), SUM(height), 3 SUM(weight) 4 FROM student 5 GROUP BY GROUPING SETS(grade,deptno1); GRADE DEPTNO1 COUNT(*) SUM(HEIGHT) SUM(WEIGHT) ---------- ---------- ---------- ----------- ----------- 102 4 683 257 201 6 1037 402 301 2 344 120 101 4 688 240 202 2 359 125 103 2 331 103 1 5 852 312 2 5 878 337 4 5 879 341 3 5 833 257
LISTAGG() : 나열하는 함수. WITHIN GROUP 사이에 가로로 나열하고 싶은 규칙을 ORDER BY 로 적어주면 됨. 연결자를 설정해주지 않으면 모든 데이터가 한줄로 연결되어 보기 안좋음.(WITHIN GROUP 절에 아무 조건도 조지 않으면 에러발생, 연결자에 예약어 사용못함)
SCOTT>SELECT deptno, 2 LISTAGG(ename, '->') WITHIN GROUP(ORDER BY hiredate) "LISTAGG" 3 FROM emp 4 GROUP BY deptno; DEPTNO LISTAGG ---------- -------------------------------------------------- 10 CLARK->KING->MILLER 20 SMITH->JONES->FORD->SCOTT 30 ALLEN->WARD->BLAKE->TURNER->MARTIN->JAMES
PIVOT() : Row 단위를 Column 단위로 변경.(UNPIVOT()은반대로 변경)
- ex1) 우선, pivot 기능 사용하지 않고 decode 함수 사용해 달력만들기( 달력만들기로 유명한 방법)
SCOTT>SELECT MAX(DECODE(day, 'SUN', dayno)) SUN, 2 MAX(DECODE(day, 'MON', dayno)) MON, 3 MAX(DECODE(day, 'TUE', dayno)) TUE, 4 MAX(DECODE(day, 'WED', dayno)) WED, 5 MAX(DECODE(day, 'THU', dayno)) THU, 6 MAX(DECODE(day, 'FRI', dayno)) FRI, 7 MAX(DECODE(day, 'SAT', dayno)) SAT 8 FROM cal 9 GROUP BY weekno 10 ORDER BY weekno;
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 사용해 달력 만들기. (PIVOT 절에 MAX(dayno) 절은 DECODE 문장에서 사용되는 함수를 적으면 되고 FOR 절에는 화면에 집계될 그룹핑 할 컬럼을 적으면 됨. 아래의 예는 가상의 뷰를 하나 생성 한 후 pivot 기능을 활용. IN 연산자 뒤에는 서브쿼리를 사용할 수 없음)
SCOTT>SELECT * FROM (SELECT weekno "WEEK", day, dayno 2 FROM cal) 3 PIVOT 4 ( MAX(dayno) FOR day IN('SUN' AS "SUN" , 5 'MON' AS "MON" , 6 'TUE' AS "TUE" , 7 'WED' AS "WED" , 8 'THU' AS "THU" , 9 'FRI' AS "FRI" , 10 'SAT' AS "SAT") 11 ) 12 ORDER BY "WEEK";
WEEK SUN MON TUE WED THU FRI SAT ---- ---- ---- ---- ---- ---- ---- ---- 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 31ex2) 부서별로 각 직급별 인원이 몇명인지 계산하기(decode/pivot 비교)
SCOTT>SELECT deptno, 2 COUNT(DECODE(job, 'CLERK', '0')) "CLERK", 3 COUNT(DECODE(job, 'MANAGER', '0')) "MANAGER", 4 COUNT(DECODE(job, 'PRESIDENT', '0')) "PRESIDENT", 5 COUNT(DECODE(job, 'ANALYST', '0')) "ANALYST", 6 COUNT(DECODE(job, 'SALESMAN', '0')) "SALESMAN" 7 FROM emp 8 GROUP BY deptno 9 ORDER BY deptno;
DEPTNO CLERK MANAGER PRESIDENT ANALYST SALESMAN ---------- ---------- ---------- ---------- ---------- ---------- 10 1 1 1 0 0 20 1 1 0 2 0 30 1 1 0 0 4
SCOTT>SELECT * FROM (SELECT deptno, job, empno 2 FROM emp) 3 PIVOT 4 ( 5 COUNT(empno) FOR job IN ('CLERK' AS "CLERK", 6 'MANAGER' AS "MANAGER", 7 'PRESIDENT' AS "PRESIDENT", 8 'ANALYST' AS "ANALYST", 9 'SALESMAN' AS "SALESMAN") 10 ) 11 ORDER BY deptno ;
DEPTNO CLERK MANAGER PRESIDENT ANALYST SALESMAN ------- ---------- ---------- ---------- ---------- ---------- 10 1 1 1 0 0 20 1 1 0 2 0 30 1 1 0 0 4조금 더 복잡하지만 PIVOT 부분에 아래와 같이 그룹핑 조건을 여러 개 사용해도 됨.(부서별 인원수와 각 인원수 별 급여 합계까지 함께 출력하는 예제)
SCOTT>SELECT * FROM (SELECT deptno, job, empno, sal 2 FROM emp) 3 PIVOT 4 ( 5 COUNT(empno) AS COUNT, 6 SUM(NVL(sal,0)) AS SUM FOR job IN('CLERK' AS "C", 7 'MANAGER' AS "M", 8 'PRESIDENT' AS "P", 9 'ANALYST' AS "A", 10 'SALESMAN' AS "S") 11 ) 12 ORDER BY deptno;
DEPTNO C_COUNT C_SUM M_COUNT M_SUM P_COUNT P_SUM A_COUNT A_SUM S_COUNT S_SUM ----- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- 10 1 1300 1 2450 1 5000 0 0 20 1 800 1 2975 0 2 6000 0 30 1 950 1 2850 0 0 4 5600UNPIVOT : 합쳐져 있는 결과를 다시 풀어줌.(테스트를 위해 아래와 같이 테이블생성 후 실습)
SCOTT>CREATE TABLE upivot 2 AS SELECT * FROM (SELECT deptno, job, empno 3 FROM emp) 4 PIVOT 5 ( COUNT(empno) FOR job IN('CLERK' AS "CLERK", 6 'MANAGER' AS "MANAGER", 7 'PRESIDENT' AS "PRESIDENT", 8 'ANALYST' AS "ANALYST", 9 'SALESMAN' AS "SALESMAN") 10 ); Table created.
SCOTT>SELECT * FROM upivot;
DEPTNO CLERK MANAGER PRESIDENT ANALYST SALESMAN ---------- ---------- ---------- ---------- ---------- ---------- 30 1 1 0 0 4 20 1 1 0 2 0 10 1 1 1 0 0
SCOTT>SELECT * FROM upivot 2 UNPIVOT 3 ( 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 1 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
LAG() / LEAD(): 이전/이후 행 값을 가져 올 때 사용하는 함수
RANK() : 순위계산. 같은 순위의 경우 같은 출력 값을 가지기 때문에 출력결과가 연속하지 않을 수 있다. 특정 순위만 볼수도 있고 전체 순위를 볼수도 있음.
특정 데이터의 순위 확인하기 :
RANK(조건값) WITHIN GROUP (ORDER BY 조건값 컬럼명 [ASC | DESC])SCOTT>SELECT RANK('SMITH') WITHIN GROUP (ORDER BY ename) "RANK" 2 FROM emp;
RANK ---------- 11전체순위 확인하기 :
RANK() OVER(ORDER BY 조건컬럼명 [ASC | DESC])SCOTT>SELECT empno, ename, sal, 2 RANK() OVER (ORDER BY sal) AS RANK_ASC, 3 RANK() OVER (ORDER BY sal DESC) AS RANK_DESC 4 FROM emp;
EMPNO ENAME SAL RANK_ASC RANK_DESC ----- -------- ---------- ---------- ---------- 7839 KING 5000 13 1 7788 SCOTT 3000 11 2 7902 FORD 3000 11 2 7566 JONES 2975 10 4 7698 BLAKE 2850 9 5 7782 CLARK 2450 8 6 7499 ALLEN 1600 7 7 7844 TURNER 1500 6 8 7934 MILLER 1300 5 9 7654 MARTIN 1250 3 10 7521 WARD 1250 3 10 7900 JAMES 950 2 12 7369 SMITH 800 1 13ex) PARTITION BY 절 이후에 분류 조건 추가하기
SCOTT>SELECT empno, ename, sal, deptno, 2 RANK() OVER (PARTITION BY deptno //사번, 이름, 급여, 부서번호, 부서별 급여순위를 출력 3 ORDER BY sal DESC) "RANK" 4 FROM emp; EMPNO ENAME SAL DEPTNO RANK -------- ---------- ---------- ---------- ---------- 7839 KING 5000 10 1 7782 CLARK 2450 10 2 7934 MILLER 1300 10 3 7788 SCOTT 3000 20 1 7902 FORD 3000 20 1 7566 JONES 2975 20 3 7369 SMITH 800 20 4 7698 BLAKE 2850 30 1 7499 ALLEN 1600 30 2 7844 TURNER 1500 30 3 7521 WARD 1250 30 4 7654 MARTIN 1250 30 4 7900 JAMES 950 30 6
SCOTT>SELECT empno, ename, sal, deptno, 2 RANK() OVER (PARTITION BY deptno, job //부서번호 내에서 job 별로 RANK 3 ORDER BY sal DESC) "RANK" 4 FROM emp;
EMPNO ENAME SAL DEPTNO RANK ---------- ---------- ---------- ---------- ---------- 7934 MILLER 1300 10 1 7782 CLARK 2450 10 1 7839 KING 5000 10 1 7788 SCOTT 3000 20 1 7902 FORD 3000 20 1 7369 SMITH 800 20 1 7566 JONES 2975 20 1 7900 JAMES 950 30 1 7698 BLAKE 2850 30 1 7499 ALLEN 1600 30 1 7844 TURNER 1500 30 2 7521 WARD 1250 30 3 7654 MARTIN 1250 30 3
SUM() OVER() : 누계
- ex) panmae 테이블을 사용하여 1000 번 대리점의 판매 내역을 출력하되 판매일자, 제품코드, 판매량, 누적 판매금액을 출력
SCOTT>SELECT p_date, p_code, p_qty, p_total, 2 SUM(p_total) OVER(ORDER BY p_total) "TOTAL" 3 FROM panmae 4 WHERE p_store = 1000;
P_DATE P_CODE P_QTY P_TOTAL TOTAL -------- ---------- ---------- ---------- ---------- 20110103 100 2 1600 1600 20110102 102 2 2000 3600 20110101 100 3 2400 6000 20110102 105 2 3000 9000
SCOTT>SELECT p_date, p_code, p_qty, p_total, 2 SUM(p_total) OVER(PARTITION BY p_code ORDER BY p_total) "TOTAL" //제품 코드별로 분류한 후 누계. 3 FROM panmae 4 WHERE p_store = 1000;
P_DATE P_CODE P_QTY P_TOTAL TOTAL -------- ---------- ---------- ---------- ---------- 20110103 100 2 1600 1600 20110101 100 3 2400 4000 20110102 102 2 2000 2000 20110102 105 2 3000 3000
SCOTT>SELECT p_code, p_store, p_date, p_qty, p_total, 2 SUM(p_total) OVER(PARTITION BY p_code, p_store ORDER BY p_date) "TOTAL" 3 FROM panmae;
P_CODE P_STO P_DATE P_QTY P_TOTAL TOTAL ------- ----- -------- ---------- ---------- ---------- 100 1000 20110101 3 2400 2400 100 1000 20110103 2 1600 4000 100 1001 20110103 3 2400 2400 100 1002 20110104 2 1600 1600 100 1003 20110104 4 3200 3200 100 1004 20110103 10 8000 8000 100 1004 20110104 5 4000 12000 101 1001 20110101 5 4500 4500 101 1001 20110104 3 2700 7200 101 1002 20110104 4 3600 3600 101 1003 20110103 4 3600 3600 101 1003 20110104 3 2700 6300 102 1000 20110102 2 2000 2000 102 1001 20110104 4 4000 4000 102 1002 20110104 2 2000 2000 102 1003 20110101 2 2000 2000 103 1002 20110102 5 4500 4500 103 1003 20110104 2 1800 1800 103 1004 20110101 6 5400 5400 104 1002 20110102 3 2400 2400 105 1000 20110102 2 3000 3000
RATIO_TO_REPORT() : 비율출력
- ex) 각 판매점 별 판매비중 구하기
SCOTT>SELECT p_code, SUM(SUM(p_qty)) OVER() "TOTAL_QTY", 2 SUM(SUM(p_total)) OVER() "TOTAL_PRICE", p_store, p_qty, p_total, 3 ROUND((RATIO_TO_REPORT(SUM(p_qty)) OVER())*100,2) "qty_%", 4 ROUND((RATIO_TO_REPORT(SUM(p_total)) OVER())*100,2) "total_%" 5 FROM panmae 6 WHERE pcode = 100 7 GROUP BY p_code, p_store, p_qty, p_total;
P_CODE TOTAL_QTY TOTAL_PRICE P_STO P_QTY P_TOTAL qty_% 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
LAG() 함수 이용해 차이 구하기 예제.
전일 판매량과 금액을 구하여 당일과 전일 판매 수량과 금액의 차이를 보여주는 예제. 아래 코드에서 P_STORE 는 판매점 코드, P_DATE 는 판매날짜, P_QTY 는 판매수량 , D-1 QTY 는 전일 판매량 , DIFF-QTY 는 전일과 당일 판매량 차이 , P_TOTAL 은 당일판매금액 , D-1 PRICE 는 전일 판매금액 , DIFF_PRICE 는 전일과 당일 판매 금액 차이
SCOTT>SELECT p_code, SUM(SUM(p_qty)) OVER() "TOTAL_QTY", 2 SUM(SUM(p_total)) OVER() "TOTAL_PRICE", p_store, p_qty, p_total, 3 ROUND((RATIO_TO_REPORT(SUM(p_qty)) OVER())*100,2) "qty_%", 4 ROUND((RATIO_TO_REPORT(SUM(p_total)) OVER())*100,2) "total_%" 5 FROM panmae 6 WHERE p_code = 100 7 GROUP BY p_code, p_store, p_qty, p_total;
P_CODE TOTAL_QTY TOTAL_PRICE P_STO P_QTY P_TOTAL qty_% 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모든 판매점을 판매점 별로 구분해서 다 출력하고 싶을 경우
SCOTT>SELECT p_store, p_date, p_code, p_qty, 2 LAG(p_qty,1) OVER(PARTITION BY p_store ORDER BY p_date) "D-1 QTY", 3 p_qty - LAG(p_qty,1) OVER(PARTITION BY p_store ORDER BY p_date) "DIFF-QTY", 4 p_total, 5 LAG(p_total,1) OVER(PARTITION BY p_store ORDER BY p_date) "D-1 PRICE", 6 p_total - LAG(p_total,1) OVER(PARTITION BY p_store ORDER BY p_date) "DIFF-PRICE" 7 FROM panmae;
P_STO P_DATE P_CODE P_QTY D-1 QTY DIFF-QTY P_TOTAL D-1 PRICE DIFF-PRICE ----- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1000 20110101 100 3 2400 1000 20110102 105 2 3 -1 3000 2400 600 1000 20110102 102 2 2 0 2000 3000 -1000 1000 20110103 100 2 2 0 1600 2000 -400 1001 20110101 101 5 4500 1001 20110103 100 3 5 -2 2400 4500 -2100 1001 20110104 101 3 3 0 2700 2400 300 1001 20110104 102 4 3 1 4000 2700 1300 1002 20110102 104 3 2400 1002 20110102 103 5 3 2 4500 2400 2100 1002 20110104 101 4 5 -1 3600 4500 -900 1002 20110104 100 2 4 -2 1600 3600 -2000 1002 20110104 102 2 2 0 2000 1600 400 1003 20110101 102 2 2000 1003 20110103 101 4 2 2 3600 2000 1600 1003 20110104 103 2 4 -2 1800 3600 -1800 1003 20110104 100 4 2 2 3200 1800 1400 1003 20110104 101 3 4 -1 2700 3200 -500 1004 20110101 103 6 5400 1004 20110103 100 10 6 4 8000 5400 2600 1004 20110104 100 5 10 -5 4000 8000 -4000
QUIZ
1) emp 테이블을 사용하여 사원 중에서 급여(sal)와 보너스(comm)를 합친 금액이 가장 많은 경우와 가장 적은 경우 , 평균 금액을 구하세요. 단 보너스가 없을 경우는 보너스를 0 으로 계산하고 출력 금액은 모두 소수점 첫째 자리까지만 나오게 하세요
2) student 테이블의 birthday 컬럼을 참조해서 아래와 같이 월별로 생일자수를 출력하세요.
3) Student 테이블의 tel 컬럼을 참고하여 아래와 같이 지역별 인원수를 출력하세요. 단 02 –서울 , 031 – 경기 , 051 – 부산 , 052 – 울산 , 053 – 대구 , 055 – 경남으로 출력하세요
4) 먼저 emp 테이블에 아래의 두 건의 데이터를 입력 하신 후 작업하세요.
Emp 테이블을 사용하여 아래의 화면과 같이 부서별로 직급별로 급여 합계 결과를 출력하세요.
SCOTT>INSERT INTO emp (empno,deptno,ename,sal)
2 VALUES (1000,10,'Tiger',3600) ;
SCOTT> INSERT INTO emp (empno,deptno,ename,sal)
2 VALUES (2000,30,'Cat',3000);
SCOTT> COMMIT ;
5) emp 테이블을 사용하여 직원들의 급여와 전체 급여의 누적 급여금액이 아래와 같도록 출력하세요. 단 급여를 오름차순으로 정렬해서 출력하세요.
6) fruit 테이블을 아래와 같은 형태로 출력하세요.
7) student 테이블의 Tel 컬럼을 사용하여 아래와 같이 지역별 인원수와 전체대비 차지하는 비율을 출력하세요. (단 02 –서울 , 031 – 경기 , 051 – 부산 , 052 – 울산 , 053 – 대구 , 055 – 경남으로 출력하세요)
8) emp 테이블을 사용하여 아래와 같이 부서별로 급여 누적 합계가 나오도록 출력하세요 단 부서번호로 오름차순 출력하세요.
9) emp 테이블을 사용하여 아래와 같이 각 사원의 급여액이 전체 직원 급여총액에서 몇 %의 비율을 차지하는지 출력하세요. 단 급여 비중이 높은 사람이 먼저 출력되도록 하세요.
10) emp 테이블을 조회하여 아래와 같이 각 직원들의 급여가 해당 부서 합계금액에서 몇 %의 비중을 차지하는지를 출력하세요. 단 부서번호를 기준으로 오름차순으로 출력하세요.
11) loan 테이블을 사용하여 1000번 지점의 대출 내역을 출력하되 대출일자,대출코드,대출금액,일자별 누적대출금액을 아래와 같이 출력하세요.
12) loan 테이블을 사용하여 전체 지점의 대출코드 , 대출지점 , 대출날짜 , 대출건수 , 대출금액을 대출코드와 대출지점별로 누적 합계를 구하세요.
13) professor 테이블에서 각 교수들의 급여를 구하고 각 교수의 급여액이 전체 교수의 급여 합계에서 차지하는 비율을 출력하세요
14) professor 테이블을 조회하여 학과번호 , 교수명 , 급여 , 학과별 급여 합계를 구하고 각 교수의 급여가 해당 학과별 급여 합계에서 차지하는 비율을 출력하세요.
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저
'Oracle > sql' 카테고리의 다른 글
| [SQL] 5. DDL과 Data Dictionary (773) | 2016.09.15 |
|---|---|
| [SQL] 4. JOIN (796) | 2016.09.12 |
| [SQL] 2. 단일행함수(정규식표현) (315) | 2016.09.05 |
| [SQL] 2. 단일행함수(일반함수) (11) | 2016.09.02 |
| [SQL] 2. 단일행함수(숫자, 날짜, 형변환 함수) (993) | 2016.08.30 |