统计表中值的连续出现次数

Question

我有下表

create table #t (Id int, Name char)

insert into #t values
(1, 'A'),
(2, 'A'),
(3, 'B'),
(4, 'B'),
(5, 'B'),
(6, 'B'),
(7, 'C'),
(8, 'B'),
(9, 'B')

我想计算名称列中的连续值

+------+------------+
| Name | Repetition |
+------+------------+
| A    |          2 |
| B    |          4 |
| C    |          1 |
| B    |          2 |
+------+------------+

我尝试过的最好的事情是：

 select Name
, COUNT(*) over (partition by Name order by Id) AS Repetition
from #t
order by Id

但它没有给我预期的结果

原文由 FLICKER 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

一种方法是行号的差异：

 select name, count(*)
from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by name order by id)
             ) as grp
      from t
     ) t
group by grp, name;

如果您运行子查询并分别查看每个行号的值然后查看差异，则逻辑最容易理解。

原文由 Gordon Linoff 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

您可以使用诸如 LAG 类的窗口函数并运行总计：

 WITH cte AS (
 SELECT Id, Name, grp = SUM(CASE WHEN Name = prev THEN 0 ELSE 1 END) OVER(ORDER BY id)
 FROM (SELECT *, prev = LAG(Name) OVER(ORDER BY id) FROM t) s
)
SELECT name, cnt = COUNT(*)
FROM cte
GROUP BY grp,name
ORDER BY grp;

db<>小提琴演示

第一个 cte 返回组号：

 +-----+-------+-----+
| Id  | Name  | grp |
+-----+-------+-----+
|  1  | A     |   1 |
|  2  | A     |   1 |
|  3  | B     |   2 |
|  4  | B     |   2 |
|  5  | B     |   2 |
|  6  | B     |   2 |
|  7  | C     |   3 |
|  8  | B     |   4 |
|  9  | B     |   4 |
+-----+-------+-----+

主要查询基于 grp 列进行分组：

 +-------+-----+
| name  | cnt |
+-------+-----+
| A     |   2 |
| B     |   4 |
| C     |   1 |
| B     |   2 |
+-------+-----+

原文由 Lukasz Szozda 发布，翻译遵循 CC BY-SA 4.0 许可协议