sql - 如何确定在定义的剧集中是否发生了一行

Question

我有一个表，其中包含事件发生时的人员和日期:

Person   DateOfEvent
1        1/1/2014
1        1/20/2014
1        9/17/2014
2        1/1/2014
2        3/3/2014
2        6/30/2014
3        4/10/2014
3        4/22/2014

由于缺少更好的术语，我需要能够将这些行分类为剧集。事件的第一次发生将开始一个持续 30 天的事件。 30 天内的任何日期都应被视为该情节的一部分，并且不会重新开始计数。如果某行的日期不在首次出现后的 30 天内，它将开始新的一集。

我想象的是这样的:

Person  Date        Episode
1       1/1/2014    1
1       1/20/2014   1
1       9/17/2014   2
2       1/1/2014    1
2       3/3/2014    2
2       6/30/2014   3
3       4/10/2014   1
3       4/22/2014   1

在 T-SQL 中执行此操作的最佳方法是什么(最好没有游标)？

Answer 1

不幸的是，这是一个迭代问题。您可以使用递归 CTE 解决它，但它们不会很快。

以下方法从每个人的第一个值开始，然后根据您的逻辑逐个分配剧集。

with data as (
      select person, date, row_number() over (partition by person order by date) as seqnum
      from table t
     ),
     cte as (
      select person, date, seqnum, 1 as episode, date as episodestart
      from data
      where seqnum = 1
      union all
      select data.person, data.date, data.seqnum,
             (case when datediff(day, cte.episodestart, data.date) < 30 then cte.episode
                   else cte.episode + 1
              end) as episode,
             (case when datediff(day, cte.episodestart, data.date) < 30 then cte.episodestart
                   else data.date
              end) as episodestart                 
      from cte join
           data
           on data.person = cte.person and data.seqnum - 1 = cte.seqnum
    )
select person, date, episode
from cte;

Here是显示结果的 SQL Fiddle。