Unexpected behavior note: If the keys for the order are identical, the cumulative sum repeats the highest value. The following works correctly without 'orderby', but with it produces the same results as without the rolling call. aq.table({ x: [1, 1, 1, 1, 1, 1] }) .orderby('x') .derive({ ra: aq.rolling(d => op.sum(d.x)) }) .view()

Thanks for pointing this out. I agree it is weird, but it is "correct" in that the SQL standard for window functions defaults to treating ties as "peer values" that are included in the same window frame (akin to having multiple values at the same "index" position). You can disable this behavior by telling the window to ignore peers using the third argument to rolling: rolling(fn, null, true). (Here null results in the default sliding window frame: [-Infinity, 0].)

I've updated the example to convey these issues! Thanks again.

Update: As of Arquero v0.5.0, the rolling() function defaults to being peer-insensitive, as this appears to better align with default expectations.

All right, thanks for the explanation! Yeah, the non-compliant method is probably easier to think about, because an 'ordered' dataset in dplyr/arquero doesn't quite feel the same as query including an order by element in SQL.

Hi Jeff, this library looks fantastic! One data problem I've been dealing with is that I have a weighted vector of samples as (value, count) pairs, and would like to compute the median or other quantiles of the values in this representation, without needing to create a dense vector with repeated samples based on the counts. I wonder – is there / might there in the future be a way to accomplish this using Arquero?

Thanks! Currently the answer is "no": quantile calculation operates over a flat list of values. However, Arquero does support adding new aggregate functions, so a custom JavaScript implementation can be plugged in to support the task efficiently. Also, here is some code for the dense vector approach. While not optimal compared to a sparse approach, it does limit the data copying to just the values needed for the rollup. The sequence generation is done inline, and the unroll drop parameter ensures only the repeated values are written out. aq.table({ value: [10, 8, 1, 2, 3, 4, 6, 9], count: [ 2, 3, 1, 1, 2, 2, 5, 1] }) .unroll({ i: d => op.sequence(d.count) }, { drop: aq.not('value') }) .rollup({ median: op.median('value') }) .view()

Thank you! 🎉

Hi Jeff, I have another question that I haven't been able to answer looking through the documentation – sorry for the trouble if this is covered somewhere; it seems very basic! I'd like to abstract out part of a table expression, such as a value by which to filter a field: https://observablehq.com/d/0d799ea879bc40ad I understand that the argument passed to `filter` is parsed and rewritten and that the lambda function is not a true closure, but I'm not sure what the alternative would be. Initially I thought that I can construct an `op` expression and pass that in (something like `op.equal(op.field('letter', 'b'))`, but I didn't see a way to do that in the docs for operations. Oops, typo: I meant to say `op.equal(op.field('letter'), 'b')` in the last paragraph.

Actually, that should be `op.equal(op.field('letter'), valueToSearchFor)`. Sorry for the email spam; one day Observable will add comment editing support :) The idea was that one could essentially specify the query AST directly, allowing the introduction of "dynamic" nodes. I figured out that you can inject values like this: t.filter(`d => d.letter == '${valueToSearchFor}'`)

There are at least two options here: 1. Use strings rather than functions, as you noted above. From Arquero's point of view this is totally fine, so long as your interpolated value can map to a string without issue. Of course, you will lose linting, auto-complete, etc. :( 2. Use the new table.params() method (newly released in 0.7.0) to define named parameters that you can reference in your table expressions! These can be arbitrary JS values. Initial documentation is here: https://uwdata.github.io/arquero/api/table#params. Note that params persist across verbs.

Got it. The table.params() method looks like a better and more robust way to do this. Thank you! :) In case you're curious, I'm exploring using Arquero with some tree data to see whether relational queries are sufficiently fast/expressive for small/medium datasets. There's a really interesting set of ideas described in https://mitpress.mit.edu/books/mathematics-big-data about how one can use sparse matrices to performantly model both relational and network data, but public implementations aren't quite there yet as far as I'm aware, even outside of the browser.

calcuate → calculate

Thanks!

Missing link: https://observablehq.com/@uwdata/tidy-data-in-javascript

Thanks!