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:
