更新表:没有使用游标或while循环的前行摘要

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:

我想快速更新表，根据前两列填充三个正确的列，而不使用游标或While循环。我的表有数百万行，使用while循环计算需要花费数小时。其结果应该是:

|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

谢谢你！

3 个解决方案

#1

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

使用一个带有row_number()的通用表表达式和一个间隔和岛屿样式解决方案，以及另一个用于计数的row_number():

;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

rextester演示: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

#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

#3

You can use LAG analytic function to access previous row value, calculate up/down votes on a base of previous row and use it then to update like it described here or in the way like it suggested by SqlZim. Nevertheless it can be still slow for millions of records, especially if you have indexes

您可以使用LAG analytic function来存取先前的行值，在先前的行基础上计算向上/向下的投票，然后使用它来更新像这里描述的或者像SqlZim建议的那样。尽管如此，对于数百万条记录来说，它仍然是缓慢的，特别是如果您有索引的话

However, even a cursor can act fast if you use short transactions and doing this operation by relatively small batches (1000 - 10000 per one batch)

然而，如果您使用短事务并以相对较小的批数(每批1000 - 10000)执行此操作，即使是游标也可以快速执行。