curso sql server prog u2-06

23/04/2010

1

Prof. Marlon S. Ramrez M.

USANDO TRANSACT-SQL

1. Consultas de los datos

2. Funciones SQL (8 hrs)

3. Grupos y Agregacin

4. Consultas multi-tablas

SQL Server - Programacin USANDO TRANSACT-SQL 1


Objetivos

Aprender a utilizar las funciones agregadas para resumir datos y

realizar anlisis estadstico.

Ver cmo los registros se pueden agrupar y

agregar en subtotales.

Ensear a utilizar las caractersticas de

agrupacin especializada para generar informes

agrupados.


23/04/2010

2


Agrupar o no agrupar

Los criterios utilizados para la agrupacin puede ser el valor de una columna o una combinacin de varias columnas.

Agrupar y agregar los datos pueden ofrecer un contexto significativo para el anlisis de la informacin empresarial.

Permite a los usuarios para realizar comparaciones y detectar tendencias o anomalas en los datos.

Los campos utilizados para las agregaciones son un tipo de campo conocido como medidas o hechos.

Estos campos suelen almacenar valores numricos que se pueden resumir, promediar, u otro tipo de agregados.

Una excepcin a esta regla es que las claves y los campos de atributos se utilizan a menudo para el recuento de los registros, que es una forma de agregacin.



Funciones Agregadas Simples

Funcin Descripcin

COUNT() Calcula el recuento de todos los valores no nulos para una columna especfica. Tambin puede utilizar COUNT (*) para devolver el recuento absoluto de filas, independientemente de los valores null. Devuelve el tipo de datos int.

COUNT_BIG () Lo mismo que la funcin COUNT () pero devuelve el tipo de datos bigint. Esto sera slo es necesaria cuando la tabla contiene ms de dos billones de filas.

SUM () Devuelve la suma de todos los valores no nulos en el rango. Devuelve el tipo de datos igual a la columna sumada.

AVG () Devuelve el promedio de todos los valores no nulos en el rango. Devuelve el tipo de datos igual a la columna sumada.

MIN () Devuelve el valor menor no nulo en el rango. Puede ser utilizado con cualquier tipo de datos que se pueda ordenar.

MAX () Devuelve el valor menor no nulo en el rango. Puede ser utilizado con cualquier tipo de datos que se pueda ordenar.


23/04/2010

3


Funciones Agregadas Estadsticas

Categora Propsito

DESVEST () Devuelve la desviacin estndar sencilla para todos los valores no nulos en un rango numricos. Devuelve un tipo de datos float, independientemente del tipo de columna

DESVESTP () Devuelve la desviacin estndar de una poblacin, para todos los valores no nulos en un rango numrico. Devuelve un tipo de datos float, independientemente del tipo de columna.

VAR () Devuelve la varianza simple para todos los valores no nulos en un rango numrico. Devuelve un tipo de datos float, independientemente del tipo de columna.

VARP () Devuelve la varianza de una poblacin, para todos los valores no nulos en un rango numrico. Devuelve un tipo de datos float, independientemente del tipo de columna.

CHECKSUM_AGG Devuelve una suma de comprobacin de valores en un rango de agregado. Esto se utiliza para comparar un rango de valores en contra de otro rango de saber si son lo mismo. El resultado valor es por lo general no es til, excepto como una comparacin con otro de control.



Agrupando Datos


Ejemplos

La funciones agregadas solo

regresan un valor simple. (resultado

escalar)

Los grupos se utilizan para

resumir las filas

S puede realizar clculos agregados en cada una de las

agrupaciones.

Las columnas devueltas por una consulta agrupada

deben ser referenciado con

un GROUP BY

Ejemplo 1: SELECT SalesOrderID, SUM(OrderQty) FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

Ejemplo 2: SELECT SalesOrderID

, SUM(OrderQty) AS QtySum

, COUNT(SalesOrderID) AS DetailCount

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

PRACTICA 6.1: Ejecutar los ejemplos 1 y 2, y analizar resultados.

23/04/2010

4


