how can i write the max value of different consecutive values in the new column by length

Posted on

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

dbfiddle

Leave a Reply

Your email address will not be published.