WITH T AS (
SELECT '110930' DATE1, '41880' OGR_NO, '92831' USER_ID, '102000' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '10144' USER_ID, '102000' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '62611' USER_ID, '140500' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '60532' USER_ID, '140500' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '03035' USER_ID, '140800' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '03042' USER_ID, '140900' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '03042' USER_ID, '142100' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '03035' USER_ID, '142100' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '51821' USER_ID, '154500' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '70431' USER_ID, '154500' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '10641' USER_ID, '161000' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '23031' USER_ID, '161100' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '42032' USER_ID, '162900' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '72011' USER_ID, '162900' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '32311' USER_ID, '171500' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '32311' USER_ID, '171600' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '32021' USER_ID, '171700' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '02211' USER_ID, '172300' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '70212' USER_ID, '172400' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '12212' USER_ID, '174200' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '20811' USER_ID, '174200' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '12212' USER_ID, '174200' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '52832' USER_ID, '174300' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '20331' USER_ID, '174400' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '60631' USER_ID, '180000' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '02131' USER_ID, '180100' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '61011' USER_ID, '180100' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '70641' USER_ID, '180200' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '12321' USER_ID, '180200' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '51721' USER_ID, '180200' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '60812' USER_ID, '180300' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '20732' USER_ID, '180400' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '40912' USER_ID, '184700' ENTRY_TM FROM DUAL UNION ALL
SELECT '110930' DATE1, '41880' OGR_NO, '01611' USER_ID, '184700' ENTRY_TM FROM DUAL
)
SELECT * FROM T ORDER BY ENTRY_TM;
위와 같은 데이터가 있습니다.
일자/조직코드별 사용자(USER_ID) 의 등록시간입니다.
ENTRY_TM 으로 SORT 를 해보면 ENTRY_TM 이 1분이내인 연속적인 값들이 있습니다.
여기에서 1분이내 등록이 되어있는 연속된값이 5개 이상인 데이터를 찾고 싶습니다.
굵은색 글씨로 되어있는 174200 부터 174400 까지
그리고 180000 부터 180400 까지가 되겠네요.
나머지는 1분이내의 연속적인 값들이긴 하나 5개가 되지 않는 관계로 제외가 되면 됩니다.
일단 RANGE BETWEEN INTERVAL 로 1분이내의 값들은 다 찾아내긴 했느데 그 이후부터가 해결이 안됩니다.
앞뒤값 비교는 하겠는데 앞으로 진행하면서 1분이내인값들을 누적시켜서 COUNT 하는게 제겐 벅차네요.
도움을 요청드립니다.
|