使用按另一列分組的滾動總和進行更新

我正在嘗試建構一個查詢來更新一個看起來像這樣的 MySQL 8.0 表

-----------------------
user_id | total | delta
-----------------------
     1 |     0 |    30
     1 |     0 |   -10
     2 |     0 |    -5
     2 |     0 |    10
     2 |     0 |   -10
     3 |     0 |    30

進入這個：

-----------------------
user_id | total | delta
-----------------------
     1 |    30 |    30
     1 |    20 |   -10
     2 |    -5 |    -5
     2 |     5 |    10
     2 |    -5 |   -10
     3 |    30 |    30

基本上它應該為每個使用者計算一個滾動總和，並用每一行的值更新表。您可以將每一行視為發生在某個時間點的事務，但為簡單起見，我剛剛刪除了日期資訊。對於這個例子，假設它們已經按“日期”排序。

做這樣的事情很容易對整個列進行滾動求和：

SET @sum := 0;
UPDATE tablename
SET total = (@sum := @sum + delta)

但我不確定如何讓它執行單獨的滾動總和。我也有數以億計的條目，所以它需要是高性能的。

我想我得到了我需要的東西。把它貼在這里以防其他人需要這樣的東西：

SET @sum := 0;
SET @curr_user_id := 0;
UPDATE tablename
SET total = (@sum := delta +
            CASE WHEN user_id = @curr_user_id
                 THEN @sum 
                 ELSE (@curr_user_id := user_id) - user_id
            END)
ORDER BY user_id;

它非常 hacky 並試圖保持 ‘rolling user_id’ @curr_user_id。它應該逐行掃描，並且無論何時user_id更改，我們都應該點擊ELSE子句並更新@curr_user_id到新的user_id，也返回零並重置@sum。

隨意發布一個不那麼 hacky 的替代方案，特別是因為在表達式中設置使用者變數似乎已被棄用，並將在 MySQL 的未來版本中刪除。

您將需要對行進行一些定義的排序，否則，CUMULATIVE SUM每次執行更新時都會得到不同的結果。我添加了一個自動增量列，但您可能有一個可以使用的時間戳或類似的：

create table T 
( seqid int not null auto_increment primary key
, user_id int not null
, total int not null
, delta int not null 
);

insert into T (user_id, total, delta)
values (1, 0, 30)
    , (1, 0, -10)
    , (2, 0, -5)
    , (2, 0, 10)
    , (2, 0, -10)
    , (3, 0, 30);

視窗函式是在大約 20 年前在 SQL99 中引入的，但直到最近才進入 MySQL。它們對於此類任務非常方便：

with TT as (
   select seqid, user_id, delta, total
        , sum(delta) over (partition by user_id order by seqid) as new_total
   from T
)
update T, TT
   set T.total = TT.new_total
where T.seqid = TT.seqid;

由於某種原因，我無法讓更新與 ansi 連接一起使用，它留給讀者作為練習；-)

引用自：https://dba.stackexchange.com/questions/243958