Skip to content

Instantly share code, notes, and snippets.

@jmakeig
Last active September 15, 2023 22:51
Show Gist options
  • Save jmakeig/22da6fbf6492194c67b71b738ca492a4 to your computer and use it in GitHub Desktop.
Save jmakeig/22da6fbf6492194c67b71b738ca492a4 to your computer and use it in GitHub Desktop.
Use case: Timeseries data that’s stored densely, but needs to be reported sparsely, i.e. with explicit zeros for missing periods. (RIGHT JOIN for the win!)
-- PostgreSQL 14
SELECT
days AS observation_date,
-- Explicitly coalescing missing values to zero.
-- This logic will be use case-dependent.
coalesce(metrics.metric, 0) AS metric
FROM
-- Replace this with the dense metrics table
(values ('2023-01-01'::date, 111111), ('2023-06-15'::date, 222222)) AS metrics (observation_date, metric)
RIGHT JOIN
generate_series('2023-01-01'::timestamp, now()::timestamp, '1 day'::interval) AS days
ON metrics.observation_date = days
ORDER BY
days ASC
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment