vault/dsl: introduce some way to talk about aggregates

[rohany]

It would be useful to write a query like: "give me all instances that have meta['key'] = max(meta['key'])". It is not clear how to do such a query in our current straightforward transformation pass.

[rohany]

In order to do something like this, we'd probably have to pull the generation of the entire SQL query (not just the predicate) into the translation step.

[rohany]

We can use CTE's to do these aggregations. An example of this is as follows:

with weija (y) as (select max(x) from t)
select x from t where x in (select y from weija)

We can create a fresh CTE for each aggregate, and replace references to the aggregate with the select in.

[rohany]

even better, we can just create a separate column in the aggregate CTE for each aggregate --

with agg_tbl (agg1, agg2 ...) as (select agg1(...), agg2(...) from t)
select .... where ...