limited transformations using vgplot

[declann]

Hi,

vgplot mark from takes an array of objects ("bypassing" the database) or else a table/view reference for database-backed marks. But it can't take a Query or transform the table/view in any general way: only within mapped fields, where an SQL expression can be provided. So we can use SQL expressions within vgplots, and we can filter by selections, but we can't, for instance, calculate a window function over different frames.

Are these limitations accurate? If so can this change - e.g. permitting SQL queries in from, or is there a particular difficulty with doing so? (maybe something breaks about selection/interactors, or maybe it's data cube related)

I tried a few things to transform data after a selection (window functions). I guess my options are to push the transform up (disadvantage is this means transforming unfiltered data: before selection, unless there's something else I'm missing), or else to transform in a custom client, which will be a lot more boilerplate versus vgplot. I guess that I'll be OK with option 1, but interested if I'm missing something and if I'm understanding things correctly.

Grateful for any comments!

Declan

[jheer]

Hi Declan, you are correct.

Except for the special case where one passes in JS data directly (and in which case selections are not supported), currently vgplot assumes that it is filtering a named table in the database. Many transformations can be precomputed: create a new table as part of your data prep (e.g., as a table defined by a query in a declarative spec, or an exec query using standard JS) and then have vgplot query that.

As you note, this can break down in some cases where you want a transformation parameterized by a Param or Selection. However, a number of dynamic transformations can be specified using SQL expressions as part of your vgplot spec. Aggregates and binning are obvious examples, and some window functions can also be applied (as in https://idl.uw.edu/mosaic/examples/moving-average.html, which dynamically changes a window frame). However, other more complex transforms, such as aggregation of window function output, are not well supported.

If pre-computation or existing expression support don't cover your needs, I'm afraid the options are either (1) write your own custom client or (2) we try to figure out if we can extend vgplot in a principled fashion. Happy to learn about concrete use cases to see if #2 is needed and viable.

[declann]

Thanks for the response Jeff!

Mentioning aggregation of window function - my transform is definitely in that more complex category, so I'll pre compute. There probably won't be a performance penalty given duckdb/olap, and datacube optimisations I'll get. I think there's an interesting ergonomic penalty because it would have been nice if I could use one set of columns and one set of code.
The usecase is:

I have many versions of a detailed set of numbers (already I need to sum them), and I want to be able to show histograms (many fields) of a single version AND ALSO the Difference between any two versions (but not at the same time). A selection controlled by a custom UI client filters for either one or two applicable values.

I filterBy the selection in various vgplot histograms. I can filter and do a vg.lag, but I definitely can't filter and sum and do a vg.lag and subtract (aggregate functions cannot contain window function calls: also not other aggregate functions).

So, definitely in that complex category - one pass at an SQL query without subqueries won't work at all. Pre-computing sounds ok here anyways (or a custom client), but I really wanted to try this and now I'm glad I can understand some more about Mosaic - thanks a lot!