Update table: Summary of previous rows without using cursor or while loop

Question

I have a table as below:

|ID | UpDown | ContinuesUP | ContinuesDown | ContinuesStop|
|---------------------------------------------------------|
|1  |   1    |             |               |              |
|---------------------------------------------------------|
|2  |   1    |             |               |              |
|---------------------------------------------------------|
|3  |   1    |             |               |              |
|---------------------------------------------------------|
|4  |  -1    |             |               |              |
|---------------------------------------------------------|
|5  |   1    |             |               |              |
|---------------------------------------------------------|
|6  |   0    |             |               |              |
|---------------------------------------------------------|
|7  |  -1    |             |               |              |
|---------------------------------------------------------|
|8  |  -1    |             |               |              |
|---------------------------------------------------------|
|9  |  -1    |             |               |              |
|---------------------------------------------------------|
|10 |  -1    |             |               |              |
|---------------------------------------------------------|
|11 |   0    |             |               |              |
|---------------------------------------------------------|
|12 |   0    |             |               |              |
|---------------------------------------------------------|
|13 |   1    |             |               |              |
|---------------------------------------------------------|
|14 |   1    |             |               |              |
|---------------------------------------------------------|
|15 |  -1    |             |               |              |
|---------------------------------------------------------|

I want to fast update the table to populate the three right columns based on first two columns without using Cursor or While loop. My table has millions of rows and it takes hours when i use while loop to calculate that. The Result Should be this:

|ID | UpDown | ContinuesUP | ContinuesDown | ContinuesStop|
|---------------------------------------------------------|
|1  |   1    |     1       |               |              |
|---------------------------------------------------------|
|2  |   1    |     2       |               |              |
|---------------------------------------------------------|
|3  |   1    |     3       |               |              |
|---------------------------------------------------------|
|4  |  -1    |             |       1       |              |
|---------------------------------------------------------|
|5  |   1    |     1       |               |              |
|---------------------------------------------------------|
|6  |   0    |             |               |       1      |
|---------------------------------------------------------|
|7  |  -1    |             |       1       |              |
|---------------------------------------------------------|
|8  |  -1    |             |       2       |              |
|---------------------------------------------------------|
|9  |  -1    |             |       3       |              |
|---------------------------------------------------------|
|10 |  -1    |             |       4       |              |
|---------------------------------------------------------|
|11 |   0    |             |               |       1      |
|---------------------------------------------------------|
|12 |   0    |             |               |       2      |
|---------------------------------------------------------|
|13 |   1    |      1      |               |              |
|---------------------------------------------------------|
|14 |   1    |      2      |               |              |
|---------------------------------------------------------|
|15 |  -1    |             |       1       |              |
|---------------------------------------------------------|

any advice and suggestions will be greatly appreciated

Thank you

Answer 1

using a common table expression with row_number() and a gaps and islands style solution, and another row_number()for the counting:

;with cte as (
  select 
      id
    , updown
    , grp = row_number() over (order by id) - row_number() over (partition by UpDown order by id)
  from t
)
select 
    id
  , updown
  , ContinuesUp   = case when updown =  1 then row_number() over (partition by updown, grp order by id) end
  , ContinuesDown = case when updown = -1 then row_number() over (partition by updown, grp order by id) end
  , ContinuesStop = case when updown =  0 then row_number() over (partition by updown, grp order by id) end
from cte
order by id

rextester demo: http://rextester.com/KLZ58591

returns:

+----+--------+-------------+---------------+---------------+
| id | updown | ContinuesUp | ContinuesDown | ContinuesStop |
+----+--------+-------------+---------------+---------------+
|  1 |      1 | 1           | NULL          | NULL          |
|  2 |      1 | 2           | NULL          | NULL          |
|  3 |      1 | 3           | NULL          | NULL          |
|  4 |     -1 | NULL        | 1             | NULL          |
|  5 |      1 | 1           | NULL          | NULL          |
|  6 |      0 | NULL        | NULL          | 1             |
|  7 |     -1 | NULL        | 1             | NULL          |
|  8 |     -1 | NULL        | 2             | NULL          |
|  9 |     -1 | NULL        | 3             | NULL          |
| 10 |     -1 | NULL        | 4             | NULL          |
| 11 |      0 | NULL        | NULL          | 1             |
| 12 |      0 | NULL        | NULL          | 2             |
| 13 |      1 | 1           | NULL          | NULL          |
| 14 |      1 | 2           | NULL          | NULL          |
| 15 |     -1 | NULL        | 1             | NULL          |
+----+--------+-------------+---------------+---------------+

---

As an update:

;with cte as (
  select *
    , Continues = row_number() over (partition by updown, grp order by id)
  from (
    select *
      , grp = row_number() over (order by id) - row_number() over (partition by UpDown order by id)
    from t
  ) sub
)
update cte set 
    ContinuesUp   = case when updown =  1 then Continues end
  , ContinuesDown = case when updown = -1 then Continues end
  , ContinuesStop = case when updown =  0 then Continues end

Answer 2

You can use this query for the update.

;WITH T AS 
(
select *, 
    RNID = ROW_NUMBER() OVER(  ORDER BY ID) 
    ,RNUpDown = ROW_NUMBER() OVER( PARTITION BY UpDown ORDER BY ID) 
from @T
)
,Y AS 
(
    SELECT *, RN=ROW_NUMBER() OVER(PARTITION BY UpDown,RNID-RNUpDown ORDER BY ID)   FROM T 
)
UPDATE Y SET 
    ContinuesUP = CASE WHEN UpDown = 1 THEN RN ELSE ContinuesUP END,
    ContinuesDown = CASE WHEN UpDown = -1 THEN RN ELSE ContinuesUP END,
    ContinuesStop = CASE WHEN UpDown = 0 THEN RN ELSE ContinuesUP END