- Categories:
Aggregate functions (General) , Window function syntax and usage (General)
STDDEV, STDDEV_SAMP¶
Returns the sample standard deviation (square root of sample variance) of non-NULL values. STDDEV and STDDEV_SAMP are aliases for the same function.
See also STDDEV_POP, which returns the population standard deviation (square root of variance).
Syntax¶
Aggregate function
{ STDDEV | STDDEV_SAMP } ( [ DISTINCT ] <expr1> )
Window function
{ STDDEV | STDDEV_SAMP } ( [ DISTINCT ] <expr1> ) OVER (
[ PARTITION BY <expr2> ]
[ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ]
)
For details about window_frame syntax, see Usage notes for window frames.
Arguments¶
expr1An expression that evaluates to a numeric value. This is the expression on which the standard deviation is calculated.
expr2This is the optional expression to partition by.
expr3This is the optional expression to order by within each partition.
Returns¶
The data type of the returned value is DOUBLE.
If all records inside a group are NULL, this function returns NULL.
Usage notes¶
For single-record inputs, STDDEV and STDDEV_SAMP 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 this function is called as a window function and the OVER clause contains an ORDER BY clause:
The DISTINCT keyword is prohibited and results in a SQL compilation error.
A window frame must be specified. If you do not specify a window frame, the following implied window frame is used:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
For more details about window frames, including syntax and examples, see Usage notes for window frames.
Aggregate function examples¶
The following example calculates the standard deviation for a small sample of integers:
CREATE TABLE t1 (c1 INTEGER); INSERT INTO t1 (c1) VALUES (6), (10), (14) ; SELECT STDDEV(c1) FROM t1;+----------+ | STDDEV() | |----------| | 4 | +----------+
Note that the function STDDEV_SAMP returns the same result:
SELECT STDDEV_SAMP(c1) FROM t1;+-----------------+ | STDDEV_SAMP(C1) | |-----------------| | 4 | +-----------------+
The following example uses a small table named menu_items, which lists items for sale from a food
truck. If you would like to create and load this table, see Create and load the menu_items table.
To find the sample standard deviation for both the cost of goods sold (COGS) and the sale price for the
Dessert rows, run this query:
SELECT menu_category, STDDEV(menu_cogs_usd) stddev_cogs, STDDEV(menu_price_usd) stddev_price FROM menu_items WHERE menu_category='Dessert' GROUP BY 1;+---------------+-------------+--------------+ | MENU_CATEGORY | STDDEV_COGS | STDDEV_PRICE | |---------------+-------------+--------------| | Dessert | 1.00519484 | 1.471960144 | +---------------+-------------+--------------+
Window function example¶
The following example also uses the menu_items table (see Create and load the menu_items table)
but calls the STDDEV function as a window function.
The window function partitions rows by the menu_category column. Therefore, the standard deviation is
calculated once for each category, and that value is repeated in the result for each row in the group.
In this example, the rows must be grouped by both the menu category and the cost of goods sold.
SELECT menu_category, menu_cogs_usd, STDDEV(menu_cogs_usd) OVER(PARTITION BY menu_category) stddev_cogs FROM menu_items GROUP BY 1,2 ORDER BY menu_category;
The following output is a partial result set for this query (the first 15 rows):
+---------------+---------------+--------------+ | MENU_CATEGORY | MENU_COGS_USD | STDDEV_COGS | |---------------+---------------+--------------| | Beverage | 0.50 | 0.1258305738 | | Beverage | 0.65 | 0.1258305738 | | Beverage | 0.75 | 0.1258305738 | | Dessert | 1.25 | 1.054751155 | | Dessert | 3.00 | 1.054751155 | | Dessert | 1.00 | 1.054751155 | | Dessert | 2.50 | 1.054751155 | | Dessert | 0.50 | 1.054751155 | | Main | 4.50 | 3.444051572 | | Main | 2.40 | 3.444051572 | | Main | 1.50 | 3.444051572 | | Main | 11.00 | 3.444051572 | | Main | 8.00 | 3.444051572 | | Main | NULL | 3.444051572 | | Main | 12.00 | 3.444051572 | ...