쿼리 짜던 중 잘 되지 않아 도움 요청드려요
data 기준은 일자별로 row 형태로 들어가있습니다.
이름 | 일자 | 금액 | |
홍길동 | 20230101 | 100 | |
홍길동 | 20230131 | 100 | |
홍길동 | 20230201 | 100 | |
홍길동 | 20230228 | 100 | |
홍길동 | 20230301 | 100 | |
홍길동 | 20230315 | 100 | |
홍길동 | 20230325 | 80 | |
홍길동 | 20230331 | 80 | |
위의 정보처럼
1월달은 1월1일부터 31일 까지 31 row 형태로 들어가있고 2월달도 2월 전체 일수별 들어가있습니다.
3월달일 경우 3월 1일부터 15일까지 위위 기준으로 들어가 있으며, 25일부터 31일까지 들어가있습니다.
16일부터 24일까지는 데이터가 없습니다.
위의 정보대로 삽입되어있는 데이터를 홍길동은 1월 홍길동은 2월 홍길동은 3월 금액을 총 합 한다음에 일수 만큼 나눠서 평균 금액을 소수점 2째자리에서 반올림 하려면 어떻게 해야할까요?
홍길동 | 202301 | 3100 | 100 |
홍길동 | 202302 | 2800 | 100 |
홍길동 | 202303 | 2060 | 93.64 |
도움 요청 드립니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | WITH tbl AS ( select '홍길동' as "이름" , '20230101' as "날짜" , '100' as "금액" from dual UNION ALL select '홍길동' as "이름" , '20230131' as "날짜" , '100' as "금액" from dual UNION ALL select '홍길동' as "이름" , '20230201' as "날짜" , '100' as "금액" from dual UNION ALL select '홍길동' as "이름" , '20230228' as "날짜" , '100' as "금액" from dual UNION ALL select '홍길동' as "이름" , '20230301' as "날짜" , '100' as "금액" from dual UNION ALL select '홍길동' as "이름" , '20230315' as "날짜" , '100' as "금액" from dual UNION ALL select '홍길동' as "이름" , '20230325' as "날짜" , '80' as "금액" from dual UNION ALL select '홍길동' as "이름" , '20230331' as "날짜" , '80' as "금액" from dual ) SELECT min (이름) 이름, SUBSTR(날짜,1,6) 날짜, sum (daycount*금액) 총합, round( sum (daycount*금액)/ sum (daycount), 2) 평균 FROM ( SELECT 이름 ,날짜, 금액 , (TO_NUMBER( SUBSTR(lead(날짜) over( ORDER BY 날짜),7,2) ) - TO_NUMBER( SUBSTR(날짜,7,2) ) + 1 ) AS daycount , mod(ROW_NUMBER() OVER( ORDER BY 날짜), 2 ) rn FROM tbl ) A WHERE rn = 1 GROUP BY SUBSTR(날짜,1,6) ORDER BY 날짜 |
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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | WITH t AS ( SELECT '홍길동' nm, '20230101' dt, 100 amt FROM dual UNION ALL SELECT '홍길동' , '20230102' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230103' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230104' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230105' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230106' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230107' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230108' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230109' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230110' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230111' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230112' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230113' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230114' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230115' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230116' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230117' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230118' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230119' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230120' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230121' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230122' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230123' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230124' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230125' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230126' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230127' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230128' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230129' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230130' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230131' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230201' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230202' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230203' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230204' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230205' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230206' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230207' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230208' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230209' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230210' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230211' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230212' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230213' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230214' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230215' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230216' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230217' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230218' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230219' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230220' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230221' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230222' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230223' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230224' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230225' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230226' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230227' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230228' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230301' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230302' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230303' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230304' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230305' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230306' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230307' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230308' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230309' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230310' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230311' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230312' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230313' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230314' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230315' , 100 FROM dual UNION ALL SELECT '홍길동' , '20230325' , 80 FROM dual UNION ALL SELECT '홍길동' , '20230326' , 80 FROM dual UNION ALL SELECT '홍길동' , '20230327' , 80 FROM dual UNION ALL SELECT '홍길동' , '20230328' , 80 FROM dual UNION ALL SELECT '홍길동' , '20230329' , 80 FROM dual UNION ALL SELECT '홍길동' , '20230330' , 80 FROM dual UNION ALL SELECT '홍길동' , '20230331' , 80 FROM dual ) SELECT nm , SUBSTR(dt, 1, 6) ym , SUM (amt) sum_amt , ROUND( AVG (amt), 2) avg_amt FROM t GROUP BY nm, SUBSTR(dt, 1, 6) ORDER BY nm, ym ; |