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
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 2
23/04/2010
2
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 3
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 4
23/04/2010
3
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 5
Prof. Marlon S. Ramrez M.
Agrupando Datos
SQL Server - Programacin USANDO TRANSACT-SQL 6
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
Prof. Marlon S. Ramrez M.
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
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
2. Agregue la sentencia ORDER BYSELECT ProductID
, SpecialOfferID
FROM Sales.SalesOrderDetail
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
FROM Sales.SalesOrderDetail
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
SQL Server - Programacin USANDO TRANSACT-SQL 7
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 8
23/04/2010
5
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 9
Prof. Marlon S. Ramrez M.
Sub-agrupaciones Ms de una columna referenciada en la clusula GROUP BY
SQL Server - Programacin USANDO TRANSACT-SQL 10
23/04/2010
6
Prof. Marlon S. Ramrez M.
La clusula ROLLUP Esta es la opcin ms sencilla para calcular subtotales y
totales en la primera columna en el GROUP BY.
SQL Server - Programacin USANDO TRANSACT-SQL 11
Presenta los totales de la columnas ID1
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 12
Presenta los totales de la columnas ID1
23/04/2010
7
Prof. Marlon S. Ramrez M.
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)
SQL Server - Programacin USANDO TRANSACT-SQL 13
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
Prof. Marlon S. Ramrez M.
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)
FROM #MyHypotheticalTable
GROUP BY ROLLUP(ID1, ID2)
3. Ejecutar consulta con GROUP BY y analizar resultadoSELECT ID1, ID2, SUM(MyValue)
FROM #MyHypotheticalTable
GROUP BY CUBE(ID1, ID2)
SQL Server - Programacin USANDO TRANSACT-SQL 14
23/04/2010
8
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 15
Prof. Marlon S. Ramrez M.
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
SQL Server - Programacin USANDO TRANSACT-SQL 16
SELECT ID1, ID2, SUM(MyValue),
GROUPING(ID1) AS 'Group1', GROUPING(ID2) AS 'Group2'
FROM #MyHypotheticalTable
GROUP BY ROLLUP(ID1, ID2)
SELECT ID1, ID2, SUM(MyValue)
GROUPING(ID1) AS 'Group1', GROUPING(ID2) AS 'Group2'
FROM #MyHypotheticalTable
GROUP BY CUBE(ID1, ID2)
23/04/2010
9
Prof. Marlon S. Ramrez M.
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.
SQL Server - Programacin USANDO TRANSACT-SQL 17
Prof. Marlon S. Ramrez M.
PRCTICA 6.6: Uso de la clusula COMPUTE
1.Ejecutar consulta con COMPUTE y analizar resultadoSELECT ProductID, SalesOrderID, OrderQty
FROM Sales.SalesOrderDetail
ORDER BY ProductID, SalesOrderID
COMPUTE SUM(OrderQty)
2.Ejecutar consulta con COMPUTE BY y analizar resultadoSELECT ProductID, SalesOrderID, OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID > 75100
ORDER BY ProductID, SalesOrderID
COMPUTE SUM(OrderQty) BY ProductID
SQL Server - Programacin USANDO TRANSACT-SQL 18
23/04/2010
10
Prof. Marlon S. Ramrez M.
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
SQL Server - Programacin USANDO TRANSACT-SQL 19