Блог

Я работаю над таблицей Postgres, которая имитирует события во время игры в бейсбол (соответствующие поля включают id, game_id, home_score, away_score, game_id), и я пытаюсь определить момент в игре, когда лидерство изменилось в последний раз (по упрощенному способу определения выигрыша и проигрыша питчеров в конце матча).время).

Я смог использовать last_value , чтобы найти, кто является окончательным победителем, и я могу определить последнюю запись, где это НЕ так… но мне нужна следующая запись, и я даже не могу понять, какой лучший способ использовать lag(id,1) OVER (order by id) для этой части. Я просто чувствую, что есть более элегантный способ сделать это.

 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
  

Попробуйте пометить основные изменения, а затем выполните distinct on . Это запустит его для всех игр:

 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;