一千萬個為什麽

搜索

查找表中最長的值序列

這是一個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

調用sp /句子4會給1調用sp/sentecne 3會得到2 因為第3行有2次。

我正在運行SQL Server 2008。

最佳答案

更新:我生成了一百萬行隨機數據,並放棄了遞歸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]);

這裏是我如何創建我的測試數據 - 1,000,001行隨著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;

轉載註明原文: 查找表中最長的值序列