問題描述

假設我們有這個簡單的架構和數據:

Suppose we have this simple schema and data:

DROP TABLE #builds
CREATE TABLE #builds (
    Id INT IDENTITY(1,1) NOT NULL,
    StartTime INT,
    IsPassed BIT
)
INSERT INTO #builds (StartTime, IsPassed) VALUES
(1, 1),
(7, 1),
(10, 0),
(15, 1),
(21, 1),
(26, 0),
(34, 0),
(44, 0),
(51, 1),
(60, 1)

SELECT StartTime, IsPassed, NextStartTime,
    CASE IsPassed WHEN 1 THEN 0 ELSE NextStartTime - StartTime END Duration
FROM (
    SELECT  
        LEAD(StartTime) OVER (ORDER BY StartTime) NextStartTime,
        StartTime, IsPassed
    FROM #builds
) x
ORDER BY StartTime

它產生以下結果集:

StartTime   IsPassed    NextStartTime   Duration
1           1           7               0
7           1           10              0
10          0           15              5
15          1           21              0
21          1           26              0
26          0           34              8
34          0           44              10
44          0           51              7
51          1           60              0
60          1           NULL            0

我需要總結非零連續 Duration 值，并在批處理中第一行的 StartTime 顯示它們.IE.我需要解決這個問題:

I need to summarize the non zero consecutive Duration values and to show them at the StartTime of the first row in the batch. I.e. I need to get to this:

StartTime   Duration
10          5
26          25

我就是不知道該怎么做.

I just can't figure out how to do it.

PS:當然，真實表包含更多行.

PS: The real table contains many more rows, of course.

推薦答案

這是一個間隙和孤島問題，需要將 IsPassed 恒定的每個部分劃分到不同的組中.這可以通過計算整個表的 ROW_NUMBER() 與由 IsPassed 分區的差異來完成.然后，您可以 SUM 為每個組的 Duration 值，其中 IsPassed = False 并取 MIN(StartTime) 到給出組第一行的StartTime:

This is a gaps and islands problem, requiring partitioning each section where IsPassed is constant into a different group. That can be done by computing the difference between ROW_NUMBER() over the entire table against partitioned by IsPassed. You can then SUM the Duration Values for each group where IsPassed = False and take the MIN(StartTime) to give the StartTime of the first row of the group:

WITH CTE AS (
  SELECT StartTime, IsPassed,
         LEAD(StartTime) OVER (ORDER BY StartTime) AS NextStartTime
  FROM #builds
),
CTE2 AS (
  SELECT StartTime, IsPassed, NextStartTime,
         CASE IsPassed WHEN 1 THEN 0 ELSE NextStartTime - StartTime END Duration,
         ROW_NUMBER() OVER (ORDER BY StartTime) -
         ROW_NUMBER() OVER (PARTITION BY IsPassed ORDER BY StartTime) AS grp
  FROM CTE
)
SELECT MIN(StartTime) AS StartTime, SUM(Duration) AS Duration
FROM CTE2
WHERE IsPassed = 0
GROUP BY grp
ORDER BY MIN(StartTime)

輸出:

StartTime   Duration
10          5
26          25

dbfiddle 演示

這篇關于將連續的非零行標記為不同的分區?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！