Syntax¶

Aggregate function

VAR_POP([DISTINCT]<expr1>)

Window function

VAR_POP([DISTINCT]<expr1>)OVER([PARTITIONBY<expr2>][ORDERBY<expr3>[ASC|DESC][<window_frame>]])

For detailedwindow_frame syntax, seeWindow function syntax and usage.

Arguments¶

expr1

Theexpr1 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 isNUMBER(<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 castcannot be performed, an error is returned.

• When this function is called as a window function with an OVER clause that contains an ORDER BY clause:

  • A window frame is required. If no window frame is specified explicitly, the following implied window frame is used:

    RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW

    For more information about window frames, including syntax, usage notes, and examples, seeWindow function syntax and usage.

  • 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:

createtableaggr(kint,vdecimal(10,2),v2decimal(10,2));insertintoaggrvalues(1,10,null),(2,10,11),(2,20,22),(2,25,null),(2,30,35);

Copy

Query the table:

SELECTk,var_pop(v),var_pop(v2)FROMaggrGROUPBYkORDERBYk;+---+---------------+---------------+| K |    VAR_POP(V) |   VAR_POP(V2) ||---+---------------+---------------|| 1 |  0.0000000000 |          NULL || 2 | 54.6875000000 | 96.2222222222 |+---+---------------+---------------+

Copy