Generated SQL for custom reducer


#1

I asked this as part of another question here but maybe this will get answered faster if I post it as a separate question.

I’m trying to replicate the “custom measure” type query that I can successfully do from within the Immerse Dashboard. Within Immerse I can enter the following SQL:

SUM(return)/COUNT(DISTINCT(Date))

and I can see in the back-end logs that it generates a query like so:

SELECT Portfolio as key0, SUM(return)/COUNT(DISTINCT(Date)) as val FROM MyTable GROUP by key0....

However when I try to do this from the mapd-charting/crossfilter API:

var dimension = crossfilter.dimension("Portfolio");
var groups = dimension.group.reduce([{ expression: "SUM(return)/COUNT(DISTINCT(Date))", agg_mode: "custom", name: "val" }]);

I get a SQL that look like this:

SELECT Portfolio as key0, SUM(return)/COUNT(DISTINCT(Date)) FROM MyTable WHERE COUNT(DISTINCT(DATE)) IS NOT NULL GROUP by key0...

This is not a valid SQL b/c of the WHERE clause. How do I make it not do that and just get the same behavior that Dashboard does?

Thanks!


#2

Just figured this one out by stepping through the mapd-crossfilter code. If I set the isComposite field in the reduce expression it works. That is, it will not try to parse the composite SQL expression and put filters in the WHERE clause.

So the groups should then be:

var groups = dimension.group.reduce([{ expression: "SUM(return)/COUNT(DISTINCT(Date))", agg_mode: "custom", name: "val", isComposite = "true" }]);

This should be documented somewhere…


Cross-filtering/charting with nested aggregation