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