Division of fields pre-aggregation

Hi all,

Using SQL I can execute a query like this:

select sum(cast(field1 as float)/cast(field2 as float)) from mydatasource where __time BETWEEN TIMESTAMP ‘time1’ and TIMESTAMP ‘time2’

However I don’t know how to execute this using the JSON API. I only know how to apply division as a post aggregation. Can someone enlighten me please?

Thanks,

Druid SQL is doing that using a spooky secret feature! And by “secret” I mean “not fully documented yet and the API is not yet stable”. You can do it too by passing “expression” instead of “fieldName” to the longSum or doubleSum aggregators. Expression syntax is documented on http://druid.io/docs/latest/misc/math-expr.html which is also “secret” in that no other pages link to it.

Use at your own risk, although in future Druid versions I expect the API will stabilize. Watch for it in the release notes.

You can get Druid SQL to cough up the exact expression it’s using by prepending “EXPLAIN PLAN FOR” to your SQL query.

Thanks Gian - I like where this is headed :-).

I am trying this out, but I am getting the following error:

Server Error Druid Error: Unknown exception Query is:

{

“dimensions”:[

],

“aggregations”:[

{

“expression”:“CAST (total_external_transaction_amount, ‘DOUBLE’)/CAST(external_transaction_amount_factor,‘DOUBLE’)”,

“type”:“doubleSum”,

“name”:“sum__real_external_transaction_amount”

}

],

“intervals”:“2017-06-02T00:00:00+00:00/2017-06-09T07:01:05+00:00”,

“limitSpec”:{

“limit”:50000,

“type”:“default”,

“columns”:[

{

“direction”:“descending”,

“dimension”:“sum__real_external_transaction_amount”

}

]

},

“granularity”:{

“timeZone”:“UTC”,

“type”:“period”,

“period”:“P1D”

},

“postAggregations”:[

],

“queryType”:“groupBy”,

“dataSource”:“bnc_log_all_bfq22017”

}

I’m doing something wrong with the cast function - removing that allows the query to execute (although producing an undesired output value due to the missing cast of course).

Thanks for your help!

–Ben

There should be more details about what went wrong in the full error object. “Unknown exception” is the errorCode but there should also be an errorMessage, errorClass, and host.