Oracle Variance Functions Guide, FAQ, and Examples

In this article, we’ll look at a range of Variance functions (VARIANCE, CUME_DIST, CORR, VAR_POP, COVAR_POP, VAR_SAMP, and COVAR_SAMP), explain what they do, and see some examples.

These functions can be used as an window function or an aggregate function. I’ll explain both formats in this article.

 

What is a Variance?

Before we look at the functions, let’s understand what a variance is.

A variance is a mathematical concept, which represents how different the values are in a set of values (such as  a column of data).

That link can probably explain it better than I can!

 

Purpose of the Oracle Variance Functions

There are quite a few variance functions in Oracle. Let’s take a look at what they all do.

VARIANCE

The purpose of the VARIANCE function is to, well, return the variance of a set of numbers.

CUME_DIST

The purpose of the CUME_DIST function is to find the cumulative distribution of a value in a list of values.

The value that is returned will be between 0 and 1.

CORR

The Oracle CORR function returns the “coefficient of correlation” of a set of number pairs.

What is a coefficient of correlation? Here’s an definition of what it means.

COVAR_POP

The Oracle COVAR_POP function calculates the population covariance of a set of number pairs.

What is a “population covariance”? Good question. Here’s an article that explains what the definition is.

VAR_SAMP

The VAR_SAMP function will return the sample variance of a set of numbers, after excluding NULLs.

What does this mean?

The sample variation calculates the variation of a sample of numbers from the overall set. The higher the result, the larger the variation between the numbers.

COVAR_SAMP

This function returns the covariance of a set of number pairs.

What does that mean? Here’s an explanation of what a covariance is.

VAR_POP

The VAR_POP function finds the population variance of a set of numbers, excluding NULLs.

What does this mean?

The population variance is a calculation that determines the difference or variation in a complete set of numbers. The higher the number is, the bigger the variance.

 

Oracle VARIANCE Function Syntax and Parameters

Aggregate Function

The syntax of the VARIANCE function when used as an aggregate function is:

VARIANCE ( [DISTINCT | ALL] expression)

The parameters of the VARIANCE aggregate function are:

• DISTINCT or ALL (optional): If you specify ALL, then all values are considered for the variance. If you specify DISTINCT, only the distinct values are considered in the variance (duplicates are ignored).
• expression (mandatory): This is the expression (such as a column) to be analysed for variance.

The data type can be any numeric data type, and the return value is the same data type that was provided to it.

 

Analytic Function

If you’re using the VARIANCE function as an analytic function, then the syntax is:

VARIANCE ( [DISTINCT | ALL] expression) OVER (analytic_clause)

The parameters of VARIANCE when using it as an analytic function are:

• DISTINCT or ALL (optional): If you specify ALL, then all values are considered for the variance. If you specify DISTINCT, only the distinct values are considered in the variance (duplicates are ignored).
• expression (mandatory): This is the expression (such as a column) to be analysed for variance.
• analytic_clause (mandatory): This is where you specify a query partition clause, order by clause,, and windowing clause.

If you specify DISTINCT, then you are only able to specify the query partition clause inside the analytic clause. The order by clause and windowing clause are not allowed.

 

Oracle CUME_DIST Syntax and Parameters

Aggregate Function

The syntax for this function is different depending on how you use it.

As an aggregate function, it looks like this:

CUME_DIST (expression1, … expression_n) WITHIN GROUP (ORDER BY expression_order1, … expression_order_n)

The parameters of the CUME_DIST function as an aggregate function are:

• expression1 (mandatory): The list of expressions to evaluate a distribution for.
• expression_order1 (mandatory): The list of expressions that contain the larger list of values. Expression1 is checked within expression_order1.

You can have more than one expression in each set of parameters, separated by a comma. However, the data types need to match between them.

So, the data type of expression1 needs to match expression_order1, and the data type of expression2 needs to match expression_order2, and so on.

 

Analytic Function

As an analytic function, it looks like this:

CUME_DIST() OVER ( [query_partition_clause] ORDER BY order_clause )

When it is run as an analytic function, it performs the cumulative distribution for the row’s value against a range of values. The result is likely different for each row.

The parameters for the analytic version of CUME_DIST are:

• query_partition_clause (optional): You can specify how you want your CUME_DIST value grouped. If this is not specified, the function looks at all values.
• order_by_clause (mandatory): This is the column used to calculate the cumulative distribution.

 

Oracle CORR Function Syntax and Parameters

Aggregate Function

The syntax for CORR as an aggregate function is:

CORR (expression1, expression2)

