3 ms·

Awesome stuff, I am actually hacking on something similar, so I would love to hear your thoughts RE: supporting "windowed" aggregations. (E.g. a moving average

Awesome stuff, I am actually hacking on something similar, so I would love to hear your thoughts RE: supporting "windowed" aggregations. (E.g. a moving average over a timeseries). Are you planning on adding that and which syntax are you planning to use? Have you looked at postgres "OVER PARTITION"? While it seems powerful it's also fairly unintuitive IMHO. I was experimenting with adding a GROUP BY clause that allows each input row to appear in more than one group in the result set. Something like: SELECT time, mean(value) FROM mymetric GROUP OVER TIMEWINDOW(time, 60);

electrum 11y ago
You can do that like this: GROUP BY date_trunc('minute', time)
- paulasmuth 11y ago
  The snippet you posted will compute an aggregation based on a fixed time interval. I.e. it will put every row into a "bucket" of per-minute granularity and then compute an aggregate function for each of those buckets bucket, taking into account only the rows that ended up in that specific bucket (i.e. only rows from that specific minute). To put it another way this is asking the question "Please give me the aggregate of some value per minute". To make my question more precise; I was trying to ask specifically about a "moving window aggregation" (e.g. a moving average over a timeseries). This is more like asking the question "Please give me every minute an aggregate based on all values in the last N minutes". To do that you need each input row to end up in more than one bucket (or have a special type of aggregation function like postgres does). For example, if you were doing a moving aggregation with a 1-minute interval ("bucket size") and a 5 minute window ("lookback"), you would need to place each row into 5 buckets: The bucket into which it belongs based on it's timestamp and the 4 previous buckets. And a vanilla SQL GROUP BY can't do that. Hope that makes sense.

electrum 11y ago

You can do that like this: GROUP BY date_trunc('minute', time)

paulasmuth 11y ago
The snippet you posted will compute an aggregation based on a fixed time interval. I.e. it will put every row into a "bucket" of per-minute granularity and then compute an aggregate function for each of those buckets bucket, taking into account only the rows that ended up in that specific bucket (i.e. only rows from that specific minute). To put it another way this is asking the question "Please give me the aggregate of some value per minute". To make my question more precise; I was trying to ask specifically about a "moving window aggregation" (e.g. a moving average over a timeseries). This is more like asking the question "Please give me every minute an aggregate based on all values in the last N minutes". To do that you need each input row to end up in more than one bucket (or have a special type of aggregation function like postgres does). For example, if you were doing a moving aggregation with a 1-minute interval ("bucket size") and a 5 minute window ("lookback"), you would need to place each row into 5 buckets: The bucket into which it belongs based on it's timestamp and the 4 previous buckets. And a vanilla SQL GROUP BY can't do that. Hope that makes sense.