Question :
i have a data frame as “a”.
a:
[,1] 1 2 3 0 1 2 0 [,2] 0 0 1 2 3 0 1 [,3] 1 2 3 4 5 0 0 and dim(a)=7*3
i want to create a new column including maximum value for each set of consecutive numbers till 0. and calculate for next set till next zero. like this:
result:
[,1] 3 3 3 0 2 2 0 [,2] 0 0 3 3 3 0 1 [,3] 5 5 5 5 5 0 0
unfortunatly i couldn’t put the picture of my data frame(a).
please help me to write the right function
Answer :
If you are doing a difference between first column ([1,]) and d1 you will get something like this:
SELECT id,d1
,id-d1 as diff_d1
FROM Table1
output:
id d1 diff_d1
1 1 0
2 2 0
3 3 0
4 0 4
5 1 4
6 2 4
7 0 7
Doing a max on [d1] and grouping by [diff_d1] column you will get : here
maxD1
3
2
0
Putting all together
;WITH cteSource AS
(SELECT
id,
d1,d2,d3
,MAX(d1) OVER(PARTITION BY id-d1 ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)as maxD1
,MAX(d2) OVER(PARTITION BY id-d2 ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)as maxD2
,MAX(d3) OVER(PARTITION BY id-d3 ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)as maxD3
FROM Table1
)
SELECT id
,CASE WHEN d1<> 0 THEN maxD1
ELSE 0 END AS d1
,CASE WHEN d2 <> 0 THEN maxD2
ELSE 0 END AS d2
,CASE WHEN d3 <> 0 THEN maxD3
ELSE 0 END AS d3
FROM cteSource
Output:
id d1 d2 d3
1 3 0 5
2 3 0 5
3 3 3 5
4 0 3 5
5 2 3 5
6 2 0 0
7 0 1 0