vault/dsl: introduce some way to talk about aggregates
rohany opened this issue · 3 comments
rohany commented
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 commented
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 commented
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 commented
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 ...