按值对行进行分组和计数，直到它发生变化 [英] Grouping and counting rows by value until it changes

问题描述

我有一个表，消息在发生时存储在其中.通常有一个消息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

这篇关于按值对行进行分组和计数，直到它发生变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！