Hi, I want to make a division form 2 queries
(SELECT COUNT(id) FROM "production" WHERE state='created')
/
(SELECT COUNT(id) AS number FROM "production")
How would you divide the counts between these two?
Hi, I want to make a division form 2 queries
(SELECT COUNT(id) FROM "production" WHERE state='created')
/
(SELECT COUNT(id) AS number FROM "production")
How would you divide the counts between these two?
Hey @AlfredoC, so you can't do this directly in the SQL but you should be able to accomplish it using the math
and string
expression functions. Here is a link to the Canvas expression documentation: https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html
Here is another Discuss post that answers a similar question: Kibana Canvas , Calculate percentage using to aggregations
The important bit is:
| essql
query="SELECT order_date, customer_gender FROM \"kibana_sample_data_ecommerce\""
count=10000
| math {
string "count(customer_gender)/"
{filters group="order_date" ungrouped=true
| essql query="SELECT order_date, customer_gender FROM \"kibana_sample_data_ecommerce\""
count=10000
| math "count(customer_gender)"
}
}
Hi there @tims...
I am trying to solve a similar problem and the solution you posted was helpful except that my ESSQL queries have a "group by" clause which I want to use to calculate the ratio for each group of items. Any tips on how that could be accomplished? Currently when I try I get the error in Canvas saying "[math] > [string] > [math] > Expressions must return a single number. Try wrapping your expression in mean() or sum()"
Hey @tshayan, I don't have a full example for you but it sounds like maybe you want the ply
function. Here are the docs:
https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#ply_fn
ply
lets you subdivide the datatable and pass an expression, something like:
| ply by="group_by_field" fn={math "sum(count)" | as "count"}
Hopefully that's enough to get you started.
I think we are one step closer to what I'm trying to achieve but not quite... The issue is that the expression part of the ply function has to return 'a' datatable, which gets applied to every single group (or ply), however, I need different expressions to apply to different groups.
Let's take a fictitious example... Suppose I have a query where I want to find the male percentage in each city.
select count(*) as maleCount, city from population where gender='male' group by city
The ficticious result would be something like this:
maleCount, City
100, Toronto
200, New York
300, Washington
400, London
Then I need to divide each of those groups by the corresponding total population in each city. So:
select count(*) as totalPop, city from population group by city
which returns:
totalPop, City
1000, Toronto
2000, New York
3000, Washington
4000, London
So the resulting table should be the first table divided by the second table to return something like this:
malePercentage, City
0.1, Toronto
0.2, New York
0.3, Washington
0.4, London
Hope it makes sense what I am trying to do but I have not found a way yet to do it in Canvas.
Thank in advance.
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.
© 2020. All Rights Reserved - Elasticsearch
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant logo are trademarks of the Apache Software Foundation in the United States and/or other countries.