데이터베이스 질문과 답변

오라클 두 테이블간의 특정값을 이용하여 조회하는 쿼리... 1 8 3,067

by HSky [SQL Query] query plsql sql [2022.11.29 11:58:19]

오전이라 머리가 굳었는지 쿼리가 짜여지질 않네요 도움 부탁드립니다..ㅠ
A 테이블에 code라는 컬럼이 있고

?
1
2
3
4
5
code
-------
  A
  B
  C

, B 테이블에 name, code, score 라는 컬럼이 있습니다. 

?
1
2
3
4
5
6
7
8
9
10
name   |   code   |   score
----------------------------------
홍길동 |   A      |     1
홍길동 |   B      |     5
영희   |   A      |     2
철수   |   A      |     3
철수   |   C      |     8
 
B 테이블의 code와 A 테이블의 code 를 비교하여 A 테이블의 code 값 만큼, 그리고 B 테이블의 name 값 만큼 조회를 하려합니다.
만약 B 테이블의 특정 name 으로 조회된 결과의 code가 A 테이블의 code값만큼 없다면 score를 0으로 nvl처리 하려합니다.
?
1
2
3
4
5
6
7
8
9
10
11
name   |   code   |   score
----------------------------------
홍길동 |   A      |     1
홍길동 |   B      |     5
홍길동 |   C      |     0
영희   |   A      |     2
영희   |   B      |     0
영희   |   C      |     0
철수   |   A      |     3
철수   |   B      |     0
철수   |   C      |     8

B테이블의 홍길동(name)의 code는 A 테이블의 code에 있는 A, B값을 가지고 있으니 score는 1,5가 조회되지만 C값은 없으니 0으로,

영희는 B,C 가 없으니 B,C의 score는 0으로 처리하려는 식입니다.

이걸 다시 행을 열로 pivot 처리 하려합니다....만 도저히 감이 오질 않습니다. 

?
1
2
3
4
5
   name   |   score_A   |   score_B   |   score_C
-----------------------------------------------------
  홍길동            1              5            0
  영희              2              0            0
  철수              3              0            8

 

도움을 요청합니다..!

by 동동동 [2022.11.29 13:25:10]
?
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- 1번
WITH A_TBL AS (
SELECT 'A' AS COD FROM DUAL UNION ALL
SELECT 'B' AS COD FROM DUAL UNION ALL
SELECT 'C' AS COD FROM DUAL
)
, B_TBL AS (
SELECT '홍길동' AS NM, 'A' AS COD, 1 AS SCORE FROM DUAL UNION ALL
SELECT '홍길동' AS NM, 'B' AS COD, 5 AS SCORE FROM DUAL UNION ALL
SELECT '영희'   AS NM, 'A' AS COD, 2 AS SCORE FROM DUAL UNION ALL
SELECT '철수'   AS NM, 'A' AS COD, 3 AS SCORE FROM DUAL UNION ALL
SELECT '철수'   AS NM, 'C' AS COD, 8 AS SCORE FROM DUAL
)
, C_TBL AS (
SELECT B.NM
     , DECODE(B.COD, 'A', B.SCORE, 0) AS SCORE_A
     , DECODE(B.COD, 'B', B.SCORE, 0) AS SCORE_B
     , DECODE(B.COD, 'C', B.SCORE, 0) AS SCORE_C
  FROM A_TBL A
     , B_TBL B
 WHERE B.COD = A.COD
)
SELECT NM
     , SUM(SCORE_A) AS SCORE_A
     , SUM(SCORE_B) AS SCORE_B
     , SUM(SCORE_C) AS SCORE_C
  FROM C_TBL
 GROUP BY NM
