I want to know how mysql 5.7 engine works the same as sum over part. Cannot replace query engine because it is not a personal operation.
There was an attempt to resolve it through the @set variable function, but I find it difficult to get a cumulative sum for each of the four columns.
I looked at the link below, but it was a little bit difficult to resolve. how to rank over partition in MySql
My table
id a b c d
----------------------------
abs 1 0 0 1
abs 0 1 1 0
abs 1 0 1 0
abs 1 1 1 1
qwe 0 0 0 0
qwe 0 0 0 1
qwe 1 0 1 0
qwe 1 1 0 1
trx 0 1 1 0
trx 1 1 0 0
Expected
id a b c d
----------------------------
abs 1 0 0 1
abs 1 1 1 1
abs 2 1 2 1
abs 3 2 3 2
qwe 0 0 0 0
qwe 0 0 0 1
qwe 1 0 1 1
qwe 2 1 1 2
trx 0 1 1 0
trx 1 2 1 0
Thanks in advance.
You want cumulative sums for each column. In order for this to work, you need a column that specifies the ordering -- SQL tables represent unordered sets.
Assuming you have such a column, then the following would often work:
select t.*,
(@a := if(@id = id, @a + a, 0)) as a,
(@b := if(@id = id, @b + b, 0)) as b,
(@c := if(@id = id, @c + c, 0)) as c,
(@d := if(@id = id, @d + d, 0)) as d,
@id := id
from (select t.*
from t
order by t.id, ? -- column for the ordering
) t cross join
(select @id := -1, @a := 0, @b := 0, @c := 0 @d := 0) params;
However, this is not guaranteed to work, because MySQL does not guarantee the order of evaluation of expressions in a SELECT. So, I think subqueries might be the simplest method:
select t.id,
(select sum(t2.a) from t t2 where t2.id = t.id and t2.? <= t.?) as a,
(select sum(t2.b) from t t2 where t2.id = t.id and t2.? <= t.?) as b,
(select sum(t2.c) from t t2 where t2.id = t.id and t2.? <= t.?) as c,
(select sum(t2.d) from t t2 where t2.id = t.id and t2.? <= t.?) as d
from t;