Collections:
STDDEV_SAMP() - Sample Standard Deviation
How to calculate the sample standard deviation of a field expression in result set groups using the STDDEV_SAMP() function?
✍: FYIcenter.com
STDDEV_SAMP(expr) is a MySQL built-in aggregate function that calculates the sample standard deviation of a field expression in result set groups. For example:
SELECT help_category_id, STDDEV_SAMP(help_topic_id), COUNT(help_topic_id) FROM mysql.help_topic GROUP BY help_category_id; -- +------------------+----------------------------+----------------------+ -- | help_category_id | STDDEV_SAMP(help_topic_id) | COUNT(help_topic_id) | -- +------------------+----------------------------+----------------------+ -- | 1 | 0.7071067811865476 | 2 | -- | 2 | 10.404669281488138 | 35 | -- | 3 | 85.43279646520068 | 59 | -- | 4 | 0.7071067811865476 | 2 | -- | 5 | 2.081665999466132 | 3 | -- ... -- +------------------+----------------------------+----------------------+ SELECT help_category_id, help_topic_id FROM mysql.help_topic WHERE help_category_id = 5; -- +------------------+---------------+ -- | help_category_id | help_topic_id | -- +------------------+---------------+ -- | 5 | 40 | -- | 5 | 43 | -- | 5 | 44 | -- +------------------+---------------+
STDDEV_SAMP() is also a window function, you can call it with the OVER clause to calculate the population standard deviation of the given expression in the current window. For example:
SELECT help_topic_id, help_category_id, STDDEV_SAMP(help_topic_id) OVER w FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +---------------+------------------+-----------------------------------+ -- | help_topic_id | help_category_id | STDDEV_SAMP(help_topic_id) OVER w | -- +---------------+------------------+-----------------------------------+ -- | 0 | 1 | 0.7071067811865476 | -- | 1 | 1 | 0.7071067811865476 | -- | 2 | 2 | 10.404669281488138 | -- | 6 | 2 | 10.404669281488138 | -- | 7 | 2 | 10.404669281488138 | -- | 8 | 2 | 10.404669281488138 | -- | 9 | 2 | 10.404669281488138 | -- ... -- +---------------+------------------+-----------------------------------+
Reference information of the STDDEV_SAMP() function:
STDDEV_SAMP(expr): std Returns the sample standard deviation of expr (the square root of VAR_SAMP(). Arguments, return value and availability: expr: Required. The field expression in result set groups. std: Return value. The sample standard deviation of the input expression. Available since MySQL 5.7.
Related MySQL functions:
⇒ SUM() - Total Value in Group
⇐ STDDEV_POP() - Population Standard Deviation
2023-11-18, 274🔥, 0💬
Popular Posts:
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...
What Are Date and Time Functions in MySQL? MySQL offers a number of functions for date and time valu...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...