问题描述
假设我们有这个简单的架构和数据:
Suppose we have this simple schema and data:
它产生以下结果集:
我需要总结非零连续 Duration 值,并在批处理中第一行的 StartTime 显示它们.IE.我需要解决这个问题:
I need to summarize the non zero consecutive Duration values and to show them at the StartTime of the first row in the batch. I.e. I need to get to this:
我就是不知道该怎么做.
I just can't figure out how to do it.
PS:当然,真实表包含更多行.
PS: The real table contains many more rows, of course.
推荐答案
这是一个间隙和孤岛问题,需要将 IsPassed
恒定的每个部分划分到不同的组中.这可以通过计算整个表的 ROW_NUMBER()
与由 IsPassed
分区的差异来完成.然后,您可以 SUM
为每个组的 Duration
值,其中 IsPassed = False
并取 MIN(StartTime)
到给出组第一行的StartTime
:
This is a gaps and islands problem, requiring partitioning each section where IsPassed
is constant into a different group. That can be done by computing the difference between ROW_NUMBER()
over the entire table against partitioned by IsPassed
. You can then SUM
the Duration
Values for each group where IsPassed = False
and take the MIN(StartTime)
to give the StartTime
of the first row of the group:
输出:
dbfiddle 演示
这篇关于将连续的非零行标记为不同的分区?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!