데이터베이스 질문과 답변

루프가 아닌 한방쿼리로 부탁합니다. 0 5 2,305

by 오라클초보 [SQL Query] 한방쿼리 [2015.08.19 21:41:39]

내용이 길지만 고수님들의 많은 도움 부탁드립니다. 

테이블에 모두 조회하여 루프를 돌면서 "자체등급 구하는 우선순위"에 따라서 자체등급, 일련번호를 구하지 않고 한방 쿼리로 구하고 싶습니다.

예1~4)까지에 나오는 결과를 표기하였고 테이블과 자체등급 구하는 우선순위는 아래에 있습니다.

예1) 기업규모:대기업, 자산:10억이상, 폐업등급:부도, 신용등급:AAA, 회사채등급:A, 어음등급:NR, 외부등급:A+, 소호등급:0

결과 -> 자체등급:99, 일련번호:2

예2) 기업규모:대기업, 자산:10억이상, 폐업등급:정상, 신용등급:AA, 회사채등급:A, 어음등급:A2, 외부등급:A+, 소호등급:0

결과-> 자체등급:2, 일련번호:5

예3) 기업규모:대기업, 자산:10억이상, 폐업등급:정상, 신용등급:NR, 회사채등급:NR, 어음등급:NR, 외부등급:NR, 소호등급:0

결과-> 자체등급:88, 일련번호:24

예4) 기업규모:대기업, 자산:10억미만, 폐업등급:정상, 신용등급:AAA, 회사채등급:A, 어음등급:A2, 외부등급:A, 소호등급:0

결과-> 자체등급:1, 일련번호:32

1. 자체등급 구하는 우선순위

    1) 폐업등급 : 폐업, 부도

     2) 기업규모:대기업, 자산:10억이상, 등급우선순위:신용등급->회사채등급->소호등급->폐업등급->어음등급->외부등급

     3) 기업규모:대기업, 자산:10억미만, 등급우선순위:소호등급->신용등급->회사채등급->폐업등급->어음등급->외부등급

2. 테이블('ALL'은 어느 값이라도 상관없다는 뜻입니다.)

일련번호 자산 폐업등급 신용등급 회사채등급 어음등급 외부등급 소호등급 자체등급
1 ALL 폐업 ALL ALL ALL ALL ALL 99
2 ALL 부도 ALL ALL ALL ALL ALL 99
3 10억이상 정상 AAA ALL ALL ALL ALL 1
4 10억이상 정상 AA+ ALL ALL ALL ALL 2
5 10억이상 정상 AA ALL ALL ALL ALL 2
6 10억이상 정상 A+ ALL ALL ALL ALL 3
7 10억이상 정상 A ALL ALL ALL ALL 3
8 10억이상 정상 NR AAA ALL ALL ALL 1
9 10억이상 정상 NR AA+ ALL ALL ALL 2
10 10억이상 정상 NR AA ALL ALL ALL 2
11 10억이상 정상 NR A+ ALL ALL ALL 3
12 10억이상 정상 NR A ALL ALL ALL 3
13 10억이상 정상 NR NR A1 ALL ALL 1
14 10억이상 정상 NR NR A2+ ALL ALL 2
15 10억이상 정상 NR NR A2 ALL ALL 3
16 10억이상 정상 NR NR NR AAA ALL 1
17 10억이상 정상 NR NR NR AA+ ALL 2
18 10억이상 정상 NR NR NR AA ALL 2
19 10억이상 정상 NR NR NR A+ ALL 3
20 10억이상 정상 NR NR NR A ALL 3
21 10억이상 정상 NR NR NR NR 1 1
22 10억이상 정상 NR NR NR NR 2 2
23 10억이상 정상 NR NR NR NR 3 3
24 10억이상 정상 NR NR NR NR 0 88
25 10억이상 관심 NR NR NR NR 0 5
26 10억이상 관찰1 NR NR NR NR 0 7
27 10억이상 관찰2 NR NR NR NR 0 8
28 10억이상 관찰3 NR NR NR NR 0 9
29 10억미만 정상 ALL ALL ALL ALL 1 1
30 10억미만 정상 ALL ALL ALL ALL 2 2
31 10억미만 정상 ALL ALL ALL ALL 3 3
32 10억미만 정상 AAA ALL ALL ALL 0 1
33 10억미만 정상 AA+ ALL ALL ALL 0 2
34 10억미만 정상 AA ALL ALL ALL 0 2
35 10억미만 정상 A+ ALL ALL ALL 0 3
36 10억미만 정상 A ALL ALL ALL 0 3
by 마농 [2015.08.19 23:01:16]

