SQL Server GROUP BY COUNT 仅计算连续的行

Question

我有一个名为DATA的表格，位于Microsoft SQL Server 2008 R2上，其中有三个非空整数字段：ID、Sequence和Value。具有相同ID的序列值将是连续的，但可以以任何值开始。我需要一个查询来返回具有相同ID和Value的连续行的计数。

例如，假设我有以下数据：

ID  Sequence  Value
--  --------  -----
1         1      1
5         1    100
5         2    200
5         3    200
5         4    100
10       10     10

我希望得到以下结果：

ID  Start  Value  Count
--  -----  -----  -----
1      1      1      1
5      1    100      1
5      2    200      2
5      4    100      1
10    10     10      1

我尝试过

SELECT ID, MIN([Sequence]) AS Start, Value, COUNT(*) AS [Count]
 FROM DATA
 GROUP BY ID, Value
 ORDER BY ID, Start

但这会给予

ID  Start  Value  Count
--  -----  -----  -----
1      1      1      1
5      1    100      2
5      2    200      2
10    10     10      1

这个问题是如何对具有相同值的所有行进行分组，而不仅仅是连续的行。

有什么想法吗？从我所看到的来看，我认为我需要使用ROW_NUMBER()在自身上进行左连接以获取连续行，但我不确定如何从中获取计数。

提前感谢。

- Windows

---

请查阅gaps and islands。 - Ross Presser

Answer 1

您可以使用Sequence - ROW_NUMBER() OVER (ORDER BY ID, Val, Sequence) AS g来创建一个分组：

SELECT
  ID,
  MIN(Sequence) AS Sequence,
  Val,
  COUNT(*) AS cnt
FROM
(
  SELECT
    ID,
    Sequence,
    Sequence - ROW_NUMBER() OVER (ORDER BY ID, Val, Sequence) AS g,
    Val
  FROM
    yourtable
) AS s
GROUP BY
  ID, Val, g

请查看这个fiddle，链接在这里。

- fthiella