Collections:
VAR_POP() - Population Standard Variance
How to calculate the population standard variance of a field expression in result set groups using the VAR_POP() function?
✍: FYIcenter.com
VAR_POP(expr) is a MySQL built-in aggregate function that
calculates the population standard variance of a field expression in result set groups.
For example:
SELECT help_category_id, VAR_POP(help_topic_id), COUNT(help_topic_id) FROM mysql.help_topic GROUP BY help_category_id; -- +------------------+------------------------+----------------------+ -- | help_category_id | VAR_POP(help_topic_id) | COUNT(help_topic_id) | -- +------------------+------------------------+----------------------+ -- | 1 | 0.25 | 2 | -- | 2 | 105.16408163265307 | 35 | -- | 3 | 7175.054869290432 | 59 | -- | 4 | 0.25 | 2 | -- | 5 | 2.888888888888887 | 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 | -- +------------------+---------------+
VAR_POP() is also a window function, you can call it with the OVER clause to calculate the population standard variance of the given expression in the current window. For example:
SELECT help_topic_id, help_category_id, VAR_POP(help_topic_id) OVER w FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +---------------+------------------+-------------------------------+ -- | help_topic_id | help_category_id | VAR_POP(help_topic_id) OVER w | -- +---------------+------------------+-------------------------------+ -- | 0 | 1 | 0.25 | -- | 1 | 1 | 0.25 | -- | 2 | 2 | 105.16408163265307 | -- | 6 | 2 | 105.16408163265307 | -- | 7 | 2 | 105.16408163265307 | -- | 8 | 2 | 105.16408163265307 | -- | 9 | 2 | 105.16408163265307 | -- ... -- +---------------+------------------+-------------------------------+
Reference information of the VAR_POP() function:
VAR_POP(expr): var Returns the population standard variance of expr. It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator. If there are no matching rows, VAR_POP() returns NULL. Arguments, return value and availability: expr: Required. The field expression in result set groups. var: Return value. The population standard variance of the input expression. Available since MySQL 5.7.
Related MySQL functions:
⇒ VAR_SAMP() - Sample Standard Variance
⇐ SUM() - Total Value in Group
2023-12-01, 389🔥, 0💬
Popular Posts:
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops? You can u...