Categories:: Aggregate Functions (General) , Window Functions (General, Window frame)

VAR_POP¶

Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.

Aliases:: VARIANCE_POP

Syntax¶

Aggregate function

VAR_POP( [ DISTINCT ] <expr1> )

Window function

VAR_POP( [ DISTINCT ] <expr1> ) OVER (
                                     [ PARTITION BY <expr2> ]
                                     [ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ]
                                     )

For details about window_frame syntax, see Window Frame Syntax and Usage.

Arguments¶

expr1: The expr1 should evaluate to one of the numeric data types.
expr2: This is the optional expression to partition by.
expr3: This is the optional expression to order by within each partition.

Returns¶

The data type of the returned value is NUMBER(<precision>, <scale>). The scale depends upon the values being processed.

Usage Notes¶

When passed a VARCHAR expression, this function implicitly casts the input to floating point values. If the cast cannot be performed, an error is returned.

When this function is called as a window function (i.e. with an OVER clause):
- If the OVER clause contains an ORDER BY subclause, then:
  - A window frame is required. If no window frame is specified explicitly, then the ORDER BY implies a cumulative window frame:
    
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    
    For information about window frames, including syntax and examples, see Window Frame Syntax and Usage.
    
    For information about implied window frames, see also Window Frame Usage Notes.
  - Using the keyword DISTINCT inside the window function is prohibited and results in a compile-time error.

Examples¶

This example shows how to use the VAR_POP function:

Create and fill a table:

create table aggr(k int, v decimal(10,2), v2 decimal(10, 2));
insert into aggr values 
   (1, 10, null),
   (2, 10, 11), 
   (2, 20, 22), 
   (2, 25, null), 
   (2, 30, 35);

Query the table, calling VAR_POP():

SELECT k, var_pop(v), var_pop(v2) 
    FROM aggr 
    GROUP BY k;
+---+---------------+---------------+
| K |    VAR_POP(V) |   VAR_POP(V2) |
|---+---------------+---------------|
| 1 |  0.0000000000 |          NULL |
| 2 | 54.6875000000 | 96.2222222222 |
+---+---------------+---------------+