PRCTICA 6.2: Ordenando una Consulta Agrupada-Agregada

1. Agrupe los datos a como se muestra y revise el orden en que aparecen los datos

SELECT

ProductID

, SpecialOfferID


GROUP BY ProductID, SpecialOfferID

2. Agregue la sentencia ORDER BYSELECT ProductID

, SpecialOfferID


GROUP BY ProductID, SpecialOfferID

ORDER BY 1, 2

Tambin puede utilizar numero de columna para referirse a los campos en ORDER BY.

4. Agrupe por ProductID y agregue con la funcin COUNT la columna SpecialOfferID

SELECT ProductID

, COUNT(SpecialOfferID) Cantidad


GROUP BY ProductID

5. Ordene la consulta anterior por el valor regresado por la funcin agregada

Opcin 1ORDER BY COUNT(SpecialOfferID)

Opcin 2ORDER BY Cantidad



La clusula HAVING

Si tiene que filtrar los resultados de una consulta agrupada en funcin del resultado de un valor agregado, entonces el agregado se debe realizar en primer lugar.

No puedes utilizar la clusula WHERE, ya que se procesa antes de la agrupamiento y agregacin, por lo tanto, necesita alguna manera de filtrar las filas despus de que el grupo ha sido completado.

Este es el trabajo de la clusula HAVING.


23/04/2010

5


PRCTICA 6.3: Ejecute las siguientes consultas que utilizan HAVING

1. Consulta para recuperar las ventas totales de cada ao.SELECT

DATEPART(yyyy,OrderDate) AS N'Year'

,SUM(TotalDue) AS N'Total Order Amount'

FROM Sales.SalesOrderHeader

GROUP BY DATEPART(yyyy,OrderDate)

ORDER BY DATEPART(yyyy,OrderDate)

2. Aplique un filtro a la consulta para que aparezca solo datos de aos a partir del 2003

SELECT

DATEPART(yyyy,OrderDate) AS N'Year'

,SUM(TotalDue) AS N'Total Order Amount'

FROM Sales.SalesOrderHeader

GROUP BY DATEPART(yyyy,OrderDate)

HAVING DATEPART(yyyy,OrderDate) >= N'2003'

ORDER BY DATEPART(yyyy,OrderDate)

3. Ejecute la consulta anterior utilizando WHERE en lugar de HAVING.



Sub-agrupaciones Ms de una columna referenciada en la clusula GROUP BY


23/04/2010

6


La clusula ROLLUP Esta es la opcin ms sencilla para calcular subtotales y

totales en la primera columna en el GROUP BY.


Presenta los totales de la columnas ID1


La clusula CUBE El operador CUBE es una versin ampliada del operador ROLLUP. En

lugar de resumir los valores agregados de la primera columna en la lista GROUP BY, CUBE realiza este resumen por cada combinacin de valores de las columnas agrupadas.


Presenta los totales de la columnas ID1

23/04/2010

7


PRCTICA 6.4a: Clusulas ROLLUP y CUBE - Crear Tabla Ejemplo

1. Ejecutar comando para crear la tabla MyHypotheticalTableCREATE TABLE #MyHypotheticalTable

(ID1 varchar(1), ID2 varchar(1), MyValue int)

2. Ejecutar comandos para llenar la tablaINSERT INTO #MyHypotheticalTable VALUES('A', 'X', 2)

INSERT INTO #MyHypotheticalTable VALUES('A', 'X', 1)

INSERT INTO #MyHypotheticalTable VALUES('A', 'Y', 2)

INSERT INTO #MyHypotheticalTable VALUES('A', 'Y', 1)

INSERT INTO #MyHypotheticalTable VALUES('B', 'X', 3)

INSERT INTO #MyHypotheticalTable VALUES('B', 'Y', 2)

INSERT INTO #MyHypotheticalTable VALUES('B', 'Y', 2)


Nota: SQL Server utiliza el signo # al inicio del nombre de un objeto para indicar que el objeto es temporal. Los objetos temporales slo existen durante la operacin en la que se crean


PRCTICA 6.4b: Clusulas ROLLUP y CUBE Ejecutar Consultas

