Calculos Estadisticos en SQL/PLus - Oracle
Quisiera saber una funcion en sql/plus o la forma como calculo la mediana y la moda de una columna de una tabla por ejemplo de montoprestamo de mi tabla prestamo, pues la media si se puede con la funcion avg. Existen funciones para hacer las que mencione tambien o quien podria decirme como haria.
Para promedio (media) seria :
select avg(montoprestamo) from prestamo;
Para promedio (media) seria :
select avg(montoprestamo) from prestamo;
1 Respuesta
Respuesta de thetaurus
1
CORR(expr1, expr2) OVER(analytic_clause)
CORR returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.
COVAR_POP(expr1, expr2) OVER(analytic_clause)
COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.
COVAR_SMP(expr1, expr2) OVER(analytic_clause)
COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.
CUME_DIST(expr) WITHIN GROUP(ORDER BY expr ASC|DESC NULLS FIRST|LAST)
CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.
PERCENT_RANK(expr) WITHIN GROUP(ORDER BY expr ASC|DESC NULLS FIRST|LAST)
The PERCENT_RANK function is similar to the CUME_DIST (cumulative distribution) function. The range of values returned by PERCENT_RANK is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0.
PERCENTILE_CONT(expr) WITHIN GROUP(ORDER BY expr ASC|DESC)
The PERCENTILE_CONT function is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation.
PERCENTILE_DISC(expr) WITHIN GROUP(ORDER BY expr ASC|DESC)
The PERCENTILE_DISC function is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.
REGR_AVGX(expr1, expr2) OVER(analytic_clause)
REGR_AVGX evaluates the average of the independent variable (expr2) of the regression line. It makes the following computation after the elimination of null (expr1, expr2) pairs: AVG(expr2)
REGR_AVGY(expr1, expr2) OVER(analytic_clause)
REGR_AVGY evaluates the average of the dependent variable (expr1) of the regression line. It makes the following computation after the elimination of null (expr1, expr2) pairs: AVG(expr1)
STDDEV([DISTINCT | ALL] n)
Determines the standard deviation, the square root of the variance, of the values in a column. See COUNT for usage of DISTINCT
STDDEV_POP(expr) OVER(analytic_clause)
STDDEV_POP computes the population standard deviation and returns the square root of the population variance. You can use it as both an aggregate and analytic function.
STDDEV_SAMP(expr) OVER(analytic_clause)
STDDEV_SAMP computes the cumulative sample standard deviation and returns the square root of the sample variance. You can use it as both an aggregate and analytic function.
VAR_POP(expr) OVER(analytic_clause)
VAR_POP returns the population variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.
VAR_SAMP(expr) OVER(analytic_clause)
VAR_SAMP returns the sample variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.
VARIANCE( [DISTINCT | ALL] n)
Determines the variance of the values in a column. See COUNT for usage of DISTINCT
CORR returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.
COVAR_POP(expr1, expr2) OVER(analytic_clause)
COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.
COVAR_SMP(expr1, expr2) OVER(analytic_clause)
COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.
CUME_DIST(expr) WITHIN GROUP(ORDER BY expr ASC|DESC NULLS FIRST|LAST)
CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.
PERCENT_RANK(expr) WITHIN GROUP(ORDER BY expr ASC|DESC NULLS FIRST|LAST)
The PERCENT_RANK function is similar to the CUME_DIST (cumulative distribution) function. The range of values returned by PERCENT_RANK is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0.
PERCENTILE_CONT(expr) WITHIN GROUP(ORDER BY expr ASC|DESC)
The PERCENTILE_CONT function is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation.
PERCENTILE_DISC(expr) WITHIN GROUP(ORDER BY expr ASC|DESC)
The PERCENTILE_DISC function is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.
REGR_AVGX(expr1, expr2) OVER(analytic_clause)
REGR_AVGX evaluates the average of the independent variable (expr2) of the regression line. It makes the following computation after the elimination of null (expr1, expr2) pairs: AVG(expr2)
REGR_AVGY(expr1, expr2) OVER(analytic_clause)
REGR_AVGY evaluates the average of the dependent variable (expr1) of the regression line. It makes the following computation after the elimination of null (expr1, expr2) pairs: AVG(expr1)
STDDEV([DISTINCT | ALL] n)
Determines the standard deviation, the square root of the variance, of the values in a column. See COUNT for usage of DISTINCT
STDDEV_POP(expr) OVER(analytic_clause)
STDDEV_POP computes the population standard deviation and returns the square root of the population variance. You can use it as both an aggregate and analytic function.
STDDEV_SAMP(expr) OVER(analytic_clause)
STDDEV_SAMP computes the cumulative sample standard deviation and returns the square root of the sample variance. You can use it as both an aggregate and analytic function.
VAR_POP(expr) OVER(analytic_clause)
VAR_POP returns the population variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.
VAR_SAMP(expr) OVER(analytic_clause)
VAR_SAMP returns the sample variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.
VARIANCE( [DISTINCT | ALL] n)
Determines the variance of the values in a column. See COUNT for usage of DISTINCT
