기간 내 카운트 문의 0 11 319

by 임태원 [Oracle 기초] [2018.01.09 18:15:56]


고수님들의 도움을 받고자 쿼리 문의 드립니다.

Raw는 다음과 같이 쌓입니다.

ID 날짜 제품 판매
1 20170801 a O
2 20170801 b X
3 20170801 c X
4 20171031 a O
5 20171031 b O
6 20171031 c X
7 20180203 a X
8 20180203 b O
9 20180203 c O

그 날짜에 그 제품에 E 열에 판매누적건을 쓰고 싶습니다.

대신 조건이 판매 날짜로부터 90일 이내 이런 조건으로요.

ID 날짜 제품 판매 판매누적
1 20170801 a O 1
2 20170801 b X 0
3 20170801 c X 0
4 20171031 a O 2
5 20171031 b O 1
6 20171031 c X 0
7 20180303 a X 0(최종날짜로부터 90일경과)
8 20180303 b O 1(최종날짜로부터 90일경과)
9 20180303 c O 1(최종날짜로부터 90일경과)

 다음과 같이 카운트를 하고 싶습니다.

 

 

 

 

 

by 우리집아찌 [2018.01.09 18:54:59]
WITH T (ID,DT,ITEM,SALE) AS (
SELECT  1, '20170801' ,'a' ,'O' FROM DUAL UNION ALL
SELECT  2, '20170801' ,'b' ,'X' FROM DUAL UNION ALL
SELECT  3, '20170801' ,'c' ,'X' FROM DUAL UNION ALL
SELECT  4, '20171031' ,'a' ,'O' FROM DUAL UNION ALL
SELECT  5, '20171031' ,'b' ,'O' FROM DUAL UNION ALL
SELECT  6, '20171031' ,'c' ,'X' FROM DUAL UNION ALL
SELECT  7, '20180203' ,'a' ,'X' FROM DUAL UNION ALL
SELECT  8, '20180203' ,'b' ,'O' FROM DUAL UNION ALL
SELECT  9, '20180203' ,'c' ,'O' FROM DUAL 
)
SELECT ID , DT , ITEM , SALE 
     , SUM(CASE WHEN SALE = 'O' THEN 1 ELSE 0 END ) OVER(PARTITION BY ITEM ORDER BY TO_DATE(DT,'YYYYMMDD') 
                                                    RANGE BETWEEN INTERVAL '99' DAY PRECEDING  AND INTERVAL '1' DAY PRECEDING     
           ) SUM_SALE
  FROM T 
 ORDER BY ID , ITEM


 


by 마농 [2018.01.10 07:30:15]

1. 스칼라서브쿼리라서 안되는건 아닐 듯 하네요.
  에러메시지를 꼼꼼히 확인해 보세요.
  날짜 -90 의 계산식이나, 날짜비교 등에서 형식이 맞지 않아서 나는 에러가 아닐까? 생각되네요.
  해당 날짜 항목의 데이터 타입이 무엇인지 확인해 보세요.
  계산이나 비교식 등은 데이터 타입을 맞춰줘야 합니다.
2. 판매누적 컬럼이 뭔지 잘 모르겠네요.
  설명이나 예시자료가 이해하기 어렵네요.


by 마농 [2018.01.10 09:35:38]
-- PostgreSQL --
WITH t AS
(
SELECT 1 id, '20170801' dt, 'a' cd, 'O' ox
UNION ALL SELECT 2, '20170801', 'b', 'X'
UNION ALL SELECT 3, '20170801', 'c', 'X'
UNION ALL SELECT 4, '20171031', 'a', 'O'
UNION ALL SELECT 5, '20171031', 'b', 'O'
UNION ALL SELECT 6, '20171031', 'c', 'X'
UNION ALL SELECT 7, '20180203', 'a', 'X'
UNION ALL SELECT 8, '20180203', 'b', 'O'
UNION ALL SELECT 9, '20180203', 'c', 'O'
)
SELECT id, dt, cd, ox
     , SUM(v) OVER(PARTITION BY cd, grp ORDER BY dt) cnt
  FROM (SELECT id, dt, cd, ox
             , CASE ox WHEN 'O' THEN 1 ELSE 0 END v
             , SUM(flag) OVER(PARTITION BY cd ORDER BY dt) grp
          FROM (SELECT id, dt, cd, ox
                     , CASE WHEN LAG(dt) OVER(PARTITION BY cd ORDER BY dt)
                              >= TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 91, 'yyyymmdd')
                            THEN 0 ELSE 1 END flag
                  FROM t
                ) a
        ) a
 ORDER BY id
