funciones de agregado bd
DESCRIPTION
Son funciones que sirven para el manejo de los datos en una Base de Datos.TRANSCRIPT
1SQL ANALÍTICA
David Ortiz Gutiérrez.Agosto 2015.
Universidad Piloto de Colombia.Ingeniería de Sistemas.
Electiva – Aplicaciones de Bases de Datos
Resumen
Las Bases de Datos son un área o rama muy extensa de las Ciencias de la Computación. Las Bases de Datos en esencia son una manera de digitalizar los datos que se tienen en físico de un entidad (institución, industria, empresa, entre otros), pero con los datos que se tienen registrados en estas se pueden hacer muchas cosas, una de ellas son los cálculos estadísticos para su uso en la entidad; para hacer estos cálculos se hace uso de las Funciones de Agregado, estas funciones son sentencias en lenguaje SQL que permiten utilizar los datos almacenados para obtener resultados.
ii
ContenidoCapítulo 1 Funciones de Agregación en SQL de Oracle.................................................................1
AVG.............................................................................................................................................1COLLECT(COLUMN)...............................................................................................................1CORR..........................................................................................................................................1CORR_*......................................................................................................................................1COUNT........................................................................................................................................2COVAR_POP..............................................................................................................................2COVAR_SAMP..........................................................................................................................2CUME_DIST...............................................................................................................................2DENSE_RANK...........................................................................................................................2FIRST..........................................................................................................................................2GROPU_ID..................................................................................................................................3GROUPING.................................................................................................................................3GROUPING_ID..........................................................................................................................3LAST...........................................................................................................................................3MAX............................................................................................................................................3MEDIAN.....................................................................................................................................3MIN..............................................................................................................................................3PERCENT_RANK......................................................................................................................3PERCENTILE_CONT................................................................................................................4PERCENTILE_DISC..................................................................................................................4RANK..........................................................................................................................................4REGREG_ (LINEAR REGRESSION).......................................................................................4STATS_BINOMIAL_TEST........................................................................................................5STATS_CROSSTAB..................................................................................................................5STATS_F_TEST.........................................................................................................................5STATS_KS_TEST......................................................................................................................5STATS_MODE...........................................................................................................................5STATS_MW_TEST....................................................................................................................6STATS_ONE_WAY_ANOVA...................................................................................................6STATS_T_TEST_*.....................................................................................................................6STATS_WSR_TEST...................................................................................................................6STDDEV......................................................................................................................................7STDDEV_POP............................................................................................................................7STDDEV_SAMP.........................................................................................................................7SUM.............................................................................................................................................7VAR_POP....................................................................................................................................7VAR_SAMP................................................................................................................................7VARIANCE.................................................................................................................................8
Capítulo 4 Resultados y discusión...................................................................................................9Lista de referencias........................................................................................................................10
iii
Capítulo 1
Funciones de Agregación en SQL de Oracle
Las funciones de agregación en SQL nos permiten realizar operaciones sobre un grupo o conjunto de datos o resultados, el resultado de estas operaciones es un único valor agregado de estos datos o resultados. La media, máximo y mínimo son el resultado de realizar estas operaciones que se pueden hacer a este grupo de datos. COUNT, MIN, MAX, SUM Y AVG son cinco de las treinta y ocho (38) operaciones que componen las funciones de agregación y que se van a explicar a continuación.1
AVG
Esta función es la encargada de calcular el promedio o la media de un conjunto de datos numéricos. El uso de esta operación se debe hacer de la siguiente forma: 2
SELECT AVG(COLUMN_NAME) FROM TABLE_NAME;3
COLLECT(COLUMN)
Toma como argumento una columna de cualquier tipo y crea una tabla anidada. Para obtener el resultado de esta función se debe usar con la función CAST. El uso de esta operación se debe hacer de la siguiente forma:
SELECT CAST(COLLECT(COLUMN_NAME)) FROM TABLE_NAME;4
CORR
Devuelve el coeficiente de correlación de un conjunto de pares de números. CORR(expr1,expr2), Oracle elimina primero aquellos pares de números cuyo expr1 o expr2 sea nulo. El uso de esta operación se debe hacer de la siguiente forma:
SELECT COLUMN_NAME, CORR(COLUMN_NAME)FROM TABLE_NAME;5
CORR_*
Coeficiente de correlación de Pearson. El uso de esta operación se debe hacer de la siguiente forma:
1
CORR_S(expr1,expr2,’COEFFICIENT’)CORR_S(expr1,expr2,’ONE_SIDED_SIG’)CORR_S(expr1,expr2,’TWO_SIDED_SIG’)
CORR_K(expr1,expr2,’COEFFICIENT’)CORR_K(expr1,expr2,’ONE_SIDED_SIG’)
CORR_K(expr1,expr2,’TWO_SIDED_SIG’)6
COUNT
Devuelve el número filas que retorna la consulta. Si se incluye la clausula DISTINCT cuenta el número de filas con distinta expresión. No se cuentan las filas con expresión igual a nulo. Si ponemos COUNT(*) entonces sí se cuentan filas con campos nulos. El uso de esta operación se debe hacer de la siguiente forma:7
SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;SELECT COUNT(*) FROM TABLE_NAME;
SELECT COUNT(DISTINCT COLUMN_NAME) FROM TABLE_NAME;8
COVAR_POP
Covarianza múltiple de un conjunto de pares de números.9
COVAR_SAMP
Covarianza múltiple de un conjunto de pares de números.10
CUME_DIST
Calcula la distribución acumulada de un valor en un conjunto de valores. CUME_DIST devuelve un valor entre 0 y 1.11
DENSE_RANK
Calcula el ranking de una fila en un conjunto ordenados de filas devolviendo dicho ranking como un numero. El ranking es un numero entero consecutivo empezando por el 1. Las filas con igual valor en el criterio del ranking tienen el mismo ranking. Esta función se utiliza para devolver los TOP-N o BOTTOM-N de un ranking.12
FIRST
La función FIRST() devuelve el primer valor de la columna seleccionada.13
2
GROPU_ID
Distingue grupos duplicados resultantes de una especificación GROUP BY. Solamente se puede utilizar en consultas en las que se utilice la clausula GROUP BY.14
GROUPING
Se emplea con los operadores "rollup" y "cube" y permite diferenciar si los valores "null" que aparecen en el resultado son valores nulos de las tablas o si son una fila generada por los operadores "rollup" o "cube".15
GROUPING_ID
Es una función que calcula el nivel de agrupación. GROUPING_ID sólo se puede utilizar en la lista SELECT HAVING o cláusulas ORDER BY cuando se especifica GROUP BY.16
LAST
La función LAST() devuelve el último valor de la columna seleccionada.17
MAX
Devuelve el máximo valor de la expresión. Se puede usar como función agregada o como función analítica.18
MEDIAN
Toma un valor numérico o de fecha y devuelve el valor medio o un valor interpolado que podría ser el valor medio una vez que los valores se han ordenado. Los valores nulos son ignorados en el cálculo.19
MIN
Devuelve el mínimo valor de la expresión. Se puede usar como función agregada o como función analítica.20
PERCENT_RANK
Calcula el rango relativo de una fila dentro de un grupo de filas. UsE PERCENT_RANK para evaluar la posición relativa de un valor dentro de un resultado de consulta establecido o partición. PERCENT_RANK es similar a la función CUME_DIST.21
3
PERCENTILE_CONT
Calcula un percentil basado en una distribución continua del valor de columna. El resultado se interpola y podría no ser igual a cualquiera de los valores específicos en la columna.22
PERCENTILE_DISC
Calcula un percentil específico de valores ordenados en todo un conjunto de filas o dentro de particiones distintas de un conjunto de filas. Para un valor percentil dado P , PERCENTILE_DISC ordena los valores de la expresión en la cláusula ORDER BY y devuelve el valor con el valor CUME_DIST más pequeña (con respecto a la misma especificación) que es mayor o igual a P . Por ejemplo, PERCENTILE_DISC (0.5) calculará el percentil 50 (es decir, la mediana) de una expresión. PERCENTILE_DISC calcula el percentil basado en una distribución discreta de los valores de la columna; el resultado es igual a un valor específico en la columna.23
RANK
Calcula el ranking de un valor en un grupo de valores. Devuelve un valor numérico.24
REGREG_ (LINEAR REGRESSION)
Funciones de regresión lineal:
REGR_SLOPE
REGR_INTERCEPT
REGR_COUNT
REGR_R2
REGR_AVGX
REGR_AVGY
REGR_SXX
REGR_SYY
4
REGR_SXY25
STATS_BINOMIAL_TEST
Es una prueba de probabilidad exacta utilizada para las variables dicotómicas, donde existen sólo dos valores posibles. Se prueba la diferencia entre una proporción de la muestra y una proporción dada. El tamaño de la muestra en este tipo de pruebas es generalmente pequeño.26
STATS_CROSSTAB
Es un método utilizado para analizar dos variables nominales. La STATS_ función CROSSTAB toma tres argumentos: dos expresiones y un valor de retorno de tipo VARCHAR2. expr1 y expr2 son las dos variables que se analizan. La función devuelve un número determinado por el valor del tercer argumento. Si omite el tercer argumento, el valor predeterminado es CHISQ_SIG.27
STATS_F_TEST
Prueba si dos varianzas son significativamente diferentes. El valor observado de f es la relación de la varianza una a la otra, por lo que valores muy diferentes de 1 por lo general indican diferencias significativas.28
STATS_KS_TEST
Es una función de Kolmogorov-Smirnov que compara dos muestras para probar si son de la misma población o de poblaciones que tienen la misma distribución. No asume que la población de la que se tomaron las muestras se distribuye normalmente.29
STATS_MODE
Toma como argumento un conjunto de valores y devuelve el valor que ocurre con mayor frecuencia. Si existe más de un modo, la Base de Datos Oracle opta por uno y devuelve sólo un valor.30
STATS_MW_TEST
Una prueba de Mann Whitney compara dos muestras independientes para probar la hipótesis nula de que dos poblaciones tienen la misma función de distribución contra la hipótesis alternativa de que las dos funciones de distribución son diferentes.31
5
STATS_ONE_WAY_ANOVA
Prueba diferencias en las medias (para grupos o variables) para la significación estadística mediante la comparación de dos estimaciones diferentes de varianza. Una estimación se basa en las variaciones dentro de cada grupo o categoría. Esto se conoce como los cuadrados medios dentro o error cuadrático medio. La otra estimación se basa en las variaciones entre las medias de los grupos. Esto se conoce como los cuadrados medios entre. Si el medio de los grupos es significativamente diferente, entonces los cuadrados medios entre serán más grande de lo esperado y no coincidirán con los cuadrados medios dentro. Si los cuadrados medios de los grupos son consistentes, entonces las dos estimaciones de la varianza serán aproximadamente el mismo.32
STATS_T_TEST_*
Las funciones de t -test son:
STATS_T_TEST_ONE : Una sola muestra de t -test
STATS_T_TEST_PAIRED : Una muestra de dos, emparejado t -test (también conocido como un cruzado t -test)
STATS_T_TEST_INDEP : Un t -test de dos grupos independientes con la misma varianza (varianzas combinadas)
STATS_T_TEST_INDEPU : Un t -test de dos grupos independientes con varianza desigual (varianzas no puestos en común).33
STATS_WSR_TEST
Es una prueba Wilcoxon Signed Rank de muestras pareadas para determinar si la mediana de las diferencias entre las muestras es significativamente diferente de cero. Los valores absolutos de las diferencias están ordenados y rangos asignados. Entonces la hipótesis nula establece que la suma de las filas de las diferencias positivas es igual a la suma de los rangos de las diferencias negativas.34
STDDEV
Devuelve la desviación estándar de la muestra de expr , un conjunto de números. Se puede utilizar tanto como un agregado y la función analítica. Se diferencia de STDDEV_SAMP, porque en STDDEV devuelve cero cuando se tiene sólo 1 fila de datos de entrada, mientras que STDDEV_SAMP devuelve null.
6
Oracle Database calcula la desviación estándar como la raíz cuadrada de la varianza definida para la variación en la función de agregado.35
STDDEV_POP
Calcula la desviación estándar de la población y devuelve la raíz cuadrada de la varianza de la población. Se puede utilizar tanto como un agregado y la función analítica.36
STDDEV_SAMP
Calcula la desviación estándar de la muestra acumulada y devuelve la raíz cuadrada de la varianza de la muestra. Se puede utilizar tanto como un agregado y la función analítica.37
SUM
Devuelve la sumatoria de los valores de la expresión. El uso de esta operación se debe hacer de la siguiente forma:38
SELECT SUM(COLUMN_NAME) FROM TABLE_NAME;39
VAR_POP
Devuelve la varianza de la población de un conjunto de de números después de descartar los nulos de este conjunto. Se puede utilizar tanto como un agregado y la función analítica.40
VAR_SAMP
Devuelve la varianza de la muestra de un conjunto de números después de descartar los nulos en este conjunto. Se puede utilizar tanto como un agregado y la función analítica.41
VARIANCE
Devuelve la varianza de expr. Se puede utilizar como una función de agregado o analítica.
Oracle Database calcula la varianza de expr de la siguiente manera: 0 si el número de filas en expr = 1 VAR_SAMP si el número de filas en expr > 1
Si se especifica DISTINCT , entonces usted puede especificar sólo el query_partition_clause del analytic_clause . El order_by_clause y windowing_clause no están permitidos.42
7
8
Capítulo 4
Resultados y discusión.
Pudimos investigar o consultar acerca de las funciones de agregado de SQL y obtuvimos conocimiento para saber que las Bases de Datos no son simplemente un “armario” o “archivador” de datos sino que también permite la manipulación de sus datos para poder obtener nuevos para el uso de la entidad donde se tengan. Esta manipulación se hace sobre un conjunto de datos y son unos cálculos matemáticos que se derivan de la estadística, son conocidos como Funciones de Agregado.
9
Lista de referencias
1CampusMVP. (2014). Fundamentos de SQL: Agrupaciones y funciones de agregación. 21 julio 2014 , de CampusMVP Sitio web:
http://www.campusmvp.es/recursos/post/Fundamentos-de-SQL-Agrupaciones-y-funciones-de-agregacion.aspx
2Oracle SQL*Plus Avanzado. AVG. https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/avg
3W3SCHOOLS. SQL AVG() Function. http://www.w3schools.com/sql/sql_func_avg.asp
4Oracle SQL*Plus Avanzado. COLLECT(Column). https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/
collectcolumn
5Oracle SQL*Plus Avanzado.CORR. https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/corr
6Oracle SQL*Plus Avanzado. CORR_*. https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/corr_
7Oracle SQL*Plus Avanzado. COUNT. https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/count
8W3SCHOOLS. SQL COUNT() Function. http://www.w3schools.com/sql/sql_func_count.asp
9Oracle SQL*Plus Avanzado. COVAR_POP.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/
covar_pop
10Oracle SQL*Plus Avanzado. COVAR_SAMP.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/
covar_samp
11Oracle SQL*Plus Avanzado. CUME_DIST.
10
https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/cume_dist
12Oracle SQL*Plus Avanzado. DENSE_RANK.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/
dense_rank
13W3SCHOOLS. SQL FIRST() Function. http://www.w3schools.com/sql/sql_func_first.asp
14Oracle SQL*Plus Avanzado. GROUP_ID.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/
group_id
15SQL Server YA. 41-Función Grouping. http://www.sqlserverya.com.ar/temarios/descripcion.php?cod=47&punto=41
16Microsoft Developer Network. GROUPING_ID (Transact-SQL). https://msdn.microsoft.com/en-us/library/bb510624.aspx
17W3SCHOOLS. SQL LAST() Function. http://www.w3schools.com/sql/sql_func_last.asp
18Oracle SQL*Plus Avanzado. MAX.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/max
19Oracle SQL*Plus Avanzado. MEDIAN.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/median
20Oracle SQL*Plus Avanzado. MIN.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/min
21Microsoft Developer Network. PERCENT_RANK (Transact-SQL). https://msdn.microsoft.com/es-co/library/hh213573.aspx
22Microsoft Developer Network. PERCENTILE_CONT (Transact-SQL). https://msdn.microsoft.com/es-co/library/hh231473.aspx
23Microsoft Developer Network. PERCENTILE_DISC (Transact-SQL). https://msdn.microsoft.com/en-us/library/hh231327.aspx
11
24Oracle SQL*Plus Avanzado. RANK.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/rank
25Oracle SQL*Plus Avanzado. REGREG_ (LINEAR REGRESSION) Functions.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/ regr_-
linear-regression-functions
26ORACLE Help Center. STATS_BINOMIAL_TEST.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions150.htm
27ORACLE Help Center. STATS_CROSSTAB.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions151.htm#
28ORACLE Help Center. STATS_F_TEST.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions152.htm
29ORACLE Help Center. STATS_KS_TEST.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions153.htm
30ORACLE Help Center. STATS_MODE.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions154.htm
31ORACLE Help Center. STATS_MW_TEST.http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions174.htm
32ORACLE Help Center. STATS_ONE_WAY_ANOVA.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions156.htm
33ORACLE Help Center. STATS_T_TEST.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions157.htm
34ORACLE Help Center. STATS_WSR_TEST.http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions177.htm
35ORACLE Help Center. STDDEV.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions159.htm
36ORACLE Help Center. STDDEV_POP.http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions145.htm
37ORACLE Help Center. STDDEV_SAMP.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions161.htm
12
38Oracle SQL*Plus Avanzado. SUM.https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas/sum
3917W3SCHOOLS. SQL SUM() Function. http://www.w3schools.com/sql/sql_func_sum.asp
40ORACLE Help Center. VAR_POP.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions210.htm
41ORACLE Help Center. VAR_SAMP.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions211.htm
42ORACLE Help Center. VARIANCE.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions212.htm
13