CREATE TABLE TEMP01 (성명 varchar2(02) PRIMARY KEY,
직급 varchar2(05),점수 number (03));
INSERT INTO TEMP01 VALUES ('A','5급',96);
INSERT INTO TEMP01 VALUES ('B','6급',98);
INSERT INTO TEMP01 VALUES ('C','6급',96);
INSERT INTO TEMP01 VALUES ('D','6급',95);
CREATE TABLE TEMP02 (성명 varchar2(02) PRIMARY KEY,
직급 varchar2(05),점수 number (03));
INSERT INTO TEMP02 VALUES ('E','5급',82);
INSERT INTO TEMP02 VALUES ('F','6급',82);
CREATE TABLE TEMP03 (성명 varchar2(02) PRIMARY KEY,
직급 varchar2(05),점수 number (03));
INSERT INTO TEMP03 VALUES ('G','5급',78);
INSERT INTO TEMP03 VALUES ('H','5급',78);
REPORT 양식 또는 TABLE 형식에 맞추기위한 QUERY.
조건. QUERY문을 사용하면서 새삼스럽게 느끼는 거지만
SQL을 구사하는데는 정답이 없다.
사용환경과 요구조건 사이에서 가장 적절한 길을 찾아가는 노력이
여러가지 답 중 하나로 나타날 뿐이라고 생각한다.
그러기 위해서는 여러가지 접근방식을 미리 MASTER 해 두는것이
큰 도움이 될 것이다.
이번문제도 다른 방법이 충분히 있을 것이다.
하지만 REPORT 또는 특정형태의 TABLE 양식에 맞는 QUERY를
한문장으로 구현해야 할경우에는
REPORT는 DATA가 위치할 ROW와 COLUMN의 위치를 결정지어주고
TABLE의 경우에는 몇번째 RECORD 몇번째 COLUMN에 INSERT 시켜
줄 것인가에서부터 생각하면 의외로 해결이 쉬운 경우가 많다.
이경우 INSER INTO ... SELECT... 문장으로 바로 해결할 수 있다는
장점이 있다.
말이 너무 많은 듯...
Q&A에 올라온 그대로를 달아 놓는다.
테이블의 구조는 다음과 같구요
TEMP01에는 90점이상만, TEMP02는 80점 이상,
TEMP03은 70점 이상이구요 FIELD는 모두
성명, 직급, 점수로 동일합니다
master table이 존재하구요
TEMP01
--------------
A | 5급| 96
B | 6급| 98
C | 6급| 96
D | 6급| 95
TEMP02
---------------
E | 5급| 82
F | 6급| 82
TEMP 03
---------------
G | 5급| 78
H | 5급| 78
MASTER
----------
A | 5급
B | 6급
C | 6급
D | 6급
E | 5급
F | 6급
G | 5급
H | 5급
문제 .
이에 따른 출력물은 다음과 같습니다
직급 | 90점 이상 | 80점 이상 | 70점이상 |
---------------------------------------------------------
6급 | B | F | |
---------------------------------------------------------
| C | | |
---------------------------------------------------------
| D | | |
---------------------------------------------------------
5급 | A | E | G |
---------------------------------------------------------
| | | H |
---------------------------------------------------------
생각 .
이 문제를 접하면서 느꼈던 것은
정보를 요구하는 사람의 욕구는 참으로 다양하다는 것이다.
그렇다고 그 요구를 무조건 우리가 구현하기 쉬운 방향으로만
유도해 가기도 또한 쉽지 않은 일이다.
애초에 설계단계에서부터 반영해 놓지 않은 경우는 더욱 그렇다.
일단 요구하면 해줘야 내마음도 시원해지고..
엿보기1.이미 TABLE이 점수대별로 DATA를 나눠서 관리하고 있다.
가장먼저 생각할 일은 각 점수대별로 직급에 DESCENDING 하고
성명에 ASCENDING하게 배열이 되어야 한다는 것이고
bsp; G_CNT T_CNT
---------- ---------- ----------
5급 1 1
6급 1 2
6급 2 3
6급 3 4
여기서 T_CNT는 TEMP01과 JOIN을 걸때 KEY로 사용된다.
단계2.여기에 더해서 여기에 해당하는 사람이 누구인가를 찾아서
MATCH 시켜주고 COLUMN 순서 1을 부여하면된다.
SELECT S01.직급 LEV,
S02.G_CNT R_CNT,
S01.CCNT C_CNT,
S01.성명 NAME
FROM
(
SELECT ROWNUM RCNT,직급,1 CCNT,성명
FROM TEMP01
) S01,
(
SELECT A.직급 직급,B.CNT G_CNT,ROWNUM T_CNT
FROM (SELECT 직급,COUNT(*) CNT
FROM TEMP01
GROUP BY 직급) A,
(SELECT ROWNUM CNT
FROM TEMP01) B
WHERE B.CNT <= A.CNT
) S02
WHERE S02.직급 = S01.직급
AND S02.T_CNT = S01.RCNT
결과는 다음과 같다.
LEV R_CNT C_CNT NAME
---------- ---------- ---------- ----------
5급 1 1 A
6급 1 1 B
6급 2 1 C
6급 3 1 D
단계3.이제 각 점수대별로 동일한 행위를 거쳐 생성되는 DATA를
UNION으로 연결해 주자. 결과는 다음과 같을 것이다.
LEV R_CNT C_CNT NAME
---------- ---------- ---------- ----------
5급 1 1 A
6급 1 1 B
6급 2 1 C
6급 3 1 D
5급 1 2 E
6급 1 2 F
5급 1 3 G
5급 2 3 H
단계4.여기까지 된것을 LEV + R_CNT별로 GROUP BY해서
그 MAX 값만을 취하면 다음과 같이 된다.
KEY1 COL1 COL2 COL3
----------------------- ---------- ---------- ----------
5급1&nb?DATA를
UNION으로 연결해 주자. 결과는 다음과 같을 것이다.
LEV R_CNT C_CNT NAME
---------- ---------- ---------- ----------
5급 1 1 A
6급 1 1 B
6급 2 1 C
6급 3 1 D
5급 1 2 E
6급 1 2 F
5급 1 3 G
5급 2 3 H
단계4.여기까지 된것을 LEV + R_CNT별로 GROUP BY해서
그 MAX 값만을 취하면 다음과 같이 된다.
KEY1 COL1 COL2 COL3
----------------------- ---------- ---------- ----------
5급1 A E G
5급2 H
6급1 B F
6급2 C
6급3 D
단계5.마지막으로 직급에 붙어 있는 R_CNT 를 떼어내고
직급에 DESCENDING하게 ORDER BY 해 주면 답이 나온다.
이렇게...
직급 90점이상 80점이상 70점이상
---- ---------- ---------- ----------
6급 B F
6급 C
6급 D
5급 A E G
5급 H
완성된 문장은 다음과 같다.
SELECT SUBSTRB(KEY1,1,3) 직급,
COL1 "90점이상",
COL2 "80점이상",
COL3 "70점이상"
FROM
(
SELECT LEV||TO_CHAR(R_CNT) KEY1,
MAX(DECODE(C_CNT,1,NAME)) COL1,
MAX(DECODE(C_CNT,2,NAME)) COL2,
MAX(DECODE(C_CNT,3,NAME)) COL3
FROM
(
SELECT S01.직급 LEV,
S02.G_CNT R_CNT,
S01.CCNT C_CNT,
S01.성명 NAME
FROM
(
SELECT ROWNUM RCNT,직급,1 CCNT,성명
FROM TEMP01
) S01,
(
SELECT A.직급 직급,B.CNT G_CNT,
ROWNUM T_CNT
FROM (SELECT 직급,COUNT(*) CNT
FROM TEMP01
GROUP BY 직급) A,
(SELECT ROWNUM CNT
FROM TEMP01) B
WHERE B.CNT <= A.CNT
) S02
WHERE S02.직급 = S01.직급
AND S02.T_CNT = S01.RCNT
UNION ALL
SELECT S01.직급,S02.G_CNT,S01.CCNT,S01.성명
FROM
(
SELECT ROWNUM RCNT,직급,2 CCNT,성명
FROM TEMP02
) S01,
(
SELECT A.직급 직급,B.CNT G_CNT,
ROWNUM T_CNT
FROM (SELECT 직급,COUNT(*) CNT
FROM TEMP02
GROUP BY 직급) A,
(SELECT ROWNUM CNT
FROM TEMP02) B
WHERE B.CNT <= A.CNT
) S02
WHERE S02.직급 = S01.직급
AND S02.T_CNT = S01.RCNT
UNION ALL
SELECT S01.직급,S02.G_CNT,S01.CCNT,S01.성명
FROM
(
SELECT ROWNUM RCNT,직급,3 CCNT,성명
FROM TEMP03
) S01,
(
SELECT A.직급 직급,B.CNT G_CNT,
ROWNUM T_CNT
FROM (SELECT 직급,COUNT(*) CNT
FROM TEMP03
GROUP BY 직급) A,
(SELECT ROWNUM CNT
FROM TEMP03) B
WHERE B.CNT <= A.CNT
) S02
WHERE S02.직급 = S01.직급
AND S02.T_CNT = S01.RCNT
) M00
GROUP BY
LEV||TO_CHAR(R_CNT)
)
ORDER BY SUBSTRB(KEY1,1,3) DESC
PLAN.
-------------------------------------------------------------
SELECT STATEMENTCost Estimate:
SORTORDER BY
VIEW(1)
SORTGROUP BY
VIEW(2)
UNION-ALL
MERGE JOIN
SORTJOIN
VIEW(5)
COUNT
NESTED LOOPS
VIEW(8)
COUNT
TABLE ACCESSFULL:SCOTT,,TEMP01(9)
VIEW(6)
SORTGROUP BY
TABLE ACCESSFULL:SCOTT,,TEMP01(7)
SORTJOIN
VIEW(3)
COUNT
TABLE ACCESSFULL:SCOTT,,TEMP01(4)
MERGE JOIN
SORTJOIN
VIEW(12)
COUNT
NESTED LOOPS
VIEW(15)
COUNT
TABLE ACCESSFULL:SCOTT,,TEMP02(16)
VIEW(13)
SORTGROUP BY
TABLE ACCESSFULL:SCOTT,,TEMP02(14)
SORTJOIN
VIEW(10)
COUNT
TABLE ACCESSFULL:SCOTT,,TEMP02(11)
MERGE JOIN
SORTJOIN
VIEW(19)
COUNT
NESTED LOOPS
VIEW(22)
COUNT
TABLE ACCESSFULL:SCOTT,,TEMP03(23)
VIEW(20)
SORTGROUP BY
TABLE ACCESSFULL:SCOTT,,TEMP03(21)
SORTJOIN
VIEW(17)
COUNT
TABLE ACCESSFULL:SCOTT,,TEMP03(18)
역시 전체 TABLE을 대상으로 DATA를 원하는 형식에 맞추고자하는
의도로 작성된 문장이기 때문에 PLAN 은 최적화 되어있지 않다.
방법론을 찾아내었다면 PLAN을 최적화 시키는것이 다음
목적이 될것이다. 그 부분은 다루지 않겠다.
뒷풀이.
핵심은 각 RECORD 단위로 자리를 매겨주는 것이 아닐까 한다.
너는 몇번째 RECORD 몇번째 COLUMN으로..하고 명령만하면 지가
알아서 찾아간다.
무지하게 길어서 어려운듯 해도 세세히 뜯어보면 단순한 기법
하나가 들어가 있을 뿐이다.
이런경우 어쩌겠는가 ..
아무래도 한번 겪어 본 사람이, 어쩔 수 없는 경우에 만들더라도
만들기가 쉬워지지 않겠는가.
소화해서 내것으로 만들면 언젠가는 써먹을 수 있으리라.
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 46 | 빠진이빨찾기 II | 운영자 | 2002.09.18 | 3067 |
| 45 | 빠진이빨찾기 | 운영자 | 2002.09.18 | 3122 |
| 44 | 순환참조에서의 상위코드로집계 | 운영자 | 2002.09.18 | 3226 |
| 43 | CONNECT BY의 SORT와 JOIN | 운영자 | 2002.09.18 | 2811 |
| » | REPORT 양식맞추기4 | 운영자 | 2002.09.18 | 2597 |
| 41 | REPORT 양식 맞추기 III | 운영자 | 2002.09.18 | 3015 |
| 40 | REPORT 양식 맞추기 II | 운영자 | 2002.09.18 | 2646 |
| 39 | REPORT 양식 맞추기 | 운영자 | 2002.09.18 | 31359 |
| 38 | 1:1 JOIN / 1:M JOIN | 운영자 | 2002.09.18 | 3000 |
| 37 | TUNING-01 | 운영자 | 2002.09.18 | 2739 |
| 36 | 소계와 합계 | 운영자 | 2002.09.18 | 3489 |
| 35 | 중복DATA 찾아내기 | 운영자 | 2002.09.17 | 2817 |
| 34 | GROUP별 DATA 분류 | 운영자 | 2002.09.17 | 2982 |
| 33 | PARAMETER값에 따라 변경이 일어나는 문장 | 운영자 | 2002.09.17 | 15808 |
| 32 | SELF JOIN | 운영자 | 2002.09.17 | 3408 |
| 31 | Outer Join | 운영자 | 2002.09.17 | 2918 |
| 30 | HINTS (출처-Oracle8.0 Tuning Guide) | 운영자 | 2002.09.17 | 2735 |
| 29 | 단일행 문자 함수 | 운영자 | 2002.09.17 | 2977 |
| 28 | 단일행 수치 함수 | 운영자 | 2002.09.17 | 2869 |
| 27 | NOT IN 의 함정 | 운영자 | 2002.09.17 | 2713 |