The parameters of the CORR aggregate function are:

• expression1 (mandatory): The first set of data to use in the coefficient calculation.
• expression2 (mandatory): The second set of data to use in the coefficient calculation.

Both of these fields are mandatory because the calculation needs two sets of data.

Analytic Function

The syntax of the CORR function as an analytic function is:

CORR (expression1, expression2) OVER (analytic_clause)

Just like the aggregate function, both expressions are required. The analytic_clause is new.

The analytic_clause allows you to specify the query partition clause, the order by clause, and the windowing clause.

 

Oracle VAR_POP Function Syntax and Parameters

The syntax of the VAR_POP function is:

VAR_POP ( expression ) [OVER (analytic_clause)]

Parameters

• expression (mandatory): This is the expression, such as a set of data or a column, to perform the population variance calculation on.
• analytic_clause (optional): This is the clause that determines how the analytic function behaves, if using this as an analytic function.

The expression can be any numeric data type, and returns the same data type as the expression parameter.

 

Oracle COVAR_POP Function Syntax and Parameters

Aggregate Function

The syntax of the COVAR_POP aggregate function is:

COVAR_POP ( expression1, expression2 )

The parameters of the COVAR_POP function are:

• expression1 (mandatory): The first set of numbers to use in the calculation.
• expression2 (mandatory): The second set of numbers to use in the calculation.

If there is a pair of numbers that are both set to NULL, then the function does not consider them.

 

Analytic Function

When you use COVAR_POP as an analytic function, the syntax is:

COVAR_POP (expression1, expression2) OVER (analytic_clause)

The analytic_clause is added here, which is common for analytic queries.

The parameters of the COVAR_POP analytic function are:

• expression1 (mandatory): The first set of numbers to use in the calculation.
• expression2 (mandatory): The second set of numbers to use in the calculation.
• analytic_clause (mandatory): This defines how the analytic function operates. It includes a query partition clause, order by clause, and a windowing clause.

 

Oracle VAR_SAMP Function Syntax and Parameters

The syntax of the VAR_SAMP function is:

VAR_SAMP ( expression ) [OVER (analytic_clause)]

The parameters of the VAR_SAMP function are:

• expression (mandatory): This is the expression, such as a set of data or a column, to get the sample variance for.
• analytic_clause (optional): This clause determines how the analytic function behaves.

The expression can be any numeric data type, and returns the same data type as the expression parameter.

 

Oracle COVAR_SAMP Function Syntax and Parameters

Aggregate Function

When using COVAR_SAMP as an aggregate function, the syntax is:

COVAR_SAMP (expression1, expression2)

The parameters of the COVAR_SAMP function are:

• expression1 (mandatory): The first set of numbers to use in the calculation.
• expression2 (mandatory): The second set of numbers to use in the calculation.

These two parameters are a numeric data type, and return the same data type as the parameters.

 

Analytic Function

If you want to use the COVAR_SAMP function as an analytic function, the syntax is:

COVAR_SAMP (expression1, expression2) OVER (analytic_clause)

The only difference here is the addition of the analytic_clause, which is quite common for analytic queries.

The parameters of the COVAR_SAMP analytic function are:

• expression1 (mandatory): The first set of numbers to use in the calculation.
• expression2 (mandatory): The second set of numbers to use in the calculation.
• analytic_clause (mandatory): This defines how the analytic function operates. It includes a query partition clause, order by clause, and a windowing clause.

 

Examples of the Oracle VARIANCE Function

Example 1 – simple aggregate

This is an example of the VARIANCE function with a simple set of parameters.

SELECT VARIANCE(fees_paid)
FROM student;

Results:

VARIANCE(FEES_PAID)

38910.25641

 

Example 2 – aggregate using DISTINCT

This example uses the DISTINCT keyword to eliminate duplicates in the variance calculation.

SELECT VARIANCE(DISTINCT fees_paid) AS variance_test
FROM student;

Results:

VARIANCE_TEST

47102.5

 

Example 3 – analytic function

SELECT first_name, last_name, fees_paid,
VARIANCE(fees_paid) OVER (ORDER BY enrolment_date) AS variance_value
FROM student;

Result:

FIRST_NAME	LAST_NAME	FEES_PAID	VARIANCE_VALUE
Susan	Johnson	150	0
Mark	Holloway	410	33800
Michelle	Randall	(null)	33800
Tanya	Hall	150	22533.33333
Robert	Pickering	100	19691.66667
John	Smith	100	16870
Julie	Armstrong	0	19016.66667
Andrew	Cooper	400	24657.14286
Tom	Capper	320	23341.07143
Steven	Webber	80	22125
John	Rogers	700	38910.25641
Jarrad	Winston	300	38910.25641
Mary	Taylor	100	38910.25641
Mark	Anderson	45	38910.25641

