Обновление с помощью Self Join Sql Server

Question

У меня есть огромные данные и образец таблицы выглядит ниже

+-----------+------------+-----------+-----------+
| Unique_ID |    Date    | RowNumber | Flag_Date |
+-----------+------------+-----------+-----------+
|         1 | 6/3/2014   |         1 | 6/3/2014  |
|         1 | 5/22/2015  |         2 | NULL      |
|         1 | 6/3/2015   |         3 | NULL      |
|         1 | 11/20/2015 |         4 | NULL      |
|         2 | 2/25/2014  |         1 | 2/25/2014 |
|         2 | 7/31/2014  |         2 | NULL      |
|         2 | 8/26/2014  |         3 | NULL      |
+-----------+------------+-----------+-----------+

Теперь мне нужно проверить, есть ли разница между датой во 2-й строке и Flag_date в 1-й строке. Если разница больше 180, то 2-я строка Flag_date должна быть обновлена ​​датой во 2-й строке, иначе она должна быть обновлена ​​с помощью Flag_date в 1-й строке. И то же правило следует для всех строк с одинаковым unique_ID

update a
set a.Flag_Date=case when DATEDIFF(dd,b.Flag_Date,a.[Date])>180 then a.[Date] else b.Flag_Date end
from Table1 a
inner join Table1 b
on a.RowNumber=b.RowNumber+1 and a.Unique_ID=b.Unique_ID

Приведенный выше запрос на обновление, когда он выполняется один раз, обновляется только вторая строка под каждым Unique_ID, и результат выглядит следующим образом

+-----------+------------+-----------+------------+
| Unique_ID |    Date    | RowNumber | Flag_Date  |
+-----------+------------+-----------+------------+
|         1 | 2014-06-03 |         1 | 2014-06-03 |
|         1 | 2015-05-22 |         2 | 2015-05-22 |
|         1 | 2015-06-03 |         3 | NULL       |
|         1 | 2015-11-20 |         4 | NULL       |
|         2 | 2014-02-25 |         1 | 2014-02-25 |
|         2 | 2014-07-31 |         2 | 2014-02-25 |
|         2 | 2014-08-26 |         3 | NULL       |
+-----------+------------+-----------+------------+

И мне нужно бежать четыре раза, чтобы достичь желаемого результата

+-----------+------------+-----------+------------+
| Unique_ID |    Date    | RowNumber | Flag_Date  |
+-----------+------------+-----------+------------+
|         1 | 2014-06-03 |         1 | 2014-06-03 |
|         1 | 2015-05-22 |         2 | 2015-05-22 |
|         1 | 2015-06-03 |         3 | 2015-05-22 |
|         1 | 2015-11-20 |         4 | 2015-11-20 |
|         2 | 2014-02-25 |         1 | 2014-02-25 |
|         2 | 2014-07-31 |         2 | 2014-02-25 |
|         2 | 2014-08-26 |         3 | 2014-08-26 |
+-----------+------------+-----------+------------+

Есть ли способ, где я могу запустить обновление только один раз, и все строки обновляются.

Спасибо!

Answer 1

Если вы используете SQL Server 2012+, то вы можете использовать lag():

with toupdate as (
      select t1.*,
             lag(flag_date) over (partition by unique_id order by rownumber) as prev_flag_date
      from table1 t1
     )
update toupdate
    set Flag_Date = (case when DATEDIFF(day, prev_Flag_Date, toupdate.[Date]) > 180
                          then toupdate.[Date] else prev_Flag_Date
                     end);

И эта версия, и ваша версия могут использовать индекс table1(unique_id, rownumber) или, еще лучше, table1(unique_id, rownumber, flag_date),

РЕДАКТИРОВАТЬ:

В более ранних версиях это могло бы иметь лучшую производительность:

with toupdate as (
      select t1.*, t2.flag_date as prev_flag_date
      from table1 t1 outer apply
           (select top 1 t2.flag_date
            from table1 t2
            where t2.unique_id = t1.unique_id and
                  t2.rownumber < t1.rownumber
            order by t2.rownumber desc
           ) t2
     )
update toupdate
    set Flag_Date = (case when DATEDIFF(day, prev_Flag_Date, toupdate.[Date]) > 180
                          then toupdate.[Date] else prev_Flag_Date
                     end);

CTE может использовать один и тот же индекс - и важно иметь этот индекс. Причиной лучшей производительности является то, что вы присоединились к row_number() не может использовать индекс в этом поле.