{"id":2218,"date":"2020-10-24T00:04:52","date_gmt":"2020-10-24T00:04:52","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2218"},"modified":"2020-10-24T00:04:54","modified_gmt":"2020-10-24T00:04:54","slug":"over-partition-by-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2218","title":{"rendered":"Over Partition By en SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over__D.png\" alt=\"\" class=\"wp-image-2219\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Usando la cl\u00e1usula OVER en SQL Server<\/h2>\n\n\n\n<p>La cl\u00e1usula Over en una consulta determina la partici\u00f3n y el orden de un conjunto de filas antes de que se aplique la funci\u00f3n de Windows asociada, es decir, la cl\u00e1usula OVER define un conjunto de filas especificado por el usuario dentro de un conjunto de resultados de la consulta. Luego, una funci\u00f3n de Windows calcula un valor para cada fila de la consulta.<br>Puede usar la cl\u00e1usula OVER con funciones para calcular valores agregados, como promedios m\u00f3viles, agregados acumulados, totales acumulados o un N superior por resultados de grupo.<\/p>\n\n\n\n<!--more-->\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>Sintaxis<\/strong><br>OVER (<br>[  &lt;Partition by Expresi\u00f3n> ]<br>[ &lt;Order by Columnas>]<br>[ Row or Range Cl\u00e1usula]<br>)<\/p>\n\n\n\n<p><strong>Donde<\/strong><br>[ Partition by Expresi\u00f3n ]<br>Partition by<br>Divide el conjunto de resultados de la consulta en particiones. La funci\u00f3n de Window se aplica a cada partici\u00f3n por separado y el c\u00e1lculo se reinicia para cada partici\u00f3n. <br>Expresi\u00f3n<br>Especifica la columna por la que se particiona el conjunto de filas. la Expresi\u00f3n solo puede hacer referencia a columnas disponibles mediante la cl\u00e1usula FROM. La Expresi\u00f3n no puede hacer referencia a expresiones o alias en la lista de selecci\u00f3n. La Expresi\u00f3n puede ser una expresi\u00f3n de columna, una subconsulta escalar, una funci\u00f3n escalar o una variable definida por el usuario.<br>[ &lt;Order by Columnas> ]<br>Especifica una columna o expresi\u00f3n por la que ordenar. Columnas solo puede hacer referencia a las columnas disponibles mediante la cl\u00e1usula FROM. No se puede especificar un n\u00famero entero para representar un nombre de columna o alias. (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=211\" target=\"_blank\">Ver Ordenamientos<\/a>)<br>[ Row or Range Cl\u00e1usula]<br>Limita a\u00fan m\u00e1s las filas dentro de la partici\u00f3n especificando puntos de inicio y finalizaci\u00f3n dentro de la partici\u00f3n. Esto se hace especificando un rango de filas con respecto a la fila actual, ya sea por asociaci\u00f3n l\u00f3gica o asociaci\u00f3n f\u00edsica. La asociaci\u00f3n f\u00edsica se logra utilizando la cl\u00e1usula ROWS.<br>La cl\u00e1usula ROWS limita las filas dentro de una partici\u00f3n al especificar un n\u00famero fijo de filas que preceden o siguen a la fila actual. Alternativamente, la cl\u00e1usula RANGE limita l\u00f3gicamente las filas dentro de una partici\u00f3n especificando un rango de valores con respecto al valor en la fila actual.<br>Las filas anteriores y siguientes se definen seg\u00fan el orden de la cl\u00e1usula ORDER BY.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Funciones Windows en SQL Server<\/strong><\/p>\n\n\n\n<p>Las funciones Windows se pueden clasificar en los siguientes grupos:<br><strong>Funciones de Windows agregadas<\/strong>: <a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=207\" target=\"_blank\">Ver Funciones de agregado<\/a><br><strong>Funciones de valores de Windows<\/strong>: <a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=2128\" target=\"_blank\">Ver Funciones FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()<\/a><br><strong>Funciones de clasificaci\u00f3n de Windows<\/strong>: ROW_NUMBER(), NTILE(), RANK(), DENSE_RANK(),<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicios<\/strong><\/p>\n\n\n\n<p>Usando la base de datos Northwind<\/p>\n\n\n\n<p><strong>use Northwind<br>go<\/strong><\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p>La siguiente consulta utiliza la funci\u00f3n SUM para calcular el total de unidades compradas por cada cliente y el total general. La cl\u00e1usula OVER define las particiones del c\u00e1lculo. El primer c\u00e1lculo se divide en cada cliente, lo que significa que la cantidad total por cliente se restablece<br>a cero para cada nuevo cliente. El segundo c\u00e1lculo utiliza una cl\u00e1usula OVER sin especificar particiones, lo que significa que el c\u00e1lculo se realiza en todos los conjuntos de filas de entrada.<\/p>\n\n\n\n<p>SELECT C.CustomerId As &#8216;C\u00f3d. Cliente&#8217;,<br>C.CompanyName As &#8216;Cliente&#8217;, D.Quantity As &#8216;Cantidad&#8217;,<br>SUM(D.Quantity) OVER(PARTITION BY C.CustomerID) As &#8216;Total Cliente&#8217;,<br>SUM(D.Quantity) OVER() As &#8216;Total General&#8217;<br>from Orders AS O<br>join Customers AS c ON O.CustomerID= C.CustomerID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>Where c.CustomerID is not null<br>Order by c.CustomerID, D.Quantity Desc<br>go<br>El resultado se muestra en la figura.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_01.png\" alt=\"\" class=\"wp-image-2220\" width=\"725\" height=\"627\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_01.png 754w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_01-300x259.png 300w\" sizes=\"auto, (max-width: 725px) 100vw, 725px\" \/><\/figure>\n\n\n\n<p>Explicaci\u00f3n:<br>El primer cliente, Alfreds Futterkiste tiene doce productos comprados (no necesariamente productos diferentes),  las cantidades fueron: 40, 21, 20, 20, 16, 15, 15, 15, 6, 2, 2 y 2, las que en total suman 174 unidades.<\/p>\n\n\n\n<p>Las \u00f3rdenes y las cantidades compradas por el cliente Alfreds Futterkiste se muestran el la siguiente consulta, note que las cantidades son las mismas de la consulta usando Over.<br>select<br>O.OrderId As &#8216;N\u00ba Orden&#8217;,<br>D.ProductID As &#8216;C\u00f3d. Producto&#8217;,<br>D.Quantity As &#8216;Cantidad&#8217;<br>from Orders As O<br>join [Order Details] As D on O.OrderID = D.OrderID<br>where CustomerID = &#8216;ALFKI&#8217;<br>Order by Cantidad desc<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_02.png\" alt=\"\" class=\"wp-image-2221\" width=\"562\" height=\"588\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_02.png 345w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_02-287x300.png 287w\" sizes=\"auto, (max-width: 562px) 100vw, 562px\" \/><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p>La consulta muestra los empleados y la cantidad de \u00f3rdenes atendidas<br>por cada empleado, tambi\u00e9n el total general de \u00f3rdenes atendidas.<\/p>\n\n\n\n<p>SELECT distinct<br>E.EmployeeID As &#8216;C\u00f3d. Empleado&#8217;,<br>E.LastName + Space(1) + E.FirstName As &#8216;Empleado&#8217;,<br>Count(O.OrderID) OVER(PARTITION BY E.EmployeeID ) AS &#8216;Cantidad \u00d3rdenes&#8217;,<br>Count(O.OrderID) OVER() As &#8216;Total \u00d3rdenes&#8217;<br>FROM Employees As E<br>join Orders AS O ON E.EmployeeID = O.EmployeeID<br>WHERE O.ShippedDate is not null<br>ORDER BY E.EmployeeID<br>go<br>El resultado se muestra en la figura<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_03.png\" alt=\"\" class=\"wp-image-2222\" width=\"747\" height=\"363\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_03.png 590w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_03-300x146.png 300w\" sizes=\"auto, (max-width: 747px) 100vw, 747px\" \/><\/figure>\n\n\n\n<p>La misma consulta se puede obtener usando agrupamientos y para el total de las \u00f3rdenes una subconsulta.<br>SELECT E.EmployeeID As &#8216;C\u00f3d. Empleado&#8217;,<br>E.LastName + Space(1) + E.FirstName As &#8216;Empleado&#8217;,<br>Count(O.OrderID) As &#8216;Cantidad \u00d3rdenes&#8217;,<br>(Select Count(O.OrderID) from Orders As O) As &#8216;Total \u00d3rdenes&#8217;<br>FROM Employees As E<br>join Orders AS O ON E.EmployeeID = O.EmployeeID<br>WHERE O.ShippedDate is not null<br>Group by E.EmployeeID, E.LastName + Space(1) + E.FirstName<br>ORDER BY E.EmployeeID<br>go<\/p>\n\n\n\n<p class=\"has-black-color has-pale-cyan-blue-background-color has-text-color has-background\"><strong>Importante<\/strong>:<br>Compare los tiempos de los planes de ejecuci\u00f3n estimados y seleccione el que tenga el menor tiempo.<br>Para mayor informaci\u00f3n ver:<br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=225\" target=\"_blank\">Agrupamientos en SQL Server<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=219\" target=\"_blank\">Subconsultas<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=1810\" target=\"_blank\">Comparando Planes de ejecuci\u00f3n estimados<\/a><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p>La consulta muestra los productos y la cantidad de unidades vendidas por cada uno as\u00ed como la cantidad total por categor\u00eda.<\/p>\n\n\n\n<p>Select distinct<br>C.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217;,<br>C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>P.ProductID As &#8216;C\u00f3d. Producto&#8217;,<br>P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>sum(D.Quantity) over(partition by P.ProductID)As &#8216;Total Unidades&#8217;,<br>sum(D.Quantity) over() As &#8216;Total General&#8217;<br>from Categories As C<br>join Products As P on C.CategoryID = P.CategoryID<br>join [Order Details] As D on P.ProductID = D.ProductID<br>Order by C.CategoryID, [Total Unidades] desc<br>go<br>El resultado se muestra en la figura<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_04.png\" alt=\"\" class=\"wp-image-2224\" width=\"744\" height=\"622\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_04.png 928w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_04-300x251.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_04-768x643.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_04-795x666.png 795w\" sizes=\"auto, (max-width: 744px) 100vw, 744px\" \/><\/figure>\n\n\n\n<p>Note que se muestra por cada categor\u00eda el orden de los productos m\u00e1s vendidos.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p>Usando ordenamiento en la cl\u00e1usula Over, adem\u00e1s de agrupar una cantidad espec\u00edfica de filas. La consulta muestra los productos totalizando las cantidades de las tres \u00faltimas \u00f3rdenes, las dos precedentes sumadas a la  cantidad de la venta actual. Esto es posible utilizando el operador between, especificando la cantidad de \u00f3rdenes precedentes (2 para el ejercicio) y la cantidad de la orden actual (current row).<\/p>\n\n\n\n<p>Select<br>P.ProductID As &#8216;C\u00f3d. Producto&#8217;,<br>P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>D.Quantity As &#8216;Unidades&#8217;,<br>Month(O.OrderDate) As &#8216;Mes&#8217;,<br>sum(D.Quantity) over(partition by P.ProductID<br>order by Month(O.OrderDate)<br>rows between 2 preceding and current row )As &#8216;\u00daltimas 3&#8217;,<br>sum(D.Quantity) over(partition by P.ProductID) As &#8216;Total Producto&#8217;<br>from Categories As C<br>join Products As P on C.CategoryID = P.CategoryID<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on D.OrderID = O.OrderID<br>Order by P.ProductID, Mes<br>go<br>El resultado se muestra en la figura<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_05.png\" alt=\"\" class=\"wp-image-2225\" width=\"751\" height=\"687\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_05.png 825w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_05-300x275.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_05-768x703.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_Over_05-795x728.png 795w\" sizes=\"auto, (max-width: 751px) 100vw, 751px\" \/><\/figure>\n\n\n\n<p>Explicaci\u00f3n:<br>El producto con c\u00f3digo 1, Chai, se vendi\u00f3 cuatro veces en el mes de enero (n\u00famero 1), el primer registro es de 10 unidades, en la columna \u00ab\u00daltimas 3\u00bb aparece el mismo valor, para el segundo registro, cuya venta es de 24, en la columna \u00ab\u00daltimas 3\u00bb aparece 34 que es el resultado de sumar las 24 de esa venta mas las 10 anteriores, el tercer registro que aparece se muestra una venta de 4 unidades, en la columna \u00ab\u00daltimas 3\u00bb aparece 38, que es el resultado de sumar 10 + 24 de las dos precedentes y la actual de 4. El cuarto registro suma las dos precedentes de 24 + 4 y la venta de 80, el resultado es 108.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Usando la cl\u00e1usula OVER en SQL Server La cl\u00e1usula Over en una consulta determina la partici\u00f3n y el orden de un conjunto de filas antes de que se aplique la funci\u00f3n de Windows asociada, es decir, la cl\u00e1usula OVER define un conjunto de filas especificado por el usuario dentro de un conjunto de resultados de &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2218\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2219,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,9],"tags":[256,255,254],"class_list":["post-2218","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-registros-vistas","tag-funciones-windows","tag-over-partition","tag-sql-partition-by","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2218","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2218"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2218\/revisions"}],"predecessor-version":[{"id":2226,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2218\/revisions\/2226"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2219"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}