Categories:

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

STDDEV_SAMP

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

Aliases:

STDDEV

Syntax

Aggregate function

STDDEV_SAMP( [ DISTINCT ] <expr1> )
Copy

Window function

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

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

Arguments

expr1

An expression that evaluates to a numeric value (integer, floating point, or fixed point).

expr2

This is the expression to partition by.

expr3

This is the expression to order by within each partition.

Returns

The data type of the returned value is DOUBLE.

Usage Notes

  • For single-record inputs, STDDEV_SAMP and STDDEV both return NULL. This is different from the Oracle behavior, where STDDEV_SAMP returns NULL for a single record and STDDEV returns 0.

  • 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 called as a window function:

    • The keyword DISTINCT is permitted syntactically, but is ignored.

    • If an ORDER BY sub-clause is used inside the OVER clause, then a window frame must be used. If no window frame is specified, then the ORDER BY implies a cumulative window frame:

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

      For more details about window frames, including syntax and examples, see Window Frame Syntax and Usage.

      For more information about implied window frames, see Window Frame Usage Notes.

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);
Copy
select k, stddev_samp(v), stddev_samp(v2) from aggr group by k;

---+----------------+-----------------+
 k | stddev_samp(v) | stddev_samp(v2) |
---+----------------+-----------------+
 1 | [NULL]         | [NULL]          |
 2 | 8.539125634    | 12.013880859    |
---+----------------+-----------------+
Copy
Language: English