SQL统计连续出现的状态次数

Question

我有一张列表，其中包含案例和它们的状态：

Case ID | Date       | Status
------  | ---------- |-------
   x    | 2016-11-01 | Open
   x    | 2016-11-03 | Hold
   x    | 2016-11-04 | Hold
   x    | 2016-11-05 | Open
   x    | 2016-11-08 | Hold
   x    | 2016-11-15 | Processing
   x    | 2016-11-16 | Done

我希望指出同一案件的状态保持不变，但有新记录：

Case ID | Date       | Status     | count
------  | ---------- |----------  |------
   x    | 2016-11-01 | Open       |  1 
   x    | 2016-11-03 | Hold       |  1
   x    | 2016-11-04 | Hold       |  2
   x    | 2016-11-05 | Open       |  1
   x    | 2016-11-08 | Hold       |  1
   x    | 2016-11-15 | Processing |  1
   x    | 2016-11-16 | Done       |  1

如果我这样做：

SELECT * 
FROM
    (SELECT 
         ROW_NUMBER() OVER  (partition by [Case ID], [status] ORDER BY Date) AS Row, 
         ID, Status, Date 
     FROM 
         Cases) AS C
WHERE 
    ID = x
ORDER BY
    Date

它没有考虑到最后一个“持有”是在“开放”之后的，因此放置了数字3，但我需要数字1。

- Romeo

---

请问您需要什么类型的输出？ - mansi

Answer 1

您需要检测具有相同状态值的连续记录的岛屿，并在PARTITION BY子句中使用“岛屿ID”来消耗ROW_NUMBER窗口函数：

SELECT  [Case ID], [Date], [status],
        ROW_NUMBER() OVER (PARTITION BY [Case ID], [status], grp ORDER BY [Date]) AS [count]
FROM (
   SELECT  [Case ID], [Date], [status], 
           ROW_NUMBER() OVER (PARTITION BY [Case ID] ORDER BY [Date]) -
           ROW_NUMBER() OVER (PARTITION BY [Case ID], [status] ORDER BY [Date]) AS grp   
   FROM Cases) AS t
ORDER BY [Date]

- Giorgos Betsos

---

谢谢！正是我所需要的。 - Romeo

那真的非常聪明。 - mendosi

Answer 2

你也可以尝试以下方式...

SELECT  CASEID,
       DATE,
       STATUS,
     CASE WHEN RNO<>0 THEN  (rno+r) ELSE 1 END
            FROM
        (
SELECT CASEID,
       DATE,
       STATUS,
       CASE
         WHEN STATUS = LAG(STATUS)
                         OVER (
                           PARTITION BY CASEID
                           ORDER BY CASEID,date) THEN 1
         ELSE 0
       END RNO,
       ROW_NUMBER() OVER( partition by date,status ORDER BY DATE,status) R
FROM   #TABLE1 )A

- Tharunkumar Reddy

---

谢谢。我不能使用LAG函数，因为我正在使用2008服务器。但是我可以使用Giorgos Betsos提出的解决方案。 - Romeo

@Romeo 没关系。如果你标记 SQL Server 2008，会更好。 - Tharunkumar Reddy