sql-server - 根据日期范围选择最大数量的记录。 (彼此之间的日期在 6 个月内)

Question

我有一个日期列表。

我需要能够选择最多 6 个月内的记录。

然后是下一个最大数量的记录，依此类推，直到选择完所有记录。

这是数据

1  19-Oct-2007
2  03-Dec-2007
3  16-Oct-2009
4  26-Oct-2009
5  30-Oct-2009
6  01-Nov-2009
7  16-Nov-2009
8  30-Nov-2009
9  11-Dec-2009
10  25-Dec-2009
11  01-Jan-2010
12  21-Jan-2010
13  27-Jan-2010
14  28-Jan-2010
15  28-Jan-2010
16  12-Feb-2010
17  12-Feb-2010
18  27-Feb-2010
19  09-Mar-2010
20  22-Mar-2010
21  26-Mar-2010
22  01-Apr-2010
23  22-Oct-2010
24  15-Oct-2011
25  18-Oct-2011
26  26-Oct-2011
27  16-Nov-2011
28  18-Nov-2011
29  19-Nov-2011
30  26-Nov-2011
31  29-Nov-2011
32  29-Nov-2011
33  30-Nov-2011
34  06-Dec-2011
35  16-Dec-2011
36  17-Dec-2011
37  20-Dec-2011
38  28-Dec-2011
39  01-Jan-2012
40  01-Jan-2012
41  09-Jan-2012
42  13-Jan-2012
43  27-Jan-2012
44  01-Feb-2012
45  23-Feb-2012
46  29-Feb-2012
47  01-Mar-2012
48  01-Mar-2012
49  01-Mar-2012
50  02-Mar-2012
51  04-Mar-2012
52  04-Mar-2012
53  05-Mar-2012
54  05-Mar-2012
55  17-Mar-2012
56  23-Mar-2012
57  24-Mar-2012
58  01-Apr-2012
59  03-Apr-2012
60  04-Apr-2012

一个可能的解决方案是选择

• 记录 24-60(它们彼此相隔 172 天以内)
• 记录 23(不在前一个/后一个日期的 6 个月内)
• 记录 3-22(它们彼此相隔 167 天以内)
• 记录 1-2(彼此相隔 45 天以内)

(我是从最大的日期开始往后选的，这可能不是最优解)

Answer 1

以下是解决该问题的迭代方法，目前我没有比这更好的建议了。不过，它应该可以工作:

WITH ranked AS (
  SELECT *, rnk = ROW_NUMBER() OVER (ORDER BY Date DESC)
  FROM data
),
marked AS (
  SELECT
    rnk,
    Date,
    GroupDate = date
  FROM ranked
  WHERE rnk = 1
  UNION ALL
  SELECT
    r.rnk,
    r.Date,
    GroupDate = CASE
      WHEN m.GroupDate > DATEADD(MONTH, 6, r.Date) THEN r.Date
      ELSE m.GroupDate
    END
  FROM ranked r
  INNER JOIN marked m ON r.rnk = m.rnk + 1
)
SELECT
  MinDate     = MIN(Date),
  MaxDate     = MAX(Date),
  [RowCount]  = COUNT(*),
  RangeLength = DATEDIFF(DAY, MIN(Date), MAX(Date))
FROM marked
GROUP BY
  GroupDate
ORDER BY
  GroupDate

也就是说，

1. 最后一个日期被记录并用于范围检查和组标记。

2. 后续(之前)日期会被处理，直到遇到标记的时间早半年以上。

3. 找到的日期成为新的组标记，该过程从第 1 步继续，直到没有更多的行。

在继续迭代之前，对行进行排序。但是，如果您有一列保证包含无间隙的唯一序列值，则可以使用该列而不是排名数字。

这是它为原始帖子中的示例提供的结果:

MinDate     MaxDate     RowCount     RangeLength
----------  ----------  -----------  -----------
2007-10-19  2007-12-03  2            45
2009-10-16  2010-04-01  20           167
2010-10-22  2010-10-22  1            0
2011-10-15  2012-04-04  37           172

可以使用on SQL Fiddle 找到并播放整个脚本，包括设置。 .