0

I'm trying to build a query to update a MySQL 8.0 table that looks like this

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

Into this:

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

Basically it should compute a rolling sum for each user and update the table with the value at each row. You can think of each row as being a transaction that takes place at a certain timepoint, but for simplicity's sake I've just removed the date info. For this example just presume that they're already ordered by 'date'.

It's easy to do something like this to do a rolling sum over the entire column:

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

But I'm not sure how to get it to perform separate rolling sums. I also have hundreds of millions of entries, so it needs to be performant.

  • Please tag your MySQL version. – McNets Jul 27 at 21:16
  • What version of MySQL? 8.0 has "windowing functions" to do things like this. – Rick James Jul 27 at 21:17
1

You will need some defined ordering of your rows, otherwise, you will end up with a different CUMULATIVE SUM every time you run your update. I added an auto-increment column, but you likely have a timestamp or similar you can use:

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);

Window functions were introduced in SQL99 some 20 years ago, but only recently made it into MySQL. They are very handy for this kind of task:

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;

For some reason I could not get the update to work with an ansi join, it is left as an exercise for the reader;-)

  • This seems a lot more kosher than my attempt, but its also almost 2x slower on my tests! Maybe because on my real data I have to order by 3 different columns, having to store those on TT as well. Also, will 'with TT as ()' generate a temporary table? Because that is an absolute no-go: I have so many rows it will almost certainly cause problems – Daniel Jul 28 at 13:13
  • It's tricky to foresee whether MySQL will decide to create a temp or not without a real system to investigate, check explain to see if that reveals anything. As for performance, your query does not force an order of evaluation for your rows, so it may start with 30 or -10 for user 1 which will give different results. If you want to test my query with a random order, you can try ` sum(delta) over (partition by user_id order by 'a')` and see if that levels performance a bit. – Lennart Jul 28 at 16:55
0

I think I got what I needed. Posting it here in case anybody else needs something like this:

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;

Its very hacky and tries to keep a 'rolling user_id' @curr_user_id. It should scan row by row, and whenever user_id changed, we should hit the ELSE clause and update @curr_user_id to the new user_id, also returning zero and resetting @sum.

Feel free to post a less hacky alternative, especially because setting user variables within expressions seems deprecated and will be removed in a future release of MySQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.