sql - 如何在 SQL 中找到连续的事件周？

Question

我想做的是找到某人在周日活跃的连续周数并为其分配一个值。他们必须每天至少参加 2 场比赛才能算作一周的活跃比赛。

如果他们连续 2 周处于事件状态，我想分配值为 100，连续 3 周分配值为 200，连续 4 周分配值为 300，并持续最多连续 9 周。

我的困难不是确定连续几周，而是在连续日期之间中断。假设以下数据集:

CustomerID  RaceDate    Races
1           2/2/2014    2
1           2/9/2014    5
1           2/16/2014   3
1           2/23/2014   3
1           3/2/2014    4
1           3/9/2014    3
1           3/16/2014   3
2           2/2/2014    2
2           2/9/2014    3
2           3/2/2014    2
2           3/9/2014    4  
2           3/16/2014   3

如果值为 600，CustomerID 1 将有连续 7 周。

对我来说困难的部分是 CustomerID 2。他们将有连续 2 周和连续 3 周。所以它们的总值(value)将是 100 + 200 = 300。

我希望能够通过连续几周的任何不同组合来做到这一点。

请问有什么帮助吗？

编辑:我使用的是 SQL Server 2008 R2。

Answer 1

在寻找顺序值时，有一个简单的观察会有所帮助。如果从日期中减去一个序列，则该值是一个常数。您可以将其用作分组机制

select CustomerId, min(RaceDate) as seqStart, max(RaceDate) as seqEnd,
       count(*) as NumDaysRaced
from (select t.*,
              dateadd(week, - row_number() over (partition by customerID, RaceDate),
                      RaceDate) as grp
      from table t
      where races >= 2
     ) t
group by CustomerId, grp;

然后，您可以使用它来获得最终的“积分”:

select CustomerId,
       sum(case when NumDaysRaced > 1 then (NumDaysRaced - 1) * 100 else 0 end) as Points
from (select CustomerId, min(RaceDate) as seqStart, max(RaceDate) as seqEnd,
             count(*) as NumDaysRaced
      from (select t.*,
                    dateadd(week, - row_number() over (partition by customerID, RaceDate),
                            RaceDate) as grp
            from table t
            where races >= 2
           ) t
      group by CustomerId, grp
     ) c
group by CustomerId;