post icon

Como utilizar la cláusula OVER [Partition By / Order by] en SQL [Optimización]

Ver query’s con la cláusula OVER no es muy común, normalmente la mayoría de los programadores o DBA crean sub-query’s para obtener los mismos resultados, lo que en sí no es malo ya que logran visualizar los mismos datos. El problema principal se encuentra en el rendimiento que tiene uno y otro método.

El motor que sea que estés utilizando optimiza el tiempo de ejecución y los bloqueos de las filas cuando se escribe scripts con OVER ya que el mismo trabaja sobre los datos ya obtenidos, en vez de volver a recuperarlos de origen.

La cláusula OVER está definida en el estándar ISO de SQL, pero no todos los motores lo implementan por desgracia. Personalmente solamente lo he probado sobre MSSQL y Oracle, sabiendo que Firebird recién tiene planeado incorporarlo completamente en la versión 3. En cuanto a Postgre y MySQL no me he fijado aún en su documentación.

Cabe mencionar que OVER lo que hace es determinar la partición y el orden del conjunto datos sobre el que se trabaja.

El ejemplo que veremos ahora será sobre MSSQL con la Base de Datos AdventureWorks 2005 SP2a y veremos 2 casos típicos de uso bastantes sencillos. Para el primer caso veremos como utilizar OVER junto a ORDER BY el cual especifica el orden en que se debe aplicar la función de categoría. En este ejemplo veremos como obtener el número de fila del conjunto sobre el que trabajamos

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD asc) AS 'Numero Fila Total'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;

Verán que el no es una impresión secuencial si cambia el order by de la consulta general

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD asc) AS 'Numero Fila Total'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
ORDER BY c.FirstName;

También podemos utilizar con PARTITION BY,  que divide el conjunto de resultados en particiones. La función se aplica a cada partición por separado y el cálculo se reinicia para cada partición. Para comprender mejor lo que hace les expongo ambas maneras de obtener el mismo resultado, con subselect y con OVER.

USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
	,(select SUM(SC.OrderQty) from Sales.SalesOrderDetail SC WHERE SC.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID) as 'Total'
	,(select AVG(SC.OrderQty) from Sales.SalesOrderDetail SC WHERE SC.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID) as 'Avg'
	,(select COUNT(SC.OrderQty) from Sales.SalesOrderDetail SC WHERE SC.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID) as 'Count'
	,(select MIN(SC.OrderQty) from Sales.SalesOrderDetail SC WHERE SC.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID) as 'Min'
	,(select MAX(SC.OrderQty) from Sales.SalesOrderDetail SC WHERE SC.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID) as 'Max'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO

Siendo el query más optimos el que sigue

USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
	,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO

El resultado se ve así:

En el último query verán una combinación, donde el primer OVER cuenta las filas totales, el segundo cuenta la fila de la partición por códigos postales y el tercero suma las ventas por código postal.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY FirstName asc) AS 'Numero Fila Total'
    ,ROW_NUMBER() OVER(partition by a.PostalCode ORDER BY SalesYTD asc) AS 'Numero Fila SubTotal'
    ,Sum(SalesYTD) over(partition by a.PostalCode) as 'Ventas por Codigo Postal'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;

Comentarios desde Facebook:

  1. avatar
    ANDRES UNITED STATES Google Chrome Windows
    5 septiembre 2012 at 09:55 #

    con que software trabajas el sql con fondo negro ?

    Saludos

    • avatar
      GeekZero Google Chrome Windows
      5 septiembre 2012 at 09:58 #

      Es un plugin para wordpress llamado SyntaxHighlighter

  2. avatar
    Cristian :P CHILE Google Chrome Windows
    11 noviembre 2011 at 12:59 #

    Excelente!!!! no conocía el “partition by”, con esto pude solucionar un problema del reinicio del contador con row_number.
    Gracias!

Trackbacks/Pingbacks

  1. Como utilizar la cláusula OVER [Partition By / Order by] en SQL [Optimización] « DbRunas – Noticias y Recursos sobre Bases de Datos UNITED STATES WordPress - 9 mayo 2011

    […] http://www.devtroce.com/2011/05/09/como-utilizar-la-clausula-over-partition-by-order-by-en-sql-optim… May 9, 2011   //   SQL   //   No Comments   //   […]

Responder