Skip to content

Instantly share code, notes, and snippets.

@hansthen
Last active January 7, 2023 19:39
Show Gist options
  • Save hansthen/5acd6c49eadf74fb01311ae37c9277dd to your computer and use it in GitHub Desktop.
Save hansthen/5acd6c49eadf74fb01311ae37c9277dd to your computer and use it in GitHub Desktop.
Postgres query to resample data to a specific interval (based on something from SO, but adapted use only one table)
SELECT a.user_id, b.balance, d.as_of_date
FROM (
SELECT d AS as_of_date
FROM generate_series(timestamp '2015-12-29', '2016-01-10', interval '1 hour') d
) d
JOIN LATERAL (
select user_id, as_of_date
from balances
where as_of_date <= d.as_of_date
order by as_of_date
limit 1
) a ON true
LEFT JOIN LATERAL (
SELECT user_id, balance
FROM balances
WHERE user_id = a.user_id
AND as_of_date <= d.as_of_date
ORDER BY as_of_date DESC
LIMIT 1
) b ON true
ORDER BY a.user_id, d.as_of_date;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment