database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41287 게시물 읽기
No. 41287
일자별 전월, 당월, 증감 통계 쿼리 질문입니다.
작성자
이담원
작성일
2016-11-24 02:19ⓒ
2016-11-24 02:26ⓜ
조회수
7,887
아래 일자별 품목별 판매건수, 매출금액 데이터로 일자별 전월, 당월, 증감 통계를 구하려고 합니다.
 
>> 데이터
일자       품목     판매건수     매출금액
20160601   정육         1          100
20160601   주류         2          200
20160602   야채         3          200
20160602   정육         2          100
...
20160701   주류         3          300
20160701   야채         4          500
20160701   정육         2          200
20160702   주류         1          200
20160702   야채         5          200
20160703   정육         1          100
20160703   주류         4          300
....
 
>> 결과
 
당월일자     구분         정육                     주류                    야채                   합계
                                      전월 당월 증감   전월 당월 증감  전월 당월 증감  전월 당월 증감
20160701 판매건수   1       2      1         2       3      1        2       3      1
20160701 매출금액   100  200 100     200  300 100    200  300 100    ...
20160702 판매건수   1       2      1         2       3      1        2       3      1
20160702 매출금액   100  200  100    200  300 100    200  300 100    ...
합계        판매건수   2       4       2        2       3     1         2      3       1
합계        매출금액   200  400   200     ...
 
 
unpivot을 사용하여 당월까지는 조회하였습니다만
전월 같은 일자에 값과 비교하여 증감을 표현하려니 
고수님들께 조언을 얻어야 할 것 같습니다.
 
이 글에 대한 댓글이 총 4건 있습니다.

WITH t AS
(
SELECT '20160601' dt, '정육' cd, 1 cnt, 100 amt FROM dual
UNION ALL SELECT '20160601', '주류', 2, 200 FROM dual
UNION ALL SELECT '20160602', '야채', 3, 200 FROM dual
UNION ALL SELECT '20160602', '정육', 2, 100 FROM dual
UNION ALL SELECT '20160701', '주류', 3, 300 FROM dual
UNION ALL SELECT '20160701', '야채', 4, 500 FROM dual
UNION ALL SELECT '20160701', '정육', 2, 200 FROM dual
UNION ALL SELECT '20160702', '주류', 1, 200 FROM dual
UNION ALL SELECT '20160702', '야채', 5, 200 FROM dual
UNION ALL SELECT '20160703', '정육', 1, 100 FROM dual
UNION ALL SELECT '20160703', '주류', 4, 300 FROM dual
)
SELECT *
  FROM (SELECT NVL(ymd, '합계') 당월일자
             , gb 구분
             , NVL(cd, '합계') cd
             , NVL(SUM(DECODE(mm, '전월', v)), 0) 전월
             , NVL(SUM(DECODE(mm, '당월', v)), 0) 당월
             , NVL(SUM(DECODE(mm, '당월', v)), 0)
             - NVL(SUM(DECODE(mm, '전월', v)), 0) 증감
          FROM (SELECT ym || SUBSTR(dt, 7, 2) ymd
                     , DECODE(SUBSTR(dt, 1, 6), ym, '당월', '전월') mm
                     , cd
                     , cnt
                     , amt
                  FROM t
                     , (SELECT ym
                             , TO_CHAR(
                               ADD_MONTHS(TO_DATE(ym, 'yyyymm'), -1)
                               , 'yyyymmdd') ym_b
                          FROM (SELECT '201607' ym FROM dual) -- 조회년월
                        )
                 WHERE dt >= ym_b
                   AND dt <= ym||'31'
                )
         UNPIVOT (v FOR gb IN (cnt AS '판매건수', amt AS '매출금액'))
         GROUP BY gb, CUBE(cd, ymd)
        )
 PIVOT ( SUM(전월) 전월, SUM(당월) 당월, SUM(증감) 증감
         FOR cd IN ('정육' 정육, '주류' 주류, '야채' 야채, '합계' 합계) )
 ORDER BY 당월일자, 구분 DESC
;

마농(manon94)님이 2016-11-24 10:37에 작성한 댓글입니다.

마농님 답변 감사합니다. 많은 공부가 되었습니다.

이담원님이 2016-11-24 23:00에 작성한 댓글입니다. Edit

 마농님의 댓글이 훌륭하시기는 한데요....

 

그전에 전월 동일의 의미를 고객에게 꼭 확인하셔야 할 거 같아요...

 

add_mpnths( date , -1) 을 사용하는 것이 어떤 경우 적절하지 않을 수 있어요...

예를 들어 윤년의 2월 29일의 전월은 1월 31일입니다.

윤년의 2월 28일의 전월은 1월 28일 입니다.

윤년이 아닌 경우는 2월28일의 전월이 1월31일이구요..

 

마찬가지로 말일의 전월은 말일입니다. 이게 대부분 응용에서 그냥 사용해도 문제가 없는 이유이기는 하지만

 

특수한 경우는 아닐 수도 있으니 반드시 고객에게 확인하시기를....

김흥수(protokhs)님이 2016-12-01 13:32에 작성한 댓글입니다.

저도 그부분은 작성 당시 많은 고민을 했던 부분입니다.
2월 기준 조회시 28일까지만 보여줘야 할지 하는 부분을 고민했구요.
저는 전월인 31일까지 모두 보여주는 것이 옳다라고 판단했습니다.
월별 합계도 함께 보여지기 때문에 1월의 몇일이 누락되면 안된다는 생각이었죠.


그리고 제가 시용한 add_months 는 일단위로 전월 동일일자를 가져오기 위함이 아닙니다.
그랬다면 김흥수님께서 지적하신 대로 문제가 됩니다.
하지만 저는 일단위가 아닌 월단위 전월을 가져오기 위해 사용했을 뿐입니다.
전월 당월 일자비교는 add_months 가 아닌 Group By 일자로 수행됩니다.

마농(manon94)님이 2016-12-01 14:42에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41290영업일 구하는 함수 만들고 싶은데요 잘 모르겠네요.. [1]
박대종
2016-11-29
6159
41289오라클 조인과 ANSI 조인과 관련한 질문입니다.
김흥수
2016-11-28
6259
41288계층형 쿼리관련 질문입니다.... [1]
윤주호
2016-11-24
6748
41287일자별 전월, 당월, 증감 통계 쿼리 질문입니다. [4]
이담원
2016-11-24
7887
41285중복관련 쿼리 질문입니다 [1]
김홍석
2016-11-18
6667
41284connect by 이후 조인할때 order by 깨지는것 문의드려요... [4]
서기라
2016-11-17
6842
41282Java에서, 다른 pc에 있는 dbms연결이 안됩니다. 뭐가 문제죠? [1]
최현일
2016-11-13
6293
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.021초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다