1. Ejecutar consulta con GROUP BYSELECT ID1, ID2, Sum(MyValue)

FROM #MyHypotheticalTable

GROUP BY ID1, ID2

2. Ejecutar consulta con ROLLUP y analizar resultadoSELECT ID1, ID2, SUM(MyValue)


GROUP BY ROLLUP(ID1, ID2)

3. Ejecutar consulta con GROUP BY y analizar resultadoSELECT ID1, ID2, SUM(MyValue)


GROUP BY CUBE(ID1, ID2)


23/04/2010

8


La funcin Grouping

Cuando se utiliza ROLLUP y CUBE, un valor nulo se utiliza para indicar

un resumen o fila subtotal.

Qu pasa si una columna en la lista GROUP BY en realidad contiene valores

nulos?

GROUPING()

Indica si una expresin de la columna especificada en una lista GROUP BY es

agregada o no.

Devuelve 1 para agregado o 0 para no agregadas en el conjunto de resultados.

Slo se puede utilizar en la lista SELECT, HAVING, y las clusulas ORDER BY

GROUP BY cuando se especifica.



PRCTICA 6.5: Ejecute las siguientes consultas que utilizan ROLLUP y CUBE con valores nulos

1. Inserte un registro con un valor nuloINSERT INTO #MyHypotheticalTable VALUES('A', NULL, 2)

2. Ejecute las consultas en la prctica 6.4b y analice los resultados. Observe que en los resultados de las consultas 2 y 3 donde se utilice ROLLUP y CUBE existen registros ambiguos.

3. Utilice la funcin GROUPING() en las consultas y analice los resultados


SELECT ID1, ID2, SUM(MyValue),

GROUPING(ID1) AS 'Group1', GROUPING(ID2) AS 'Group2'


GROUP BY ROLLUP(ID1, ID2)

SELECT ID1, ID2, SUM(MyValue)

GROUPING(ID1) AS 'Group1', GROUPING(ID2) AS 'Group2'


GROUP BY CUBE(ID1, ID2)

23/04/2010

9


La clusula COMPUTE

Genera totales que aparecen como

columnas de resumen

adicionales al final del conjunto de

resultados.

Cuando se utiliza con BY, la clusula COMPUTE genera interrupciones de

control y subtotales en el conjunto de

resultados.

Puede especificar COMPUTE BY y COMPUTE en la misma consulta.

Solo es recomendable en

consultas informales. No

incluir en programas.



PRCTICA 6.6: Uso de la clusula COMPUTE

1.Ejecutar consulta con COMPUTE y analizar resultadoSELECT ProductID, SalesOrderID, OrderQty


ORDER BY ProductID, SalesOrderID

COMPUTE SUM(OrderQty)

2.Ejecutar consulta con COMPUTE BY y analizar resultadoSELECT ProductID, SalesOrderID, OrderQty


WHERE SalesOrderID > 75100

ORDER BY ProductID, SalesOrderID

COMPUTE SUM(OrderQty) BY ProductID


23/04/2010

10


Ejercicios

Ejercicio 6.1: Escribir una consulta para devolver el [Title], sexo y el valor ms bajo de [LoginID] para cada grupo de empleados. Incluya slo los empleados con [Title] igual a Buyer, Recruiter y Stocker

Ejercicio 6.2: Devuelve una lista de valores [ProductSubCategoryID] de la tabla de productos. Slo incluir sub-categoras que ocurren ms de 20 veces. Adems del valor ID, tambin devuelve el nombre del producto que aparece por primera vez en orden alfabtico y el precio ms alto para los productos de esta subcategora.

Ejercicio 6.3: Producir una lista de puestos de la organizacin [Title] de la tabla [Employee]. Para cada nivel, incluyen las horas promedio de vacaciones para todos los empleados de cada sexo. Tambin producen una fila de subtotal adicionales para cada nivel que incluye el promedio de horas de vacaciones para todos los empleados de ese nivel. Esto debe hacerse utilizando slo una expresin SELECT


curso sql server prog u2-06

Documents