查找序列中的缺失日期 [英] Finding missing dates in a sequence

问题描述

我有ID和DATE的下表

I have following table with ID and DATE

ID   DATE
123   7/1/2015
123   6/1/2015
123   5/1/2015
123   4/1/2015
123   9/1/2014
123   8/1/2014
123   7/1/2014
123   6/1/2014
456   11/1/2014
456   10/1/2014
456   9/1/2014
456   8/1/2014
456   5/1/2014
456   4/1/2014
456   3/1/2014
789   9/1/2014
789   8/1/2014
789   7/1/2014
789   6/1/2014
789   5/1/2014
789   4/1/2014
789   3/1/2014

在此表中，我有三个客户ID，分别为123、456、789和日期列，该列显示了他们工作的月份.

In this table, I have three customer ids, 123, 456, 789 and date column which shows which month they worked.

我想找出哪些客户的工作存在差距. 我们的客户工作记录是每月保存的，因此日期是每月的. 每个客户都有不同的开始和结束日期.

I want to find out which of the customers have gap in their work. Our customers work record is kept per month...so, dates are monthly.. and each customer have different start and end dates.

预期结果:

ID     First_Absent_date

123    10/01/2014
456    06/01/2014

推荐答案

要获得带间隙的ID的简单列表(没有更多详细信息)，您需要分别查看每个ID，按照@mikey的建议，您可以计数月数，并查看第一个和最后一个日期，看是否跨越了几个月.

To get a simple list of the IDs with gaps, with no further details, you need to look at each ID separately, and as @mikey suggested you can count the number of months and look at the first and last date to see if how many months that spans.

如果您的表中有一个名为month的列(因为除非包含引号的标识符，否则不允许使用date)，您可以从以下内容开始:

If your table has a column called month (since date isn't allowed unless it's a quoted identifier) you could start with:

select id, count(month), min(month), max(month),
  months_between(max(month), min(month)) + 1 as diff
from your_table
group by id
order by id;

        ID COUNT(MONTH) MIN(MONTH) MAX(MONTH)       DIFF
---------- ------------ ---------- ---------- ----------
       123            8 01-JUN-14  01-JUL-15          14
       456            7 01-MAR-14  01-NOV-14           9
       789            7 01-MAR-14  01-SEP-14           7

然后在having子句中将计数与月份跨度进行比较:

Then compare the count with the month span, in a having clause:

select id
from your_table
group by id
having count(month) != months_between(max(month), min(month)) + 1
order by id;

        ID
----------
       123
       456

如果一个月中实际上可以有一个ID的多个记录，并且/或者记录的日期可能不是该月的开始，那么您可以做更多的工作来标准化日期:

If you can actually have multiple records in a month for an ID, and/or the date recorded might not be the start of the month, you can do a bit more work to normalise the dates:

select id,
  count(distinct trunc(month, 'MM')),
  min(trunc(month, 'MM')),
  max(trunc(month, 'MM')),
  months_between(max(trunc(month, 'MM')), min(trunc(month, 'MM'))) + 1 as diff
from your_table
group by id
order by id;

select id
from your_table
group by id
having count(distinct trunc(month, 'MM')) !=
  months_between(max(trunc(month, 'MM')), min(trunc(month, 'MM'))) + 1
order by id;

这篇关于查找序列中的缺失日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！