꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 숫자 계상 관련 도움 요청 드립니다.

by 초봉 [2023.06.02 17:21:58]

쿼리 짜던 중 잘 되지 않아 도움 요청드려요

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

도움 요청 드립니다.

by 뉴비디비 [2023.06.02 22:00:59]

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 날짜

by 마농 [2023.06.05 00:23:23]

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
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변