데이터베이스 질문과 답변
오전이라 머리가 굳었는지 쿼리가 짜여지질 않네요 도움 부탁드립니다..ㅠ
A 테이블에 code라는 컬럼이 있고
code ------- A B C
, B 테이블에 name, code, score 라는 컬럼이 있습니다.
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처리 하려합니다.
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 처리 하려합니다....만 도저히 감이 오질 않습니다.
name | score_A | score_B | score_C ----------------------------------------------------- 홍길동 1 5 0 영희 2 0 0 철수 3 0 8
도움을 요청합니다..!
-- 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테이블이 없어도 될것 같네요..
빠른 답변 감사합니다!
기준이 되는 A 테이블의 CODE 값이 추가되고 빠지고하는 유동적으로 변할 데이터가 들어가야해서 필요할 듯 합니다.
역시 WITH 가상테이블을 써야하겠군요.... 많은 참고되었습니다. 감사합니다 동동동님!
1. 중간 과정은 오히려 불필요한 과정입니다.
- 없는 코드값 0 을 추가하는게 오히려 더 어렵습니다.
- 중간 과정 없이 바로 뽑는게 오히려 더 쉽습니다.
2. 행을 열로 바꿀 때
- 항목의 구성을 동적으로 하는 것은 불가능합니다.
- 정해진 수의 정해진 값을 기준으로만 가능합니다.
- 동적으로 구현하는 것이 가능하긴 한데 그건 SQL 영역이 아닌 프로그래밍 영역입니다.(동적쿼리)
-- 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
;
와우..OUTER JOIN에 PARTITION BY 를 사용하면 저렇게 나올수 있군요...
또하나 배워갑니다..감사합니다 마농님..
그런데 Ansi쿼리로만 가능한 건가요??
답변 감사합니다 마농님!
PIVOT의 IN에 기재되는 값의 수가 FIX된 수들만 가능하다는 말씀이시죠?
참고용 코딩으로 조금더 공부를 해봐야겠습니다. 감사합니다!
네. 정확히 알고 있는 값에 대해서 가능합니다.
그리고, "WITH 문을 써야 한다"는 식으로 이해하신 듯 한데. 잘못 이해하신 것입니다.
위 답변의 WITH 문은 테스트용 데이터 지정 용도로 사용된 것일 뿐입니다.
아하...! 그런 의미였군요 ㅎ
하나만 더 여쭤보겠습니다.
위 동동동님의 질문과 동일한데, 마농님의 참고용 쿼리를 ANSI쿼리가 아닌 오라클 쿼리로도 구현이 가능할까요?
오라클에서 직접 테스트 해본 오라클 쿼리입니다.
LEFT OUTER JOIN 자체는 ANSI 인데
PARTITION BY 가 ANSI 인지는 잘 모르겠습니다.(오라클(10G 이상)에서만 동작함, 타 DB 는 기능 없는 것으로 압니다. 아마도?)
http://gurubee.net/lecture/2204
) 버튼을 클릭하여 작성 하시면 됩니다.