sql - 如何在月度数据系列中查找缺失的日期？

Question

我正在处理每月的小部件库存，偶尔会丢失几个月的数据。我想做一个“间隙和孤岛”类型分析，但我无法让实现正常工作（参见 sqlfiddle）。我正在尝试创建新列，专门列出间隙和岛的开始和停止日期：

http://www.sqlfiddle.com/#!18/a212a/2

任何帮助，将不胜感激

标签： sqlsql-servergaps-and-islands

Answer 1

我认为您需要以下内容。SQL小提琴

  WITH StartingPoints AS
     (SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY id, OCCURRANCE) AS rn
        FROM dates_test A
       WHERE NOT EXISTS (SELECT *
                FROM dates_test B
               WHERE B.ID = A.ID
                 AND EXTRACT(month FROM B.OCCURRANCE) =
                     EXTRACT(month FROM A.OCCURRANCE) - 1
                 and EXTRACT(year FROM B.OCCURRANCE) =
                     EXTRACT(year FROM A.OCCURRANCE))),
    EndingPoints AS
     (SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY id, OCCURRANCE) AS rn
        FROM dates_test A
       WHERE NOT EXISTS (SELECT *
                FROM dates_test B
               WHERE B.ID = A.ID
                 AND EXTRACT(month FROM B.OCCURRANCE) =
                     EXTRACT(month FROM A.OCCURRANCE) + 1
                 and EXTRACT(year FROM B.OCCURRANCE) =
                     EXTRACT(year FROM A.OCCURRANCE)))
    SELECT S.ID,
           EXTRACT(month FROM S.OCCURRANCE) AS start_range,
           EXTRACT(month FROM E.OCCURRANCE) AS end_range
      FROM StartingPoints S
      JOIN EndingPoints E
        ON E.ID = S.ID
       AND E.rn = S.rn;

您可以在下面找到 start_gap 和 end gap

                        WITH StartingPoints AS
     (SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY id, OCCURRANCE) AS rn
        FROM dates_test A
       WHERE NOT EXISTS (SELECT *
                FROM dates_test B
               WHERE B.ID = A.ID
                 AND EXTRACT(month FROM B.OCCURRANCE) =
                     EXTRACT(month FROM A.OCCURRANCE) - 1
                 and EXTRACT(year FROM B.OCCURRANCE) =
                     EXTRACT(year FROM A.OCCURRANCE))),
    EndingPoints AS
     (SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY id, OCCURRANCE) AS rn
        FROM dates_test A
       WHERE NOT EXISTS (SELECT *
                FROM dates_test B
               WHERE B.ID = A.ID
                 AND EXTRACT(month FROM B.OCCURRANCE) =
                     EXTRACT(month FROM A.OCCURRANCE) + 1
                 and EXTRACT(year FROM B.OCCURRANCE) =
                     EXTRACT(year FROM A.OCCURRANCE))),
    MissingPoints AS
     (SELECT S.ID,
             EXTRACT(month FROM S.OCCURRANCE) AS start_range,
             EXTRACT(month FROM E.OCCURRANCE) AS end_range,
             EXTRACT(YEAR FROM E.OCCURRANCE) YEAR_of_OCCR
        FROM StartingPoints S
        JOIN EndingPoints E
          ON E.ID = S.ID
         AND E.rn = S.rn),
    i1 as
     (select level num from dual connect by level <= 12),
    ms11 as
     (select ID,
             start_range,
             end_range,
             lead(start_range, 1, 0) OVER(ORDER BY id, year_of_occr, start_range, end_range) as am_i_ms,
             lead(year_of_occr, 1, 0) OVER(ORDER BY id, year_of_occr) as miss_year,
             year_of_occr
        from MissingPoints),
    miss_month1 as
     (select id,
             start_range,
             end_range,
             DECODE(end_range + num, 13, 0, 14, 0, end_range + num) missing_month,
             year_of_occr
        from ms11, i1
       where ((end_range + num < am_i_ms or sTART_Range = end_range) and
             end_range + num <= 14) or (year_of_occr<> miss_year and am_i_ms >=0 and  am_i_ms <=12 and end_range + num <= 14)
       order by year_of_occr, missing_month),
    miss_month as
     (select *
        from miss_month1 A
       where not exists
       (select 1
                from miss_month1 B
               where A.ID = B.ID
                 AND (A.missing_month = B.start_range AND
                     A.missing_month = B.end_range)
                 and A.year_of_occr = B.year_of_occr)
         and decode(end_range, 12, -1, end_range) < missing_month),
    StartingmisPoints AS
     (SELECT A.*,
             ROW_NUMBER() OVER(ORDER BY id, year_of_occr, end_range, missing_month) AS rn
        FROM miss_month A
       WHERE NOT EXISTS (SELECT *
                FROM miss_month B
               WHERE B.ID = A.ID
                 and b.start_range = a.start_range
                 and b.end_range = a.end_range
                 AND B.missing_month = A.missing_month - 1
                 and b.year_of_occr = a.year_of_occr)),
    EndingmisPoints AS
     (SELECT A.*,
             ROW_NUMBER() OVER(ORDER BY id, year_of_occr, end_range, missing_month) AS rn
        FROM miss_month A
       WHERE NOT EXISTS (SELECT *
                FROM miss_month B
               WHERE B.ID = A.ID
                 AND B.missing_month = A.missing_month + 1
                 and b.start_range = a.start_range
                 and b.end_range = a.end_range
                 and b.year_of_occr = a.year_of_occr))
    SELECT distinct  S.ID,
           S.start_range,
           S.end_range,
           S.missing_month start_gap_range,
           E.missing_month end_gap_range,
           E.year_of_occr
      FROM StartingmisPoints S
      JOIN EndingmisPoints E
        ON E.ID = S.ID
       AND E.rn = S.rn