如何在SQL中识别连续日期组？

Question

我试图写一个函数来识别日期组，并测量组的大小。

到目前为止，我一直在使用Python进行此过程，但我想将其移入SQL。

例如清单

Bill 01/01/2011 
Bill 02/01/2011 
Bill 03/01/2011 
Bill 05/01/2011 
Bill 07/01/2011

应该输出到新表中，如下所示：

Bill 01/01/2011  3 
Bill 02/01/2011  3 
Bill 03/01/2011  3 
Bill 05/01/2011  1 
Bill 07/01/2011  1

理想情况下，这还应该能够解决周末和公共假日的问题-
我表中的日期应该是周一至周五（我想我可以通过制作一个新的工作日表并按顺序编号来解决此问题）。有人建议我尝试CTE。我对此很陌生，因此，我感谢任何人都可以提供的任何指导！谢谢。

Answer 1

您可以通过巧妙地使用窗口功能来做到这一点。考虑以下：

select name, date, row_number() over (partition by name order by date)
from t

这将添加一个行号，在您的示例中该行号将简单地为1、2、3、4、5。现在，取与日期的差值，并且该组具有一个恒定值。

select name, date,
       dateadd(d, - row_number() over (partition by name order by date), date) as val
from t

最后，您需要顺序的组数。我还将添加一个组标识符（例如，以区分最后两个）。

select name, date,
       count(*) over (partition by name, val) as NumInSeq,
       dense_rank() over (partition by name order by val) as SeqID
from (select name, date,
             dateadd(d, - row_number() over (partition by name order by date), date) as val
      from t
     ) t

不知何故，我错过了有关工作日和节假日的部分。此解决方案不能解决该问题。