데이터베이스 질문과 답변
A 데이터
select
구분
, count(*)
from(
SELECT case when ACTTIME >= 200 and ACTTIME < 500 then '200이상500미만'
when ACTTIME >= 500 and ACTTIME < 1000 then '500이상1000미만'
when ACTTIME >= 1000 and ACTTIME < 2000 then '1000이상2000미만'
when ACTTIME >= 2000 and ACTTIME < 3000 then '2000이상3000미만'
when ACTTIME >= 3000 and ACTTIME < 5000 then '3000이상5000미만'
when ACTTIME >= 5000 then '5000이상'
end as 구분
from (SELECT RESNO, ROUND(SUM(ACTTIME)/60,0) AS ACTTIME FROM RESACTRES A GROUP BY RESNO) A, RESMST B
WHERE A.RESNO = B.RESNO
order by ACTTIME)
group by 구분 order by 구분
;
B 데이터
SELECT
구분
, COUNT(*)
FROM(
SELECT CASE WHEN ACTTIME >= 200 AND ACTTIME < 500 THEN '200이상500미만'
WHEN ACTTIME >= 500 AND ACTTIME < 1000 THEN '500이상1000미만'
WHEN ACTTIME >= 1000 AND ACTTIME < 2000 THEN '1000이상2000미만'
WHEN ACTTIME >= 2000 AND ACTTIME < 3000 THEN '2000이상3000미만'
WHEN ACTTIME >= 3000 AND ACTTIME < 5000 THEN '3000이상5000미만'
WHEN ACTTIME >= 5000 THEN '5000이상'
END AS 구분
FROM (SELECT RESPSNO, ROUND(SUM(ACTTIME)/60,0) AS ACTTIME FROM RESACTRES_NANUM A GROUP BY RESPSNO) A, RESMST B
WHERE A.RESPSNO = B.RESPSNO
ORDER BY ACTTIME)
GROUP BY 구분 ORDER BY 구분
;
A 데이터
| 200이상500미만 | 161,115 | |
| 500이상1000미만 | 14,568 | |
| 1000이상2000미만 | 2,987 | |
| 2000이상3000미만 | 1,560 | |
| 3000이상5000미만 | 794 | |
| 5000이상 | 39,420 | |
B 데이터
| 200이상500미만 | 8,733 | |
| 500이상1000미만 | 1,049 | |
| 1000이상2000미만 | 266 | |
| 2000이상3000미만 | 137 | |
| 3000이상5000미만 | 74 | |
| 5000이상 | 2,701 | |
A의 값과, B 값이 있는데
A값의 ACTTIME + B 값의 ACTTIME 을 합한 후에
위의 구분("200이상500미만" 등등...)에서 나타나게 하려고 하고 싶습니다.
이 다음 부분이 중요한데... 뭐라고 표현해야 할지 모르겠네요
A데이터 200 이상 161,115 이 값과, B데이터 200 이상 8,733
이 데이터를 합치면 169,848이 나올 것 같지만 그게 아니라...
ACTTIME 합이 500이 넘어가면 그 다음 "500이상1000미만" 구분에 +1이 되는 겁니다.
질문하실때 상대방(듣는사람)이 내말을 듣고 이해할 수 있을까?
라는 고민을 하고 질문해 주시면 좋겠습니다.
자기만 알고 있는 현재 상황에 대한 설명이 전혀 없이
대뜸 쿼리 하나 던져 놓고, 본인만 알고 있는 용어로 설명을 하시네요.
- 길동이라는 사람을 구별하는 항목은 무엇인가요?
- A, B 를 구별하는 항목은 무엇인가요?
- 원본 예시와 결과 예시 자료를 통해 구체적으로 질문해 주시면 좋습니다.
질문을 아예 다 바꿨습니다.
최대한 쉽게 이해하기 쉽게 예시를 들은건데...
그게 잘못 생각 했나봅니다.
지금도 질문을 잘 한건지 못한건지 모르겠습니다.
말주변이 좋지 못한터라...
죄송합니다.
입력은 있는데.. 출력은 어떻게 되나요?
예상되는 출력도 함께 있어야 할 거 같네요..
SELECT '' AS RESNO,ROUND(SUM(ACTTIME)/60,0) AS ACTTIME
를
SELECT B.RESNO AS RESNO,ROUND(SUM(ACTTIME)/60,0) AS ACTTIME
로 바꿔보시죠..
출력은 예측할 수 없습니다.
위에 적혀 있듯이
A의 구분 값과, B의 구분값의 합이 일정치가 않은 부분이라...
예를 1명으로 들자면
ex) A의 구분 값 "200 이상"에 해당하는 사람이 있습니다.
이 사람은 현재 499시간을 가지고 있는데 B 값에 200 이라는 시간을 더 가지고 있어
499 + 200 = 699시간 이기 때문에 500이상1000미만 에 +1 이 되서
| 200이상500미만 | 161,114 | |
기존 - 1
| 500이상1000미만 | 14,569 | |
기존 +1
이 되는 겁니다.
3개 테이블이 존재합니다.
- A : resactres
- B : resactres_nanum
- C : resmst
중요 항목이 3개 존재하네요.
- resno
- acttime
- respsno
몇가지 의문사항
- resmst 와의 조인은 꼭 필요한 것인지?
- resno 와 respsno 의 차이가 뭔지?
- 두 테이블 A 와 B 의 조인키는 무엇인지?
몇가지 의문사항
- resmst 와의 조인은 꼭 필요한 것인지?
네... 초기 개발 당시 왜 이렇게 만들었는지는 모르지만
키 값이 틀립니다.
- resno 와 respsno 의 차이가 뭔지?
resno는 연번(중복이 없는값) 이고 respsno 는 주민등록번호 입니다.
- 두 테이블 A 와 B 의 조인키는 무엇인지?
resmst 이 테이블에는 resno와 respsno의 컬럼이 있습니다.
그래서 생각한 내용은
따로 출력한 A, B의 ACTTIME 값을 합쳐서 resno로 group by 를 한 다음 구분에 넣으면 될 것 같은데...
이미 여러방식의 쿼리를 만들었지만 조금씩 틀리게 만들어서 그런지 원하는 값이 안나옵니다.
연번과 주민번호는 1:1 관계인가요?
즉, 각각 한사람을 대표하는 번호인지요?
다시 말하면... 집계 기준이 연번인가요? 주민번호인가요? 둘다 같은 의미인가요?
한 사람의 데이터가 1개의 연번과 1개의 주민등록번호가 있으나
resactres 테이블의 기준은 resno 입니다.
그리고 resactres_nanum 테이블의 기준은 respsno 입니다.
SELECT
구분
, COUNT(*)
FROM(
SELECT CASE WHEN ACTTIME >= 200 AND ACTTIME < 500 THEN '200이상500미만'
WHEN ACTTIME >= 500 AND ACTTIME < 1000 THEN '500이상1000미만'
WHEN ACTTIME >= 1000 AND ACTTIME < 2000 THEN '1000이상2000미만'
WHEN ACTTIME >= 2000 AND ACTTIME < 3000 THEN '2000이상3000미만'
WHEN ACTTIME >= 3000 AND ACTTIME < 5000 THEN '3000이상5000미만'
WHEN ACTTIME >= 5000 THEN '5000이상'
END AS 구분
FROM (select b.resno, ACTTIME
from (SELECT RESPSNO, ROUND(SUM(ACTTIME)/60,0) AS ACTTIME FROM RESACTRES_NANUM A GROUP BY RESPSNO) A, RESMST B
WHERE A.RESPSNO = B.RESPSNO
ORDER BY ACTTIME))bb
GROUP BY 구분 ORDER BY 구분
resmst 테이블에 resno도 있어 출력 가능 합니다.
SELECT 구분 , COUNT(*) cnt FROM (SELECT resno , CASE WHEN acttime >= 200 AND acttime < 500 THEN ' 200이상 500미만' WHEN acttime >= 500 AND acttime < 1000 THEN ' 500이상1000미만' WHEN acttime >= 1000 AND acttime < 2000 THEN '1000이상2000미만' WHEN acttime >= 2000 AND acttime < 3000 THEN '2000이상3000미만' WHEN acttime >= 3000 AND acttime < 5000 THEN '3000이상5000미만' WHEN acttime >= 5000 THEN '5000이상' END AS 구분 FROM (SELECT resno , ROUND(SUM(acttime)/60) acttime FROM (SELECT resno , SUM(acttime) acttime FROM resactres a GROUP BY resno UNION ALL SELECT c.resno , SUM(b.acttime) acttime FROM resactres_nanum b , resmst c WHERE b.respsno = c.respsno GROUP BY c.resno ) GROUP BY resno ) ) GROUP BY 구분 ORDER BY 구분 ;
SELECT 구분, COUNT (*)
FROM (SELECT CASE
WHEN ACTTIME >= 200 AND ACTTIME < 500
THEN
'200이상500미만'
END
AS 구분
FROM ( SELECT ROUND (SUM (AA.ACTTIME + CC.ACTTIME) / 60, 0)
AS ACTTIME
FROM ( SELECT a.RESNO, b.respsno, a.ACTTIME
FROM ( SELECT RESNO,
ROUND (SUM (ACTTIME) / 60, 0)
AS ACTTIME
FROM RESACTRES A
GROUP BY RESNO) A,
RESMST B
WHERE A.RESNO = B.RESNO
ORDER BY ACTTIME) aa,
( SELECT A.RESPSNO,
ROUND (SUM (ACTTIME) / 60, 0)
AS ACTTIME
FROM RESACTRES_NANUM A
GROUP BY RESPSNO) cc
WHERE aa.respsno = cc.respsno(+)
ORDER BY ACTTIME)
)
GROUP BY 구분
ORDER BY 구분;
비슷하게 만든게 있긴한데...
약간 틀리네요... 하아... ㅠㅠ
작성하신 쿼리를 보면
aa 와 cc 를 아우터 조인 했네요.
이 경우 aa 의 사람이 cc 의 사람보다 많고,
cc 의 모든 사람이 aa 에 포함된다고 가정하고 작성하는 거죠.
이 경우 cc.acttime 을 더할 때는 NVL 처리를 해줘야 하겠죠.
위의 가정이 틀림 없을 때만 적용할 수 있는 쿼리이구요.
반대의 경우(cc가 큰 경우)라면 아우터 조인의 방향이 바뀌어야 하고
포함관계가 아닌 경우라면 풀아우터 조인을 해야 합니다.
각각을 round 하고 합산한 뒤 또 round 하네요.
이 경우 이중 반올림이 될 수 있습니다.
각각을 60으로 나누고 또 60으로 나누네요?
이중반올림이야 오차가 작지만...
60으로 두번 나누면 완전 틀린 결과가 되겠네요.
각각을 구분지어 집계한 결과를 합치려 한게 잘못입니다.
- 각각을 합친 뒤에 구분지어 집계하면 됩니다.
두개 집합을 하나로 합치기 위해서는 정확한 기준이 마련되어야 합니다.
- 기준은 resno 로 합니다.
테이블간의 연결관계가 있다고 해서 항상 조인해야 하는 것은 아닙니다.
- 필요한 경우에만 조인합니다.
구분값의 정확한 정렬을 위해 공백을 앞에 추가했습니다.
인라인뷰 안의 정렬은 불필요하며 성능저하의 원인이 됩니다.
아... 그렇군요 ㅠㅠ
제가 짠 쿼리를 보니 round와 /60 이 계속 들어갔었네요
아웃터조인의 경우는 aa의 사람이 cc의 4, 5배 가량 많아서 ㅎㅎ;;
최종적으로 한번만 들어갔으면 됐는데...
ㅜㅡ 매번 이렇게 도움 받아서 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.