;
 
 
-- 2번
WITH A_TBL AS (
SELECT 'A' AS COD FROM DUAL UNION ALL
SELECT 'B' AS COD FROM DUAL UNION ALL
SELECT 'C' AS COD FROM DUAL
)
, B_TBL AS (
SELECT '홍길동' AS NM, 'A' AS COD, 1 AS SCORE FROM DUAL UNION ALL
SELECT '홍길동' AS NM, 'B' AS COD, 5 AS SCORE FROM DUAL UNION ALL
SELECT '영희'   AS NM, 'A' AS COD, 2 AS SCORE FROM DUAL UNION ALL
SELECT '철수'   AS NM, 'A' AS COD, 3 AS SCORE FROM DUAL UNION ALL
SELECT '철수'   AS NM, 'C' AS COD, 8 AS SCORE FROM DUAL
)
SELECT NM
     , NVL(SCORE_A, 0) AS SCORE_A
     , NVL(SCORE_B, 0) AS SCORE_B
     , NVL(SCORE_C, 0) AS SCORE_C
  FROM (
SELECT NM
     , COD
     , SCORE
  FROM B_TBL
  )
 PIVOT (
         SUM(SCORE) FOR COD IN ('A' AS SCORE_A, 'B' AS SCORE_B, 'C' AS SCORE_C)
       )
;

 

무조건 score_A, score_B, score_C 로 표현하는 거면 A테이블이 없어도 될것 같네요..

 

by HSky [2022.11.29 13:41:35]

빠른 답변 감사합니다!

기준이 되는 A 테이블의 CODE 값이 추가되고 빠지고하는 유동적으로 변할 데이터가 들어가야해서 필요할 듯 합니다.

역시 WITH 가상테이블을 써야하겠군요.... 많은 참고되었습니다. 감사합니다 동동동님!

by 마농 [2022.11.29 13:57:36]

1. 중간 과정은 오히려 불필요한 과정입니다.
- 없는 코드값 0 을 추가하는게 오히려 더 어렵습니다.
- 중간 과정 없이 바로 뽑는게 오히려 더 쉽습니다.
2. 행을 열로 바꿀 때
- 항목의 구성을 동적으로 하는 것은 불가능합니다.
- 정해진 수의 정해진 값을 기준으로만 가능합니다.
- 동적으로 구현하는 것이 가능하긴 한데 그건 SQL 영역이 아닌 프로그래밍 영역입니다.(동적쿼리)

?
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
-- GROUP BY ~ DECODE --
SELECT name
     , NVL(MIN(DECODE(code, 'A', score)), 0) a
     , NVL(MIN(DECODE(code, 'B', score)), 0) b
     , NVL(MIN(DECODE(code, 'C', score)), 0) c
  FROM t_data b
 GROUP BY name
;
 
-- PIVOT --
SELECT *
  FROM t_data b
 PIVOT (MIN(score) FOR code IN ('A' a, 'B' b, 'C' c))
;
 
-- 중간과정(참고용) --
SELECT b.name
     , a.code
     , NVL(b.score, 0) score
  FROM t_code a
  LEFT OUTER JOIN t_data b
  PARTITION BY (b.name)
    ON a.code = b.code
 ORDER BY name, code
;

 

by 동동동 [2022.11.29 14:15:21]

와우..OUTER JOIN에 PARTITION BY 를 사용하면 저렇게 나올수 있군요...

또하나 배워갑니다..감사합니다 마농님..

 

그런데 Ansi쿼리로만 가능한 건가요??

by HSky [2022.11.29 14:09:45]

답변 감사합니다 마농님!

PIVOT의 IN에 기재되는 값의 수가 FIX된 수들만 가능하다는 말씀이시죠?

참고용 코딩으로 조금더 공부를 해봐야겠습니다. 감사합니다!

by 마농 [2022.11.29 14:33:47]

네. 정확히 알고 있는 값에 대해서 가능합니다.
그리고, "WITH 문을 써야 한다"는 식으로 이해하신 듯 한데. 잘못 이해하신 것입니다.
위 답변의 WITH 문은 테스트용 데이터 지정 용도로 사용된 것일 뿐입니다.

by HSky [2022.11.29 14:42:48]

아하...! 그런 의미였군요 ㅎ

하나만 더 여쭤보겠습니다. 

위 동동동님의 질문과 동일한데, 마농님의 참고용 쿼리를 ANSI쿼리가 아닌 오라클 쿼리로도 구현이 가능할까요?

by 마농 [2022.11.29 14:49:31]

오라클에서 직접 테스트 해본 오라클 쿼리입니다.
LEFT OUTER JOIN 자체는 ANSI 인데
PARTITION BY 가 ANSI 인지는 잘 모르겠습니다.(오라클(10G 이상)에서만 동작함, 타 DB 는 기능 없는 것으로 압니다. 아마도?)
http://gurubee.net/lecture/2204

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