如何使用保留订单的运行计数形成查询 [英] How do I form a query with a running count retaining the order

问题描述

我有一个看起来像这样的跟踪表

I have a trace table which looks like this

我想得到一个运行总数，它看起来像下面的输出 - 保留顺序非常重要 - 因为这是存储程序的执行顺序 - 它将帮助我分析系统中的瓶颈

I'd like to get a running total which looks like the following output - its very important that I retain the order - as this is the execution order of the stored porcedures - It will help me analyze bottle necks in the system

我试过了

select max(RowNumber),objectname, count(1) from rob
where eventclass = 42
group by objectname

但这会打乱顺序

这甚至可以在 SQL 中实现吗?

Is this even possible in SQL?

更新:我试过了

select RowNumber,objectname, count(1) from rob
where eventclass = 42
group by objectname,RowNumber
order by RowNumber

但是这个(因为查询非常正确地表示按行号分组(必须有那个才能按顺序排列))

But this (as the query quite rightly says groups by rownumber (have to have that to have it in the order by) )

推荐答案

select objectname, 
       count(*)
from (
     select RowNumber,
            objectname,
            row_number() over(order by RowNumber) - row_number() over(order by objectname, RowNumber) as grp
     from rob
     where eventclass = 42
     ) as T
group by grp, objectname
order by min(RowNumber)

使用表变量的工作示例.

Working sample using a table variable.

declare @T table
(
  RowNumber int,
  objectname varchar(50)
)

insert into @T values
(8, 'f_system_log_init'),
(10, 'f_purge_system_log'),
(25, 'f_system_log_msg'),
(65, 'f_system_log_msg'),
(104, 'f_system_log_msg'),
(143, 'f_system_log_msg'),
(182, 'f_system_log_msg'),
(221, 'f_system_log_msg'),
(5015, 'f_get_system_logs_parent_log_id_for_dataloader'),
(5055, 'f_system_log_msg'),
(5096, 'f_system_log_msg')

select objectname, 
       count(*)
from (
     select RowNumber,
            objectname,
            row_number() over(order by RowNumber) - row_number() over(order by objectname, RowNumber) as grp
     from @T
     ) as T
group by grp, objectname
order by min(RowNumber)

结果:

objectname                                         
-------------------------------------------------- -----------
f_system_log_init                                  1
f_purge_system_log                                 1
f_system_log_msg                                   6
f_get_system_logs_parent_log_id_for_dataloader     1
f_system_log_msg                                   2

这篇关于如何使用保留订单的运行计数形成查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！