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, 267🔥, 0💬
Popular Posts:
How To Generate CREATE VIEW Script on an Existing View in SQL Server? If you want to know how an exi...
What Are Out-of-Range Errors with DATETIME values in SQL Server Transact-SQL? When you enter DATETIM...
How to download and install Microsoft SQL Server Management Studio Express in SQL Server? Microsoft ...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...