standard deviation presto
I would like to calculate the standard deviation for avg_total_orders_last_30_days using the avg_total_orders_last_12_months.
sample table
customer_id | avg_total_orders_last_30_days | avg_total_orders_last_12_months 939 103 94 441 107 118 082 313 293 This is what I have tried so far:
select customer_id avg_total_orders_last_30_days, avg_total_orders_last_12_months, approx_distinct(SUM(avg_total_orders_last_12_months)) OVER (partition by customer_id ) as stdev_rep from table group by 1 1 Answer
I think this is what you're trying to do, but your [avg_total_orders_last_12_months] field contains numbers that are too large to act as 'e' for approx_distinct.
approx_distinct(x, e) → bigint#
Returns the approximate number of distinct input values. This function provides an approximation of count(DISTINCT x). Zero is returned if all input values are null. This function should produce a standard error of no more than e, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. The current implementation of this function requires that e be in the range of [0.0040625, 0.26000].
If you're looking to get the true sample standard deviation for the field use the STDDEV(x) as outlined below:
ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobmlqZGyFeoCOrKuappSWv6V5w56topmknryvec%2BrnKysnw%3D%3D