고정 헤더 영역
상세 컨텐츠
본문
오라클 사용자를 위한
SQL 튜닝 실무 사례
( PART III )
2007년 6월 21일
PART III
퀴즈 1. 중복된 로우만 골라서 삭제하라..........................................................................................................
퀴즈 2. 정확히 10위까지만 구하라......................................................................................................................
퀴즈 3. 공수별 비용 집계를 구하라..................................................................................................................
퀴즈 4. 제품별 집계와 등급별 집계를 같이 구하라...............................................................................
퀴즈 5. 당일 분, 일주일 분, 한달 분을 같이 구하라.............................................................................
퀴즈 6. 한 테이블의 값을 가로로 두 번 표시하라..................................................................................
퀴즈 7. 값이 바뀔 때 마다 일련번호를 다시 시작하라........................................................................
퀴즈 8. 불용 카드 수를 구하라............................................................................................................................
퀴즈 9. 판매일자, 판매량 별로 누적 판매량을 구하라.........................................................................
퀴즈 10. 4/4 분기 동안 점포별 매출액을 구하라...................................................................................
퀴즈 11. 특정 품종에 대한 총 판매수량과 금액을 구하라................................................................
퀴즈 12. 한번이라도 매출을 일으킨 고객을 구하라...............................................................................
퀴즈 13. 매출액 20,000 이하 또는 없는 고객을 삭제하라...................................................................
퀴즈 14. 가산점과 신용 한도를 증가시켜라................................................................................................
퀴즈 15. 매출 순위 10위까지의 고객 리스트를 작성하라..................................................................
퀴즈 16. 일별 총계 및 제품별 총계를 같이 구하라...............................................................................
퀴즈 17. 입력 구분에 따라 서로 다른 총매출액을 구하라................................................................
퀴즈 18. 년간 매출을 주 단위로 매출 구분하여 구하라.....................................................................
여기서 소개된 퀴즈들은 독자들의 SQL 이해력을 높이고 집합적 사고 능력을 함양 시키고자 준비한 것으로 간단하지만 많은 생각을 하여야만 정확한 답을 구할 수가 있다.
다만, 주의 할 점은 데이터가 많아졌을 때 나타날 수 있는 성능 상의 문제는 고려하지 않았으므로 실무 적용 시에는 다른 여러 가지 요소를 같이 고려하여 적용해야 한다는 것이다.
퀴즈 1. 중복된 로우만 골라서 삭제하라.
1) 배경
프로그램 개발을 위하여 샘플 데이터를 로딩한 뒤 PRIMARY KEY나 UNIQUE 인덱스를 만들고 싶지만 원시 데이터의 잘못으로 중복된 로우가 있으면 에러가 발생한다.
이럴 경우에 이를 확인하고 삭제하고 싶을 때가 있다.
EMP 테이블을 예를 들어 설명한다.
SQL> SELECT ROWID, ROWNUM, EMPNO, ENAME
2 FROM EMP
3 ORDER BY EMPNO, ROWID;
ROWID ROWNUM EMPNO ENAME
------------------ ------- ------- ----------
00024F04.0002.0005 3 7698 BLAKE
00024F04.0005.0005 4 7698 BLAKE <-- 삭제 대상
00024F04.0006.0005 5 7782
00024F04.0001.0005 2 7788 SCOTT
00024F04.0007.0005 6 7788 SCOTT <-- 삭제 대상
00024F04.000B.0005 10 7788 SCOTT <-- 삭제 대상
00024F04.0008.0005 7 7839 KING
00024F04.0000.0005 1 7844 TURNER
00024F04.0009.0005 8 7844 TURNER <-- 삭제 대상
00024F04.000A.0005 9 7876
10 rows selected.
2) 요구 내용
위의 데이터 중 EMPNO 컬럼의 값이 중복된 로우에 대하여 ROWID 값이 큰 로우만 골라서 삭제하라.
3) 해답
중복된 로우를 찾아보기만 하는 경우
SELECT EMPNO, ENAME FROM EMP A
WHERE ROWID > ( SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
중복된 로우를 찾아서 ROWID가 더 큰 것을 삭제하는 경우 이 때 ROWID가 더 큰 것은 아마도 뒤에 생긴 데이터일 가능성이 더 많기 때문이다.
DELETE FROM EMP A
WHERE ROWID > ( SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
만약 나중에 들어온 데이터를 살리고 먼저 들어온 데이터를 삭제하고자 한다면 WHERE 조건의 부등호와 MIN을 MAX로 바꾸어서 다음과 같이 하면 된다.
DELETE FROM EMP A
WHERE ROWID < ( SELECT MAX(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
퀴즈 2. 정확히 10위까지만 구하라.
1) 배경
고정된 형태의 화면에서 항상 정확히 10 등까지의 데이터만 필요한 경우이다.
다음과 같은 EMP 테이블과 데이터가 있다.
SELECT ROWNUM, SAL, ENAME, JOB, EMPNO
FROM EMP
ORDER BY SAL DESC, ENAME;
ROWNUM 데이터는 ORDER BY에 의해 소트 가 수행되기 전에 붙여지므로 결과는 다음과 같이 나타난다.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
9 5000 KING PRESIDENT 7839
13 3000 FORD ANALYST 7902
8 3000 SCOTT ANALYST 7788
4 2975 JONES MANAGER 7566
6 2850 BLAKE MANAGER 7698
7 2450
2 1600 ALLEN SALESMAN 7499
10 1500 TURNER SALESMAN 7844
14 1300 MILLER CLERK 7934 <--- 9 번째 로우 9 위
5 1250 MARTIN SALESMAN 7654 <--- 10 번째 로우 10 위 : CASE 2
3 1250 WARD SALESMAN 7521 <--- 11 번째 로우 10 위 : CASE 1
11 1100 ADAMS CLERK 7876 <--- 12 번째 로우 12 위
12 950 JAMES CLERK 7900
1 800 SMITH CLERK 7369
14 rows selected.
2) 요구 내용
위의 데이터를 이용하여 순위를 나타내는 컬럼과 함께 10위 까지 나오는 SQL문과 (위의 샘플 데이터 경우 WARD, 11 번째 로우) 정확히 10번째 로우까지만 나오는 (위의 샘플 데이터 경우 MARTIN, 10 번째 로우) SQL문을 작성하라.
즉, 다음과 같은 결과가 나오게 하라.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
1 5000 KING PRESIDENT 7839
2 3000 FORD ANALYST 7902
3 3000 SCOTT ANALYST 7788
4 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450
7 1600 ALLEN SALESMAN 7499
8 1500 TURNER SALESMAN 7844
9 1300 MILLER CLERK 7934
10 1250 MARTIN SALESMAN 7654
11 1250 WARD SALESMAN 7521
11 rows selected.
3) 해답
l CASE 1 : 상위 10위 까지 의 로우를 출력
SELECT ROWNUM, SAL, ENAME, JOB, EMPNO
FROM EMP E1
WHERE 10 > ( SELECT COUNT(*)
FROM EMP E2
WHERE E2.SAL > E1.SAL )
ORDER BY SAL DESC ;
다음과 같은 출력이 나온다.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
8 5000 KING PRESIDENT 7839
7 3000 SCOTT ANALYST 7788
10 3000 FORD ANALYST 7902
3 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450
1 1600 ALLEN SALESMAN 7499
9 1500 TURNER SALESMAN 7844
11 1300 MILLER CLERK 7934
2 1250 WARD SALESMAN 7521
4 1250 MARTIN SALESMAN 7654
11 rows selected.
그러나 이 출력은 ROWNUM 값이 소트 되기 전에 붙으므로 원하는 결과와 다르다.
ROWNUM을 순차적으로 표시하기 위해 IN-LINE VIEW를 하나 더 씌운다.
그러나 이렇게 되면 IN-LINE VIEW 내에서 ORDER BY 문을 사용할 수 없게 되므로 GROUP BY 문을 활용하여 소트 기능을 대신한다.
SELECT ROWNUM AS RANK, -SAL SAL, ENAME, JOB, EMPNO
FROM ( SELECT -SAL SAL, ENAME, JOB, EMPNO
FROM EMP E1
WHERE 10 > ( SELECT COUNT(*)
FROM EMP E2
WHERE E2.SAL > E1.SAL )
GROUP BY -SAL, ENAME, JOB, EMPNO) ;
다음과 같은 출력이 나온다.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
1 5000 KING PRESIDENT 7839
2 3000 SCOTT ANALYST 7788
3 3000 FORD ANALYST 7902
4 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450
7 1600 ALLEN SALESMAN 7499
8 1500 TURNER SALESMAN 7844
9 1300 MILLER CLERK 7934
10 1250 WARD SALESMAN 7521
11 1250 MARTIN SALESMAN 7654
11 rows selected.
l CASE 2 : 상위 10째 까지 의 로우를 출력
SELECT ROWNUM AS RANK, -SAL SAL, ENAME, JOB, EMPNO
FROM ( SELECT -SAL SAL, ENAME, JOB, EMPNO
FROM EMP E1
WHERE 10 > ( SELECT COUNT(*)
FROM EMP E2
WHERE E2.SAL > E1.SAL )
GROUP BY -SAL, ENAME, JOB, EMPNO)
WHERE ROWNUM <= 10 ;
최종 결과는 위와 같다.
정확히 10 번째 로우에서 자르기 위해 ROWNUM <= 10 조건을 추가하였다.
그러나 처음부터 10 번째 로우에서 자르겠다면 굳이 위와 같이 하지 않고 간단히 아래와 같이 하는 방법도 있다.
SELECT ROWNUM, -SAL SAL, ENAME, JOB, EMPNO
FROM (SELECT -SAL SAL, ENAME, JOB, EMPNO
FROM EMP
GROUP BY -SAL, ENAME, JOB, EMPNO)
WHERE ROWNUM <= 10 ;
위의 두 SQL문의 결과는 다음과 같다.
ROWNUM SAL ENAME JOB EMPNO
------- ------- ---------- --------- -------
1 5000 KING PRESIDENT 7839
2 3000 SCOTT ANALYST 7788
3 3000 FORD ANALYST 7902
4 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450
7 1600 ALLEN SALESMAN 7499
8 1500 TURNER SALESMAN 7844
9 1300 MILLER CLERK 7934
10 1250 MARTIN SALESMAN 7654
10 rows selected.
퀴즈 3. 공수별 비용 집계를 구하라.
1) 배경
어떤 건설 회사의 전표 테이블에서 투입된 공임에 대한 데이터를 다음과 같이 관리하고 있다.
동일한 성격의 일을 하고 있는데 작업 번호 101 번은 1 명이 투입되어 6 시간 걸려서 끝냈고 그때의 비용은 200,000 원 이었고, 102 번은 3 명이 투입되어 2 시간 만에 끝냈으며 그 때의 비용은 230,000원 이다.
|
작업 번호 |
인원 |
시간 |
비용 |
|
101 |
1 |
6 |
200,000 |
|
102 |
3 |
2 |
230,000 |
|
103 |
2 |
3 |
220,000 |
|
104 |
6 |
1 |
300,000 |
|
105 |
1 |
8 |
300,000 |
|
106 |
5 |
3 |
500,000 |
|
107 |
2 |
6 |
400,000 |
|
108 |
1 |
15 |
550,000 |
|
109 |
1 |
12 |
450,000 |
|
110 |
3 |
5 |
530,000 |
|
|
... |
... |
... |
|
150 |
4 |
6 |
900,000 |
2) 요구 내용
작업 번호 101, 102번을 예로 들면 이 두 작업은 서로 다른 것 같지만 "인원*시간" 을 한 값이 같으므로 같은 공수로 볼 수 있다.
이와 같은 맥락으로 보면 작업 번호 103, 104번도 동일한 공수이다.
따라서 공수가 6인 작업의 비용을 모두 구하면 200,000 + 230,000 + 220,000 + 300,000 = 950,000원이 된다.
|
공수 |
비용합계 |
요구 내용 |
|
6 |
950,000 |
왼편의 표와 같이 모든 공수의 종류를 구하고 그 공수별로 들어간 비용의 합계를 구하라. |
|
8 |
300,000 | |
|
12 |
850,000 | |
|
15 |
1,580,000 | |
|
... |
..... | |
|
24 |
900,000 |
3) 해답
SELECT 시간*인원 "공수", SUM(비용) "금액"
FROM 전표테이블
WHERE 발생일 LIKE '9610%'
GROUP BY 시간*인원 ;
GROUP BY 시간*인원 라는 아이디어를 이용하여 하나의 SQL 문으로 풀 수 있다.
퀴즈 4. 제품별 집계와 등급별 집계를 같이 구하라.
1) 배경
어떤 제조 회사의 제품별 판매 내용이 다음과 같다.
1996년 10월에 판매된 제품의 내용은 다음과 같다.
|
제품명 |
모델명 |
수량 |
매출액 |
집계구분 | |
|
HP |
HJ300 |
1 |
10,000 |
제 품 별 집 계 | |
|
HP |
HJ300 |
2 |
20,000 | ||
|
HP |
HA120 |
1 |
5,000 | ||
|
HP |
HB580 |
1 |
20,000 | ||
|
HP |
HB580 |
3 |
6,000 | ||
|
LD |
A530G |
1 |
100,000 |
모 델 명 사 용 |
모 델 별 집 계 |
|
LD |
A530G |
3 |
300,000 | ||
|
LD |
A210K |
2 |
400,000 | ||
|
LD |
A210K |
3 |
600,000 | ||
|
PP |
P530C |
10 |
100,000 | ||
|
PP |
P530D |
5 |
25,000 |
기 타 | |
|
PP |
R640A |
10 |
15,000 | ||
|
PP |
R650B |
20 |
20,000 | ||
2) 요구 내용
위와 같이 판매일자와 함께 각각의 제품에 대한 판매 내용이 있을 때 전체 판매에 대한 수량과 매출액 등의 통계자료를 보고싶은 것이다.
단, 통계자료를 볼 때 제품명이 HP인 제품은 저가여서 제품별로 만 봐도 되니까 모델 별은 무시하고 제품별로 만 수량과 매출액 합계를 내고, 제품명이 LD인 제품은 고가이므로 각 모델별로 수량과 매출액 합계를 내고, 제품명이 PP인 제품은 저가 단품들 이므로 모든 모델을 하나로 몰아서 기타로 하여 수량과 매출액에 대한 합계를 구하라.
그러나 제품명이 PP인 제품 중에서 모델명이 P530C인 제품은 최근에 개발한 신제품이므로 기타에 합치지 말고 별도로 빼내어 모델명까지 구분하도록 하라.
3) 해답
GROUP BY 와 DECODE 의 절묘한 조화로 다음과 같이 하나의 SQL 문으로 답을 구할 수 있다.
SELECT DECODE(제품,'HP', 제품, 'LD', 모델명, 'PP',
DECOE(모델명, 'P530C', 모델명, '기타')), <--- 제품이 'PP'인 경우
SUM(수량), SUM(매출액)
FROM 매출테이블
WHERE 매충일 LIKE '199610%'
GROUP BY DECODE(제품,'HP', 제품, 'LD', 모델명, 'PP',
DECODE(모델명, 'P530C', 모델명, '기타')) ;
퀴즈 5. 당일 분, 일주일 분, 한달 분을 같이 구하라.
1) 배경
다음과 같은 판매 데이터가 들어있는 테이블이 있다.
온라인 화면에서 사용되며 통계 테이블은 별도로 준비되어 있지 않다.
|
|
SALEDEPT |
ITEM |
CHULQTY |
SALEAMT |
|
|
... |
... |
... |
... |
... |
|
|
19961021 |
100 |
P100 |
100 |
1000 |
|
|
19961021 |
100 |
P102 |
50 |
500 |
|
|
19961021 |
101 |
Q110 |
10 |
2000 |
|
|
19961021 |
101 |
Q111 |
10 |
2000 |
|
|
19961021 |
102 |
R110 |
20 |
1000 |
|
|
19961022 |
100 |
P100 |
200 |
2000 |
최근 최고분 |
|
19961022 |
100 |
P102 |
100 |
1000 |
|
|
19961022 |
101 |
Q110 |
5 |
1000 |
최근 최고분 |
|
... |
... |
... |
... |
... |
|
2) 요구 내용
위 테이블을 이용하여 당일분 합계, 최근 일주일분 합계, 가장 최근의 최고 판매량분, 월간 합계를 같이 구하라.
통상 " 가장 최근의 최고 판매량분" 은 그날의 판매 데이터 중 최고 판매량 이지만 그날의 데이터가 아직 들어가지 않은 상태라면 어제의 판매량 중 최고의 데이터를 가져온다.
만약 오늘이
3) 해답
DECODE, SIGN, MAX 함수를 사용하여 다음과 같이 하나의 SQL 문으로 해결할 수 있다.
SELECT SALEDEPT,
SUM(DECODE(SALEDATE, TO_CHAR(SYSDATE,'YYYYMMDD'),SALEQTY,0)) AS '당일합계' ,
SUM(DECODE(SIGN (8 - (SYSDATE - TO_DATE(SALEDATE,'YYYYMMDD'))
), 1, SALEQTY)) AS '일주일분합계' ,
SUBSTR(MAX(SALEDATE || CHULQTY),9,20) AS '최근 최고분'
SUM(SALEAMT) AS '월간합계'
FROM 판매테이블
WHERE SALEDATE LIKE '9610%'
GROUP BY SALEDEPT ;
퀴즈 6. 한 테이블의 값을 가로로 두 번 표시하라.
1) 배경
일반 프로그램이나 웹 브라우저에서 동일한 테이블의 데이터를 옆으로 반복하여 표시하고 할 때 사용할 수 있다.
다음과 같은 데이터가 있다고 본다.
SQL> SELECT EMPNO,ENAME FROM EMP ;
EMPNO ENAME
------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782
7788 SCOTT
7839 KING
7844 TURNER
7876
11 rows selected.
2) 요구 내용
위와 같은 결과가 다음과 같이 한 테이블의 값을 가로로 두 번 표시하라.
ORD EMPNO ENAME JOB EMPNO ENAME JOB
------- ------- ---------- --------- ------- ---------- ---------
1 7369 SMITH CLERK 7499 ALLEN SALESMAN
2 7521 WARD SALESMAN 7566 JONES MANAGER
3 7654 MARTIN SALESMAN 7698 BLAKE MANAGER
4 7782
5 7839 KING PRESIDENT 7844 TURNER SALESMAN
6 7876
6 rows selected.
3) 해답
STEP 1 : 우선 ROWNUM을 짝수, 홀수로 구분하여 가로로 표시한다.
SQL> SELECT ROWNUM,TRUNC((ROWNUM+1)/2) AS ORD,
2 DECODE(MOD(ROWNUM,2),1,EMPNO,NULL) AS FIRST_EMPNO,
3 DECODE(MOD(ROWNUM,2),0,EMPNO,NULL) AS SECOND_EMPNO
4 FROM EMP ;
ROWNUM ORD FIRST_EMPNO SECOND_EMPNO
------- ------- ----------- ------------
1 1 7369
2 1 7499
3 2 7521
4 2 7566
5 3 7654
6 3 7698
7 4 7782
8 4 7788
9 5 7839
10 5 7844
11 6 7876
11 rows selected.
STEP 2 : 빈 컬럼을 없애기 위해 SUM(DECODE...) 를 사용한다.
SQL> SELECT TRUNC((ROWNUM+1)/2) AS ORD,
2 SUM(DECODE(MOD(ROWNUM,2),1,EMPNO,NULL)) AS FIRST_EMPNO,
3 SUM(DECODE(MOD(ROWNUM,2),0,EMPNO,NULL)) AS SECOND_EMPNO
4 FROM EMP
5 GROUP BY TRUNC((ROWNUM+1)/2) ;
ORD FIRST_EMPNO SECOND_EMPNO
------- ----------- ------------
1 7369 7499
2 7521 7566
3 7654 7698
4 7782 7788
5 7839 7844
6 7876
6 rows selected.
STEP 3 : 해당 EMPNO 컬럼에 ENAME, JOB 데이터를 덧붙인다.
SQL> SELECT ORD,
2 F_EMPNO AS EMPNO,A.ENAME,A.JOB,
3 S_EMPNO AS EMPNO,B.ENAME,B.JOB
4 FROM ( SELECT ORD,
5 SUM(FIRST_EMPNO) AS F_EMPNO,
6 SUM(SECOND_EMPNO) AS S_EMPNO
7 FROM ( SELECT TRUNC((ROWNUM+1)/2) AS ORD,
8 DECODE(MOD(ROWNUM,2),1,EMPNO,NULL) AS FIRST_EMPNO,
9 DECODE(MOD(ROWNUM,2),0,EMPNO,NULL) AS SECOND_EMPNO
10 FROM EMP)
11 GROUP BY ORD ), EMP A, EMP B
12 WHERE F_EMPNO = A.EMPNO
13 AND S_EMPNO = B.EMPNO(+)
14 ORDER BY ORD ;
OUTER 조인을 사용하지 않으면 SECOND_EMPNO 컬럼의 값이 NULL인 경우 전체 로우의 값이 나오지 않게 되므로 반드시 S_EMPNO와 조인되는 B.EMPNO 컬럼에 (+) 사인을 추가해야 한다.
ORD EMPNO ENAME JOB EMPNO ENAME JOB
------- ------- ---------- --------- ------- ---------- ---------
1 7369 SMITH CLERK 7499 ALLEN SALESMAN
2 7521 WARD SALESMAN 7566 JONES MANAGER
3 7654 MARTIN SALESMAN 7698 BLAKE MANAGER
4 7782
5 7839 KING PRESIDENT 7844 TURNER SALESMAN
6 7876
6 rows selected.
퀴즈 7. 값이 바뀔 때 마다 일련번호를 다시 시작하라.
1) 배경
다음과 같은 데이터가 들어있는 테이블이 있다.
이해를 돕기 위해 DUMMY 컬럼을 넣어서 작성하였지만 실제로는 DUMMY 컬럼은 없다.
참고로 ROWNUM 컬럼도 같이 보기 바란다.
SQL> DESC TSTBL;
Name Null? Type
------------------------------- -------- ----
PK1 CHAR(3)
PK2 CHAR(5)
DUMMY NUMBER
SQL> SELECT PK1, PK2, DUMMY, ROWNUM FROM TSTBL
2 ORDER BY PK1, PK2 ;
PK1 PK2 DUMMY ROWNUM
--- ----- ------- -------
A01 AS011 1 1
A01 AS012 2 2
A01 AS013 3 3
A01 AS014 4 4
A01 AS015 5 5
A02 AS021 1 6
A02 AS022 2 7
A02 AS023 3 8
A03 AS021 1 9
B01 BS011 1 10
B01 BS012 2 11
B01 BS013 3 12
B01 BS014 4 13
B01 BS015 5 14
B01 BS016 6 15
B01 BS017 7 16
B01 BS018 8 17
B01 BS018 8 17
B02 BS021 1 18
B02 BS022 2 19
B02 BS023 3 20
B02 BS024 4 21
C01 CS011 1 22
C01 CS012 2 23
C02 CS021 1 24
C02 CS022 2 25
C02 CS023 3 26
C02 CS024 4 27
27 rows selected.
2) 요구 내용
위의 데이터 중 PK1, PK2 컬럼만 사용하여 PK1 컬럼의 값이 바뀔 때 마다 일련번호가 1 부터 새롭게 시작되도록 하라.
즉, 일련번호가 나오는 컬럼을 SEQ 컬럼이라 하면 PK1, PK2 컬럼만 사용하여 SEQ 컬럼이 값이 DUMMY 컬럼의 값처럼 나오게 하라.
3) 해답
우선 기존에 나오는 ROWNUM 컬럼의 값과 PK1으로 GROUP BY하여 나오는 COUNT(*) 값을 이용하여 구한다.
STEP 1 : 우선 GROUP BY 한 값을 COUNT 한 뒤 ROWNUM을 붙여서 이 결과를 SELF 조인 하여 바로 전 PK1 값 까지 의 COUNT 값을 구한다.
SQL> SELECT B.PK1, B.RNUM AS B_RNUM, C.RNUM AS C_RNUM, C.CNT
2 FROM (SELECT ROWNUM AS RNUM, PK1, CNT
3 FROM (SELECT PK1, COUNT(*) AS CNT FROM TSTBL
4 GROUP BY PK1)) B,
5 (SELECT ROWNUM AS RNUM, PK1, CNT
6 FROM (SELECT PK1, COUNT(*) AS CNT FROM TSTBL
7 GROUP BY PK1)) C
8 WHERE B.RNUM > C.RNUM ;
PK1 B_RNUM C_RNUM CNT
--- ------- ------- -------
A02 2 1 5 <-- A01 값의 COUNT(*) <-- A02
A03 3 1 5 <-- A01 값의 COUNT(*) <-- A03
A03 3 2 3 <-- A02 값의 COUNT(*)
B01 4 1 5 <-- A01 값의 COUNT(*) <-- B01
B01 4 2 3 <-- A02 값의 COUNT(*)
B01 4 3 1 <-- A03 값의 COUNT(*)
B02 5 1 5 <-- A01 값의 COUNT(*) <-- B02
B02 5 2 3 <-- A02 값의 COUNT(*)
B02 5 3 1 <-- A03 값의 COUNT(*)
B02 5 4 8 <-- B01 값의 COUNT(*)
C01 6 1 5 <-- A01 값의 COUNT(*) <-- C01
C01 6 2 3 <-- A02 값의 COUNT(*)
C01 6 3 1 <-- A03 값의 COUNT(*)
C01 6 4 8 <-- B01 값의 COUNT(*)
C01 6 5 4 <-- B02 값의 COUNT(*)
C02 7 1 5 <-- A01 값의 COUNT(*) <-- C02
C02 7 2 3 <-- A02 값의 COUNT(*)
C02 7 3 1 <-- A03 값의 COUNT(*)
C02 7 4 8 <-- B01 값의 COUNT(*)
C02 7 5 4 <-- B02 값의 COUNT(*)
C02 7 6 2 <-- C01 값의 COUNT(*)
STEP 2 : 바로 이전 PK1 값까지의 누계 COUNT 값을 알기 위해 PK1 값과 B.RNUM 값으로 GROUP BY 한다.
SQL> SELECT B.PK1, B.RNUM, SUM(C.CNT) AS ACC_SUM
2 FROM (SELECT ROWNUM AS RNUM, PK1, CNT
3 FROM (SELECT PK1,COUNT(*) AS CNT FROM TSTBL
4 GROUP BY PK1)) B,
5 (SELECT ROWNUM AS RNUM, PK1, CNT
6 FROM (SELECT PK1,COUNT(*) AS CNT FROM TSTBL
7 GROUP BY PK1)) C
8 WHERE B.RNUM > C.RNUM
9 GROUP BY B.PK1, B.RNUM ;
PK1 RNUM ACC_SUM
--- ------- -------
A02 2 5
A03 3 8
B01 4 9
B02 5 17
C01 6 21
C02 7 23
6 rows selected.
STEP 3 : 바로 이전 PK1 값까지의 누계 값과 원래의 ROWNUM을 이용하여 최종결과를 구한다. 이때 OUTER 조인을 사용해야만 "PK1 = A01" 값이 빠지지 않게 된다.
SQL> SELECT A.PK1, A.RNUM - NVL(ACC_SUM,0) AS SEQ, A.PK2, A.DUMMY
2 FROM (SELECT PK1,ROWNUM AS RNUM,PK2,DUMMY
3 FROM (SELECT PK1,PK2,DUMMY
4 FROM TSTBL
5 GROUP BY PK1,PK2,DUMMY)) A,
6 (SELECT B.PK1,B.RNUM,SUM(C.CNT) AS ACC_SUM
7 FROM (SELECT ROWNUM AS RNUM, PK1,CNT
8 FROM (SELECT PK1,COUNT(*) AS CNT FROM TSTBL
9 GROUP BY PK1)) B,
10 (SELECT ROWNUM AS RNUM, PK1,CNT
11 FROM (SELECT PK1,COUNT(*) AS CNT FROM TSTBL
12 GROUP BY PK1)) C
13 WHERE B.RNUM > C.RNUM
14 GROUP BY B.PK1,B.RNUM) D
15 WHERE A.PK1 = D.PK1(+)
16 ORDER BY A.PK1, A.RNUM - NVL(ACC_SUM,0) ;
실행 결과는 다음과 같으며 SEQ 컬럼은 PK1 값이 바뀌면 일련번호를 다시 시작하는 것을 볼 수 있다.
DUMMY 컬럼은 SEQ 값이 정확히 잘 나왔는가를 검토하는 목적으로 추가된 것이고 실제의 테이블에는 없다.
또한 SQL 컬럼도 IN-LINE VIEW 내의 PSEUDO 컬럼인 ROWNUM 컬럼에서 나왔으므로 실제 테이블에는 없다.
PK1 SEQ PK2 DUMMY
--- ------- ----- -------
A01 1 AS011 1
A01 2 AS012 2
A01 3 AS013 3
A01 4 AS014 4
A01 5 AS015 5
A02 1 AS021 1
A02 2 AS022 2
A02 3 AS023 3
A03 1 AS021 1
B01 1 BS011 1
B01 2 BS012 2
B01 3 BS013 3
B01 4 BS014 4
B01 5 BS015 5
B01 6 BS016 6
B01 7 BS017 7
B01 8 BS018 8
B02 1 BS021 1
B02 2 BS022 2
B02 3 BS023 3
B02 4 BS024 4
C01 1 CS011 1
C01 2 CS012 2
C02 1 CS021 1
C02 2 CS022 2
C02 3 CS023 3
C02 4 CS024 4
27 rows selected.
퀴즈 8. 불용 카드 수를 구하라.
1) 배경
카드 사에서 본인 카드와 그것의 가족 카드를 연관하여 일정 기간 사용되지 않은 카드 수를 계산한다.
다음과 같은 샘플 데이터로 단순화 시켜서 카드 수를 구한다.
|
카드정보 테이블 |
카드사용실적 테이블 | ||
|
|
본인카드번호 |
카드번호 |
사용금액 |
|
101 |
NULL |
101 |
10,000 |
|
102 |
101 |
101 |
5,500 |
|
103 |
101 |
105 |
35,000 |
|
104 |
NULL |
07 |
70,000 |
|
105 |
NULL |
|
|
|
106 |
NULL |
|
|
|
107 |
104 |
|
|
|
108 |
104 |
|
|
|
109 |
NULL |
|
|
|
110 |
NULL |
|
|
2) 요구 내용
위 샘플 데이터로부터 보면 사용 실적이 없는 카드 번호의 단순 리스트는 102, 103, 104, 106, 108, 109, 110등 모두 7개 카드지만 여기서 문제는 102, 103 카드는 이것의 본인 카드인 101이 사용 실적이 있으므로 실제적으로 답에서 빠져야 하며 104 카드도 역시 해당 가족 카드인 107이 사용 실적이 있으므로 104, 108도 역시 답에서 빠져야 하기 때문에 결과적으로 사용 실적이 없는 카드의 답은 106, 109, 110 모두 3개의 카드가 된다.
이를 구하는 쿼리를 작성하라.
3) 해답
SELECT 카드번호
FROM 카드정보 A
WHERE NOT EXISTS (SELECT X FROM 카드정보 B
WHERE EXISTS ( SELECT X FROM 카드사용실적 C
WHERE C.카드번호 = B.카드번호)
START WITH B.카드번호 = NVL(A.본인카드번호, A.카드번호)
CONNECT BY PRIOR B.카드번호 = B.본인카드번호) ;
위에서 START WITH B.카드번호 = NVL(A.본인카드번호, A.카드번호) 절은 연관 카드를 찾아서 수행되도록 하여 요구 사항의 누락이 없도록 본인 카드로 부터 시작하여 카드 사용 실적을 찾도록 하기 위함이며 START WITH ~ CONNECT BY PRIOR은 이책의 PART II 사례 23번, 26번을 참조하여 이해를 구하거나 오라클 "Sql Language Reference Manual" Chapter 4 Commands 의 SELECT 부분을 참조하기 바란다.
퀴즈 9. 판매일자, 판매량 별로 누적 판매량을 구하라.
1) 배경
다음과 같은 데이터가 들어있는 테이블이 있다.
SQL> DESC BFUN1;
Name Null? Type
------------------------------- -------- ----
판매일자 Not Null VARCHAR2(8)
판매량 NUMBER
SQL> SELECT * FROM BFUN1;
판매일자 판매량
-------- -------
19970225 10
19970214 40
19970218 23
19970220 65
19970208 43
19970203 22
19970211 18
19970218 15
19970213 60
19970218 19
10 개의 행이 선택되었습니다.
2) 요구 내용
위의 10개 데이터를 판매일자를 기준으로 소트 하였을 때 그에 해당하는 판매량 까지 구분하여 판매량을 누적한 누적 판매량을 같이 표시하도록 하라.
즉, 다음과 같이 데이터가 나오도록 하라.
판매일자 판매량 누적판매량
-------- ------- ----------
19970203 22 22
19970208 43 65 <--- 22 + 43
19970211 18 83 <--- 65 + 18
... .. ..
19970218 15 198 <--- 판매량까지 구분한 누적판매량
19970218 19 217
19970218 23 240
... .. ..
3) 해답
STEP 1 : 우선 판매일자, 판매량으로 GROUP BY 한 결과를 구한다.
GROUP BY에 의해 소트는 자동적으로 수행된다.
SELECT A.판매일자, A.판매량, SUM(B.판매량) AS 누적판매량
FROM BFUN1 A, BFUN1 B
WHERE A.판매일자 >= B.판매일자
GROUP BY A.판매일자, A.판매량 ;
판매일자 판매량 누적판매량
-------- ------- ----------
19970203 22 22
19970208 43 65
19970211 18 83
19970213 60 143
19970214 40 183
19970218 15 240 <---+
19970218 19 240 | 문제가 되는 부분
19970218 23 240 <---+
19970220 65 305
19970225 10 315
STEP 2 : 그러나 이렇게 하면 19960218 판매일자인 경우 누적 판매량이 최종 값인 240으로 다 같이 나오는 문제가 있다.
이 문제를 해결하기 위해 판매일자가 동일할 경우 판매량으로 구분하여 누적 합계를 구하는 부분이 추가되어야 한다.
SELECT A.판매일자, A.판매량, SUM(B.판매량) AS 누적판매량
FROM (SELECT 판매일자, 판매량 FROM BFUN1 GROUP BY 판매일자, 판매량) A,
(SELECT 판매일자, 판매량 FROM BFUN1 GROUP BY 판매일자, 판매량) B,
WHERE A.판매일자 > B.판매일자 <--- 판매일자까지만 구분하여 누적 분을 구함
OR (A.판매일자 = B.판매일자 AND A.판매량 >= B.판매량) <--- 판매량까지 구분함
GROUP BY A.판매일자, A.판매량;
"WHERE A.판매일자 > B.판매일자" 조건은 판매일자 까지만 구분하여 누적 분을 구하므로 판매일자가 다른 경우는 이 조건만으로도 해결된다.
그러나, 동일한 판매일자일 경우 판매량 까지 구분하여 누적 합계를 구하고자 한다면
"OR (A.판매일자 = B.판매일자 AND A.판매량 >= B.판매량)" 조건이 추가되어야 한다.
위 SQL을 수행시키면 다음과 같은 결과를 얻는다.
판매일자 판매량 누적판매량
-------- ------- ----------
19970203 22 22
19970208 43 65
19970211 18 83
19970213 60 143
19970214 40 183
19970218 15 198 <--+
19970218 19 217 | 판매일자, 판매량 별로 누적 판매량이 계산됨
19970218 23 240 <--+
19970220 65 305
19970225 10 315
참고로 판매일자 까지만 구분하여 누적 합계를 구하는 SQL은 다음과 같다.
SELECT A.판매일자, SUM(B.판매량) AS 누적판매량
FROM (SELECT 판매일자 FROM BFUN1 GROUP BY 판매일자) A,
(SELECT 판매일자, SUM(판매량) AS 판매량 FROM BFUN1 GROUP BY 판매일자) B,
WHERE A.판매일자 >= B.판매일자
GROUP BY A.판매일자 ;
IN-LINE VIEW 내에서는 ORDER BY 문을 사용할 수 없으므로 GROUP BY를 사용하여 소트를 대신한 점을 눈 여겨 볼만 하다.
퀴즈 10. 4/4 분기 동안 점포별 매출액을 구하라.
1) 배경
다음과 같은 관계의 테이블들이 있다.
l 인덱스 정보 (테이블명 : 인덱스명 : 컬럼구성)
점포 : PK_BRANCH : BR_NO
고객 : PK_CUSTOMER : CUST_NO
물품 : PK_PRODUCT : PROD_NO
매출 : PK_SALE : SALEDATE + CUST_NO + PROD_NO
매출 : SALE_CUST_NO_SALEDATE : CUST_NO + SALEDATE
매출 : SALE_PROD_NO_SALEDATE : PROD_NO + SALEDATE
각 테이블에 대한 상세한 속성은 다음과 같다.
|
테이블명 |
한글 컬럼명 |
영문 컬럼명 |
속성 |
인덱스 |
비고 |
|
점포 |
점포번호 |
BR_NO |
VARCHAR(6) |
PK |
100 건 |
|
BRANCH |
점포명 |
BR_NAME |
VARCHAR2(10) |
|
|
|
|
점포주소 |
BR_ADDR |
VARCHAR2(30) |
|
|
|
고객 |
고객번호 |
CUST_NO |
VARCHAR2(6) |
PK |
1000 건 |
|
CUATOMER |
고객명 |
CUST_NAME |
VARCHAR2(10) |
|
|
|
|
가산점 |
BONUS_POINT |
NUMBER |
|
|
|
|
신용한도 |
CREDIT_LIMIT |
NUMBER |
|
|
|
|
점포번호 |
BR_NO |
VARCHAR2(6) |
|
|
|
매출 |
매출일자 |
SALEDATE |
VARCHAR2(8) |
PK |
180,000 건 |
|
|
고객번호 |
CUST_NO |
VARCHAR2(6) |
PK |
|
|
|
물품번호 |
PROD_NO |
VARCHAR2(6) |
PK |
|
|
|
판매량 |
SALE_AMT |
NUMBER |
|
|
|
|
매출구분 |
SALE_CLASS |
VARCHAR2(1) |
|
|
|
|
단가 |
UNIT_PRICE |
NUMBER |
|
|
|
물품 |
물품번호 |
PROD_NO |
VARCHAR2(6) |
PK |
500 건 |
|
PRODUCT |
물품명 |
PROD_NAME |
VARCHAR2(4) |
|
|
|
|
품종 |
PROD_CLASS |
VARCHAR2(10) |
|
|
|
|
단가 |
UNIT_PRICE |
NUMBER |
|
|
물품의 데이터 구성 내용은 식품류 80 %, 가구류 10 %, 기타 10 % 비율로 구성되어 있다.
2) 요구 내용
1995년도 4/4 분기 동안 점포별 매출액을 구하는 가장 효과적인 SQL 문을 작성하시오.
3) 해답
일반적인 SQL 문은 다음과 같다.
SELECT A.BR_NAME, SUM(C.SALE_AMT*C.UNIT_PRICE) AS SUM_SALE_AMT
FROM BRANCH A, CUSTOMER B,
WHERE A.BR_NO=B.BR_NO
AND B.CUST_NO=C.CUST_NO
AND C.SALEDATE BETWEEN '19951001' AND '19951231'
GROUP BY A.BR_NAME ; ELAPSED :
위 SQL 문을 좀더 개선 시켜서 튜닝하면 다음과 같다.
SELECT B.BR_NAME, SUM(V.SUM_SALE_AMT)
FROM ( SELECT CUST_NO,SUM(SALE_AMT*UNIT_PRICE) AS SUM_SALE_AMT
FROM
WHERE SALEDATE BETWEEN '19951001' AND '19951231'
GROUP BY CUST_NO ) V,
CUSTOMER A, BRANCH B
WHERE B.BR_NO=A.BR_NO
AND A.CUST_NO=V.CUST_NO
GROUP BY B.BR_NAME ; ELAPSED :
퀴즈 11. 특정 품종에 대한 총 판매수량과 금액을 구하라.
1) 배경
배경은 이전과 동일하다.
2) 요구 내용
1995 년도 1/4 분기 동안에 품종이 가구류와 가구류/식품류인 제품에 대한 총 판매 수량과 판매금액을 구하시오.
3) 해답
SELECT SUM(SALE_AMT) AS TOTAL_SALE_QTY,
SUM(SALE_AMT * A.UNIT_PRICE) AS TOTAL_SALE_AMT
FROM PRODUCT A,
WHERE A.PROD_NO = B.PROD_NO
AND A.PROD_CLASS IN ('FURNITURE', 'FOODS')
AND B.SALEDATE BETWEEN '19950101' AND '19950331' ; ELAPSED :
위 SQL 문을 좀더 개선 시켜서 튜닝하면 다음과 같다.
SELECT SUM(V.TOTAL_QTY) AS TOTAL_SALE_QTY,
SUM((V.TOTAL_QTY*A.UNIT_PRICE)) AS TOTAL_SALE_AMT
FROM (SELECT PROD_NO, SUM(SALE_AMT) AS TOTAL_QTY
FROM
GROUP BY PROD_NO) V, PRODUCT A
WHERE A.PROD_NO = V.PROD_NO
AND A.PROD_CLASS IN ('FURNITURE' , 'FOODS') ; ELAPSED :
퀴즈 12. 한번이라도 매출을 일으킨 고객을 구하라.
1) 배경
배경은 이전과 동일하다.
2) 요구 내용
1995년도에 한번이라도 매출을 일으킨 고객명 및 그 고객이 속한 점포명을 구하시오.
3) 해답
SELECT DISTINCT A.CUST_NAME, B.BR_NAME
FROM CUSTOMER A, BRANCH B,
WHERE A.BR_NO = B.BR_NO AND A.CUST_NO = C.CUST_NO
AND C.SALEDATE LIKE '1995%' ; ELAPSED :
위 SQL 문을 좀더 개선시켜서 튜닝하면 다음과 같다.
SELECT A.CUST_NAME, B.BR_NAME
FROM CUSTOMER A, BRANCH B
WHERE A.BR_NO = B.BR_NO
AND A.CUST_NO IN (SELECT CUST_NO FROM
WHERE SALEDATE LIKE '1995%') ; ELAPSED :
위 SQL 문을 좀더 개선 시켜서 튜닝하면 다음과 같다.
SELECT A.CUST_NAME, BR_NAME
FROM CUSTOMER A, BRANCH B
WHERE EXISTS (SELECT 'X' FROM
WHERE A.CUST_NO = C.CUST_NO AND SALEDATE LIKE '1995%')
AND A.BR_NO = B.BR_NO ; ELAPSED :
퀴즈 13. 매출액 20,000 이하 또는 없는 고객을 삭제하라.
1) 배경
배경은 이전과 동일하다.
2) 요구 내용
1995년도 매출액이 20,000 이하 이거나 한번도 매출이 없는 고객을 찾아서 고객 정보 테이블에서 삭제하라.
3) 해답
-- DELETE CUSTOMER A
SELECT COUNT(CUST_NO) FROM CUSTOMER A <--- 삭제 시에는 이 줄을 지우고 위 줄을 살린다.
WHERE A.CUST_NO IN (SELECT CUST_NO FROM
WHERE SALEDATE LIKE '1995%'
GROUP BY CUST_NO HAVING SUM(SALE_AMT*UNIT_PRICE) < 200000)
OR NOT EXISTS (SELECT 'X' FROM
AND C.SALEDATE LIKE '1995%') ; ELAPSED :
위 SQL 문을 좀더 개선 시켜서 튜닝하면 다음과 같다.
-- DELETE CUSTOMER A
SELECT COUNT(CUST_NO) FROM CUSTOMER A <--- 삭제 시에는 이 줄을 지우고 위 줄을 살린다.
WHERE A.CUST_NO IN (SELECT CUST_NO FROM
WHERE SALEDATE LIKE '1995%'
AND A.CUST_NO = C.CUST_NO <--- 인덱스 사용을 위해 반드시 추가
GROUP BY CUST_NO HAVING SUM(SALE_AMT*UNIT_PRICE) < 200000)
OR NOT EXISTS (SELECT 'X' FROM
AND C.SALEDATE LIKE '199501%') ; ELAPSED :
퀴즈 14. 가산점과 신용 한도를 증가시켜라.
1) 배경
배경은 이전과 동일하다.
2) 요구 내용
1995년도 하반기 총 매출액이 300,000 이상인 고객에 대하여 가산점을 매출액 100,000 원당 1 점씩을 증가시키고 신용 한도를 100,000 증가시키는 SQL 문을 작성하라.
3) 해답
UPDATE CUSTOMER C
SET CREDIT_LIMIT = CREDIT_LIMIT + 100000,
BONUS_POINT = (SELECT BONUS_POINT+TRUNC(SUM(SALE_AMT*B.UNIT_PRICE)/100000)
FROM
WHERE SALEDATE BETWEEN '19950701' AND '19951231'
AND A.PROD_NO = B.PROD_NO
AND A.CUST_NO = C.CUST_NO)
WHERE C.CUST_NO IN (SELECT CUST_NO
FROM
WHERE SALEDATE BETWEEN '19950701' AND '19951231'
AND A.PROD_NO = B.PROD_NO AND A.CUST_NO = C.CUST_NO
GROUP BY A.CUST_NO HAVING SUM(A.SALE_AMT*B.UNIT_PRICE) > 300000) ;
ELAPSED :
위 SQL 문과 동일한 기능을 갖도록 PL/SQL로 작성하면 다음과 같다.
PROMPT 'Create Procedure PEX4_3'
CREATE OR REPLACE PROCEDURE PEX4_3
AS
BEGIN
DECLARE
CURSOR EX4_3 IS
SELECT CUST_NO FROM CUSTOMER;
CUSTNO CHAR(6);
AMOUNT NUMBER;
BEGIN
OPEN EX4_3;
FETCH EX4_3 INTO CUSTNO;
EXIT WHEN EX4_3%NOTFOUND;
SELECT SUM(SALE_AMT*B.UNIT_PRICE)
INTO AMOUNT
FROM
WHERE SALEDATE BETWEEN '19950701' AND '19951231'
AND A.PROD_NO = B.PROD_NO
AND A.CUST_NO = CUSTNO;
IF AMOUNT > 300000 THEN
UPDATE CUSTOMER
SET CREDIT_LIMIT = CREDIT_LIMIT + 100000,
BONUS_POINT = BONUS_POINT+TRUNC(AMOUNT/100000)
WHERE CUST_NO = CUSTNO;
END IF;
END
CLOSE EX4_3;
END;
END;
/ ELAPSED :
퀴즈 15. 매출 순위 10위까지의 고객 리스트를 작성하라.
1) 배경
배경은 이전과 동일하다.
2) 요구 내용
1995 년도 4/4 분기 매출 순위 10위 까지 의 고객 리스트를 작성하여 순위와 매출액을 함께 나타내는 SQL 문을 작성하라.
단, 이때에 판매구분이 'R' 인 고객은 우수 고객이므로 판매금액을 10 % 더 증가시켜서 계산하라.
3) 해답
STEP 1 : 우선 판매금액별로 순위를 정하는 SQL 문을 다음과 같이 작성한다.
SELECT CUST_NO,
SUM(DECODE(SALE_CLASS,'R',SALE_AMT*UNIT_PRICE*1.1,
SALE_AMT*UNIT_PRICE)) AS SUM_SALE_AMT
FROM
WHERE SALEDATE BETWEEN '19951001' AND '19951231'
GROUP BY CUST_NO
ORDER BY SUM_SALE_AMT DESC ;
STEP 2 : IN-LINE VIE에서는ORDER BY 문을 사용할 수 없기 때문에 GROUP BY를 사용하여 소트 기능을 대신한다.
최종 SQL 문은 다음과 같다.
SELECT ROWNUM AS ORDER_SALE,
CUST_NO,-SUM_SALE_AMT AS SUM_SALE_AMT
FROM (SELECT CUST_NO, -SUM_SALE_AMT AS SUM_SALE_AMT
FROM (SELECT CUST_NO,
SUM(DECODE(SALE_CLASS,'R',SALE_AMT*UNIT_PRICE*1.1,
SALE_AMT*UNIT_PRICE)) AS SUM_SALE_AMT
FROM
WHERE SALEDATE BETWEEN '19951001' AND '19951231'
GROUP BY CUST_NO)
GROUP BY -SUM_SALE_AMT, CUST_NO)
WHERE ROWNUM <= 10 ;
퀴즈 16. 일별 총계 및 제품별 총계를 같이 구하라.
1) 배경
배경은 이전과 동일하다.
2) 요구 내용
1995년도 3/4 분기 제품별 매출액을 일별 총계 및 제품별 총계를 하나의 SQL 문으로 같이 구하라.
3) 해답
SELECT V.SALEDATE, V.PROD_NO,
DECODE(V.PROD_NO,'P00000','일별 총계',A.PROD_NAME) AS PROD_NAME,
V.TOTAL_SALE_AMT
FROM PRODUCT A,
(SELECT SALEDATE,'P00000' AS PROD_NO, <--- 일별 총계
SUM(SALE_AMT*UNIT_PRICE) AS TOTAL_SALE_AMT
FROM
WHERE SALEDATE BETWEEN '19950701' AND '19950930'
GROUP BY SALEDATE
UNION ALL
SELECT SALEDATE,PROD_NO, <--- 일별, 제품별 총계
SUM(SALE_AMT*UNIT_PRICE) AS TOTAL_SALE_AMT
FROM
WHERE SALEDATE BETWEEN '19950701' AND '19950930'
GROUP BY SALEDATE, PROD_NO) V
WHERE V.PROD_NO = A.PROD_NO(+) <--- 'P00000' 때문에 반드시 OUTER 조인을 사용해야 함
ORDER BY V.SALEDATE, V.PROD_NO ; ELAPSED :
위 SQL 문을 좀더 개선 시켜서 튜닝하면 다음과 같다.
REPLICA의 값이 1 이면 일자별 총계 이고 2 이면 일자별, 제품별 총계이다.
SELECT V2.SALEDATE, V2.PROD_NO,
DECODE(V2.PROD_NO,'P00000','일별 총계',A.PROD_NAME) AS PROD_NAME,
V2.TOTAL_SALE_AMT
FROM PRODUCT A,
(SELECT V1.SALEDATE,
DECODE(REPLICA,'1','P00000',V1.PROD_NO) AS PROD_NO,
SUM(V1.TOTAL_SALE_AMT) AS TOTAL_SALE_AMT
FROM (SELECT SALEDATE, PROD_NO, SUM(SALE_AMT*UNIT_PRICE) AS TOTAL_SALE_AMT
FROM
WHERE SALEDATE BETWEEN '19950701' AND '19950930'
GROUP BY SALEDATE,PROD_NO ) V1, REPLICA1
GROUP BY V1.SALEDATE, DECODE(REPLICA,'1','P00000',V1.PROD_NO) ) V2
WHERE V2.PROD_NO = A.PROD_NO(+) ; ELAPSED: 00: 00 : 21.30
l 참고로 위 SQL의 IN-LINE VIEW 안에 있는 REPLICA1테이블은 복제를 위해서 사용되는 테이블로 다음과 2로우를 갖고 있다.
CREATE TABLE REPLICA1 ( REPLICA CHAR(1) );
INSERT INTO REPLICA1 VALUES('1');
INSERT INTO REPLICA1 VALUES('2');
COMMIT;
퀴즈 17. 입력 구분에 따라 서로 다른 총매출액을 구하라.
1) 배경
배경은 이전과 동일하다.
2) 요구 내용
1995년도 제품 번호가 P20081에 해당하는 매출액을 구하는데 입력 구분의 값이 1 이면 P20081에 대해서만 구하고 2 이면 P20081의 품종에 해당하는 모든 제품에 대해서 매출액을 구하라.
3) 해답
WHERE 조건에 DECODE 함수를 잘 활용하여 다음과 같이 작성할 수 있다.
SELECT SUM(SALE_AMT*B.UNIT_PRICE)
FROM
WHERE SALEDATE LIKE '1995%'
AND A.PROD_NO = B.PROD_NO
AND DECODE ('&입력구분', '1', B.PROD_NO, '2', 'P20081') = 'P20081'
AND B.PROD_CLASS IN (SELECT PROD_CLASS FROM PRODUCT WHERE PROD_NO = 'P20081') ;
퀴즈 18. 년간 매출을 주 단위로 매출 구분하여 구하라
1) 배경
배경은 이전과 동일하다.
2) 요구 내용
1995년도 매출액을 주간 단위로 구하는데 보는 컬럼은 판매월, 주일련번호, 현금매출액, 신용매출액, 주별합계 이고 주를 계산하는데 2 가지 형태로 구한다.
CASE 1은
CASE 2는 1995년 첫째 주는 1월1일(수)부터 1월 5일(일)이고 그 다음부터는 월요일부터 일요일을 주 단위로 계산한다.
3) 해답
DATE 타입의 'IW', 'WW'를 이용하여 구한다.
l CASE 1
SELECT SUBSTR(매출일자,1,6) AS 판매월,
TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'WW') AS 주일련번호,
SUM(DECODE(매출구분,'현금',판매량*단가)) AS 현금매출액,
SUM(DECODE(매출구분,'신용',판매가*단가)) AS 신용매출액,
SUM(판매량*단가) AS 주별합계
FROM 매출
WHERE 매출일자 LIKE '1995%'
GROUP BY SUBSTR(매출일자,1,6), TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'WW')
l CASE 2
SELECT SUBSTR(매출일자,1,6) as 판매월,
TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'IW') as 주일련번호,
SUM(DECODE(매출구분,'현금',판매량*단가)) as 현금매출액,
SUM(DECODE(매출구분,'신용',판매가*단가)) as 신용매출액,
SUM(판매량*단가) as 주별합계
FROM 매출
WHERE 매출일자 LIKE '1995%'
GROUP BY SUBSTR(매출일자,1,6), TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'WW')
CASE 1의TO_CHAR(TO_DATE(매출일자,'YYYYMMDD') , 'WW') 와
CASE 2의TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'), 'IW') 를 주의해서 보기를 바란다. 그런데 CASE 2는 다음의 주의를 요한다.
1년은 53주 인데 만약, 1995의 12월 31일이 일요일이 아닐 경우, 예를 들어 금요일 이었다면 마지막 일요일 (12월 26일)까지는 52번째 주가 되고 12월 27일(월) 부터 그 다음 해인
따라서, 다음과 같이 DECODE를 이용하여 매출년월(SUBSTR(매출일자,1,6))이 '199512'이고,'IW'가 '01'이면 이는 '53'으로 값을 주어 정확한 결과가 나오도록 쿼리를 수정해야 한다. 참고로 위 경우는 GROUP BY 절에 매출월이 들어가 있어서 금액은 틀리지 않으나 만약에 GROUP BY를 주일련번호만으로 하였다면 12월27일부터 12월 31일까지의 합계가 첫째 주 ('01')와 같이 합쳐져서 틀린 결과가 나오게 된다.
이를 반영하여 정확한 값이 나오도록 하기 위해서는 다음과 같이 SQL이 수정되어야 한다.
l 수정 된 CASE 2
SELECT SUBSTR(매출일자,1,6) AS 판매월,
DECODE(SUBSTR(매출일자,1,6),'199512',
DECODE(TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'IW'),'01','53',
TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'IW')),
TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'IW')) AS 주일련번호,
SUM(DECODE(매출구분,'현금',판매량*단가)) AS 현금매출액,
SUM(DECODE(매출구분,'신용',판매가*단가)) AS 신용매출액,
SUM(판매량*단가) AS 주별합계
FROM 매출
WHERE 매출일자 LIKE '1995%'
GROUP BY SUBSTR(매출일자,1,6),
DECODE(SUBSTR(매출일자,1,6),'199512',
DECODE(TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'IW'),'01','53',
TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'IW')),
TO_CHAR(TO_DATE(매출일자,'YYYYMMDD'),'IW')) ;
찾아보기
PART III
퀴즈 1. 중복된 로우만 골라서 삭제하라..........................................................................................................
위의 데이터 중 EMPNO 컬럼의 값이 중복된 로우에 대하여 ROWID 값이 큰 로우만 골라서 삭제하라.
퀴즈 2. 정확히 10위까지만 구하라......................................................................................................................
위의 데이터를 이용하여 순위를 나타내는 컬럼과 함께 10위 까지 나오는 SQL문과 (위의 샘플 데이터 경우 WARD, 11 번째 로우) 정확히 10번째 로우까지만 나오는 (위의 샘플 데이터 경우 MARTIN, 10 번째 로우) SQL문을 작성하라.
퀴즈 3. 공수별 비용 집계를 구하라..................................................................................................................
왼편의 표와 같이 모든 공수의 종류를 구하고 그 공수별로 들어간 비용의 합계를 구하라.
퀴즈 4. 제품별 집계와 등급별 집계를 같이 구하라...............................................................................
위와 같이 판매일자와 함께 각각의 제품에 대한 판매 내용이 있을 때 전체 판매에 대한 수량과 매출액 등의 통계자료를 보고싶은 것이다.
단, 통계자료를 볼 때 제품명이 HP인 제품은 저가여서 제품별로 만 봐도 되니까 모델 별은 무시하고 제품별로 만 수량과 매출액 합계를 내고, 제품명이 LD인 제품은 고가이므로 각 모델별로 수량과 매출액 합계를 내고, 제품명이 PP인 제품은 저가 단품들 이므로 모든 모델을 하나로 몰아서 기타로 하여 수량과 매출액에 대한 합계를 구하라.
퀴즈 5. 당일 분, 일주일 분, 한달 분을 같이 구하라.............................................................................
위 테이블을 이용하여 당일분 합계, 최근 일주일분 합계, 가장 최근의 최고 판매량분, 월간 합계를 같이 구하라.
퀴즈 6. 한 테이블의 값을 가로로 두 번 표시하라..................................................................................
위와 같은 결과가 다음과 같이 한 테이블의 값을 가로로 두 번 표시하라.
퀴즈 7. 값이 바뀔 때 마다 일련번호를 다시 시작하라........................................................................
위의 데이터 중 PK1, PK2 컬럼만 사용하여 PK1 컬럼의 값이 바뀔 때 마다 일련번호가 1 부터 새롭게 시작되도록 하라.
퀴즈 8. 불용 카드 수를 구하라............................................................................................................................
카드 사에서 본인 카드와 그것의 가족 카드를 연관하여 일정 기간 사용되지 않은 카드 수를 계산한다.
퀴즈 9. 판매일자, 판매량 별로 누적 판매량을 구하라.........................................................................
위의 10개 데이터를 판매일자를 기준으로 소트 하였을 때 그에 해당하는 판매량 까지 구분하여 판매량을 누적한 누적 판매량을 같이 표시하도록 하라.
퀴즈 10. 4/4 분기 동안 점포별 매출액을 구하라...................................................................................
1995년도 4/4 분기 동안 점포별 매출액을 구하는 가장 효과적인 SQL 문을 작성하시오.
퀴즈 11. 특정 품종에 대한 총 판매수량과 금액을 구하라................................................................
1995 년도 1/4 분기 동안에 품종이 가구류와 가구류/식품류인 제품에 대한 총 판매 수량과 판매금액을 구하시오.
퀴즈 12. 한번이라도 매출을 일으킨 고객을 구하라...............................................................................
1995년도에 한번이라도 매출을 일으킨 고객명 및 그 고객이 속한 점포명을 구하시오.
퀴즈 13. 매출액 20,000 이하 또는 없는 고객을 삭제하라...................................................................
1995년도 매출액이 20,000 이하 이거나 한번도 매출이 없는 고객을 찾아서 고객 정보 테이블에서 삭제하라.
퀴즈 14. 가산점과 신용 한도를 증가시켜라................................................................................................
1995년도 하반기 총 매출액이 300,000 이상인 고객에 대하여 가산점을 매출액 100,000 원당 1 점씩을 증가시키고 신용 한도를 100,000 증가시키는 SQL 문을 작성하라.
퀴즈 15. 매출 순위 10위까지의 고객 리스트를 작성하라..................................................................
1995 년도 4/4 분기 매출 순위 10위 까지 의 고객 리스트를 작성하여 순위와 매출액을 함께 나타내는 SQL 문을 작성하라.
단, 이때에 판매구분이 'R' 인 고객은 우수 고객이므로 판매금액을 10 % 더 증가시켜서 계산하라.
퀴즈 16. 일별 총계 및 제품별 총계를 같이 구하라...............................................................................
1995년도 3/4 분기 제품별 매출액을 일별 총계 및 제품별 총계를 하나의 SQL 문으로 같이 구하라.
퀴즈 17. 입력 구분에 따라 서로 다른 총매출액을 구하라................................................................
1995년도 제품 번호가 P20081에 해당하는 매출액을 구하는데 입력 구분의 값이 1 이면 P20081에 대해서만 구하고 2 이면 P20081의 품종에 해당하는 모든 제품에 대해서 매출액을 구하라.
퀴즈 18. 년간 매출을 주 단위로 매출 구분하여 구하라.....................................................................
1995년도 매출액을 주간 단위로 구하는데 보는 컬럼은 판매월, 주일련번호, 현금매출액, 신용매출액, 주별합계 이고 주를 계산하는데 2 가지 형태로 구한다.