This shows the variance for the fees_paid column.

 

Examples of the Oracle CUME_DIST Function

Let’s take a look at some examples of the CUME_DIST function.

Example 1 – single parameter as aggregate

This example uses a single parameter for CUME_DIST.

SELECT
CUME_DIST(300) WITHIN GROUP (ORDER BY fees_paid) AS cume_dist_test
FROM student;

Result:

CUME_DIST_TEST

0.5882352941

This shows the cumulative distribution of a student who has a fees_paid value of 300 within the entire table.

 

Example 2 – multiple parameters as aggregate

This example uses multiple parameters for CUME_DIST.

SELECT
CUME_DIST(300, 200) WITHIN GROUP (ORDER BY fees_paid, fees_required) AS cume_dist_test
FROM student;

Result:

CUME_DIST_TEST

0.5294117647

This shows the cumulative distribution of a student who has a fees_paid value of 300 and a fees_required value of 200 within the entire table.

 

Example 3 – analytic, no partition

This example has no query partition clause.

SELECT first_name, last_name, fees_paid,
CUME_DIST() OVER (ORDER BY fees_paid) AS cume_dist_test
FROM student;

Result:

FIRST_NAME	LAST_NAME	FEES_PAID	CUME_DIST_TEST
Julie	Armstrong	0	0.07142857143
Mark	Anderson	45	0.1428571429
Steven	Webber	80	0.2142857143
John	Smith	100	0.4285714286
Robert	Pickering	100	0.4285714286
Mary	Taylor	100	0.4285714286
Susan	Johnson	150	0.5714285714
Tanya	Hall	150	0.5714285714
Jarrad	Winston	300	0.6428571429
Tom	Capper	320	0.7142857143
Andrew	Cooper	400	0.7857142857
Mark	Holloway	410	0.8571428571
John	Rogers	700	0.9285714286
Michelle	Randall	(null)	1

The CUME_DIST column shows the cumulative distribution of each row’s fees_paid value, with no partitioning.

 

Example 4 – analytic with partition

This example is similar, but includes a query partition clause.

SELECT first_name, last_name, fees_paid,
CUME_DIST() OVER (PARTITION BY address_state ORDER BY fees_paid) AS cume_dist_test
FROM student;

Result:

FIRST_NAME	LAST_NAME	FEES_PAID	CUME_DIST_TEST
Mark	Anderson	45	1
Robert	Pickering	100	0.5
Susan	Johnson	150	1
Michelle	Randall		1
Tom	Capper	320	0.5
John	Rogers	700	1
Steven	Webber	80	0.3333333333
John	Smith	100	0.6666666667
Mark	Holloway	410	1
Mary	Taylor	100	1
Julie	Armstrong	0	0.3333333333
Tanya	Hall	150	0.6666666667
Andrew	Cooper	400	1
Jarrad	Winston	300	1

This example also shows the cumulative distribution of the fees_paid column for each row, but it is grouped into the address_state values.

 

Examples of the Oracle CORR Function

Here are some examples of the CORR function.

Example 1 – aggregate function

SELECT CORR(fees_paid, fees_required) AS corr_test
FROM student;

Result:

CORR_TEST

0.140645438

 

Example 2 – analytic function

This example uses CORR as an analytic function.

SELECT first_name, last_name,
CORR(fees_paid, fees_required) OVER (ORDER BY first_name) AS corr_value
FROM student;

Result:

FIRST_NAME	LAST_NAME	CORR_VALUE
Andrew	Cooper	(null)
Jarrad	Winston	1
John	Smith	-0.5214414501
John	Rogers	-0.5214414501
Julie	Armstrong	0.102484048
Mark	Anderson	-0.1255519091
Mark	Holloway	-0.1255519091
Mary	Taylor	-0.112526085
Michelle	Randall	-0.112526085
Robert	Pickering	0.01309498303
Steven	Webber	0.1030915728
Susan	Johnson	0.1288908087
Tanya	Hall	0.1486480032
Tom	Capper	0.140645438

 

Examples of the Oracle VAR_POP Function

Here are some examples of the VAR_POP function, both as an analytic function and an aggregate function.

Example 1 – Aggregate Function

This example shows how to use VAR_POP as an aggregate function

SELECT
VAR_POP(fees_required) AS var_pop_test
FROM student;

Result:

