I have to calculate standard deviation for a numeric field. Since there is no direct aggregate function available to calculate this in N1Ql. I am doing a join with subquery to get average of the column and then calculate standard deviation with the below formula

sqrt(sum(field value - avg value)^2/ count)

This query is taking long time to execute. Is there a better or more efficient way of calculating standard deviation of a numeric field

Below is a sample query for what I am doing

Select c.col1,

sum(power((c.col2 - a.col2_avg),2))/count(*) as col2_variance

from

(select b.col1, avg(b.col2) as col2_avg from default b group by b.col1) as a

inner join default c ON a.col1 = c.col1

group by c.col1