mysql 월별 집계 또 다시 질문 드립니다.. 0 4 2,281

by 쿼리는어려워 [PL/SQL] mysql 월별집계 [2016.05.27 11:21:45]


제목 없음.jpg (105,300Bytes)

 

어제 질문글 올린 후 도움을 받았는데요. 먼저 감사드립니다 ㅠㅠ

사용 DB는 MYSQL 이고요.

 

현재 이런식으로 월별 집계를 구하고 있는데

"집계월"    "전월"    "접수"    "완료"    "취소"    "미결"
"2016-05"    "0"       "18"       "12"       "2"       "4"
"2016-04"    "0"       "34"       "24"       "3"       "7"
"2016-03"    "0"       "21"       "19"       "0"       "2"
"2016-02"    "0"       "11"       "11"       "0"       "0"

 

 

문제는 2016-04월의 미결 데이터인 7건이 2016-05월 전월에 들어가야 합니다

2016-04에는 03의 미결인 2건이 들어가야 하구요.

 

이걸 쿼리로 하려는데 도무지 감이 안옵니다...

select date_format(regdate, '%Y-%m') as 집계월
		, '0' as 전월
#		, date_format( date_sub(regdate, interval 1 month), '%Y-%m') as 이전월
		, sum(if(stat_cd>=50,1,0)) as 접수
		, sum(if(stat_cd=999,1,0)) as 완료
		, sum(if(stat_cd=40,1,0)) as 취소
		, COALESCE(sum(if(stat_cd>=50,1,0)),0)-COALESCE(sum(if(stat_cd=999,1,0)),0)-COALESCE(sum(if(stat_cd=40,1,0)),0) as 미결
from req_service_tbl
where date_format(regdate, '%Y-%m') >=  '2016-01'
group by date_format(regdate, '%Y-%m')
order by 집계월 desc;

 

그럼 조언 부탁 드립니다.

감사합니다~~!!

by 푸르른 [2016.05.27 13:04:18]
with t as
(
select '1' id, '50' statcd , to_date('2014-07-03', 'YYYY-MM-DD') dt from dual union all
select '1' id, '40' statcd , to_date('2014-07-05', 'YYYY-MM-DD') dt from dual union all
select '2' id, '50' statcd , to_date('2014-04-29', 'YYYY-MM-DD') dt from dual union all
select '3' id, '60' statcd , to_date('2014-05-02', 'YYYY-MM-DD') dt from dual union all
select '4' id, '99' statcd , to_date('2014-06-01', 'YYYY-MM-DD') dt from dual union all
select '5' id, '99' statcd , to_date('2014-04-10', 'YYYY-MM-DD') dt from dual union all
select '6' id, '50' statcd , to_date('2014-03-01', 'YYYY-MM-DD') dt from dual union all
select '6' id, '99' statcd , to_date('2014-03-01', 'YYYY-MM-DD') dt from dual union all
select '6' id, '99' statcd , to_date('2014-03-01', 'YYYY-MM-DD') dt from dual union all
select '6' id, '50' statcd , to_date('2014-03-01', 'YYYY-MM-DD') dt from dual union all
select '6' id, '40' statcd , to_date('2014-02-01', 'YYYY-MM-DD') dt from dual union all
select '6' id, '60' statcd , to_date('2014-03-01', 'YYYY-MM-DD') dt from dual

)
select	 dt1
	, nvl(LEAD(미결, 1) OVER(ORDER BY dt1 desc),0) 전월
	,접수
	,완료
	,취소
	,미결 
from
(
select  to_char(dt,'YYYY-MM')    dt1       
        , count(*) 접수
        , count(decode(statcd,'99','1')) 완료
        , count(decode(statcd,'40','1')) 취소
        , count(*)-nvl(sum(decode(statcd,'99','1')),0)-nvl(sum(decode	(statcd,'40','1')),0) 미결
	from t
	where to_char(dt,'YYYY-MM')  >=  '2014-01'
	group by to_char(dt,'YYYY-MM') 	)
order by dt1 desc; 

답글이없어서 일단 남기긴 하는데 제가. 배우는 입장이라 오라클로밖엔 모르겠네요 하하

오라클 버전입니다..ㅠㅠ

nvl(LEAD(미결, 1) OVER(ORDER BY dt1 desc),0) 전월 요부분을 응용하시면 될꺼같으나...

더쉽고 ..정확한건 선배님들이 도와주시겠죠 ...ㅎ

DT1           전월      접수       완료       취소       미결
------- ---------- ---------- ---------- ---------- ----------
2014-07          0          2          0          1          1
2014-06          1          1          1          0          0
2014-05          1          1          0          0          1
2014-04          3          2          1          0          1
2014-03          0          5          2          0          3
2014-02          0          1          0          1          0


by 쿼리는어려워 [2016.05.27 13:32:05]

매번 감사드립니다 ㅠㅠ 푸르른님도 배운다고 하셨는데 저에겐 스승님이시네요 ㅎㅎ

참고해서 한번 해보겠습니다!!


by 마농 [2016.05.30 08:23:28]

글쎄요?
전월의 "미결"이 다음달의 "전월" 항목에 표시되어야 한다면?
"미결"과 "전월" 항목은 계속 누적되어야 하는것 아닌가요?
즉, 2016-04 의 "미결" 은 7 이 아닌 9가 되어야 하는게 아닌지?


그리고.
쿼리의 미결 또한 잘못 계산했네요.
접수건 중에는 취소건이 포함되어 있지 않은데도
미결 계산시 접수건에서 취소건을 차감하고 있네요.
취소가 이중 차감되었네요.


2016-04 의 경우
취소건 3건을 제외한 접수건 34건중
24건이 완료되고 10건이 미결이되며
여기에 전월의 미결건 2건까지 더해지면
총 12건이 미결로 나와야 할 듯.


by 마농 [2016.05.30 11:27:31]
SELECT *
  FROM (SELECT 집계월
             , @미결 AS 전월
             , 접수
             , 완료
             , 취소
             , @미결 := 접수 - 완료         AS 미결  -- 단순집계용
--           , @미결 := 접수 - 완료 + @미결 AS 미결  -- 누적집계용
          FROM (SELECT DATE_FORMAT(regdate, '%Y-%m') AS 집계월
                     , COUNT(CASE WHEN stat_cd >= 50 THEN 1 END) AS 접수
                     , COUNT(CASE WHEN stat_cd = 999 THEN 1 END) AS 완료
                     , COUNT(CASE WHEN stat_cd =  40 THEN 1 END) AS 취소
                  FROM req_service_tbl
                 WHERE regdate >= CONCAT('2016-01', '-01')
                 GROUP BY DATE_FORMAT(regdate, '%Y-%m')
                 ORDER BY 집계월
                ) a
             , (SELECT @미결 := 0) b
        ) a
 ORDER BY 집계월 DESC
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입