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, 380🔥, 0💬
Popular Posts:
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
Where to find tutorials to answer some frequently asked questions on Microsoft SQL Server Transact-S...
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...