sql - 按日期范围消除行

Question

我有以下内容:

Index   dateOfInquiry   
649454      2016-02-05 
649455      2016-02-05   

我有这个查询:

SELECT COUNT(a.dateOfInquiry) as NumberRecords
FROM 
( 
    SELECT ROW_NUMBER() OVER(ORDER BY dateOfInquiry ASC) 
        Id, dateOfInquiry

    FROM 
    Table
) a 

INNER JOIN 
(
    SELECT ROW_NUMBER() OVER(ORDER BY dateOfInquiry ASC) 
            Id, dateOfInquiry

    FROM 
        Table
) 
b 

ON b.ID = a.ID + 1 
WHERE ABS( DATEDIFF(d, a.dateOfInquiry, b.dateOfInquiry ) ) > 14
GROUP BY a.strTransactionID 

我想做的是每 14 天只返回 1 条记录。所以我想要上面的数据集中的 1，但我得到的是 0(它消除了两条记录，因为它们之间没有 14 天)

此外，如果有更多记录并且行为不太正确，它会变得更加复杂。如果我在 1 日、2 日、3 日等到 15 日有记录，我仍然只想要 1 条记录，然后在 16 日或之后的下一条记录处再次开始计数，忽略更多记录，直到差异为超过 14 天。

本质上，我想将一条记录计为 1，然后忽略所有其他记录，直到 14 天结束。

另一个示例，我想要的结果是 2:

Index   dateOfInquiry   
649454      2016-02-01  <- count
649455      2016-02-12  -ignore (<l4 past 649454)
649456      2016-02-12  -ignore (<l4 past 649454)
649457      2016-02-17  <- count
649458      2016-02-22  -ignore (<l4 past 649457)
649459      2016-02-25  -ignore (<l4 past 649457)

Answer 1

使用 outer apply()获取每一行的下一个合格行的 id，并使用递归 common table expression从头开始并继续前进:

;with cte as (
  select t.id, t.dateOfInquiry, x.next_id
  from t
    outer apply (
      select top 1 next_id = i.id
      from t as i
      where i.dateOfInquiry > dateadd(day,14,t.dateOfInquiry)
      order by dateofInquiry, id asc
    ) x
)
, r_cte as (
    --anchor row(s) / starting row(s)
  select 
      id
    , dateOfInquiry
    , next_id
  from cte t
  where not exists (
    select 1
    from cte as i
    where i.id < t.id
    )
  union all
  --recursion starts here
  select 
      c.id
    , c.dateOfInquiry
    , c.next_id
  from cte c
    inner join r_cte p
      on c.id = p.next_id
)
select id, dateOfInquiry
from r_cte

rextester 演示:http://rextester.com/PIMVPM32168

返回:

+--------+---------------+
|   id   | dateOfInquiry |
+--------+---------------+
| 649454 | 2016-02-01    |
| 649457 | 2016-02-17    |
+--------+---------------+