WITH t AS ( SELECT '2018-08-03 오후 1:47:47' DT, '1' CARD_NO, '1234' BIZ_NO, 50000 AMT FROM DUAL UNION ALL SELECT '2018-08-03 오후 2:42:46', '1', '1234', 100000 FROM DUAL UNION ALL SELECT '2018-08-21 오후 2:19:56', '2' ,'4567', 50000 FROM DUAL UNION ALL SELECT '2018-08-24 오후 5:10:12', '2' ,'1456', 10000 FROM DUAL ) SELECT * FROM t;
결과
DT | CARD_NO | BIZ_NO | AMT |
2018-08-03 오후 1:47:47 | 1 | 1234 | 50000 |
2018-08-03 오후 2:42:46 | 1 | 1234 | 100000 |
카드사용내역 중에 동일 가맹점(BIZ_NO)에서 동일카드(CARD_NO)로 2시간 이내에 사용한 이력을 위와 같이 가져오고 싶습니다.
LAG 함수를 써서 이전 사용내역을 불러와서 사용 시간간격을 비교해서 처리해보려고 해봤는데 잘못 잡고 있는지 해서 문의드립니다.
WITH t AS ( SELECT '2018-08-03 오후 1:47:47' dt, '1' card_no, '1234' biz_no, 50000 amt FROM dual UNION ALL SELECT '2018-08-03 오후 2:42:46', '1', '1234', 100000 FROM dual UNION ALL SELECT '2018-08-21 오후 2:19:56', '2', '4567', 50000 FROM dual UNION ALL SELECT '2018-08-24 오후 5:10:12', '2', '1456', 10000 FROM dual ) , t1 AS ( -- 0. dt 는 Date Type 이라고 가정합니다. SELECT TO_DATE(dt, 'yyyy-mm-dd pm hh:mi:ss') dt , card_no, biz_no, amt FROM t ) -- 1. Lag/Lead 이용하여 시간 계산 SELECT * FROM (SELECT dt, card_no, biz_no, amt , LAG (dt) OVER(PARTITION BY card_no ORDER BY dt) dt_lag , LEAD(dt) OVER(PARTITION BY card_no ORDER BY dt) dt_lead FROM t1 ) WHERE dt - dt_lag <= 2/24 OR dt_lead - dt <= 2/24 ; -- 2. range 를 이용하여 interval 범위 지정 SELECT * FROM (SELECT dt, card_no, biz_no, amt , COUNT(*) OVER(PARTITION BY card_no ORDER BY dt RANGE BETWEEN INTERVAL '2' HOUR PRECEDING AND INTERVAL '2' HOUR FOLLOWING ) cnt FROM t1 ) WHERE cnt > 1 ;