docs/build/plain-html/topics/impala_variance.html - impala - Git at Google

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE html
   PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 <head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

 <meta name="copyright" content="(C) Copyright 2025" />
 <meta name="DC.rights.owner" content="(C) Copyright 2025" />
 <meta name="DC.Type" content="concept" />
 <meta name="DC.Title" content="VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP Functions" />
 <meta name="DC.Relation" scheme="URI" content="../topics/impala_aggregate_functions.html" />
 <meta name="prodname" content="Impala" />
 <meta name="prodname" content="Impala" />
 <meta name="version" content="Impala 3.4.x" />
 <meta name="version" content="Impala 3.4.x" />
 <meta name="DC.Format" content="XHTML" />
 <meta name="DC.Identifier" content="variance" />
 <link rel="stylesheet" type="text/css" href="../commonltr.css" />
 <title>VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP Functions</title>
 </head>
 <body id="variance">


   <h1 class="title topictitle1" id="ariaid-title1">VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP Functions</h1>


   <div class="body conbody">

     <p class="p">


       An aggregate function that returns the
       <a class="xref" href="http://en.wikipedia.org/wiki/Variance" target="_blank">variance</a> of a set of
       numbers. This is a mathematical property that signifies how far the values spread apart from the mean. The
       return value can be zero (if the input is a single value, or a set of identical values), or a positive number
       otherwise.
     </p>


     <p class="p">
         <strong class="ph b">Syntax:</strong>
       </p>


 <pre class="pre codeblock"><code>{ VARIANCE | VAR[IANCE]_SAMP | VAR[IANCE]_POP } ([DISTINCT | ALL] <var class="keyword varname">expression</var>)</code></pre>

     <p class="p">
       This function works with any numeric data type.
     </p>


     <p class="p">
         <strong class="ph b">Return type:</strong> <code class="ph codeph">DOUBLE</code> in Impala 2.0 and higher;
         <code class="ph codeph">STRING</code> in earlier releases
       </p>


     <p class="p">
       This function is typically used in mathematical formulas related to probability distributions.
     </p>


     <p class="p">
       The <code class="ph codeph">VARIANCE_SAMP()</code> and <code class="ph codeph">VARIANCE_POP()</code> functions compute the sample
       variance and population variance, respectively, of the input values. (<code class="ph codeph">VARIANCE()</code> is an alias
       for <code class="ph codeph">VARIANCE_SAMP()</code>.) Both functions evaluate all input rows matched by the query. The
       difference is that <code class="ph codeph">STDDEV_SAMP()</code> is scaled by <code class="ph codeph">1/(N-1)</code> while
       <code class="ph codeph">STDDEV_POP()</code> is scaled by <code class="ph codeph">1/N</code>.
     </p>


     <p class="p">
       The functions <code class="ph codeph">VAR_SAMP()</code> and <code class="ph codeph">VAR_POP()</code> are the same as
       <code class="ph codeph">VARIANCE_SAMP()</code> and <code class="ph codeph">VARIANCE_POP()</code>, respectively. These aliases are
       available in Impala 2.0 and later.
     </p>


     <p class="p">
       If no input rows match the query, the result of any of these functions is <code class="ph codeph">NULL</code>. If a single
       input row matches the query, the result of any of these functions is <code class="ph codeph">"0.0"</code>.
     </p>


     <p class="p">
         <strong class="ph b">Examples:</strong>
       </p>


     <p class="p">
       This example demonstrates how <code class="ph codeph">VARIANCE()</code> and <code class="ph codeph">VARIANCE_SAMP()</code> return the
       same result, while <code class="ph codeph">VARIANCE_POP()</code> uses a slightly different calculation to reflect that the
       input data is considered part of a larger <span class="q">"population"</span>.
     </p>


 <pre class="pre codeblock"><code>[localhost:21000] &gt; select variance(score) from test_scores;
 +-----------------+
 | variance(score) |
 +-----------------+
 | 812.25          |
 +-----------------+
 [localhost:21000] &gt; select variance_samp(score) from test_scores;
 +----------------------+
 | variance_samp(score) |
 +----------------------+
 | 812.25               |
 +----------------------+
 [localhost:21000] &gt; select variance_pop(score) from test_scores;
 +---------------------+
 | variance_pop(score) |
 +---------------------+
 | 811.438             |
 +---------------------+
 </code></pre>

     <p class="p">
       This example demonstrates that, because the return value of these aggregate functions is a
       <code class="ph codeph">STRING</code>, you convert the result with <code class="ph codeph">CAST</code> if you need to do further
       calculations as a numeric value.
     </p>


 <pre class="pre codeblock"><code>[localhost:21000] &gt; create table score_stats as select cast(stddev(score) as decimal(7,4)) `standard_deviation`, cast(variance(score) as decimal(7,4)) `variance` from test_scores;
 +-------------------+
 | summary           |
 +-------------------+
 | Inserted 1 row(s) |
 +-------------------+
 [localhost:21000] &gt; desc score_stats;
 +--------------------+--------------+---------+
 | name               | type         | comment |
 +--------------------+--------------+---------+
 | standard_deviation | decimal(7,4) |         |
 | variance           | decimal(7,4) |         |
 +--------------------+--------------+---------+
 </code></pre>

     <p class="p">
         <strong class="ph b">Restrictions:</strong>
       </p>


     <p class="p">
         This function cannot be used in an analytic context. That is, the
         <code class="ph codeph">OVER()</code> clause is not allowed at all with this function.
       </p>


     <p class="p">
         <strong class="ph b">Related information:</strong>
       </p>


     <p class="p">
       The <code class="ph codeph">STDDEV()</code>, <code class="ph codeph">STDDEV_POP()</code>, and <code class="ph codeph">STDDEV_SAMP()</code> functions
       compute the standard deviation (square root of the variance) based on the results of
       <code class="ph codeph">VARIANCE()</code>, <code class="ph codeph">VARIANCE_POP()</code>, and <code class="ph codeph">VARIANCE_SAMP()</code>
       respectively. See <a class="xref" href="impala_stddev.html#stddev">STDDEV, STDDEV_SAMP, STDDEV_POP Functions</a> for details about the standard deviation property.
     </p>

   </div>

 <div class="related-links">
 <div class="familylinks">
 <div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_aggregate_functions.html">Impala Aggregate Functions</a></div>
 </div>
 </div></body>
 </html>
	<?xml version="1.0" encoding="UTF-8"?>
	<!DOCTYPE html
	PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
	<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

	<meta name="copyright" content="(C) Copyright 2025" />
	<meta name="DC.rights.owner" content="(C) Copyright 2025" />
	<meta name="DC.Type" content="concept" />
	<meta name="DC.Title" content="VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP Functions" />
	<meta name="DC.Relation" scheme="URI" content="../topics/impala_aggregate_functions.html" />
	<meta name="prodname" content="Impala" />
	<meta name="prodname" content="Impala" />
	<meta name="version" content="Impala 3.4.x" />
	<meta name="version" content="Impala 3.4.x" />
	<meta name="DC.Format" content="XHTML" />
	<meta name="DC.Identifier" content="variance" />
	<link rel="stylesheet" type="text/css" href="../commonltr.css" />
	<title>VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP Functions</title>
	</head>
	<body id="variance">


	<h1 class="title topictitle1" id="ariaid-title1">VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP Functions</h1>




	<div class="body conbody">

	<p class="p">





	An aggregate function that returns the
	<a class="xref" href="http://en.wikipedia.org/wiki/Variance" target="_blank">variance</a> of a set of
	numbers. This is a mathematical property that signifies how far the values spread apart from the mean. The
	return value can be zero (if the input is a single value, or a set of identical values), or a positive number
	otherwise.
	</p>


	<p class="p">
	<strong class="ph b">Syntax:</strong>
	</p>


	<pre class="pre codeblock"><code>{ VARIANCE \| VAR[IANCE]_SAMP \| VAR[IANCE]_POP } ([DISTINCT \| ALL] <var class="keyword varname">expression</var>)</code></pre>

	<p class="p">
	This function works with any numeric data type.
	</p>


	<p class="p">
	<strong class="ph b">Return type:</strong> <code class="ph codeph">DOUBLE</code> in Impala 2.0 and higher;
	<code class="ph codeph">STRING</code> in earlier releases
	</p>


	<p class="p">
	This function is typically used in mathematical formulas related to probability distributions.
	</p>


	<p class="p">
	The <code class="ph codeph">VARIANCE_SAMP()</code> and <code class="ph codeph">VARIANCE_POP()</code> functions compute the sample
	variance and population variance, respectively, of the input values. (<code class="ph codeph">VARIANCE()</code> is an alias
	for <code class="ph codeph">VARIANCE_SAMP()</code>.) Both functions evaluate all input rows matched by the query. The
	difference is that <code class="ph codeph">STDDEV_SAMP()</code> is scaled by <code class="ph codeph">1/(N-1)</code> while
	<code class="ph codeph">STDDEV_POP()</code> is scaled by <code class="ph codeph">1/N</code>.
	</p>


	<p class="p">
	The functions <code class="ph codeph">VAR_SAMP()</code> and <code class="ph codeph">VAR_POP()</code> are the same as
	<code class="ph codeph">VARIANCE_SAMP()</code> and <code class="ph codeph">VARIANCE_POP()</code>, respectively. These aliases are
	available in Impala 2.0 and later.
	</p>


	<p class="p">
	If no input rows match the query, the result of any of these functions is <code class="ph codeph">NULL</code>. If a single
	input row matches the query, the result of any of these functions is <code class="ph codeph">"0.0"</code>.
	</p>


	<p class="p">
	<strong class="ph b">Examples:</strong>
	</p>


	<p class="p">
	This example demonstrates how <code class="ph codeph">VARIANCE()</code> and <code class="ph codeph">VARIANCE_SAMP()</code> return the
	same result, while <code class="ph codeph">VARIANCE_POP()</code> uses a slightly different calculation to reflect that the
	input data is considered part of a larger <span class="q">"population"</span>.
	</p>


	<pre class="pre codeblock"><code>[localhost:21000] > select variance(score) from test_scores;
	+-----------------+
	\| variance(score) \|
	+-----------------+
	\| 812.25 \|
	+-----------------+
	[localhost:21000] > select variance_samp(score) from test_scores;
	+----------------------+
	\| variance_samp(score) \|
	+----------------------+
	\| 812.25 \|
	+----------------------+
	[localhost:21000] > select variance_pop(score) from test_scores;
	+---------------------+
	\| variance_pop(score) \|
	+---------------------+
	\| 811.438 \|
	+---------------------+
	</code></pre>

	<p class="p">
	This example demonstrates that, because the return value of these aggregate functions is a
	<code class="ph codeph">STRING</code>, you convert the result with <code class="ph codeph">CAST</code> if you need to do further
	calculations as a numeric value.
	</p>


	<pre class="pre codeblock"><code>[localhost:21000] > create table score_stats as select cast(stddev(score) as decimal(7,4)) `standard_deviation`, cast(variance(score) as decimal(7,4)) `variance` from test_scores;
	+-------------------+
	\| summary \|
	+-------------------+
	\| Inserted 1 row(s) \|
	+-------------------+
	[localhost:21000] > desc score_stats;
	+--------------------+--------------+---------+
	\| name \| type \| comment \|
	+--------------------+--------------+---------+
	\| standard_deviation \| decimal(7,4) \| \|
	\| variance \| decimal(7,4) \| \|
	+--------------------+--------------+---------+
	</code></pre>

	<p class="p">
	<strong class="ph b">Restrictions:</strong>
	</p>


	<p class="p">
	This function cannot be used in an analytic context. That is, the
	<code class="ph codeph">OVER()</code> clause is not allowed at all with this function.
	</p>


	<p class="p">
	<strong class="ph b">Related information:</strong>
	</p>


	<p class="p">
	The <code class="ph codeph">STDDEV()</code>, <code class="ph codeph">STDDEV_POP()</code>, and <code class="ph codeph">STDDEV_SAMP()</code> functions
	compute the standard deviation (square root of the variance) based on the results of
	<code class="ph codeph">VARIANCE()</code>, <code class="ph codeph">VARIANCE_POP()</code>, and <code class="ph codeph">VARIANCE_SAMP()</code>
	respectively. See <a class="xref" href="impala_stddev.html#stddev">STDDEV, STDDEV_SAMP, STDDEV_POP Functions</a> for details about the standard deviation property.
	</p>

	</div>

	<div class="related-links">
	<div class="familylinks">
	<div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_aggregate_functions.html">Impala Aggregate Functions</a></div>
	</div>
	</div></body>
	</html>