The correct STDDEV function used is STDDEV_SAMP not STDDEV_POP. That is
the correct one.
You can actually work that one out yourself
BTW Hive also gives a wrong value. This is what I reported back in April
about Hive giving incorrect value
Both Oracle and Sybase point STDDEV to STDDEV_SAMP not STDDEV_POP. Also I
did tests with Spark 1.6 as well. Spark correctly points STTDEV to
STDDEV_SAMP.
The following query was used
SELECT
SQRT((SUM(POWER(AMOUNT_SOLD,2))-(COUNT(1)*POWER(AVG(AMOUNT_SOLD),2)))/(COUNT(1)-1))
AS MYSTDDEV,
STDDEV(amount_sold) AS STDDEV,
STDDEV_SAMP(amount_sold) AS STDDEV_SAMP,
STDDEV_POP(amount_sold) AS STDDEV_POP
from sales;
The following is from running the above query on Hive where STDDEV -->
STDDEV_POP which is incorrect
+--------------------+---------------------+--------------------+---------------------+--+
| mystddev | stddev | stddev_samp |
stddev_pop |
+--------------------+---------------------+--------------------+---------------------+--+
| 260.7270919450411 | 260.72704617040444 | 260.7270722861465 |
260.72704617040444
|
+--------------------+---------------------+--------------------+---------------------+--+
The following is from Spark-sql where STDDEV --> STDDEV_SAMP which is
correct
+--------------------+---------------------+--------------------+---------------------+--+
| mystddev | stddev | stddev_samp |
stddev_pop |
+--------------------+---------------------+--------------------+---------------------+--+
| 260.7270919450411 | 260.7270722861637 | 260.7270722861637 |
260.72704617042166 |
+--------------------+---------------------+--------------------+---------------------+--+
HTH
Dr Mich Talebzadeh
LinkedIn *
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.
On 7 July 2016 at 09:29, Sean Owen <so...@cloudera.com> wrote:
> No, because these are different values defined differently. If you
> have 1 data point, the sample stdev is undefined while population
> stdev is defined. Refer to their definition.
>
> On Thu, Jul 7, 2016 at 9:23 AM, Mungeol Heo <mungeol....@gmail.com> wrote:
> > Hello,
> >
> > As I mentioned at the title, stddev_samp function gives a NaN while
> > stddev_pop gives a numeric value on the same data.
> > The stddev_samp function will give a numeric value, if I cast it to
> decimal.
> > E.g. cast(stddev_samp(column_name) as decimal(16,3))
> > Is it a bug?
> >
> > Thanks
> >
> > - mungeol
> >
> > ---------------------------------------------------------------------
> > To unsubscribe e-mail: user-unsubscr...@spark.apache.org
> >
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: user-unsubscr...@spark.apache.org
>
>
