TSQL计数连续记录

Question

假设我有以下记录：

KeyCol  ColA  ColB 
------------------------ 
1   1  A 
2   2  B 
3   2  B 
4   2  C 
5   2  B 
6   1  A 
7   2  B 
8   2  B 

我想算有这个结果可乐COLB相同值的连续记录

Col A  ColB  Start Count 
--------------------------------- 
1   A  1  1 
2   B  2  2 
2   C  4  1 
2   B  5  1 
1   A  6  1 
2   B  7  2 

有很多相似关于分组和计数的问题，但我没有看到如何将它翻译成这个问题。特别是许多其他示例没有明确的键列。

我曾尝试使用的分区函数计算的连续记录数，并从那里：

SELECT KeyCol, ColA, ColB 
     ,ROW_NUMBER() OVER 
      ( PARTITION 
       BY ColA, ColB 
       ORDER BY KeyCol 
      ) as RowNo 
FROM MyTable 

然而，这会产生这样的结果：

KeyCol Col A  ColB  RowNo 
--------------------------------- 
1   1   A  1 
2   2   B  1 
3   2   B  2 
4   2   C  1 
5   2   B  3 (Needs to be 1) 
6   1   A  2 (Needs to be 1) 
7   2   B  4 (Needs to be 1) 
8   2   B  5 (Needs to be 2) 

正如你所看到的，即使记录不连续，所有同一ColA，ColB的行号也会增加。

非常感谢！

Answer 1

这是一个Gaps and Islands problem。您需要使用排名函数来确定ColB具有相同值的组（岛）。下面的查询：

SELECT KeyCol, 
     ColA, 
     ColB, 
     GroupBy = ROW_NUMBER() OVER(ORDER BY KeyCol) - 
        ROW_NUMBER() OVER(PARTITION BY ColA, ColB ORDER BY KeyCol) 
FROM dbo.T 
ORDER BY KeyCol; 

你会得到输出：

KeyCol  ColA  ColB GroupBy 
----------------------------------------- 
1   1  A   0 
2   2  B   1 
3   2  B   1 
4   2  C   3 
5   2  B   2 
6   1  A   4 
7   2  B   3 
8   2  B   3 

正如你所看到的，这个标识您的岛屿，其中两个（或更多）连续的行有可乐COLB相同的值，您将在列GroupBy中获得相同的值。

一旦你有了它，它是一个简单的例子，它可以得到你需要的输出。给出最后一个查询（带有样本数据）：

DECLARE @T TABLE (KeyCol INT, ColA INT, ColB CHAR(1)); 
INSERT @T (KeyCol, ColA, ColB) 
VALUES 
    (1, 1, 'A'), (2, 2, 'B'), (3, 2, 'B'), (4, 2, 'C'), 
    (5, 2, 'B'), (6, 1, 'A'), (7, 2, 'B'), (8, 2, 'B'); 

WITH RankedData AS 
( SELECT KeyCol, 
      ColA, 
      ColB, 
      GroupBy = ROW_NUMBER() OVER(ORDER BY KeyCol) - 
         ROW_NUMBER() OVER(PARTITION BY ColA, ColB ORDER BY KeyCol) 
    FROM @T 
) 
SELECT ColA, 
     ColB, 
     Start = MIN(KeyCol), 
     [Count] = COUNT(*) 
FROM RankedData 
GROUP BY ColA, ColB, GroupBy 
ORDER BY Start;