Kurtosis function not playing nice

user_1352490 0
Viewed 204

I'm playing with Snowflake and when using the KURTOSIS function I'm getting a divide by zero error :-(

Function works perfect if there's more than 1 distinct value (and more than 4 values):

select 
    kurtosis(bob) 
from 
    (select seq4() bob from table(generator(rowcount => 10)));

enter image description here

But fails when all values are identical .. in this case 1:

select 
    kurtosis(bob) 
from 
   (select 1 bob from table(generator(rowcount => 10)));

enter image description here

Aside from doing a count distinct or HLL test prior to each evaluation can anyone suggest a solution?

I did see this article talking about Pandas "Rolling skewness and kurtosis fail on a sample of all equal values" but this was almost 10 years old way before Snowflake was even a pipe dream.

And the SKEW function has the identical problem.

My temporary yucky solution is:

select 
    DECODE(HLL(bob),1,0,SKEW(bob::FLOAT)) yucky_solution
from 
   (select 1 bob from table(generator(rowcount => 10)));

enter image description here

snowflake-cloud-data-platform sql
1 Answers

I think this reflects on how the kurtosis function works (regardless of Snowflake).

I tested this in Wolfram Alpha and on this online kurtosis calculator: https://www.socscistatistics.com/tests/skewness/default.aspx

The numbers from 0 to 9 give the same value than Snowflake:

enter image description here

But 10 identical numbers return a NaN value:

enter image description here

Mathematically speaking, this is the formula for kurtosis:

enter image description here

Which you get by diving (something) by the standard deviation. The standard deviation of a series of identical numbers is 0 -- hence the division by 0 error.

Hence the SQL way to avoid an exception is to calculate the stddev first:

select 
    iff(stddev(bob)=0, null, kurtosis(bob))
from 
   (select 1 bob from table(generator(rowcount => 10))); 

-- null
user_1324380
Related