在上一个问题中，我需要计算一个变量出现的次数。
提供的代码如下：
SELECT [Date], Code, [Count] = COUNT(*) OVER (PARTITION BY Code ORDER BY [Date] ROWS UNBOUNDED PRECEDING) FROM dbo.YourTable ORDER BY [Date];
但是，现在我需要对该代码进行改进：
假设我有下表：
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
现在，我需要计算特定时间段内的出现次数。期望的输出如下（为简单起见，假设时间范围为n = 2天）
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
也就是说，我需要知道在过去的“ n”个月中，代码“ x”在我的表中出现了多少次。
它在SQL Server 2012中运行。
先感谢您。
将选项与CTE，CROSS APPLY运算符和ROW_NUMBER排序功能一起使用
;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]
关于 SQLFiddle的 演示
为了提高性能，请使用此索引
CREATE INDEX x ON dbo.YourTable([Date], Code)
