CODEWITHSUNDEEP
postgresqlwindow-functions
MitchO
MitchO

Reputation: 43

Find the record on a Postgres table where the final value change happened?

I am working on a Postgres table that simulates events during a baseball game (pertinent fields include id, game_id, home_score, away_score, game_id), and I'm trying to identify the moment in the game where the lead changed for the final time (over simplified way of determining winning and losing pitchers at the time).

I've been able to use last_value to find who the final winner is, and I can identify the last record where that ISN'T the case ... but I want the next record, and I can't quite even wrap my ahead around the best way to use lag(id,1) OVER (order by id) for this part. I just feel like there's an overall more elegant way to do this.

SELECT DISTINCT ON (id) * FROM
(
    SELECT 
    *, 
    last_value(win_state) OVER 
    (
        ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_state
    FROM
    (
        SELECT id, home_score, away_score,
        CASE
          WHEN home_score > away_score THEN 'home' 
          when away_score > home_score THEN 'away'
          ELSE 'tie'
        END AS win_state
        FROM DATA.game_events
        WHERE game_id = foo
        ORDER BY id
    ) a
) b
WHERE win_state <> last_state
ORDER BY id DESC
LIMIT 1

Upvotes: 0

Views: 31

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

Try marking the lead changes and then do a distinct on. This will run it for all games:

with stats as (
  select id, game_id,
         case
           when home_score > away_score then 'home'
           when home_score < away_score then 'away'
           else 'tie'
         end as win_state
    from data.game_events
), changes as (
  select id, game_id, win_state,
         case
           when win_state = lag(win_state) 
             over (partition by game_id
                       order by id) then false
           else true
         end as lead_change
    from stats
)
select distinct on (game_id) ge.*
  from data.game_events ge
       join changes c
         on c.id = ge.id
 where c.lead_change
 order by game_id, id desc;

Upvotes: 1

Related Questions