데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 38999 게시물 읽기

No. 38999

연속된값들의 갯수세기
작성자	이인호	작성일	2011-10-24 21:04ⓒ 2011-10-24 23:00ⓜ	조회수	7,462

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 하는게 제겐 벅차네요.

도움을 요청드립니다.

이 글에 대한 댓글이 총 5건 있습니다.

애매한부분이 있는거 같은데요

예를 들어서

11:01

11:10

11:20

11:30

11:50

12:05

위와 같은 데이터가 있을시 위에서 5개는 연속된값이고 아래서부터 5개는 다시 연속된값인데..

어떤 결과집합이 나와야 하는건지 애매하네요....

이동호(ssamldh)님이 2011-10-24 21:52에 작성한 댓글입니다.

네 질문을 잘 못드린거 같습니다.

제가 원하는 값은 ENTRY_TM 값이 굵게 표시되어있는

110930 41880 12212 174200
110930 41880 20811 174200
110930 41880 12212 174200
110930 41880 52832 174300
110930 41880 20331 174400

과

110930 41880 60631 180000
110930 41880 02131 180100
110930 41880 61011 180100
110930 41880 70641 180200
110930 41880 12321 180200
110930 41880 51721 180200
110930 41880 60812 180300
110930 41880 20732 180400

값이 모두 나오면 됩니다.

여기서 첫번째 그룹의 ENTRY_TM 값이 첫번째 값과 두번째값의 차이가 1분이내

두번째에서 세번째 차이가 1분이내 세번째 네번째 차이도 역시 1분이내...

이런식으로 1분이내의 값이 연속적으로 5개 이상인경우만 추리고 싶습니다.

두번째 그룹군도 역시 1분이내의 값들이지요.

제가 고민인게... 순차적으로 1분이내인 값 연산은 가능하나

이걸 어떻게 그룹으로 나누고 그게 동일한 그룹인지를 인지할수가 있는지가 고민입니다.

USER_ID 값으로 PARTITON 을 나눌수만 있어도 가능할텐데 그런 그룹으로 잡을 컬럼이 없는게 문제입니다.

이인호님이 2011-10-24 23:02에 작성한 댓글입니다. Edit Ｘ

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
  DATE1
    ,OGR_NO
    ,USER_ID
    ,ENTRY_TM
FROM
(
     SELECT
        DATE1
        ,OGR_NO
        ,USER_ID
        ,ENTRY_TM
        ,SUM(1) OVER (PARTITION BY SUBSTR(ENTRY_TM,0,3)) as CNT
    FROM T
    ORDER BY ENTRY_TM
)
WHERE CNT >= 5

조금이나마 도움이 되셨으면 합니다.

초보님이 2011-10-25 16:47에 작성한 댓글입니다. Edit Ｘ

초보님 답변에 감사드립니다만

단순히 substr 해서 되는건 아니고요

공교롭게 에제가 앞의 세자리가 같아서 그런 결과가 나온겁니다.

답변에 감사드립니다.

이인호님이 2011-10-25 22:56에 작성한 댓글입니다. Edit Ｘ

SELECT date1, ogr_no, user_id, entry_tm
     , grp, cnt
FROM (
        SELECT date1, ogr_no, user_id, entry_tm
             , grp
             , COUNT(*) OVER(PARTITION BY date1, ogr_no, grp) cnt
          FROM (
                SELECT date1, ogr_no, user_id, entry_tm
                     , SUM(flag) OVER(PARTITION BY date1, ogr_no ORDER BY entry_tm) grp
                  FROM (
                        SELECT date1, ogr_no, user_id, entry_tm
                             , CASE
                               WHEN TO_CHAR(TO_DATE(entry_tm, 'hh24miss') - 1/24/60, 'hh24miss')
                                 <= LAG(entry_tm) OVER(PARTITION BY date1, ogr_no ORDER BY entry_tm)
                               THEN 0 ELSE 1 END flag
                          FROM t
                        )
                )
        )
WHERE cnt >= 5
;

마농(manon94)님이 2011-10-26 14:23에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
39006	테이블 조인하여 UPDATE [1]	질문	2011-10-25	4399
39005	select 한 날자값(300개) 가로 출력 [3]	강영호	2011-10-25	5199
39004	전체의 값에서 나눌때 반올림시 숫자를 정확히 맞추기 [3]	윤명철	2011-10-25	4302
38999	연속된값들의 갯수세기 [5]	이인호	2011-10-24	7462
38998	프로시저안에서 원격지 프로시저를 호출할수 있나요? [2]	박주영	2011-10-24	4067
38997	UI에 쿼리 결과 뿌릴때 결과 건수를 함께 ? [3]	궁금이	2011-10-24	3814
38996	오라클 슬로우 쿼리 확인하는 법 [1]	장이석	2011-10-23	4146