Created
November 8, 2022 16:05
-
-
Save tucnak/85905bbf5b9b166d20180065879c8b7b to your computer and use it in GitHub Desktop.
Sparse elapsed duration in a Timescale continuous aggregate
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- the custom aggregate state | |
CREATE TYPE elapsed__t AS (acc interval, cur timestamptz); | |
-- state transition function | |
CREATE FUNCTION elapsed__sf(s elapsed__t, x interval, cur timestamptz) | |
RETURNS elapsed__t IMMUTABLE LANGUAGE sql AS $$ | |
SELECT | |
coalesce(s.acc, '0'::interval) + case | |
when cur-s.cur > x | |
then null | |
else cur-s.cur end, | |
cur $$; | |
-- final function | |
CREATE FUNCTION elapsed__ff(s elapsed__t) | |
RETURNS interval IMMUTABLE LANGUAGE sql AS $$ SELECT s.acc $$; | |
-- elapsed('15 minutes', time order by time) | |
CREATE AGGREGATE elapsed(interval, timestamptz) ( | |
STYPE = elapsed__t, | |
SFUNC = elapsed__sf, | |
FINALFUNC = elapsed__ff); | |
-- continuous aggregate | |
CREATE MATERIALIZED VIEW umami.session_elapsed | |
WITH (timescaledb.continuous) AS | |
SELECT | |
time_bucket('1 day', time) ß, | |
website_id, | |
session_id, | |
elapsed('5 minutes', time order by time) active | |
elapsed('15 minutes', time order by time) passive | |
elapsed('1 hour', time order by time) engaged | |
FROM umami.event e | |
GROUP BY ß, website_id, session_id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment