topic stddev_samp when dealing with combination of NULLS and values giving NaN's in Batch SQL (Apache Hive)
http://community.cloudera.com/t5/Batch-SQL-Apache-Hive/stddev-samp-when-dealing-with-combination-of-NULLS-and-values/m-p/65344#M2568
<P>Hi All,</P><P>i have a query which does several aggregations on different columns in a 24 rollup job, but when it does stddev_samp on decimal columns which contain both nulls and values it is giving out NaN values. Is there anyway we could ignore the NULL's and do a std deviation on only the numbers? </P><P> </P><P>for eg: </P><P>column1 has 24 values(from 24 different partitions, rollup). Of the 24 values if 20 has decimal values and 4 have NULL's, is there a way to consider only those 20 values and so a standard deviation on them instead of 24?</P><P> </P><P>i mean if i need to calculate mean for this 20 values, it would be (sum of 20 values)/20</P><P> </P><P>how could i achieve this in hive?</P><P> </P><P>Thanks in advance.</P>Tue, 13 Mar 2018 14:36:01 GMTPJ2018-03-13T14:36:01Zstddev_samp when dealing with combination of NULLS and values giving NaN's
http://community.cloudera.com/t5/Batch-SQL-Apache-Hive/stddev-samp-when-dealing-with-combination-of-NULLS-and-values/m-p/65344#M2568
<P>Hi All,</P><P>i have a query which does several aggregations on different columns in a 24 rollup job, but when it does stddev_samp on decimal columns which contain both nulls and values it is giving out NaN values. Is there anyway we could ignore the NULL's and do a std deviation on only the numbers? </P><P> </P><P>for eg: </P><P>column1 has 24 values(from 24 different partitions, rollup). Of the 24 values if 20 has decimal values and 4 have NULL's, is there a way to consider only those 20 values and so a standard deviation on them instead of 24?</P><P> </P><P>i mean if i need to calculate mean for this 20 values, it would be (sum of 20 values)/20</P><P> </P><P>how could i achieve this in hive?</P><P> </P><P>Thanks in advance.</P>Tue, 13 Mar 2018 14:36:01 GMThttp://community.cloudera.com/t5/Batch-SQL-Apache-Hive/stddev-samp-when-dealing-with-combination-of-NULLS-and-values/m-p/65344#M2568PJ2018-03-13T14:36:01Z