更新:我生成了一百万行随机数据,并放弃了递归CTE解决方案,因为其查询计划在优化器中没有很好地利用索引。
但是,我最初发布的非递归解决方案表现出色,只要在(SALEDITEMS,[DATE])上有一个额外的非聚集索引。这是有道理的,因为查询需要在两个方向上过滤(按日期和SALEDITEMS)。有了这个额外的索引,对一百万行的查询在我的(不太强大的)桌面计算机上可以在2秒内返回。如果没有这个索引,查询就会变得非常慢。
顺便说一句,这是SQL Server成本基础查询优化在某些情况下完全崩溃的绝佳例子。递归CTE解决方案的成本(在我的PC上)为42,需要至少几分钟才能完成。非递归解决方案的成本为15,446(!),并在1.5秒内完成。故事的寓意是:在比较SQL Server查询计划时,不要假设成本必然与查询性能相关!
无论如何,这里是我推荐的解决方案(与我之前发布的非递归CTE相同):
DECLARE @SALEDITEMS INT = 3;
WITH SalesNoMatch ([DATE], SALEDITEMS, NoMatchDate)
AS
(
SELECT [DATE], SALEDITEMS,
(SELECT MIN([DATE]) FROM Sales s2 WHERE s2.SALEDITEMS <> @SALEDITEMS
AND s2.[DATE] > s1.[DATE]) as NoMatchDate
FROM Sales s1
)
, SalesMatchCount ([DATE], ConsecutiveCount) AS
(
SELECT [DATE], 1+(SELECT COUNT(1) FROM Sales s2 WHERE s2.[DATE] > s1.[DATE] AND s2.[DATE] < NoMatchDate)
FROM SalesNoMatch s1
WHERE s1.SALEDITEMS = @SALEDITEMS
)
SELECT MAX(ConsecutiveCount)
FROM SalesMatchCount;
以下是我用来测试的 DDL,包括您需要的索引:
CREATE TABLE [Sales](
[DATE] date NOT NULL,
[SALEDITEMS] int NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX IX_Sales ON Sales ([DATE]);
CREATE UNIQUE NONCLUSTERED INDEX IX_Sales2 ON Sales (SALEDITEMS, [DATE]);
这里是我创建测试数据的方法-- 1000001行带有升序日期,SALEDITEMS随机设置为1至10。
INSERT INTO Sales ([DATE], SALEDITEMS)
VALUES ('1/1/09', 5)
DECLARE @i int = 0;
WHILE (@i < 1000000)
BEGIN
INSERT INTO Sales ([DATE], SALEDITEMS)
SELECT DATEADD (d, 1, (SELECT MAX ([DATE]) FROM Sales)), ABS(CHECKSUM(NEWID())) % 10 + 1
SET @i = @i + 1;
END
这里是我曾经放弃的递归 CTE 解决方案:
DECLARE @SALEDITEMS INT = 3;
WITH SalesRowNum ([DATE], SALEDITEMS, RowNum)
AS
(
SELECT [DATE], SALEDITEMS, ROW_NUMBER() OVER (ORDER BY s1.[DATE]) as RowNum
FROM Sales s1
)
, SalesCTE (RowNum, [DATE], ConsecutiveCount)
AS
(
SELECT s1.RowNum, s1.[DATE], 1 AS ConsecutiveCount
FROM SalesRowNum s1
WHERE SALEDITEMS = @SALEDITEMS
UNION ALL
SELECT s1.RowNum, s1.[DATE], ConsecutiveCount + 1 AS ConsecutiveCount
FROM SalesRowNum s1
INNER JOIN SalesCTE s2 ON s1.RowNum = s2.RowNum + 1
WHERE SALEDITEMS = @SALEDITEMS
)
SELECT MAX(ConsecutiveCount)
FROM SalesCTE;