Hi all,
I’m working on a dashboard that aggregates emotes from a Twitch chat. I recently migrated my data to a hypertable. I am wondering if there is an idiomatic approach to extracting “peaks” from a timeseries. Essentially, for a time range, I want a timestamp for the top N peaks. I define a peak as the greatest sum for a given rolling interval.
I count emotes in 10 second intervals, then post the count. My data model:
count | emote_id | created_at
-------±---------±------------------------------
2 | 19 | 2023-11-06 18:41:30.198767+00
1 | 22 | 2023-11-06 18:41:30.198767+00
1 | 19 | 2023-11-06 18:41:40.199415+00
5 | 22 | 2023-11-06 18:41:40.199415+00
3 | 22 | 2023-11-06 18:41:50.199395+00
2 | 19 | 2023-11-06 18:42:00.199252+00
1 | 20 | 2023-11-06 18:42:00.199252+00
2 | 22 | 2023-11-06 18:42:00.199252+00
2 | 24 | 2023-11-06 18:42:00.199252+00
1 | 19 | 2023-11-06 18:42:30.199251+00
I imagine peak detection is a common concern, but I haven’t found any documentation on Timescale. I have a working vanilla SQL query that relies on sorting a rolling sum and filtering out non-maximum values. Performance isn’t great, though. My thought was to create a continuous aggregate rolling sum with Timescale for the intervals I care about, and then apply my filtering logic to get local maximums. My fear is I’m over complicating things.
TL;DR: is there a simple way to detect peaks with Timescale?