Bienvenido
Haz ingresado al Blog de los alumnos del 4to semestre de Ingenieria Informatica de la Universidad Tecnológica de chile INACAP.
Suscribirse a:
Enviar comentarios (Atom)
Haz ingresado al Blog de los alumnos del 4to semestre de Ingenieria Informatica de la Universidad Tecnológica de chile INACAP.
Datos del Grupo
- Nacho Coyhaique
- Estudiante de Ingeniería Informática Universidad Tecnológica de Chile
3 comentarios:
Una consulta de referencias cruzadas es aquella que nos permite visualizar los datos en filas y en columnas, estilo tabla, por ejemplo:
Producto / Año 1996 1997
Pantalones 1.250 3.000
Camisas 8.560 1.253
Zapatos 4.369 2.563
Si tenemos una tabla de productos y otra tabla de pedidos, podemos visualizar en total de productos pedidos por año para un artículo determinado, tal y como se visualiza en la tabla anterior. La sintaxis para este tipo de consulta es la siguiente:
TRANSFORM función agregada instrucción select PIVOT campo pivot
[IN (valor1[, valor2[, ...]])]
En donde:
función agregada Es una función SQL agregada que opera sobre los datos seleccionados.
instrucción select Es una instrucción SELECT.
campo pivot Es el campo o expresión que desea utilizar para crear las cabeceras de la columna en el resultado de la consulta.
valor1, valor2 Son valores fijos utilizados para crear las cabeceras de la columna.
Para resumir datos utilizando una consulta de referencia cruzada, se seleccionan los valores de los campos o expresiones especificadas como cabeceras de columnas de tal forma que pueden verse los datos en un formato más compacto que con una consulta de selección.
TRANSFORM es opcional pero si se incluye es la primera instrucción de una cadena SQL. Precede a la instrucción SELECT que especifica los campos utilizados como encabezados de fila y una cláusula GROUP BY que especifica el agrupamiento de las filas. Opcionalmente puede incluir otras cláusulas como por ejemplo WHERE, que especifica una selección adicional o un criterio de ordenación.
Los valores devueltos en campo pivot se utilizan como encabezados de columna en el resultado de la consulta. Por ejemplo, al utilizar las cifras de ventas en el mes de la venta como pivot en una consulta de referencia cruzada se crearían 12 columnas. Puede restringir el campo pivot para crear encabezados a partir de los valores fijos (valor1, valor2) listados en la cláusula opcional IN.
También puede incluir valores fijos, para los que no existen datos, para crear columnas adicionales.
Ejemplos:
TRANSFORM
Sum(Cantidad) AS Ventas
SELECT
Producto, Cantidad
FROM
Pedidos
WHERE
Fecha Between #01-01-1998# And #12-31-1998#
GROUP BY
Producto
ORDER BY
Producto
PIVOT
DatePart("m", Fecha)
(Crea una consulta de tabla de referencias cruzadas que muestra las ventas de productos por mes para un año específico. Los meses aparecen de izquierda a derecha como columnas y los nombres de los productos aparecen de arriba hacia abajo como filas.)
TRANSFORM
Sum(Cantidad) AS Ventas
SELECT
Compania
FROM
Pedidos
WHERE
Fecha Between #01-01-1998# And #12-31-1998#
GROUP BY
Compania
ORDER BY
Compania
PIVOT
"Trimestre " &
DatePart("q", Fecha)
In ('Trimestre1', 'Trimestre2', 'Trimestre 3', 'Trimestre 4')
(Crea una consulta de tabla de referencias cruzadas que muestra las ventas de productos por trimestre de cada proveedor en el año indicado. Los trimestres aparecen de izquierda a derecha como columnas y los nombres de los proveedores aparecen de arriba hacia abajo como filas.)
Un caso práctico: Se trata de resolver el siguiente problema: tenemos una tabla de productos con dos campos, el código y el nombre del producto, tenemos otra tabla de pedidos en la que anotamos el código del producto, la fecha del pedido y la cantidad pedida. Deseamos consultar los totales de producto por año, calculando la media anual de ventas.
Estructura y datos de las tablas:
ARTICULOS PEDIDOS
ID Nombre ID Fecha Cantidad
1 Zapatos 1 11/11/1996 250
2 Pantalones 2 11/11/1996 125
3 Blusas 3 11/11/1996 520
1 12/10/1996 50
2 04/10/1996 250
3 05/08/1996 100
1 01/01/1997 40
2 02/08/1997 60
3 05/10/1997 70
1 12/12/1997 8
2 15/12/1997 520
3 17/10/1997 1.250
Para resolver la consulta planteamos la siguiente consulta:
TRANSFORM
Sum(Pedidos.Cantidad) AS Resultado
SELECT
Nombre AS Producto, Pedidos.Id AS Código,
Sum(Pedidos.Cantidad) AS TOTAL,
Avg(Pedidos.Cantidad) AS Media
FROM
Pedidos, Artículos
WHERE
Pedidos.Id = Artículos.Id
GROUP BY
Pedidos.Id, Artículos.Nombre
PIVOT
Year(Fecha)
Y obtenemos el siguiente resultado:
Producto Código Total Media 1996 1997
Zapatos 1 348 87 300 48
Pantalones 2 955 238,75 375 580
Blusas 3 1940 485 620 1320
Comentarios a la consulta:
La cláusula TRANSFORM indica el valor que deseamos visualizar en las columnas que realmente pertenecen a la consulta, en este caso 1996 y 1997, puesto que las demás columnas son opcionales. SELECT especifica el nombre de las columnas opcionales que deseamos visualizar, en este caso Producto, Código, Total y Media, indicando el nombre del campo que deseamos mostrar en cada columna o el valor de la misma. Si incluimos una función de cálculo el resultado se hará basándose en los datos de la fila actual y no al total de los datos.
FROM especifica el origen de los datos. La primera tabla que debe figurar es aquella de donde deseamos extraer los datos, esta tabla debe contener al menos tres campos, uno para los títulos de la fila, otros para los títulos de la columna y otro para calcular el valor de las celdas.
En este caso en concreto se deseaba visualizar el nombre del producto, como en la tabla de pedidos sólo figuraba el código del mismo se añadió una nueva columna en la cláusula select llamada Producto que se corresponda con el campo Nombre de la tabla de artículos. Para vincular el código del artículo de la tabla de pedidos con el nombre del mismo de la tabla artículos se insertó la cláusula INNER JOIN.
La cláusula GROUP BY especifica el agrupamiento de los registros, contrariamente a los manuales de instrucción esta cláusula no es opcional ya que debe figurar siempre y debemos agrupar los registros por el campo del cual extraemos la información. En este caso existen dos campos de los que extraemos la información: pedidos.cantidad y artículos.nombre, por ello agrupamos por los campos.
Para finalizar la cláusula PIVOT indica el nombre de las columnas no opcionales, en este caso 1996 y 1997 y como vamos a el dato que aparecerá en las columnas, en este caso empleamos el año en que se produjo el pedido, extrayéndolo del campo pedidos.fecha.
Otras posibilidades de fecha de la cláusula pivot son las siguientes:
Para agrupamiento por Trimestres:
PIVOT "Tri " & DatePart("q",[Fecha]); Para agrupamiento por meses (sin tener en cuenta el año)
PIVOT Format([Fecha],"mmm") In ("Ene", "Feb", "Mar", "Abr", "May", "Jun", "Jul", "Ago", "Sep", "Oct", "Nov", "Dic"); Para agrupar por días
PIVOT Format([Fecha],"Short Date");
Pablo Pelle.
A. Utilizar SELECT para recuperar filas y columnas
En el siguiente ejemplo se muestran tres fragmentos de código. En el primer ejemplo de código, se devuelven todas las filas (no se especifica la cláusula WHERE) y todas las columnas (con *) de la tabla Product de la base de datos AdventureWorks.
USE AdventureWorks ; GO SELECT * FROM Production.Product ORDER BY Name ASC ; -- Alternate way. USE AdventureWorks ; GO SELECT p.* FROM Production.Product p ORDER BY Name ASC ; GO
En este ejemplo se devuelven todas las filas (no se ha especificado la cláusula WHERE) y sólo un subconjunto de las columnas (Name, ProductNumber, ListPrice) de la tabla Product de la base de datos AdventureWorks. Además, se agrega un encabezado de columna.
USE AdventureWorks ; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product ORDER BY Name ASC ; GO
En este ejemplo sólo se devuelven las filas de Product que tienen una línea de productos de R y cuyo valor correspondiente a los días para fabricar es inferior a 4.
USE AdventureWorks ; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = 'R' AND DaysToManufacture < 4 ORDER BY Name ASC ; GO
B. Utilizar SELECT con encabezados de columna y cálculos
En los siguientes ejemplos se devuelven todas las filas de la tabla Product. En el primer ejemplo se devuelven las ventas totales y los descuentos de cada producto. En el segundo ejemplo se calculan los beneficios totales de cada producto.
USE AdventureWorks ; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product p INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC ; GO
Ésta es la consulta que calcula el beneficio de cada producto de cada pedido de venta.
USE AdventureWorks ; GO SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ', p.Name AS ProductName FROM Production.Product p INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID ORDER BY ProductName ASC ; GO
C. Utilizar DISTINCT con SELECT
En el siguiente ejemplo se utiliza DISTINCT para evitar la recuperación de títulos duplicados.
USE AdventureWorks ; GO SELECT DISTINCT Title FROM HumanResources.Employee ORDER BY Title ; GO
D. Crear tablas con SELECT INTO
En el primer ejemplo se crea una tabla temporal denominada #Bicycles en tempdb. Para utilizar esta tabla, haga siempre referencia a ella con el nombre exacto mostrado. Eso incluye el signo de número (#).
USE tempdb ; IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL DROP TABLE #Bicycles ; GO USE AdventureWorks; GO SET NOCOUNT ON SELECT * INTO #Bicycles FROM Production.Product WHERE ProductNumber LIKE 'BK%' SET NOCOUNT OFF SELECT name FROM tempdb..sysobjects WHERE name LIKE '#Bicycles%' ; GO
Éste es el conjunto de resultados.
name ------------------------------ #Bicycles_____________________
En el segundo ejemplo se crea la tabla permanente NewProducts.
USE AdventureWorks ; GO IF OBJECT_ID ('dbo.NewProducts', 'U') IS NOT NULL DROP TABLE dbo.NewProducts ; GO ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED ; GO SELECT * INTO dbo.NewProducts FROM Production.Product WHERE ListPrice > $25 AND ListPrice < $100 SELECT name FROM sysobjects WHERE name LIKE 'New%' USE master ; GO ALTER DATABASE AdventureWorks SET RECOVERY FULL ; GO
Éste es el conjunto de resultados.
name ------------------------------ NewProducts (1 row(s) affected)
E. Utilizar subconsultas correlativas
En el siguiente ejemplo se muestran consultas que son semánticamente equivalentes y se demuestra la diferencia entre la utilización de la palabra clave EXISTS y la palabra clave IN. Ambos son ejemplos de subconsultas válidas que recuperan una instancia de cada nombre de producto cuyo modelo es un jersey de manga larga con logotipo y cuyos números de ProductModelID coinciden en las tablas Product y ProductModel.
USE AdventureWorks ; GO SELECT DISTINCT Name FROM Production.Product p WHERE EXISTS (SELECT * FROM Production.ProductModel pm WHERE p.ProductModelID = pm.ProductModelID AND pm.Name = 'Long-sleeve logo jersey') ; GO -- OR USE AdventureWorks ; GO SELECT DISTINCT Name FROM Production.Product WHERE ProductModelID IN (SELECT ProductModelID FROM Production.ProductModel WHERE Name = 'Long-sleeve logo jersey') ; GO
En el siguiente ejemplo se utiliza IN en una subconsulta correlativa o repetitiva. Se trata de una consulta que depende de la consulta externa de sus valores. Se ejecuta varias veces, una vez por cada fila que pueda seleccionar la consulta externa. Esta consulta recupera una instancia del nombre y apellido de cada empleado cuya bonificación en la tabla SalesPerson sea de 5000.00 y cuyos números de identificación coincidan en las tablas Employee y SalesPerson.
USE AdventureWorks ; GO SELECT DISTINCT c.LastName, c.FirstName FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson sp WHERE e.EmployeeID = sp.SalesPersonID) ; GO
La subconsulta anterior de esta instrucción no se puede evaluar independientemente de la consulta externa. Necesita el valor Employee.EmployeeID, aunque este valor cambia a medida que el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) examina diferentes filas de Employee.
Una subconsulta correlativa se puede usar también en la cláusula HAVING de una consulta externa. En este ejemplo se buscan los modelos cuyo precio máximo es superior al doble de la media del modelo.
USE AdventureWorks GO SELECT p1.ProductModelID FROM Production.Product p1 GROUP BY p1.ProductModelID HAVING MAX(p1.ListPrice) >= ALL (SELECT 2 * AVG(p2.ListPrice) FROM Production.Product p2 WHERE p1.ProductModelID = p2.ProductModelID) ; GO
En este ejemplo se utilizan dos subconsultas correlativas para buscar los nombres de los empleados que han vendido un producto específico.
USE AdventureWorks ; GO SELECT DISTINCT c.LastName, c.FirstName FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE EmployeeID IN (SELECT SalesPersonID FROM Sales.SalesOrderHeader WHERE SalesOrderID IN (SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ProductID IN (SELECT ProductID FROM Production.Product p WHERE ProductNumber = 'BK-M68B-42'))) ; GO
F. Utilizar GROUP BY
En este ejemplo se busca el total de cada pedido de venta de la base de datos.
USE AdventureWorks ; GO SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail sod GROUP BY SalesOrderID ORDER BY SalesOrderID ; GO
Debido a la cláusula GROUP BY, sólo se devuelve una fila que contiene la suma de todas las ventas por cada pedido de venta.
G. Utilizar GROUP BY con varios grupos
En este ejemplo se busca el precio medio y la suma de las ventas anuales hasta la fecha, agrupados por Id. de producto e Id. de oferta especial.
Use AdventureWorks SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price', SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY ProductID, SpecialOfferID ORDER BY ProductID GO
H. Utilizar GROUP BY y WHERE
En el siguiente ejemplo se colocan los resultados en grupos después de recuperar únicamente las filas con precios superiores a $1000.
USE AdventureWorks; GO SELECT ProductModelID, AVG(ListPrice) AS 'Average List Price' FROM Production.Product WHERE ListPrice > $1000 GROUP BY ProductModelID ORDER BY ProductModelID ; GO
I. Utilizar GROUP BY con una expresión
En este ejemplo se agrupa por una expresión. Puede agrupar por una expresión si ésta no incluye funciones de agregado.
USE AdventureWorks ; GO SELECT AVG(OrderQty) AS 'Average Quantity', NonDiscountSales = (OrderQty * UnitPrice) FROM Sales.SalesOrderDetail sod GROUP BY (OrderQty * UnitPrice) ORDER BY (OrderQty * UnitPrice) DESC ; GO
J. Comparar GROUP BY y GROUP BY ALL
En el primer ejemplo se crean grupos únicamente para los pedidos con cantidades > 10.
En el segundo ejemplo se crean grupos para todos los pedidos.
La columna que contiene el valor de agregado (el precio medio) es NULL para los grupos que carecen de filas que cumplan los criterios.
USE AdventureWorks ; GO SELECT ProductID, AVG(UnitPrice) AS 'Average Price' FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ProductID ORDER BY ProductID ; GO -- Using GROUP BY ALL USE AdventureWorks ; GO SELECT ProductID, AVG(UnitPrice) AS 'Average Price' FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ALL ProductID ORDER BY ProductID ; GO
K. Utilizar GROUP BY con ORDER BY
En este ejemplo se busca el precio medio de cada tipo de producto y se ordenan los resultados por precio medio.
USE AdventureWorks ; GO SELECT ProductID, AVG(UnitPrice) AS 'Average Price' FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ProductID ORDER BY AVG(UnitPrice) ; GO
L. Utilizar la cláusula HAVING
En el primer ejemplo se muestra una cláusula HAVING con una función de agregado. Agrupa las filas de la tabla SalesOrderDetail por Id. de producto y elimina aquellos productos cuyas cantidades de pedido medias son cinco o menos. En el segundo ejemplo se muestra una cláusula HAVING sin funciones de agregado.
USE AdventureWorks ; GO SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID ; GO
En esta consulta se utiliza la cláusula LIKE en la cláusula HAVING.
USE AdventureWorks ; GO SELECT SalesOrderID, CarrierTrackingNumber FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, CarrierTrackingNumber HAVING CarrierTrackingNumber LIKE '4BD%' ORDER BY SalesOrderID ; GO
M. Utilizar HAVING y GROUP BY
En el siguiente ejemplo se muestra el uso de las cláusulas GROUP BY, HAVING, WHERE y ORDER BY en una instrucción SELECT. Genera grupos y valores de resumen pero lo hace tras eliminar los productos cuyos precios superan los 25 $ y cuyas cantidades de pedido medias son inferiores a 5. También organiza los resultados por ProductID.
USE AdventureWorks ; GO SELECT ProductID FROM Sales.SalesOrderDetail WHERE UnitPrice < 25.00 GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID ; GO
N. Utilizar HAVING con SUM y AVG
En el siguiente ejemplo se agrupa la tabla SalesOrderDetail por Id. de producto y sólo se incluyen aquellos grupos de productos cuyos pedidos suman más de $1000000.00 y cuyas cantidades de pedido medias son inferiores a 3.
USE AdventureWorks ; GO SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3 ; GO
Para ver los productos cuyas ventas totales son superiores a $2000000.00, utilice esta consulta:
USE AdventureWorks ; GO SELECT ProductID, Total = SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $2000000.00 ; GO
Si desea asegurarse de que hay al menos mil quinientos elementos para los cálculos de cada producto, utilice HAVING COUNT(*) > 1500 para eliminar los productos que devuelven totales inferiores a 1500 elementos vendidos. La consulta sería la siguiente:
USE AdventureWorks ; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 1500 ; GO
O. Calcular totales de grupo mediante COMPUTE BY
En este ejemplo se utilizan dos fragmentos de código para mostrar la utilización de COMPUTE BY. En el primer ejemplo de código se utiliza un elemento COMPUTE BY con una función de agregado, mientras que en el segundo se utiliza un elemento COMPUTE BY y dos funciones de agregado.
Esta consulta calcula la suma de los pedidos de aquellos productos cuyos precios son inferiores a $5.00 por cada tipo de producto.
USE AdventureWorks ; GO SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID, LineTotal COMPUTE SUM(LineTotal) BY ProductID ; GO
Esta consulta recupera el tipo de producto y la cantidad de pedidos total para aquellos productos con precios inferiores a $5.00. La cláusula COMPUTE BY utiliza dos funciones de agregado diferentes.
USE AdventureWorks ; GO SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID, LineTotal COMPUTE SUM(LineTotal), MAX(LineTotal) BY ProductID ; GO
P. Calcular valores totales mediante COMPUTE sin BY
Se puede utilizar la palabra clave COMPUTE sin BY para generar totales generales, recuentos totales, etc.
En el siguiente ejemplo se obtiene el total general de los precios y anticipos de todos los tipos de productos cuyo precio es inferior a $2.00.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $2.00 COMPUTE SUM(OrderQty), SUM(LineTotal) ; GO
Puede utilizar COMPUTE BY y COMPUTE sin BY en la misma consulta. La siguiente consulta busca la suma de las cantidades de pedido y los totales de línea por tipo de producto y, a continuación, calcula el total de pedidos y líneas.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID COMPUTE SUM(OrderQty), SUM(LineTotal) BY ProductID COMPUTE SUM(OrderQty), SUM(LineTotal) ; GO
Q. Calcular sumas en todas las filas
En el siguiente ejemplo se muestran sólo tres columnas de la lista de selección y se proporcionan totales basados en todas las cantidades de pedido y todos los totales de línea al final de los resultados.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, LineTotal FROM Sales.SalesOrderDetail COMPUTE SUM(OrderQty), SUM(LineTotal) ; GO
R. Utilizar más de una cláusula COMPUTE
En el siguiente ejemplo se obtiene la suma de los precios de todos los pedidos cuyo precio es inferior a 5 $ organizada por Id. de producto y cantidad de pedido, así como la suma de los precios de todos los pedidos inferiores a 5 $ organizada únicamente por Id. de producto. Puede utilizar diferentes funciones de agregado en la misma instrucción si incluye más de una cláusula COMPUTE BY.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID, OrderQty, LineTotal COMPUTE SUM(LineTotal) BY ProductID, OrderQty COMPUTE SUM(LineTotal) BY ProductID ; GO
S. Comparar GROUP BY con COMPUTE
En el primer ejemplo se utiliza la cláusula COMPUTE para calcular la suma de todos los pedidos cuyo precio es inferior a $5.00 por tipo de producto. En el segundo ejemplo se genera la misma información de resumen sólo mediante GROUP BY.
USE AdventureWorks ; GO SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID COMPUTE SUM(LineTotal) BY ProductID ; GO
Ésta es la segunda consulta que utiliza GROUP BY.
USE AdventureWorks ; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID ORDER BY ProductID ; GO
T. Utilizar SELECT con las cláusulas GROUP BY, COMPUTE y ORDER BY
En el siguiente ejemplo sólo se devuelven aquellos pedidos cuyo precio es inferior a 5 $ y, a continuación, se calcula la suma total de línea por producto y el total general. Todas las columnas calculadas aparecen en la lista de selección.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty COMPUTE SUM(SUM(LineTotal)) BY ProductID, OrderQty COMPUTE SUM(SUM(LineTotal)) ; GO
U. Utilizar la instrucción SELECT con CUBE
En el siguiente ejemplo se muestran dos fragmentos de código. En el primer ejemplo se devuelve un conjunto de resultados de una instrucción SELECT mediante el operador CUBE. Con el operador CUBE, la instrucción devuelve una fila adicional.
USE AdventureWorks ; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty WITH CUBE ORDER BY ProductID ; GO
El valor NULL representa todos los valores de la columna ProductID. El conjunto de resultados devuelve valores para la cantidad vendida de cada producto y la cantidad total vendida de todos los productos. Al aplicar el operador CUBE o el operador ROLLUP, se devuelve el mismo resultado.
En este ejemplo se utiliza la tabla CubeExample para mostrar cómo afecta el operador CUBE al conjunto de resultados y se utiliza una función de agregado (SUM). La tabla CubeExample contiene un nombre de producto, un nombre de cliente y el número de pedidos que cada cliente ha realizado de un producto determinado.
USE AdventureWorks ; GO IF OBJECT_ID ('dbo.CubeExample', 'U') IS NOT NULL DROP TABLE dbo.CubeExample ; GO CREATE TABLE dbo.CubeExample( ProductName VARCHAR(30) NULL, CustomerName VARCHAR(30) NULL, Orders INT NULL ) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Filo Mix', 'Romero y tomillo', 10) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Outback Lager', 'Wilman Kala', 10) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Filo Mix', 'Romero y tomillo', 20) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Ikura', 'Wilman Kala', 10) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Ikura', 'Romero y tomillo', 10) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Outback Lager', 'Wilman Kala', 20) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Filo Mix', 'Wilman Kala', 30) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Filo Mix', 'Eastern Connection', 40) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Outback Lager', 'Eastern Connection', 10) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Ikura', 'Wilman Kala', 40) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Ikura', 'Romero y tomillo', 10) INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Filo Mix', 'Romero y tomillo', 50) ; GO
Primero, emita una consulta típica con una cláusula GROUP BY y el conjunto de resultados.
USE AdventureWorks ; GO SELECT ProductName, CustomerName, SUM(Orders) FROM CubeExample GROUP BY ProductName, CustomerName ORDER BY ProductName ; GO
GROUP BY ocasiona que el conjunto de resultados forme grupos dentro de los grupos.
Éste es el conjunto de resultados.
ProductName CustomerName ------------------------------ ------------------------------ ----------- Filo Mix Eastern Connection 40 Filo Mix Romero y tomillo 80 Filo Mix Wilman Kala 30 Ikura Romero y tomillo 20 Ikura Wilman Kala 50 Outback Lager Eastern Connection 10 Outback Lager Wilman Kala 30 (7 row(s) affected)
A continuación, emita una consulta con una cláusula GROUP BY mediante el operador CUBE. El conjunto de resultados debe incluir la misma información e información de superagregado para cada una de las columnas GROUP BY.
USE AdventureWorks ; GO SELECT ProductName, CustomerName, SUM(Orders) FROM CubeExample GROUP BY ProductName, CustomerName WITH CUBE ; GO
El conjunto de resultados del operador CUBE incluye los valores del conjunto de resultados GROUP BY simple anterior y agrega los superagregados de cada columna de la cláusula GROUP BY. NULL representa todos los valores del conjunto desde el que se calcula el agregado.
Éste es el conjunto de resultados.
ProductName CustomerName ------------------------------ ------------------------------ ----------- Filo Mix Eastern Connection 40 Filo Mix Romero y tomillo 80 Filo Mix Wilman Kala 30 Filo Mix NULL 150 Ikura Romero y tomillo 20 Ikura Wilman Kala 50 Ikura NULL 70 Outback Lager Eastern Connection 10 Outback Lager Wilman Kala 30 Outback Lager NULL 40 NULL NULL 260 NULL Eastern Connection 50 NULL Romero y tomillo 100 NULL Wilman Kala 110 (14 row(s) affected)
La línea 4 del conjunto de resultados indica que todos los clientes han realizado un total de 150 pedidos de Filo Mix.
La línea 11 del conjunto de resultados indica que el número total de pedidos de todos los productos que han realizado todos los clientes es de 260.
Las líneas 12 a 14 del conjunto de resultados indican que el número total de pedidos de cada cliente para todos los productos es de 100, 110 y 50, respectivamente.
V. Utilizar CUBE en un conjunto de resultados con tres columnas
En el siguiente ejemplo, la instrucción SELECT devuelve el Id. de modelo del producto, el nombre del producto y la cantidad de pedidos. La cláusula GROUP BY de este ejemplo incluye las columnas ProductModelID y Name .
Al utilizar el operador CUBE, el conjunto de resultados incluye información más detallada sobre las cantidades de pedido de los productos y los modelos. NULL representa todos los valores de la columna de título.
USE AdventureWorks ; GO SELECT ProductModelID, p.Name AS ProductName, SUM(OrderQty) FROM Production.Product p INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID GROUP BY ProductModelID, p.Name WITH CUBE ; GO
Al aumentar el número de columnas de la cláusula GROUP BY, se muestra por qué el operador CUBE es un operador de n dimensiones. Una cláusula GROUP BY con dos columnas devuelve tres clases más de agrupamientos cuando se utiliza el operador CUBE. El número de agrupamientos puede ser mayor de tres, en función de los distintos valores de las columnas.
El conjunto de resultados se agrupa por Id. de modelo del producto y, a continuación, por nombre de producto.
El valor NULL de la columna ProductModelID representa todos los ProductModels. El valor NULL de las columnas Name representa todos los Products. El operador CUBE devuelve los siguientes grupos de información de una instrucción SELECT:
• Cantidad de pedidos de cada modelo de un producto
• Cantidad de pedidos de cada producto
• Número total de pedidos
Cada columna a la que se hace referencia en la cláusula GROUP BY tiene una referencia cruzada con todas las demás columnas de la cláusula GROUP BY. Además, el agregado SUM se ha vuelto a aplicar. Esto da lugar a filas adicionales en el conjunto de resultados. La información que se devuelve en el conjunto de resultados crece en n dimensiones junto con el número de columnas de la cláusula GROUP BY.
W. Utilizar la función GROUPING con CUBE
En el siguiente ejemplo se muestra cómo la instrucción SELECT utiliza el agregado SUM, la cláusula GROUP BY y el operador CUBE. También utiliza la función GROUPING en las dos columnas que aparecen después de la cláusula GROUP BY.
USE AdventureWorks ; GO SELECT ProductModelID, GROUPING(ProductModelID), p.Name AS ProductName, GROUPING(p.Name), SUM(OrderQty) FROM Production.Product p INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID GROUP BY ProductModelID, p.Name WITH CUBE ; GO
El conjunto de resultados tiene dos columnas que contienen los valores 0 y 1. Son el resultado del uso de las expresiones GROUPING(ProductModelID) y GROUPING(p.Name).
X. Utilizar el operador ROLLUP
En el siguiente ejemplo se muestran dos fragmentos de código. En el primer ejemplo se recupera el nombre de producto, el nombre de cliente y la suma de pedidos realizados y se utiliza el operador ROLLUP.
USE AdventureWorks ; GO SELECT ProductName, CustomerName, SUM(Orders) AS 'Sum orders' FROM dbo.CubeExample GROUP BY ProductName, CustomerName WITH ROLLUP ; GO
Éste es el conjunto de resultados.
ProductName CustomerName Sum orders ------------------------------ ------------------------------ ----------- Filo Mix Eastern Connection 40 Filo Mix Romero y tomillo 80 Filo Mix Wilman Kala 30 Filo Mix NULL 150 Ikura Romero y tomillo 20 Ikura Wilman Kala 50 Ikura NULL 70 Outback Lager Eastern Connection 10 Outback Lager Wilman Kala 30 Outback Lager NULL 40 NULL NULL 260 (11 row(s) affected)
En el segundo ejemplo se realiza una operación ROLLUP en las columnas de la organización y el departamento y se muestra el número total de empleados.
El operador ROLLUP genera un resumen de agregados. Esto resulta útil cuando se necesita información de resumen pero un CUBE completo genera datos extraños o cuando se tienen conjuntos dentro de conjuntos. Por ejemplo, los departamentos de una organización son un conjunto dentro de un conjunto.
USE AdventureWorks ; GO IF OBJECT_ID ('dbo.Personnel', 'U') IS NOT NULL DROP TABLE dbo.Personnel ; GO CREATE TABLE dbo.Personnel ( CompanyName VARCHAR(20) NOT NULL, Department VARCHAR(15) NOT NULL, NumEmployees int NOT NULL ) INSERT dbo.Personnel VALUES ('Du monde entier', 'Finance', 10) INSERT dbo.Personnel VALUES ('Du monde entier', 'Engineering', 40) INSERT dbo.Personnel VALUES ('Du monde entier', 'Marketing', 40) INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Accounting', 20) INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Personnel', 30) INSERT dbo.Personnel VALUES ('Piccolo und mehr', 'Payroll', 40) ; GO
En la siguiente consulta, el nombre de la organización, el departamento y la suma de todos los empleados forman parte del conjunto de resultados, además de los cálculos de ROLLUP.
USE AdventureWorks ; GO SELECT CompanyName, Department, SUM(NumEmployees) FROM dbo.Personnel GROUP BY CompanyName, Department WITH ROLLUP ; GO
Éste es el conjunto de resultados.
CompanyName Department -------------------- --------------- ----------- Du monde entier Engineering 40 Du monde entier Finance 10 Du monde entier Marketing 40 Du monde entier NULL 90 Piccolo und mehr Accounting 20 Piccolo und mehr Payroll 40 Piccolo und mehr Personnel 30 Piccolo und mehr NULL 90 NULL NULL 180 (9 row(s) affected)
Y. Utilizar la función GROUPING
En el siguiente ejemplo se agregan tres nuevas filas a la tabla CubeExample. Cada uno de los tres registros NULL de una o más columnas utilizados para mostrar sólo la función ROLLUP produce un valor 1 en la columna de agrupamiento. Además, este ejemplo modifica la instrucción SELECT utilizada en el ejemplo anterior.
USE AdventureWorks ; GO -- Add first row with a NULL customer name and 0 orders. INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES ('Ikura', NULL, 0) -- Add second row with a NULL product and NULL customer with real value -- for orders. INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES (NULL, NULL, 50) -- Add third row with a NULL product, NULL order amount, but a real -- customer name. INSERT dbo.CubeExample (ProductName, CustomerName, Orders) VALUES (NULL, 'Wilman Kala', NULL) SELECT ProductName AS Prod, CustomerName AS Cust, SUM(Orders) AS 'Sum Orders', GROUPING(ProductName) AS 'Group ProductName', GROUPING(CustomerName) AS 'Group CustomerName' FROM CubeExample GROUP BY ProductName, CustomerName WITH ROLLUP ; GO
La función GROUPING sólo se puede utilizar con CUBE o ROLLUP. La función GROUPING devuelve 1 cuando una expresión se evalúa como NULL, debido a que el valor de la columna es NULL y representa el conjunto de todos los valores. La función GROUPING devuelve 0 cuando la columna correspondiente, ya sea NULL o no, no proviene de las opciones CUBE o ROLLUP como un valor de sintaxis. El valor devuelto es del tipo de datos tinyint.
Éste es el conjunto de resultados.
Prod Cust Sum Orders Group ProductName Group CustomerName ------------------------------ ------------------------------ ----------- ----------------- ------------------ NULL NULL 50 0 0 NULL Wilman Kala NULL 0 0 NULL NULL 50 0 1 Filo Mix Eastern Connection 40 0 0 Filo Mix Romero y tomillo 80 0 0 Filo Mix Wilman Kala 30 0 0 Filo Mix NULL 150 0 1 Ikura NULL 0 0 0 Ikura Romero y tomillo 20 0 0 Ikura Wilman Kala 50 0 0 Ikura NULL 70 0 1 Outback Lager Eastern Connection 10 0 0 Outback Lager Wilman Kala 30 0 0 Outback Lager NULL 40 0 1 NULL NULL 310 1 1 Warning: Null value is eliminated by an aggregate or other SET operation. (15 row(s) affected)
Z. Utilizar SELECT con GROUP BY, una función de agregado y ROLLUP
En el siguiente ejemplo se utiliza una consulta SELECT que contiene una función de agregado y una cláusula GROUP BY.
USE AdventureWorks ; GO SELECT pm.Name AS ProductModel, p.Name AS ProductName, SUM(OrderQty) FROM Production.ProductModel pm INNER JOIN Production.Product p ON pm.ProductModelID = p.ProductModelID INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID GROUP BY pm.Name, p.Name WITH ROLLUP ; GO
En el conjunto de resultados, NULL representa todos los valores de esa columna.
Si utiliza la instrucción SELECT sin el operador ROLLUP, la instrucción crea un solo agrupamiento. La consulta devuelve un valor de suma para cada combinación única de ProductModel, ProductModelID y ProductName:
ProductModel ProductModelID title SUM(qty)
Se puede utilizar la función GROUPING con el operador ROLLUP o con el operador CUBE. Puede aplicar esta función a una de las columnas de la lista de selección. La función devuelve 1 o 0, dependiendo de si la columna está agrupada mediante el operador ROLLUP.
a. Utilizar la sugerencia de optimizador INDEX
En el siguiente ejemplo se muestran dos formas de utilizar la sugerencia de optimizador INDEX. En el primer ejemplo se muestra cómo obligar al optimizador a que utilice un índice no agrupado para recuperar filas de una tabla, mientras que en el segundo ejemplo se obliga a realizar un recorrido de tabla mediante un índice igual a 0.
-- Use the specifically named INDEX. USE AdventureWorks ; GO SELECT c.FirstName, c.LastName, e.Title FROM HumanResources.Employee e WITH (INDEX(IX_Employee_ManagerID)) JOIN Person.Contact c on e.ContactID = c.ContactID WHERE ManagerID = 3 ; GO -- Force a table scan by using INDEX = 0. USE AdventureWorks ; GO SELECT c.LastName, c.FirstName, e.Title FROM HumanResources.Employee e WITH (INDEX = 0) JOIN Person.Contact c ON e.ContactID = c.ContactID WHERE LastName = 'Johnson' ; GO
b. Utilizar OPTION y las sugerencias GROUP
En el siguiente ejemplo se muestra cómo se utiliza la cláusula OPTION (GROUP) con una cláusula GROUP BY.
USE AdventureWorks ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (HASH GROUP, FAST 10) ; GO
c. Utilizar la sugerencia de consulta UNION
En el siguiente ejemplo se utiliza la sugerencia de consulta MERGE UNION.
USE AdventureWorks ; GO SELECT * FROM HumanResources.Employee e1 UNION SELECT * FROM HumanResources.Employee e2 OPTION (MERGE UNION) ; GO
d. Utilizar UNION simple
En el siguiente ejemplo, el conjunto de resultados incluye el contenido de las columnas ProductModelID y Name de las tablas ProductModel y Gloves.
USE AdventureWorks ; GO IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves ; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4) ; GO -- Here is the simple union. USE AdventureWorks ; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name ; GO
e. Utilizar SELECT INTO con UNION
En el siguiente ejemplo, la cláusula INTO de la segunda instrucción SELECT especifica que la tabla denominada ProductResults contiene el conjunto final de resultados de la unión de las columnas designadas de las tablas ProductModel y Gloves. Tenga en cuenta que la tabla Gloves se crea en la primera instrucción SELECT.
USE AdventureWorks ; GO IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL DROP TABLE dbo.ProductResults ; GO IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves ; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4) ; GO USE AdventureWorks ; GO SELECT ProductModelID, Name INTO ProductResults FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ; GO SELECT * FROM dbo.ProductResults ;
f. Utilizar UNION de dos instrucciones SELECT con ORDER BY
El orden de algunos parámetros utilizados con la cláusula UNION es importante. En el siguiente ejemplo se muestra el uso correcto e incorrecto de UNION en dos instrucciones SELECT en las que se va a cambiar el nombre de una columna en el resultado.
USE AdventureWorks ; GO IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves ; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4) ; GO /* INCORRECT */ USE AdventureWorks ; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) ORDER BY Name UNION SELECT ProductModelID, Name FROM dbo.Gloves ; GO /* CORRECT */ USE AdventureWorks ; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name ; GO
g. Utilizar UNION de tres instrucciones SELECT para mostrar los efectos de ALL y los paréntesis
En los siguientes ejemplos se utiliza UNION para combinar los resultados de tres tablas que tienen las mismas 5 filas de datos. En el primer ejemplo se utiliza UNION ALL para mostrar los registros duplicados y se devuelven las 15 filas. En el segundo ejemplo se utiliza UNION sin ALL para eliminar las filas duplicadas de los resultados combinados de las tres instrucciones SELECT y se devuelven 5 filas.
En el tercer ejemplo se utiliza ALL con el primer UNION y los paréntesis incluyen al segundo UNION que no utiliza ALL. El segundo UNION se procesa en primer lugar porque se encuentra entre paréntesis. Devuelve 5 filas porque no se utiliza la opción ALL y se quitan los duplicados. Estas 5 filas se combinan con los resultados del primer SELECT mediante las palabras clave UNION ALL. Esto no quita los duplicados entre los dos conjuntos de 5 filas. El resultado final es de 10 filas.
USE AdventureWorks ; GO IF OBJECT_ID ('EmployeeOne', 'U') IS NOT NULL DROP TABLE EmployeeOne ; GO IF OBJECT_ID ('EmployeeTwo', 'U') IS NOT NULL DROP TABLE EmployeeTwo ; GO IF OBJECT_ID ('EmployeeThree', 'U') IS NOT NULL DROP TABLE EmployeeThree ; GO SELECT c.LastName, c.FirstName, e.Title INTO EmployeeOne FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE ManagerID = 66 ; GO SELECT c.LastName, c.FirstName, e.Title INTO EmployeeTwo FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE ManagerID = 66 ; GO SELECT c.LastName, c.FirstName, e.Title INTO EmployeeThree FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE ManagerID = 66 ; GO -- Union ALL SELECT LastName, FirstName FROM EmployeeOne UNION ALL SELECT LastName, FirstName FROM EmployeeTwo UNION ALL SELECT LastName, FirstName FROM EmployeeThree ; GO SELECT LastName, FirstName FROM EmployeeOne UNION SELECT LastName, FirstName FROM EmployeeTwo UNION SELECT LastName, FirstName FROM EmployeeThree ; GO SELECT LastName, FirstName FROM EmployeeOne UNION ALL ( SELECT LastName, FirstName FROM EmployeeTwo UNION SELECT LastName, FirstName FROM EmployeeThree ) ; GO
Pablo Pelle
.|. para tu ejemplo
Publicar un comentario