I have one column with zeros and ones with preserved order, where zeros indicate breaks between pieces of data. Here is the example
我有一列零和一个保留顺序的列,其中零表示数据之间的中断。这是一个例子
A Ord
1 1
1 2
0 3
0 4
0 5
1 6
1 7
1 8
0 9
1 10
What I would like to obtain is the same column where all elements after zero (or zeros) increase by 1 in comparison to previous pack of non-zero elements. Thus above example after transformartion has to look like this:
我想要获得的是同一列,其中零(或零)之后的所有元素与先前的非零元素包相比增加1。因此,转换后的上述示例必须如下所示:
A Ord
1 1
1 2
0 3
0 4
0 5
2 6
2 7
2 8
0 9
3 10
The number of zeros and ones in each group can be any. I tried to do it using different combinations of row_number() and join, but eventually failed.
每组中的零和1的数量可以是任意数量。我尝试使用row_number()和join的不同组合来做,但最终失败了。
1 个解决方案
#1
3
If you are on SQL Server 2012 you can use lag() and sum() over().
如果您使用的是SQL Server 2012,则可以使用lag()和sum()over()。
select iif(T.A = 0, 0, sum(T.C) over(order by T.Ord rows unbounded preceding)) as A,
T.Ord
from (
select Y.A,
Y.Ord,
iif(Y.A = 1 and isnull(lag(Y.A) over(order by Y.Ord), 0) = 0, 1, 0) as C
from YourTable as Y
) as T
#1
3
If you are on SQL Server 2012 you can use lag() and sum() over().
如果您使用的是SQL Server 2012,则可以使用lag()和sum()over()。
select iif(T.A = 0, 0, sum(T.C) over(order by T.Ord rows unbounded preceding)) as A,
T.Ord
from (
select Y.A,
Y.Ord,
iif(Y.A = 1 and isnull(lag(Y.A) over(order by Y.Ord), 0) = 0, 1, 0) as C
from YourTable as Y
) as T