在表格中找到一个值的最长连续序列。

Question

这是一个SQL问题，我认为它很难 - 我不确定是否可能在简单的SQL语句或存储过程中实现：

我想找到表中一列中相同（已知）数字的最长序列的数量：

例如：

TABLE: 
DATE    SALEDITEMS
1/1/09       4
1/2/09       3
1/3/09       3
1/4/09       4
1/5/09       3

调用数字4的SP /句子将返回1。调用数字3的SP /句子将返回2，因为连续两次出现数字3。

我正在运行SQL Server 2008。

- Dani

Answer 1

更新：我生成了一百万行随机数据，并放弃了递归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;

-- recursive CTE solution (remember to set MAXRECURSION!)
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;

- Justin Grant

---

谢谢！我尝试了第二个代码（非递归）在一个小表格上（1400行），它可以工作。我在一个30000行的表格上尝试了它，但它永远不会结束，因为日期列上没有索引，所以我想我需要先解决这个问题。我能在语句中设置MaxRecursion吗？我该如何取消设置或将其返回到默认值？递归会更快吗？ - Dani

我更新了解决方案并提供了更多信息——非递归解决方案在表格行数较多时看起来更好。实际上，你需要两个索引——否则（正如你发现的那样），当有大量行时，查询将变得非常缓慢。添加第二个索引（见上文），你将能够在不到一秒钟的时间内完成你的30,000行查询。 - Justin Grant

Answer 2

未经测试，因为您没有提供DDL和样本数据：

DECLARE @SALEDITEMS INT;
SET @SALEDITEMS=3;
SELECT MAX(cnt) FROM(
SELECT COUNT(*) FROM YourTable JOIN (
SELECT y1.[Date] AS d1, y2.[Date] AS d2
FROM YourTable AS y1 JOIN YourTable AS y2 
ON y1.SALEDITEMS=@SALEDITEMS AND y2.SALEDITEMS=@SALEDITEMS
AND NOT EXISTS(SELECT 1 FROM YourTable AS y 
WHERE y.SALEDITEMS<>@SALEDITEMS
AND y1.[Date] < y.[Date] AND y.[Date] < y2.[Date])
) AS t
WHERE [Date] BETWEEN t.d1 AND t.d2
) AS t;

- A-K

---

我明白了......它可能有效，但在最后一个WHERE附近有一个错误，还不确定是什么。 - Dani