用于检查 2 个或更多连续负周值的 SQL

Question

我想计算在几周范围内具有负值的连续 2 个或更多周期间的数量。

例子:

Week | Value
201301 | 10
201302 | -5 <--| both weeks have negative values and are consecutive
201303 | -6 <--| 

 Week | Value
201301 | 10
201302 | -5 
201303 | 7
201304 | -2 <-- negative but not consecutive to the last negative value in 201302 

 Week | Value
201301 | 10
201302 | -5 
201303 | -7
201304 | -2 <-- 1st group of negative and consecutive values 
201305 | 0
201306 | -12
201307 | -8 <-- 2nd group of negative and consecutive values 

除了使用游标和重置变量并按顺序检查每一行之外，还有更好的方法吗？

这是我设置的一些 SQL 来尝试测试:

IF OBJECT_ID('TempDB..#ConsecutiveNegativeWeekTestOne') IS NOT NULL DROP TABLE #ConsecutiveNegativeWeekTestOne
IF OBJECT_ID('TempDB..#ConsecutiveNegativeWeekTestTwo') IS NOT NULL DROP TABLE #ConsecutiveNegativeWeekTestTwo

CREATE TABLE #ConsecutiveNegativeWeekTestOne
(
     [Week] INT NOT NULL
     ,[Value] DECIMAL(18,6) NOT NULL
)

-- I have a condition where I expect to see at least 2 consecutive weeks with negative values
-- TRUE : Week 201328 & 201329 are both negative.
INSERT INTO #ConsecutiveNegativeWeekTestOne
VALUES
(201327, 5)
,(201328,-11)
,(201329,-18)
,(201330, 25)
,(201331, 30)
,(201332, -36)
,(201333, 43)
,(201334, 50)
,(201335, 59)
,(201336, 0)
,(201337, 0)

SELECT * FROM #ConsecutiveNegativeWeekTestOne
WHERE Value < 0
ORDER BY [Week] ASC


CREATE TABLE #ConsecutiveNegativeWeekTestTwo
(
     [Week] INT NOT NULL
     ,[Value] DECIMAL(18,6) NOT NULL
)

-- FALSE: The negative weeks are not consecutive
INSERT INTO #ConsecutiveNegativeWeekTestTwo
VALUES

(201327, 5)
,(201328,-11)
,(201329,20)
,(201330, -25)
,(201331, 30)
,(201332, -36)
,(201333, 43)
,(201334, 50)
,(201335, -15)
,(201336, 0)
,(201337, 0)

SELECT * FROM #ConsecutiveNegativeWeekTestTwo
WHERE Value < 0
ORDER BY [Week] ASC

我的 SQL fiddle 也在这里:
http://sqlfiddle.com/#!3/ef54f/2

Answer 1

您可以使用 EXISTS 的组合。

假设你只想知道组(连续几周的系列都是负数)

--找出潜在的开始周

;WITH starts as (
    SELECT [Week]
    FROM #ConsecutiveNegativeWeekTestOne AS s
    WHERE s.[Value] < 0
      AND NOT EXISTS (
        SELECT 1
        FROM #ConsecutiveNegativeWeekTestOne AS p
        WHERE p.[Week] = s.[Week] - 1
          AND p.[Value] < 0
        )
    )
SELECT COUNT(*)
FROM
    Starts AS s
    WHERE EXISTS (
        SELECT 1
        FROM #ConsecutiveNegativeWeekTestOne AS n
        WHERE n.[Week] = s.[Week] + 1
          AND n.[Value] < 0
        )

如果你在 Week 上有一个索引，这个查询甚至应该是中等效率的。