按值对行进行分组和计数，直到它发生变化

本文介绍了按值对行进行分组和计数，直到它发生变化的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表，消息在发生时存储在其中.通常有一个消息A"，有时 A 被单个消息B"分隔.现在我想对这些值进行分组，以便我能够分析它们，例如找到最长的A"-条纹或A"-条纹的分布.

I have a table where messages are stored as they happen. Usually there is a message 'A' and sometimes the A's are separated by a single message 'B'. Now I want to group the values so I'm able to analyze them, for example finding longest 'A'-streak or distribution of 'A'-streaks.

我已经尝试过 COUNT-OVER 查询，但每条消息都会继续计数.

I already tried a COUNT-OVER query but that keeps on counting for each message.

SELECT message, COUNT(*) OVER (ORDER BY Timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

这是我的示例数据:

Timestamp Message 20150329 00:00 A 20150329 00:01 A 20150329 00:02 B 20150329 00:03 A 20150329 00:04 A 20150329 00:05 A 20150329 00:06 B

我想要以下输出

Message COUNT A 2 B 1 A 3 B 1

推荐答案

那很有趣 :)

;WITH cte as ( SELECT Messages.Message, Timestamp, ROW_NUMBER() OVER(PARTITION BY Message ORDER BY Timestamp) AS gn, ROW_NUMBER() OVER (ORDER BY Timestamp) AS rn FROM Messages ), cte2 AS ( SELECT Message, Timestamp, gn, rn, gn - rn as gb FROM cte ), cte3 AS ( SELECT Message, MIN(Timestamp) As Ts, COUNT(1) as Cnt FROM cte2 GROUP BY Message, gb) SELECT Message, Cnt FROM cte3 ORDER BY Ts

这是结果集:

Message Cnt A 2 B 1 A 3 B 1

查询可能更短，但我是这样发布的，这样您就可以看到发生了什么.结果完全符合要求.这是最重要的部分 gn - rn 这个想法是对每个分区中的行进行编号，同时对整个集中的行进行编号，然后如果您从另一个中减去一个，您将获取每个组的排名".

The query may be shorter but I post it that way so you can see what's happening. The result is exactly as requested. This is the most important part gn - rn the idea is to number the rows in each partition and at the same time number the rows in the whole set then if you subtract the one from the other you'll get the 'rank' of each group.

;WITH cte as ( SELECT Messages.Message, Timestamp, ROW_NUMBER() OVER(PARTITION BY Message ORDER BY Timestamp) AS gn, ROW_NUMBER() OVER (ORDER BY Timestamp) AS rn FROM Messages ), cte2 AS ( SELECT Message, Timestamp, gn, rn, gn - rn as gb FROM cte ) SELECT * FROM cte2 Message Timestamp gn rn gb A 2015-03-29 00:00:00.000 1 1 0 A 2015-03-29 00:01:00.000 2 2 0 B 2015-03-29 00:02:00.000 1 3 -2 A 2015-03-29 00:03:00.000 3 4 -1 A 2015-03-29 00:04:00.000 4 5 -1 A 2015-03-29 00:05:00.000 5 6 -1 B 2015-03-29 00:06:00.000 2 7 -5