카운트 최대 시퀀스 행 (Count Max Sequence row)

문제 설명

카운트 최대 시퀀스 행 (Count Max Sequence row)

날짜 정보가 있는 테이블이 있습니다(예: 201501,201502,201510 등). 행은 varchar이지만 yyymm 형식 날짜를 참조합니다.

예:

dates
‑‑‑
201501
201502
201503
201505
201506
201507
201508
201509
201510
201512
201601
201602

시퀀스 월이지만 '201504' 및 '201511'이 누락되었습니다.

각 누락된 날짜까지 rownum을 계산하고 싶습니다. 예: '201504'까지 3행이 있고 '201504' 이후 다음 누락 날짜('201511')까지 6행이 있습니다. 마지막으로 2열입니다.

참조 솔루션

방법 1:

You can do this with a difference of a row numbers from a months count to assign a group id to the sequences. So, the following identifies such sequences:

select min(d), max(d), count(*)
from (select t.*,
             (to_number(substr(d, 1, 4)) * 12 + to_number(substr(d, 5, 2)) ‑
              row_number() over (order by d)
             ) as grp
      from t
     ) t
group by grp;

You can get the max length as:

with cte as (
      select min(d), max(d), count(*) as len
      from (select t.*,
                   (to_number(substr(d, 1, 4)) * 12 + to_number(substr(d, 5, 2)) ‑ 
                    row_number() over (order by d)
                   ) as grp
            from t
           ) t
      group by grp
     )
select max(len)
from cte;

방법 2:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( dates ) AS
          SELECT 201501 FROM DUAL
UNION ALL SELECT 201502 FROM DUAL
UNION ALL SELECT 201503 FROM DUAL
UNION ALL SELECT 201505 FROM DUAL
UNION ALL SELECT 201506 FROM DUAL
UNION ALL SELECT 201507 FROM DUAL
UNION ALL SELECT 201508 FROM DUAL
UNION ALL SELECT 201509 FROM DUAL
UNION ALL SELECT 201510 FROM DUAL
UNION ALL SELECT 201512 FROM DUAL
UNION ALL SELECT 201601 FROM DUAL
UNION ALL SELECT 201602 FROM DUAL

Query 1:

SELECT MAX( diff ) AS result
FROM (
  SELECT CASE WHEN end_grp IS NOT NULL THEN 1
              ELSE LEAD( end_grp ) IGNORE NULLS OVER ( ORDER BY DATES ) ‑ dates + 1 END as diff
  FROM (
    SELECT DATES,
           CASE next_date WHEN dates+1 THEN NULL ELSE dates END AS end_grp
    FROM (
      SELECT DATES,
             LEAD( DATES ) OVER ( ORDER BY DATES ) AS next_date
      FROM   TABLE_NAME
    )
  )
)

Results:

| RESULT |
|‑‑‑‑‑‑‑‑|
|      6 |

(by Onur Cete、Gordon Linoff、MT0)

참조 문서

1. Count Max Sequence row (CC BY‑SA 2.5/3.0/4.0)