Skip to content

Instantly share code, notes, and snippets.

@willglynn
Created November 16, 2012 21:04
Show Gist options
  • Save willglynn/4090893 to your computer and use it in GitHub Desktop.
Save willglynn/4090893 to your computer and use it in GitHub Desktop.
Window functions for row pairs
-- 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);
@willglynn
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment