데이터베이스 질문과 답변
안녕하세요. 쿼리 짜다가 막히는 부분이 있어 질문드립니다..
| yyyymmddhh | yyyymmdd | yyyymm | r1 | r2 | r3 |
| 2019010100 | 20190101 | 201901 | 35.8 | 48.4 | 16213.3 |
| 2019010101 | 20190101 | 201901 | 30.5 | 34.3 | 17920.0 |
위와같은 데이터가 있습니다.
시간 기준 데이터를 월, 일 단위로 그룹핑하여 계산하려고 하는데요.
| yyyymmdd | yyyymm | r1 | r2 | r3 |
| 20190101 | 201901 | 33.0 | 46.0 | 17066.7 |
월 단위로 그룹핑하면 결과가 이렇게 되어야하고,
공식은 아래와 같습니다.
r1 = sum(r1*r3)/sum(r3)
r2 = max((R2*R3)/AVG(R3))
r3 = avg(R3)
여기서 문제가 r2가 단일그룹이 아니라 안된다고 하는데 해결방법을 잘 모르겠습니다..ㅠ
WITH TEST1 AS ( SELECT '2019010100' yyyymmddhh, '20190101' yyyymmdd, '201901' yyyymm, '35.8' r1, '48.4' r2, '16213.3' r3 FROM DUAL UNION ALL SELECT '2019010101' yyyymmddhh, '20190101' yyyymmdd, '201901' yyyymm, '30.5' r1, '34.3' r2, '17920' r3 FROM DUAL ) SELECT yyyymmdd, SUM(r1*r3)/SUM(r3) r1, MAX((R2*R3)/AVG(R3)) r2, AVG(R3) r3 FROM TEST1 GROUP BY yyyymmdd ;
WITH TEST1 AS ( SELECT '2019010100' yyyymmddhh, '20190101' yyyymmdd, '201901' yyyymm, '35.8' r1, '48.4' r2, '16213.3' r3 FROM DUAL UNION ALL SELECT '2019010101' yyyymmddhh, '20190101' yyyymmdd, '201901' yyyymm, '30.5' r1, '34.3' r2, '17920' r3 FROM DUAL ) SELECT yyyymmdd, SUM(r1*r3)/SUM(r3) r1, MAX(R2*R3)/AVG(R3) r2, AVG(R3) r3 FROM TEST1 GROUP BY yyyymmdd
질문자께서 r2 = max((R2*R3)/AVG(R3))라는 공식을 원하시는 것같은데, r2 = max(R2*R3)/AVG(R3)라고 정의해도 같은 결과값을 얻을 수 있나요???
감사합니다.
MAX(R2*R3)/AVG(R3) 로 하니깐 잘 수행됩니다 쿼리가..
결과는 일단 테스트 몇번 해보는데 맞는것같아요~!
도움 많이되었습니다.
r2 공식 자체가 이상한데요?
위 예시자료 대비 결과가 어떻게 나와야 하는지?
실제 수치를 대입해서 설명해 주세요.
음.. 공식이 이상한가요 ㅠㅠ?
실제 수치를 대입한게 본문의 표들 입니다..
제일 위에 있는 표가 시간단위 표이고, 공식을 거친 후 일 단위 표가 아래 표입니다.
이런식으로 시간단위를 일, 월 단위로 계산하려고하는 것 이구요.
r2의 공식은 r3의 평균값을 r2에 각각 곱해서 나온 결과값 중 최대(max)값을 구하는게 목적입니다.
네. 어떻게 결과가 나왔는지? 계산과정을 수치를 가지고 보여주세요.
1. 시간단위 테이블
| yyyymmddhh | yyyymmdd | yyyymm | r1 | r2 | r3 |
| 2019010100 | 20190101 | 201901 | 35.8 | 48.4 | 16213.3 |
| 2019010101 | 20190101 | 201901 | 30.5 | 34.3 | 17920.0 |
2. 일 단위 계산
| 식 | 결과 | |
| r1 | ((35.8*16213.3)+(30.5*17920))/(16213.3+17920.0) | 33.0175 |
| r2 | (48.4*16213.3)/((16213.3+17920)/2) | 45.97995 |
| (34.3*17920)/((16213.3+17920)/2) | 36.01504 | |
| r3 | (16213.3+17920.0)/2 | 17066.65 |
r2는 결과값 중 max값을 사용
| yyyymmdd | yyyymm | r1 | r2 | r3 |
| 20190101 | 201901 | 33.0 | 46.0 | 17066.7 |
최종 일 단위 결과
---
이런식으로 계산과정을 거쳤습니다.
월 단위도 일 단위 결과를 가지고 마찬가지로 수행해야하구요..
(r2*r3) 는 각 행단위 계산이고? 결과는 2건
AVG(r3) 는 집계 결과인데? 결과는 1건
이 두가지 값은 집계단위가 서로 달라서 계산이 불가능 하죠. (행단위 VS 일단위)
계산 불가능한걸 억지로 계산한 뒤 다시 MAX 를 했네요.
이렇게 집계를 중첩하여 하는 것도 불가능 한 건데요.
위 LKJ 님이 제시한 방법이 맞는 공식이 아닐까? 생각되네요.
네 시간내어서 봐주셔서 감사합니다.
애초에 억지로 하고는 있었는데 ㅎㅎ; 도움이 많이 되었습니다.
제시하신 계산식을 보니 굳이 위와 같이 복잡하게 하실 필요가 없어 보입니다.(할 수도 없구요)
위 첫 댓글의 공식으로 풀어도 결과는 동일합니다.
에러도 안나고, 계산방식도 훨씬 효율적이구요.
) 버튼을 클릭하여 작성 하시면 됩니다.