如何在SQL中识别连续日期的组？ [英] How can I identify groups of consecutive dates in SQL?

问题描述

我一直在Python中进行这样的操作，直到现在，但是我想将其移动到SQL中。

例如列表

  Bill 01/01/2011 
帐单02/01/2011 
帐单03/01/2011 
法案05/01/2011 
法案07/01/2011 
  

应该输出到新表中：

  Bill 01/01/2011 3 
帐单02/01/2011 3 
帐单03/01/2011 3 
 Bill 05/01 / 2011 1 
比尔07/01/2011 1 
  

理想情况下，这也应该能够周末和公众假期 - 我表中的日期将会是周一至周五（我想通过制作新的工作日表并按顺序编号）可以解决这个问题。有工作的人建议我尝试一个CTE。我相当新的，所以我会感谢任何人可以提供任何指导！谢谢。

解决方案

您可以通过巧妙应用窗口功能来执行此操作。请考虑以下内容：

 选择名称，日期，row_number（）（按日期按名称分配）
从t 
  

这将添加一个行号，在您的示例中，它将只是1，2，3，4 ，5.现在，从日期起，与组别有所不同。

 选择名称，日期，
 dateadd（d， -  row_number（）over（按名称按日期排列的分区），date）作为val 
从t 
  

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

 选择名称，日期，
 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（分区按日期按日期排序），date）as val 
 from t 
）t 
  

不知怎的，我错过了关于平日和假期的部分。这个解决方案不能解决这个问题。

Im trying to write a function which identifies groups of dates, and measures the size of the group.

I've been doing this procedurally in Python until now but I'd like to move it into SQL.

for example, the list

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

should be output into a new table as:

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

Ideally this should also be able to account for weekends and public holidays - the dates in my table will aways be Mon-Fri (I think I can solve this by making a new table of working days and numbering them in sequence). Someone at work suggested I try a CTE. Im pretty new to this, so I'd appreciate any guidance anyone could provide! Thanks.

解决方案

You can do this with a clever application of window functions. Consider the following:

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

This adds a row number, which in your example would simply be 1, 2, 3, 4, 5. Now, take the difference from the date, and you have a constant value for the group.

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

Finally, you want the number of groups in sequence. I would also add a group identifier (for instance, to distinguish between the last two).

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

Somehow, I missed the part about weekdays and holidays. This solution does not solve that problem.

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