Hi, I reported back in April 2015 that what Hive calls Standard Deviation Function STDDEV is a pointer to STDDEV_POP. This is incorrect and has not been rectified in Hive 2
Both Oracle and Sybase point STDDEV to STDDEV_SAMP not STDDEV_POP. Also I did tests with Spark 1.6 as well and 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 | +--------------------+---------------------+--------------------+---------------------+--+ Hopefully The Hive one will be corrected. Thanks Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com