Collections:
VAR_SAMP() - Sample Standard Variance
How to calculate the sample standard variance of a field expression in result set groups using the VAR_SAMP() function?
✍: FYIcenter.com
VAR_SAMP(expr) is a MySQL built-in aggregate function that calculates the sample standard variance of a field expression in result set groups. For example:
SELECT help_category_id, VAR_SAMP(help_topic_id), COUNT(help_topic_id) FROM mysql.help_topic GROUP BY help_category_id; -- +------------------+-------------------------+----------------------+ -- | help_category_id | VAR_SAMP(help_topic_id) | COUNT(help_topic_id) | -- +------------------+-------------------------+----------------------+ -- | 1 | 0.5 | 2 | -- | 2 | 108.25714285714287 | 35 | -- | 3 | 7298.762711864405 | 59 | -- | 4 | 0.5 | 2 | -- | 5 | 4.33333333333333 | 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_SAMP() is also a window function, you can call it with the OVER clause to calculate the sample standard variance of the given expression in the current window. For example:
SELECT help_topic_id, help_category_id, VAR_SAMP(help_topic_id) OVER w FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +---------------+------------------+--------------------------------+ -- | help_topic_id | help_category_id | VAR_SAMP(help_topic_id) OVER w | -- +---------------+------------------+--------------------------------+ -- | 0 | 1 | 0.5 | -- | 1 | 1 | 0.5 | -- | 2 | 2 | 108.25714285714287 | -- | 6 | 2 | 108.25714285714287 | -- | 7 | 2 | 108.25714285714287 | -- | 8 | 2 | 108.25714285714287 | -- | 9 | 2 | 108.25714285714287 | -- ... -- +---------------+------------------+--------------------------------+
Reference information of the VAR_SAMP() function:
VAR_SAMP(expr): var Returns the sample variance of expr. That is, the denominator is the number of rows minus one. If there are no matching rows, VAR_SAMP() returns NULL. Arguments, return value and availability: expr: Required. The field expression in result set groups. var: Return value. The sample standard variance of the input expression. Available since MySQL 5.7.
Related MySQL functions:
⇒ VARIANCE() - Synonym for VAR_POP()
⇐ VAR_POP() - Population Standard Variance
2023-12-01, 300🔥, 0💬
Popular Posts:
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...
How To Query Tables and Loop through the Returning Rows in MySQL? The best way to query tables and l...