의문사항이 몇가지 있어서 정리해 봅니다.
1. '대기업' 인지를 판단하는 항목이 테이블에 안보이네요?
2. 폐업등급은 'ALL' 이 없는데요?
  - 등급우선순위에 폐업등급이 중간에 포진되어 있을 이유가 없을 듯 합니다.
3. 제시하신 4가지 예시자료가 너무 평범하네요.
  - 코드테이블과 매칭시켜보면 1건씩만 매치되네요.
  - 우선순위까지 고려하려면 여러건이 매칭되는 예시자료가 필요합니다.

by 오라클초보 [2015.08.20 23:30:31]

답변 감사 합니다.

1. 테이블은 자체등급을 관리하기 위한 기준으로  '대기업'은 존재하지 않습니다.

2. 폐업등급에는 'ALL'인 존재하지 않습니다.

재질문 : 제시한 예시2) 와 같은 입력 값일때는 우선순위에 따라서 회사채등급: 'ALL', 어음등급:ALL, 외부등급:ALL, 소호등급:ALL 으로 가공하여 쿼리를 만들어야 하는 것인가요? 다른방법은 없나요?

by 마농 [2015.08.21 10:42:07] 
WITH code_t(no, g1, g2, g3, g4, g5, g6, g7, g8) AS
(
          SELECT  1, 'ALL'     , '폐업' , 'ALL', 'ALL', 'ALL', 'ALL', 'ALL', 99 FROM dual
UNION ALL SELECT  2, 'ALL'     , '부도' , 'ALL', 'ALL', 'ALL', 'ALL', 'ALL', 99 FROM dual
UNION ALL SELECT  3, '10억이상', '정상' , 'AAA', 'ALL', 'ALL', 'ALL', 'ALL',  1 FROM dual
UNION ALL SELECT  4, '10억이상', '정상' , 'AA+', 'ALL', 'ALL', 'ALL', 'ALL',  2 FROM dual
UNION ALL SELECT  5, '10억이상', '정상' , 'AA' , 'ALL', 'ALL', 'ALL', 'ALL',  2 FROM dual
UNION ALL SELECT  6, '10억이상', '정상' , 'A+' , 'ALL', 'ALL', 'ALL', 'ALL',  3 FROM dual
UNION ALL SELECT  7, '10억이상', '정상' , 'A'  , 'ALL', 'ALL', 'ALL', 'ALL',  3 FROM dual
UNION ALL SELECT  8, '10억이상', '정상' , 'NR' , 'AAA', 'ALL', 'ALL', 'ALL',  1 FROM dual
UNION ALL SELECT  9, '10억이상', '정상' , 'NR' , 'AA+', 'ALL', 'ALL', 'ALL',  2 FROM dual
UNION ALL SELECT 10, '10억이상', '정상' , 'NR' , 'AA' , 'ALL', 'ALL', 'ALL',  2 FROM dual
UNION ALL SELECT 11, '10억이상', '정상' , 'NR' , 'A+' , 'ALL', 'ALL', 'ALL',  3 FROM dual
UNION ALL SELECT 12, '10억이상', '정상' , 'NR' , 'A'  , 'ALL', 'ALL', 'ALL',  3 FROM dual
UNION ALL SELECT 13, '10억이상', '정상' , 'NR' , 'NR' , 'A1' , 'ALL', 'ALL',  1 FROM dual
UNION ALL SELECT 14, '10억이상', '정상' , 'NR' , 'NR' , 'A2+', 'ALL', 'ALL',  2 FROM dual
UNION ALL SELECT 15, '10억이상', '정상' , 'NR' , 'NR' , 'A2' , 'ALL', 'ALL',  3 FROM dual
UNION ALL SELECT 16, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'AAA', 'ALL',  1 FROM dual
UNION ALL SELECT 17, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'AA+', 'ALL',  2 FROM dual
UNION ALL SELECT 18, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'AA' , 'ALL',  2 FROM dual
UNION ALL SELECT 19, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'A+' , 'ALL',  3 FROM dual
UNION ALL SELECT 20, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'A'  , 'ALL',  3 FROM dual
UNION ALL SELECT 21, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'NR' , '1'  ,  1 FROM dual
UNION ALL SELECT 22, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'NR' , '2'  ,  2 FROM dual
UNION ALL SELECT 23, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'NR' , '3'  ,  3 FROM dual
UNION ALL SELECT 24, '10억이상', '정상' , 'NR' , 'NR' , 'NR' , 'NR' , '0'  , 88 FROM dual
UNION ALL SELECT 25, '10억이상', '관심' , 'NR' , 'NR' , 'NR' , 'NR' , '0'  ,  5 FROM dual
UNION ALL SELECT 26, '10억이상', '관찰1', 'NR' , 'NR' , 'NR' , 'NR' , '0'  ,  7 FROM dual
UNION ALL SELECT 27, '10억이상', '관찰2', 'NR' , 'NR' , 'NR' , 'NR' , '0'  ,  8 FROM dual
UNION ALL SELECT 28, '10억이상', '관찰3', 'NR' , 'NR' , 'NR' , 'NR' , '0'  ,  9 FROM dual
UNION ALL SELECT 29, '10억미만', '정상' , 'ALL', 'ALL', 'ALL', 'ALL', '1'  ,  1 FROM dual
UNION ALL SELECT 30, '10억미만', '정상' , 'ALL', 'ALL', 'ALL', 'ALL', '2'  ,  2 FROM dual
UNION ALL SELECT 31, '10억미만', '정상' , 'ALL', 'ALL', 'ALL', 'ALL', '3'  ,  3 FROM dual
UNION ALL SELECT 32, '10억미만', '정상' , 'AAA', 'ALL', 'ALL', 'ALL', '0'  ,  1 FROM dual
UNION ALL SELECT 33, '10억미만', '정상' , 'AA+', 'ALL', 'ALL', 'ALL', '0'  ,  2 FROM dual
UNION ALL SELECT 34, '10억미만', '정상' , 'AA' , 'ALL', 'ALL', 'ALL', '0'  ,  2 FROM dual
UNION ALL SELECT 35, '10억미만', '정상' , 'A+' , 'ALL', 'ALL', 'ALL', '0'  ,  3 FROM dual
UNION ALL SELECT 36, '10억미만', '정상' , 'A'  , 'ALL', 'ALL', 'ALL', '0'  ,  3 FROM dual
)
, mst_t(no, g1, g2, g3, g4, g5, g6, g7) AS
(
          SELECT 1, '10억이상', '부도', 'AAA', 'A' , 'NR', 'A+', '0' FROM dual --> 99,  2
UNION ALL SELECT 2, '10억이상', '정상', 'AA' , 'A' , 'A2', 'A+', '0' FROM dual -->  2,  5
UNION ALL SELECT 3, '10억이상', '정상', 'NR' , 'NR', 'NR', 'NR', '0' FROM dual --> 88, 24
UNION ALL SELECT 4, '10억미만', '정상', 'AAA', 'A' , 'A2', 'A' , '0' FROM dual -->  1, 32
)
SELECT *
  FROM mst_t  m
     , code_t c
 WHERE m.g1 LIKE REPLACE(c.g1, 'ALL', '%')
   AND m.g2 = c.g2
   AND m.g3 LIKE REPLACE(c.g3, 'ALL', '%')
   AND m.g4 LIKE REPLACE(c.g4, 'ALL', '%')
   AND m.g5 LIKE REPLACE(c.g5, 'ALL', '%')
   AND m.g6 LIKE REPLACE(c.g6, 'ALL', '%')
   AND m.g7 LIKE REPLACE(c.g7, 'ALL', '%')
;


위와 같이 LIKE 검색을 하면...
마스터 테이블에 대한 코드값이 오직 하나씩만 매칭되는것을 볼 수 있습니다.
두개 이상 나오는 경우가 없다면? 우선순위라는게 있을 이유가 없죠.
두개 이상 나오는 경우가 있다면 그때는 우선순위를 부여해야 하겠죠.
두개 이상 나오는 경우가 있는지 확인하시기 바랍니다.

by 마농 [2015.08.21 11:14:21]

위 36개 코드를 살펴보니...
모든 코드가 서로 배타적입니다.
ALL 때문에 서로 겹치는 부분이 전혀 없네요.
확인은 위 쿼리의 FROM 절의 mst_t 부분을 code_t 로 변경하여 조회해보시면 알 수 있습니다.
그렇다면 제시하신 우선순위라는게 없다는 의미죠.

by 오라클초보 [2015.08.21 21:14:53]

많은 도움이 되었습니다. 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입