I have a table structure with 4 essential columns:
match_id
date
player_id
kills (of a player in the current game)
The table will have data for many different player_ids, and what I want to calculate is how many kills each player has generated in total (from all matches he has ever played at that given point in time) after each game ordered by date asc.
So for instance if he gets 20 kills in his first match, his career sum is 20. If he gets 15 kills in the next match his career kill is 35. I would like a query that shows each of these total career kills after each match.
Previously I solved this by looping through all the rows in python and keeping track of all the players kills, but I assume there is a much better way for this.
Advertisement
Answer
Well, this kind of query can be easily achieved by windows function which finally introduced in MySQL 8.0 :
select player_id , match_id , date , kills , sum(kills) over (partition by player_id order by date asc , match_id asc) as career_kills from `match`;
Here we rely on the default setting of window frame when order by
is present in over
, which is equivalent to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW