如何在SQL中找到连续的活动周？ [英] How can I find consecutive active weeks in SQL?

问题描述

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

What I would like to do is find the number of consecutive weeks that someone is active on Sundays and assign them a value. They have to participate in at least 2 races a day to be counted as active for the week.

如果它们连续两个星期都处于活动状态，我想分配一个值100，连续3个星期分配200，连续4个星期分配300，并且连续长达9周。

If they are active for 2 consecutive weeks I would like to assign a value of 100, 3 consecutive weeks a value of 200, 4 consecutive weeks a value of 300, and continuing up to 9 consecutive weeks.

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

My difficulty is not determining consecutive weeks, but breaks in between consecutive dates. Suppose the following dataset:

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

CustomerID 1将连续7周价值为600。

CustomerID 1 would have 7 consecutive weeks for a value of 600.

对我而言，最难的部分是CustomerID2。他们将连续2周，并连续3周。因此，它们的总价值将为100 + 200 = 300。

The hard part for me is CustomerID 2. They would have 2 consecutive weeks AND 3 consecutive weeks. So their total value would be 100 + 200 = 300.

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

I would like to be able to do this with any different combination of consecutive weeks.

请帮忙吗？

编辑：我正在使用SQL Server 2008 R2。

I am using SQL Server 2008 R2.

推荐答案

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

When looking for sequential values, there is a simple observation that helps. If you subtract a sequence from the dates then the value is a constant. You can use this as a grouping mechanism

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;

然后您可以使用它来获得最终的点：

You can then use this to get your final "points":

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;

这篇关于如何在SQL中找到连续的活动周？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！