Definition:
In Oracle PL/SQL, STDDEV is a built in function which returns the standard deviation of a set of numbers, i.e. the square root of the variance for the input number set. It can be used as both an Aggregate and an Analytic function.
Standard deviation is a widely used measurement of variability or diversity used in statistics and probability theory. It shows how much variation or "dispersion" there is from the "average" (mean, or expected/budgeted value). A low standard deviation indicates that the data points tend to be very close to the mean, whereas high standard deviation indicates that the data are spread out over a large range of values.
Note that STDDEV returns zero for input set which contains only one element.
Example Syntax:
Aggregate Function:
STDDEV( [ DISTINCT | ALL ] expression )
Analytic Function:
STDDEV( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_clause ) ]
Example Usage:
The SQL statement below returns the standard deviation of the Quantity field from ORDERS table.
SELECT STDDEV(QTY)
FROM ORDERS;
STDDEV(QTY)
-----------
155.441001
The SQL statement below returns the cumulative standard deviation of the quantity over the orders against each date.
SELECT DT,STDDEV(QTY) OVER (ORDER_BY dt)
FROM ORDERS
DT STDDEV(QTY)OVER(ORDERBYDT)
--------- --------------------------
20-OCT-10 0
01-NOV-10 106.066017
09-DEC-10 76.3762616
13-JAN-11 159.373775
15-JAN-11 164.468842
16-JAN-11 159.342399
18-JAN-11 155.441001
Related Links:
Related Code Snippets:
- STDDEV - Returns the standard deviation. it returns STDDEV_SAMP if the number of pairs is more tha...
- STDDEV_POP - STDDEV_POP computes the population standard deviation and returns the square root of ...
- STDDEV_SAMP - STDDEV_SAMP computes the cumulative sample standard deviation and returns the square...