VAR_POP_TEST

66748.97959

 

Example 2 – Analytic Function

This example shows how to use VAR_POP as an analytic function.

SELECT first_name, last_name, address_state,
VAR_POP(fees_required) OVER (PARTITION BY address_state) AS var_pop_test
FROM student;

Result:

FIRST_NAME	LAST_NAME	ADDRESS_STATE	VAR_POP_TEST
Mark	Anderson	California	0
Susan	Johnson	Colorado	400
Robert	Pickering	Colorado	400
Michelle	Randall	Florida	0
Tom	Capper	Nevada	4900
John	Rogers	Nevada	4900
John	Smith	New York	35555.55556
Mark	Holloway	New York	35555.55556
Steven	Webber	New York	35555.55556
Mary	Taylor	Oregon	0
Andrew	Cooper	Texas	101666.6667
Tanya	Hall	Texas	101666.6667
Julie	Armstrong	Texas	101666.6667
Jarrad	Winston	Utah	0

 

Examples of the Oracle COVAR_POP Function

Example 1 – aggregate

Here is an example of the COVAR_POP function when used as an aggregate.

SELECT COVAR_POP(fees_paid, fees_required) AS covarpop_value
FROM student;

Result:

COVARPOP_VALUE

7079.585799

 

Example 2 – analytic function

This is an example of the COVAR_POP function as an analytic function.

SELECT first_name,
last_name,
COVAR_POP(fees_paid, fees_required) OVER (ORDER BY last_name) AS covar_pop_value
FROM student;

Result:

FIRST_NAME	LAST_NAME	COVAR_POP_VALUE
Mark	Anderson	0
Julie	Armstrong	8550
Tom	Capper	-2894.444444
Andrew	Cooper	16790.625
Tanya	Hall	15924
Mark	Holloway	14783.33333
Susan	Johnson	15360.20408
Robert	Pickering	17142.1875
Michelle	Randall	17142.1875
John	Rogers	6914.197531
John	Smith	4282.5
Mary	Taylor	2450
Steven	Webber	5397.569444
Jarrad	Winston	7079.585799

 

Examples of the Oracle VAR_SAMP Function

Now, let’s have a look at some examples of the VAR_SAMP function, both as an analytic function and an aggregate function.

Example 1 – Aggregate Function

This example shows how to use VAR_SAMP as an aggregate function

SELECT VAR_SAMP(fees_required) AS var_samp_test
FROM student;

Result:

VAR_SAMP_TEST

71883.51648

 

Example 2 – Analytic Function

This example shows how to use VAR_SAMP as an analytic function.

SELECT first_name,
last_name,
address_state,
VAR_SAMP(fees_required) OVER (PARTITION BY address_state) AS var_samp_test
FROM student;

Result:

FIRST_NAME	LAST_NAME	ADDRESS_STATE	VAR_SAMP_TEST
Mark	Anderson	California	(null)
Susan	Johnson	Colorado	800
Robert	Pickering	Colorado	800
Michelle	Randall	Florida	(null)
Tom	Capper	Nevada	9800
John	Rogers	Nevada	9800
John	Smith	New York	53333.33333
Mark	Holloway	New York	53333.33333
Steven	Webber	New York	53333.33333
Mary	Taylor	Oregon	(null)
Andrew	Cooper	Texas	152500
Tanya	Hall	Texas	152500
Julie	Armstrong	Texas	152500
Jarrad	Winston	Utah	(null)

 

Examples of the Oracle COVAR_SAMP Function

Example 1 – Aggregate Function

Here’s an example of the COVAR_SAMP function.

SELECT COVAR_SAMP(fees_paid, fees_required) AS cvsamp
FROM student;

Result:

CVSAMP

7669.551282

 

Example 2 – Analytic Function

Here’s an example of using the COVAR_SAMP as an analytic function.

SELECT first_name,
last_name,
COVAR_SAMP(fees_paid, fees_required) OVER (ORDER BY first_name) AS cvsamp
FROM student;

Result:

FIRST_NAME	LAST_NAME	CVSAMP
Andrew	Cooper
Jarrad	Winston	5000
John	Smith	-33916.66667
John	Rogers	-33916.66667
Julie	Armstrong	8500
Mark	Anderson	-9046.428571
Mark	Holloway	-9046.428571
Mary	Taylor	-7209.821429
Michelle	Randall	-7209.821429
Robert	Pickering	859.7222222
Steven	Webber	6752.222222
Susan	Johnson	8001.363636
Tanya	Hall	8731.818182
Tom	Capper	7669.551282

 

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!