CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsgaps-and-islands
hcac
hcac

Reputation: 133

Winning streaks in Postgres

As the title says - I have a Postgres database with players and matches, and I want to get a list of the winning streaks. Note that I'm not just interested in the numbers, i.e., I see a lot of questions on Stackoverflow concerning just the highest winning streak - I want to be able to get more data and details about the streaks, such as all the games.

Specifically, this is the DB I'm looking at: http://aligulac.com/about/db/. I've created a view based on this that has the essential columns, basically looking like this:

player_id | match_id | score
-------------------------------
82        | 2847     |  2
82        | 3733     |  3
82        | 4348     |  1
82        | 5237     |  1
82        | 5363     |  3
82        | 7274     | -1
51        | 2347     |  3
51        | 3746     |  2
51        | 5037     |  3
51        | 7269     | -1
...       | ...      |  ...

"score" is "player_score - opponent_score", so I want the streaks of positive scores from the same player without interruption. A resulting table could look like this:

player_id | match_id | streak
-------------------------------
82        | 5363     |  5
51        | 5037     |  3
...       | ...      |  ...

Which would allow me to retrieve the last match of a streak, and since I know how long the streak was, also all the matches before the streak. Is there a good way to store an array of IDs such that I could store all relevant matchIDs that were part of the streak in the row as well? Maybe in an enum or so?

What I did so far is to make a Postgres view of all matches, ordered by player ID/match ID, exported it into Excel CSV, and then used an Excel formula + filters to arrive at the result. It looks like this currently. But of course that's not really easy to update and I'd love to be able to do it via Query directly, or at least part of it - or even better using the Aligulac API (http://aligulac.com/about/api/).

Any ideas for how to do something along those lines? Thanks!

Upvotes: 1

Views: 957

Answers (1)

klin
klin

Reputation: 121774

Use two window functions to designate series and aggregate functions to get the longest streak:

select distinct on (player_id)
    player_id, 
    max(match_id) as match_id, 
    count(*) as streak
from (
    select 
        player_id, match_id, score, 
        sum(grp) over w as grp
    from (
        select 
            player_id, match_id, score, 
            (score < 0 and lag(score, 1, 1) over w > 0)::int as grp
        from my_view
        window w as (partition by player_id order by match_id)
        ) s
    window w as (partition by player_id order by match_id)
    ) s
where score > 0
group by player_id, grp
order by player_id desc, streak desc

 player_id | match_id | streak 
-----------+----------+--------
        82 |     5363 |      5
        51 |     5037 |      3
(2 rows)

Upvotes: 4

Related Questions