SQL Reference - STDDEV

STDDEV_SAMP(expr)

expr: The expression or column on which the standard deviation function will be calculated. It must be a floating-point expression.

Example

SELECT
  STDDEV_SAMP(price)
FROM
  products;

Output

+-------------------+
| STDDEV_SAMP(price)|
+-------------------+
|3568.421013        |
+-------------------+

Explanation

STDDEV_SAMP returns the sample standard deviation of a column. In the provided example, it calculates the sample standard deviation of the ‘price’ column in the ‘products’ table. The returned value, 3568.421013, represents the amount of variation in pricing.

STDDEV_SAMP(expression) OVER ([ partition_by_clause] order_by_clause)

expression: This parameter is the column or set of values on which the standard deviation is to be calculated. It must return a numeric value.
partition_by_clause: This parameter is optional and describes how data should be grouped into partitions. It subsets the data into groups or partitions, each of which is independently considered for the calculation of standard deviation.
order_by_clause: Another optional parameter, it dictates the order in which data within each partition should be sorted for the calculation of standard deviation. It only influences the function when used with a window frame clause. Without a present window frame clause, the order by clause has no effect on the result.

Example

CREATE TABLE t AS
SELECT generate_series(1, 5) AS val;

SELECT STDDEV_SAMP(val) AS sample_standard_deviation
FROM t;

Output

 sample_standard_deviation
---------------------------
             1.5811388300841898
(1 row)

Explanation

In this example, the standard deviation sample (STDDEV_SAMP) function calculates the sample standard deviation for a set of values from 1 to 5, stored in a table called “t”. The calculated sample standard deviation is approximately 1.58.

STDDEV_SAMP( expression ) OVER ( [ query_partition_clause ] [ order_by_clause ] )

expression: Indicates the column or set of columns for which the standard deviation is to be calculated. It can be a column, formula, or function, which returns a numeric datatype.
query_partition_clause: Divides the result set produced by the FROM clause into partitions to which the STDDEV_SAMP function is applied. Inspecting the standard deviation for each grouped section rather than the total field is facilitated by this partition clause.
order_by_clause: Orders the rows in each partition by the column set specified in this clause. For the STDDEV_SAMP function, this clause does not impact the result and can commonly be omitted. Generally used for functions that require an order, like Lead and Lag.

Example

SELECT STDDEV_SAMP(price)
FROM products;

Output

25.98076211353316

Explanation

The STDDEV_SAMP() function returns the sample standard deviation of the price column in the products table. The sample standard deviation is more accurate in predicting the standard deviation of the entire population from which the values in the price column are sampled. In this case, it resulted in 25.98076211353316.