데이터베이스 질문과 답변
by momo [2022.04.11 09:38:33]
select *
from gold_type gt left join (
SELECT gm.tn_id, gm.dt_id, gm.master, gm.gt_id, SUM(gm.variance)
FROM gold_management gm
WHERE gm.dt_id is not null
group by gm.tn_id, gm.dt_id, gm.gt_id) gm on gt.gt_id = gm.gt_id;
현재 위의 쿼리를 사용하면 아래와 같은 결과가 나옵니다.
| gt_id | gt_nm | tn_id | dt_id | master | gt_id | sum(variance) |
| 1 | A | null | null | null | null | null |
| 2 | B | null | null | null | null | null |
| 3 | C | 테 | 스트 | 1 | 3 | 0 |
| 4 | D | null | null | null | null | null |
left join시에는 기준 테이블에 없는 데이터는 null로 나오는 것은 알고 있으나, 저 null 값들을 다음처럼 마치 있는 값처럼 표현해서 뽑고 싶습니다. 어떻게 해야 해결할 수 있을지 도움을 받을 수 있을까요?
| gt_id | gt_nm | tn_id | dt_id | master | gt_id | sum(variance) |
| 1 | A | 테 | 스트 | 1 | 1 | 0 |
| 2 | B | 테 | 스트 | 1 | 2 | 0 |
| 3 | C | 테 | 스트 | 1 | 3 | 0 |
| 4 | D | 테 | 스트 | 1 | 4 | 0 |
CASE 문이랑 NVL문 활용해서 보여주면 되지 않나요?
그룹바이 구문은 표준에 어긋난 사용법입니다. (master 사용 부분)
널 대신 다른 걸 보여주려면 IFNULL 함수를 사용하면 됩니다.
다만 대체할 값이 뭔지 분명하지 않습니다.
gm.gt_id 의 경우엔 gt.gt_id 로 대체하면 되간 한데
gm.gt_id 와 gt.gt_id 두개를 동시에 조회할 이유가 없습니다.
master 는 사용법에 어긋나고
tn_id 와 dt_id 의 경우가 모호합니다.
지금은 gt_id 3 인 자료 (테 스트) 하나만 있는데?
예를 들어 gt_id 2 인 자료 (구 루비) 가 존재한다면?
나머지 빈 값에 어떤 걸 넣어야 할지 모호합니다.
SUM(gm.variance) 의 경우에 대체값으로 0 을 지정해야 하는지?
tn_id 와 dt_id 처럼 있는 값을 적용해야 하는지?
지금 조회되고 있는 값 자체가 0 이라 구별도 안가네요.
0 이 아닌 다른 값이 2개가 조회 된다면 널값을 어떤 값으로 대체해야 할지도 모호합니다.
제가 급하게 글을 써서 이상한 값까지 넣었던것 같네요 ㅠㅠ
gold type 테이블
| gt_id | gt_nm |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
gold management 테이블
| gm_id | tn_id | dt_id | gt_id | variance |
| 1 | 테스트 | 홍길동 | 3 | 10000 |
| 2 | 테스트 | 홍길동 | 3 | -10000 |
현재는 테이블이 이런식으로 되어 있습니다. gold type과 gold management 테이블은 1:N관계를 맺고 있으며 현재 gold management 테이블의 값들을 활용해서 집계를 해주고 싶습니다.
예를 들어서
SELECT *
FROM gold_type gt
LEFT JOIN (
SELECT tn_id, dt_id, gt_id, SUM(variance)
FROM gold_management
WHERE dt_id is not null
group by tn_id, dt_id, gt_id) gm
ON gt.gt_id = gm.gt_id;
이 쿼리값의 결과는 현재 아래와 같이 출력됩니다.
| gt_id | gt_nm | tn_id | dt_id | gt_id | sum(vareiance) |
| 1 | A | null | null | null | null |
| 2 | B | null | null | null | null |
| 3 | C | 테스트 | 홍길동 | 3 | 0 |
| 4 | D | null | null | null | null |
저는 다음과 같이 gold management 테이블에 해당 gold type 테이블의 외래키가 존재하지 않아도, gold type 테이블에 존재하는 데이터 만큼 뽑고 싶었습니다. gold management 테이블에 gt_id 1, 2, 4와 연관된 데이터는 존재하지 않으니까 0으로요.
| gt_id | gt_nm | tn_id | dt_id | gt_id | sum(vareiance) |
| 1 | A | 테스트 | 홍길동 | 1 | 0 |
| 2 | B | 테스트 | 홍길동 | 2 | 0 |
| 3 | C | 테스트 | 홍길동 | 3 | 0 |
| 4 | D | 테스트 | 홍길동 | 4 | 0 |
그래서 처음에는 NVL도 써보고 case 문도 써보고 했는데 gold management 테이블에 값이 많이 저장되어 있는 경우에는 다음과 같이 뽑혀야 하는데 말씀하신대로 nvl이나 case를 활용해서 어떤 식으로 값을 채워넣어야 할지 몰라서 난항을 겪고 있습니다.
제가 너무 어렵게 접근하고 있는건지, 아니면 현재 데이터 테이블에서 불가능한 방식으로 뽑아내려고 하는건지를 잘모르겠네요 ...
| gm_id | tn_id | dt_id | gt_id | variance |
| 1 | 테스트 | 홍길동 | 3 | 10000 |
| 2 | 테스트 | 홍길동 | 3 | -10000 |
| 3 | 테스트2 | 홍길동2 | 1 | 10000 |
| 4 | 테스트2 | 홍길동2 | 2 | 20000 |
| 5 | 테스트3 | 홍길동3 | 4 | 30000 |
예상 결과
| gt_id | gt_nm | tn_id | dt_id | gt_id | sum(vareiance) |
| 1 | A | 테스트 | 홍길동 | 1 | 0 |
| 2 | B | 테스트 | 홍길동 | 2 | 0 |
| 3 | C | 테스트 | 홍길동 | 3 | 0 |
| 4 | D | 테스트 | 홍길동 | 4 | 0 |
| 1 | A | 테스트2 | 홍길동2 | 1 | 10000 |
| 2 | B | 테스트2 | 홍길동2 | 2 | 20000 |
| 3 | C | 테스트2 | 홍길동2 | 3 | 0 |
| 4 | D | 테스트2 | 홍길동2 | 4 | 0 |
| 1 | A | 테스트3 | 홍길동3 | 1 | 0 |
| 2 | B | 테스트3 | 홍길동3 | 2 | 0 |
| 3 | C | 테스트3 | 홍길동3 | 3 | 0 |
| 4 | D | 테스트3 | 홍길동3 | 4 | 30000 |
WITH gold_type AS
(
SELECT 1 gt_id, 'A' gt_nm
UNION ALL SELECT 2, 'B'
UNION ALL SELECT 3, 'C'
UNION ALL SELECT 4, 'D'
)
, gold_management AS
(
SELECT 1 gm_id, '테스트' tn_id, '홍길동' dt_id, 3 gt_id, 10000 variance
UNION ALL SELECT 2, '테스트' , '홍길동' , 3, -10000
UNION ALL SELECT 3, '테스트2', '홍길동2', 1, 10000
UNION ALL SELECT 4, '테스트2', '홍길동2', 2, 20000
UNION ALL SELECT 5, '테스트3', '홍길동3', 4, 30000
)
SELECT a.gt_id
, a.gt_nm
, b.tn_id
, b.dt_id
, IFNULL(SUM(c.variance), 0) variance
FROM gold_type a
CROSS JOIN
(SELECT DISTINCT tn_id, dt_id FROM gold_management) b
LEFT OUTER JOIN gold_management c
ON a.gt_id = c.gt_id
AND b.tn_id = c.tn_id
AND b.dt_id = c.dt_id
GROUP BY b.tn_id, b.dt_id, a.gt_id, a.gt_nm
ORDER BY b.tn_id, b.dt_id, a.gt_id
;
제 생각보다 훨씬 어려운 쿼리였네요 ㅠㅠ 답변 주셔서 정말 감사합니다
1) 제일 먼저 테이블 간의 정의가 가장 중요합니다.
- Main Table : gt_id
- Detail Table : gm_id ( 1 : N )
여기서 상위 left join 에서 ,
group by 에서 [ group by tn_id, dt_id, gt_id ] ==> 이부분을 볼때 실제 자료가 gt_id 1개ekd tn_id , dt_id 가 꼭 1개 인지 확인? 검정이 필요합니다.
left join gt_id = gt_id 하셨다면, tn_id , dt_id 의 다중 문제 해결을 위해,
max( tn_id ) , max(dt_id) 를 시행하시고,
group by 절에 gt_id 1개로 유지 하는 것을 추천 드립니다.
< 상기 부분의 적용한다면, 수정된. SQL >
SELECT gt.gt_id , gt.gt_nm , gm.gt_id , nvl(gm.tn_id,'') , nvl(gm.dt_id,'') , nvl(gm.vareiance,0)
FROM gold_type gt
LEFT JOIN ( SELECT gt_id, max(tn_id) as tn_id, max(dt_id) as dt_id , SUM(variance) as variance
FROM gold_management WHERE dt_id is not null
group by gt_id) gm ON gt.gt_id = gm.gt_id
;
이결과는
Master : gt_id 정보만큼
1 , 2 , 3 , 4 에 대한 정보만큼 딱 나오게 되고,
위에 예상결과 부분은
master ( 1 , 2 , 3, 4 ) ,
detail ( 테스트 , 테스트2 , 테스트3 ) <== master 별 각 detail이 다양하게 있다는 전재하에서, 마지막 SQL 구현 하시면 됩니다.
결론은 NVL 이용시,, sub 쿼리 안에 꼭 alias 처리 하셔서 사용하시면 좋아요.
Detail : gm_id 이외에 tn_id , dt_id 까지도 존재하는 부분으로 된다면,
SELECT gt.gt_id , gt.gt_nm , gm.gt_id , nvl(gm.tn_id,'') , nvl(gm.dt_id,'') , nvl(gm.vareiance,0)
FROM gold_type gt
LEFT JOIN ( SELECT gt_id, tn_id , dt_id , SUM(variance) as variance
FROM gold_management WHERE dt_id is not null
group by gt_id ,tn_id , dt_id ) gm ON gt.gt_id = gm.gt_id ;
이결과는
Master : gt_id 정보만큼 * gm_id 이하, tn_id , dt_id 수량 곱큼 발생 됩니다.
자세하게 설명해주셔서 감사합니다. 제 로직에 뭔가 문제는 없었는지, 아니면 제가 뭘 놓치고 있었는지 다시 한번 검토해보겠습니다
) 버튼을 클릭하여 작성 하시면 됩니다.