Window functions for row pairs

gistfile1.sql

-- PostgreSQL current
SELECT
  round,
  first_value(time) OVER pair AS first_time,
  last_value(time) OVER pair AS last_time,
  first_value(groundstatsid IS NULL) OVER pair AS first_is_standing,
  last_value(groundstatsid IS NULL) OVER pair AS last_is_standing
FROM matchstats
WINDOW pair AS (PARTITION BY round ORDER BY time ROWS 1 PRECEDING);

-- PostgreSQL 8.4
SELECT
  round,
  lag(time, 1, time) OVER pair AS first_time,
  last_value(time) OVER pair AS last_time,
  lag(groundstatsid IS NULL, 1, groundstatsid IS NULL) OVER pair AS first_is_standing,
  last_value(groundstatsid IS NULL) OVER pair AS last_is_standing
FROM matchstats
WINDOW pair AS (PARTITION BY round ORDER BY time ROWS UNBOUNDED PRECEDING);

Related to this StackOverflow answer.