;

 


by 임태원 [2018.01.10 10:41:05]
 WITH t AS
(
SELECT 1 receipt_no, '20170801' report_Date, 'a' made_no, 'O' repair_code
UNION ALL SELECT 2, '20170801', 'b', 'X'
UNION ALL SELECT 3, '20170801', 'c', 'X'
UNION ALL SELECT 4, '20171031', 'a', 'O'
UNION ALL SELECT 5, '20171031', 'b', 'O'
UNION ALL SELECT 6, '20171031', 'c', 'X'
UNION ALL SELECT 7, '20171203', 'a', 'O'
UNION ALL SELECT 8, '20171203', 'b', 'O'
UNION ALL SELECT 9, '20171203', 'c', 'O'
UNION ALL SELECT 10, '20180203', 'a', 'O'
UNION ALL SELECT 11, '20180203', 'b', 'O'
UNION ALL SELECT 12, '20180203', 'c', 'O'
)
SELECT receipt_no, report_date, made_no, repair_code
     , SUM(v) OVER(PARTITION BY made_no, grp ORDER BY report_date) cnt
  FROM (SELECT receipt_no, report_date, made_no, repair_code
             , CASE repair_code WHEN 'O' THEN 1 ELSE 0 END v
             , SUM(flag) OVER(PARTITION BY made_no ORDER BY report_date) grp
          FROM (SELECT receipt_no, report_date, made_no, repair_code
                     , CASE WHEN LAG(report_Date) OVER(PARTITION BY made_no ORDER BY report_date)
                              >= TO_CHAR(TO_DATE(report_date, 'yyyymmdd') - 91, 'yyyymmdd')
                            THEN 0 ELSE 1 END flag
                  FROM t
                ) a
        ) a
 ORDER BY receipt_no

 

위와 같이 쿼리가 되면.
10번 ID에는 사실 1번의 A와는 90일이 지난 시점이라 4번7번과 10번까지 카운트 되어 4가 아닌 3이 찍혀야 하는데..
구현이 쉬울까요. ?


by 임태원 [2018.01.10 10:00:17]

우리집 아찌님.

  select 날짜 ,제품정보1,제품정보2
    ,count(1) over (partition by 제품정보1,제품정보2 order by 날짜 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    from (select * from 테이블1) a 

이렇게 하면 그냥 날짜 상관없이 RanK처럼 카운트가 가능합니다.

하지만

위 그림과 같이 BETWEEN 절에 INTERVAL 이 들어가면 ERROR가 발생합니다.

에러는 그냥 SYNTAX ERROR 이구요.

혹시 조언을 좀 받을수 있을까요?
구글링을 해봐도 UNBOUNDED나 CURRENT ROW 는 예제를 따라해도 에러가 나지 않는데..
INTERVAL예제를 아무리 따라해봐도 저렇게 에러가 나네요.


by 우리집아찌 [2018.01.10 10:01:15]

오라클 버젼이 어떻게 되시나요?


by 임태원 [2018.01.10 10:35:15]

PostgreSQL 9.2

사용중입니다.


by 우리집아찌 [2018.01.10 11:26:50]

Postgres는 확인 못했습니다.

오라클11g에서 테스트 했습니다.


by 마농 [2018.01.10 10:47:21]

요건에 대한 설명이 명확하지 않아 임으로 판단하여 구현했던 것입니다.
질문의 예시자료의 결과에 맞추기 위해 - 91 로 입력했습니다.
90 으로 바꾸세요.


by 마농 [2018.01.10 11:09:35]

저는 직전 일자가 90일 이내이면 계속 연결되도록 구현한 것입니다.
90일 이내만 카운트하도록 구현한게 아닙니다.
그리고 90일이내라고 하시는데 원하시는 결과는 91일이네요? ( 8/1 ~ 10/31 = 91일 차이)
 

SELECT a.receipt_no, a.report_Date, a.made_no, a.repair_code
     , COUNT(b.receipt_no) cnt
  FROM t a
  LEFT OUTER JOIN t b
    ON b.made_no = a.made_no
   AND b.report_date >= TO_CHAR(TO_DATE(a.report_date, 'yyyymmdd') - 91, 'yyyymmdd')
   AND b.report_date <= a.report_date
   AND b.repair_code = 'O'
 GROUP BY a.receipt_no, a.report_Date, a.made_no, a.repair_code
 ORDER BY a.receipt_no
;

 


by 임태원 [2018.01.10 11:32:50]

정말 고민많이 했던 부분을..
쉽게 풀어주시다니...
항상 좋은 조언 듣고 갑니다.

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