Categories:

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

STDDEV_POP

Returns the population standard deviation (square root of variance) of non-NULL values. If all records inside a group are NULL, returns NULL.

Syntax

Aggregate function

STDDEV_POP( [ DISTINCT ] expression_1)

Window function

STDDEV_POP( [ DISTINCT ] <expression_1> ) OVER (
                                        [ PARTITION BY <expression_2> ]
                                        [ ORDER BY <expression_3> [ ASC | DESC ] [ <window_frame> ] ]
                                        )

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

Arguments

expression_1

An expression that evaluates to a numeric value. This is the expression on which the standard deviation is calculated.

expression_2

This is the optional expression to partition by.

expression_3

This is the optional expression to order by within each partition.

Returns

The data type of the returned value is DOUBLE.

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

create or replace table aggr(k int, v decimal(10,2), v2 decimal(10, 2));
insert into aggr values(1, 10, null);
insert into aggr values(2, 10, 11), (2, 20, 22), (2, 25,null), (2, 30, 35);
select k, stddev_pop(v), stddev_pop(v2) from aggr group by k;

---+---------------+----------------+
 k | stddev_pop(v) | stddev_pop(v2) |
---+---------------+----------------+
 1 | 0             | [NULL]         |
 2 | 7.395099729   | 9.809292645    |
---+---------------+----------------+
Back to top