고수님들의 도움을 받고자 쿼리 문의 드립니다.
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일경과) |
다음과 같이 카운트를 하고 싶습니다.
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
-- 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 ;
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이 찍혀야 하는데..
구현이 쉬울까요. ?
우리집 아찌님.
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예제를 아무리 따라해봐도 저렇게 에러가 나네요.
저는 직전 일자가 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 ;