Calculating standard deviation for a numeric field

SQL++
1

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

2

use 6.5.0 beta https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/aggregatefun.html#stddev

OR
try this

SELECT
      c.col1,
      ARRAY_SUM(ARRAY POWER(v-c.col2avg,2) FOR v IN c.aagg END)/ARRAY_COUNT(c.aagg) AS col2_variance,
FROM (SELECT b.col1, AVG(b.col2) AS col2avg,
      ARRAY_AGG(CASE WHEN IS_NUMBER(b.col2) THEN b.col2 ELSE MISSING END) AS aagg
      FROM default AS b
      WHERE b.col1 IS NOT MISSING
      GROUP BY b.col1) AS c;

CREATE INDEX ix1 ON default(col1, col2);