Hacker News Reader

Comment by kcl | Hacker News Reader

Linux linking: I wasn't doing anything special with the linking. The Linux binary was compiled on a VirtualBox instance of Mint and stripped and compressed, so it's possible your tools are misreporting, or I did something I wasn't expecting. At any rate, I haven't made that as a conscious design choice, and it's easy to change (fix?) compilation styles in a future build.

SQL engine: all from scratch

View on HN

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);

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

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.

Reply on news.ycombinator.com