代码之家  ›  专栏  ›  技术社区  ›  Dani

查找表中值的最长序列

  •  4
  • Dani  · 技术社区  · 15 年前

    我想查找表中某列中相同(已知)编号的最长序列的编号:

    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/语句3将得到2 因为第三排有两次。

    我正在运行SQLServer2008。

    2 回复  |  直到 15 年前
        1
  •  1
  •   Justin Grant    15 年前

    更新:我生成了一百万行随机数据,并放弃了递归CTE解决方案,因为它的查询计划没有充分利用优化器中的索引。

    但我最初发布的非递归解决方案效果很好,只要在(salditems,[DATE])上有一个额外的非聚集索引。这是有意义的,因为查询需要在两个方向上进行过滤(按日期和按销售日期)。有了这个额外的索引,在我(不是很强壮)的桌面mathine上,一百万行的查询在不到2秒内返回。如果没有这个索引,查询速度会非常慢。

    无论如何,我推荐以下解决方案(与我之前发布的非递归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]);
    

    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解决方案: 声明@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;
    
        2
  •  0
  •   A-K    15 年前

    未测试,因为您未提供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;