SQL通过滑动窗口保持事件计数 [英] SQL keeping count of occurrences through a sliding window

问题描述

在上一个问题中（请参阅： SQL记录发生次数）我需要计算一个变量的出现次数。

提供的代码如下：

  SELECT 
 [日期]，代码，
 [计数] = COUNT（*）个（按代码排列顺序或按[日期]行未绑定的顺序）
从dbo.YourTable 
 ORDER BY [Date]; 
  

但是，现在我需要对该代码进行改进：

假设我有下表：

 日期|代码
 ------------------------ 
 2010/01/01 | 25 
 2010/01/01 | 22 
 2010/01/01 | 23 
 2010/01/01 | 25 
 2010/01/02 | 23 
 2010/01/02 | 23 
 2010/01/03 | 23 
 2010/01/04 | 23 
 2010/01/05 | 23 
 2010/01/06 | 23 
 2010/01/07 | 23 
 ..... 
 2013/03/02 | 21 
  

现在，我需要计算特定时间段内的出现次数。期望的输出如下（为简单起见，假设时间范围为n = 2天）

 代码发生
 ------------------------------------ 
 2010/01/01 | 25 | 1 
 2010/01/01 | 22 | 1 
 2010/01/01 | 23 | 1 
 2010/01/01 | 25 | 2 
 2010/01/02 | 23 | 2 
 2010/01/02 | 23 | 3 
 2010/01/03 | 23 | 3-> 
 2010/01/04 |现在，我们不在考虑在2011/01/01中发生此事件。 23 | 2->仅考虑01/03和01/04中的出现
 2010/01/05 | 23 | 2 
 2010/01/06 | 23 | 2 
 2010/01/07 | 23 | 2 
 ..... 
 2013/03/02 | 21 | 1 
  

也就是说，我需要知道代码 x出现在表中的次数在过去的'n'个月内。

这是在SQL Server 2012中运行的。

在此先感谢您。

解决方案

在 CTE ，交叉应用运算符和 ROW_NUMBER 排名函数

 ；使用cte AS 
（
 SELECT [Date] ，代码
 FROM dbo。您的表
 GROUP BY [日期]，代码
）
 SELECT c.Date，c.Code，o。出现次数
 FROM cte c 
交叉应用（
选择t2。[Date]，t2.Code，
 ROW_NUMBER（）OVER（按c。[Date]排序按t2。[Date]排序）从dbo.ourTable t2 
到
。c.Code = t2.Code 
 AND DATEDIFF（day，t2。[Date]，c。[Date]）在0和1 $ b $之间b）o 
 c.Code = o.Code AND c。[Date] = o。[Date] 
 OR BY BY c。[Date] 
  

Demo on SQLFiddle

为提高性能，请使用此索引

 在dbo.YourTable（[Date]，Code）上创建索引x 
  

In the previous question (Please refer to: SQL Keeping count of occurrences) I needed to count the number of occurrences of a variable.

The code provided was as follows:

SELECT 
    [Date], Code, 
    [Count] = COUNT(*) OVER (PARTITION BY Code ORDER BY [Date] ROWS UNBOUNDED PRECEDING)
FROM dbo.YourTable
ORDER BY [Date];

However, now I need to introduce an improvement to that code:

Let's say that I have the following table:

   Date          | Code
   ------------------------
   2010/01/01    | 25
   2010/01/01    | 22
   2010/01/01    | 23
   2010/01/01    | 25
   2010/01/02    | 23
   2010/01/02    | 23
   2010/01/03    | 23
   2010/01/04    | 23
   2010/01/05    | 23
   2010/01/06    | 23
   2010/01/07    | 23
   .....
   2013/03/02    | 21

Now, I need to count the number of occurrences in a specific period of time. The desired output would be as follows (supposing a time frame of n=2 days, for the sake of simplicity)

    Date         | Code  |  Occurrences
   ------------------------------------
   2010/01/01    | 25    |      1
   2010/01/01    | 22    |      1
   2010/01/01    | 23    |      1
   2010/01/01    | 25    |      2
   2010/01/02    | 23    |      2
   2010/01/02    | 23    |      3
   2010/01/03    | 23    |      3 -> We are not considering the occurence in 2011/01/01 as it is out of the scope now
   2010/01/04    | 23    |      2 -> Considers only occurrences in 01/03 and 01/04 
   2010/01/05    | 23    |      2
   2010/01/06    | 23    |      2
   2010/01/07    | 23    |      2
   .....
   2013/03/02    |  21   |      1

That is, I need to know how many times the code 'x' has appeared in my table in the last 'n' months.

This is run in SQL Server 2012.

Thank you in advance.

解决方案

Use option with CTE, CROSS APPLY operator and ROW_NUMBER ranking function

 ;WITH cte AS
 (
  SELECT [Date], Code
  FROM dbo.YourTable  
  GROUP BY [Date], Code
  )
  SELECT c.Date, c.Code, o.Occurrences
  FROM cte c 
    CROSS APPLY (
                 SELECT t2.[Date], t2.Code,
                        ROW_NUMBER() OVER(PARTITION BY c.[Date] ORDER BY t2.[Date]) AS Occurrences        
                 FROM dbo.YourTable t2
                 WHERE c.Code = t2.Code
                   AND DATEDIFF(day, t2.[Date], c.[Date]) BETWEEN 0 AND 1
                 ) o
  WHERE c.Code = o.Code AND c.[Date] = o.[Date] 
  ORDER BY c.[Date]

Demo on SQLFiddle

For improving performance use this index

CREATE INDEX x ON dbo.YourTable([Date], Code)

这篇关于SQL通过滑动窗口保持事件计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！