Get best of the week

Funciones de ventana con una "ventana" o c贸mo usar un marco

Hola a todos. El 26 de febrero, las clases comenzaron en OTUS en un nuevo grupo en el curso "MS SQL Server Developer" . En este sentido, quiero compartir con ustedes mi publicaci贸n sobre las funciones de la ventana. Por cierto, en la pr贸xima semana todav铆a puedes unirte al grupo ;-).




Las funciones de ventana est谩n bien establecidas en nuestra pr谩ctica, pero pocas personas saben c贸mo funcionan los marcos RANGE y ROWS.

Quiz谩s es por eso que son algo menos comunes. El prop贸sito de este art铆culo es proporcionar ejemplos de uso para que definitivamente no tenga preguntas "驴Qui茅n es qui茅n?" y "驴C贸mo aplicarlo?". La pregunta "驴Por qu茅?" El art铆culo permanecer谩 apagado.

Veamos qu茅 es un marco y c贸mo lograr un efecto similar usando ORDER By en la cl谩usula OVER ().

Para la demostraci贸n, utilizaremos una tabla simple para que pueda calcular ejemplos sin usar un compilador. En general, lo recomiendo encarecidamente: mire y piense cu谩l ser谩 el resultado de la ejecuci贸n, y luego verif铆quese usted mismo, para que encuentre puntos blancos en la percepci贸n de las funciones de la ventana, lo que puede no ser obvio cuando lee los resultados finales.

Mesa

Create table sales 
(sales_id INT PRIMARY KEY, sales_dt DATETIME2 DEFAULT GETUTCDATE(),  customer_id INT, item_id INT, cnt INT, price_per_item DECIMAL(19,4));

INSERT INTO sales
(sales_id, sales_dt, customer_id, item_id, cnt, price_per_item)
VALUES
(1, '2020-01-10T10:00:00', 100, 200, 2, 30.15),
(2, '2020-01-11T11:00:00', 100, 311, 1, 5.00),
(3, '2020-01-12T14:00:00', 100, 400, 1, 50.00),
(4, '2020-01-12T20:00:00', 100, 311, 5, 5.00),
(5, '2020-01-13T10:00:00', 150, 311, 1, 5.00),
(6, '2020-01-13T11:00:00', 100, 315, 1, 17.00),
(7, '2020-01-14T10:00:00', 150, 200, 2, 30.15),
(8, '2020-01-14T15:00:00', 100, 380, 1, 8.00),
(9, '2020-01-14T18:00:00', 170, 380, 3, 8.00),
(10, '2020-01-15T09:30:00', 100, 311, 1, 5.00),
(11, '2020-01-15T12:45:00', 150, 311, 5, 5.00),
(12, '2020-01-15T21:30:00', 170, 200, 1, 30.15);

Comencemos con un momento simple: las diferencias en la funci贸n SUMA con y sin ordenar

SELECT sales_id, customer_id, count, 
SUM(count) OVER () as total,
SUM(count) OVER (ORDER BY customer_id) AS cum,
SUM(count) OVER (ORDER BY customer_id, sales_id) AS cum_uniq
FROM sales
ORDER BY customer_id, sales_id;



Veamos el primer rastrillo discreto, 驴c贸mo crees que cu谩ntos desarrolladores piensan que cum y cum_uniq son iguales al leer el c贸digo? Pensar un poco? Quiz谩s, pero porque aqu铆 es obvio, y es tan obvio al leer el c贸digo en la aplicaci贸n, e incluso con la no tan obvia falta de uniformidad del campo de clasificaci贸n.

Ahora abre nuestra maravillosa ventana.

La ventana, o m谩s bien el marco es de 2 tipos de FILAS y RANGO, primero debe conocer las FILAS.
Opciones de restricci贸n de trama:

  1. Todo antes de la fila / rango actual y el valor real de la fila actual
    ENTRE PRECEDENTES SIN L脥MITES
    ENTRE PRECEDENTES SIN PROCESAR Y FILA ACTUAL
  2. Fila / rango actual y todo lo dem谩s,
    ENTRE FILA ACTUAL Y SIGUIENTE SIN L脥MITES
  3. Especificar cu谩ntas l铆neas antes y despu茅s de incluir (no admitidas para RANGO)
    ENTRE N Precediendo Y N Siguiendo
    ENTRE FILA ACTUAL Y N Siguiendo
    ENTRE N FILA ANTERIOR Y ACTUAL

Pero veamos el marco en acci贸n.

Abrimos la "ventana" en la l铆nea actual y todas las anteriores, para la funci贸n SUMA, como puede ver, esto coincide con la clasificaci贸n ASC

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id) AS cum_uniq,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_and_all_before2
FROM sales
ORDER BY customer_id, sales_id;



Al mismo tiempo, quiero recordarle que el orden de clasificaci贸n en la ventana (en la cl谩usula OVER ()) no est谩 relacionado con el orden de clasificaci贸n en la consulta en s铆, en el ejemplo es el mismo para simplificar el c谩lculo si decide verificar el c谩lculo y su comprensi贸n de c贸mo funciona la funci贸n

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id) AS cum_uniq,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_and_all_before2
FROM sales
ORDER BY cnt;



Ahora veamos la funcionalidad del marco cuando incluimos todas las l铆neas posteriores.

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_frame,
SUM(cnt) OVER (ORDER BY customer_id DESC, sales_id DESC) AS current_and_all_order_desc
FROM sales
ORDER BY customer_id, sales_id;



Como puede ver aqu铆, tambi茅n puede obtener el mismo resultado para una funci贸n agregada, si usa la clasificaci贸n inversa, pero ROWS es un poco m谩s estable, porque si sus campos de clasificaci贸n no son 煤nicos, puede obtener una sorpresa en la forma de los mismos valores.

Y finalmente, una opci贸n que ya no puede ser imitada por tipos, cuando especificamos un n煤mero espec铆fico de l铆neas que deben incluirse en el marco

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS before_and_current,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS current_and_1_next,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS before2_and_2_next
FROM sales
ORDER BY customer_id, sales_id;



En esta opci贸n, ya indica espec铆ficamente qu茅 l铆neas est谩n en el rango, y en mi opini贸n, es m谩s obvio por los resultados.

La diferencia entre ROWS y RANGE


La diferencia es que ROWS opera en una fila y RANGE en un rango. Es cierto, esto es obvio por el nombre, pero explica poco en la pr谩ctica?

Veamos la imagen (fuente en la parte inferior del art铆culo)





Ahora, si miramos cuidadosamente, ser谩 obvio que las filas con el mismo valor del par谩metro de clasificaci贸n se denominan rango.

Como ya se mencion贸 anteriormente, ROWS se limita a una cadena, mientras que RANGE captura todo el rango de valores coincidentes que especifique en la funci贸n de ventana ORDER BY.

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id RANGE UNBOUNDED PRECEDING) AS current_and_all_before2
FROM sales
ORDER BY 2, sales_id;



FILAS: siempre opera en una l铆nea espec铆fica, incluso si la clasificaci贸n no es 煤nica, pero RANGO solo combina per铆odos en rangos con los valores coincidentes de los campos de clasificaci贸n. En este sentido, la funcionalidad es muy similar al comportamiento de la funci贸n SUM () con la clasificaci贸n por un campo no 煤nico. Veamos otro ejemplo.

SELECT sales_id, customer_id, price_per_item, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, price_per_item) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item RANGE UNBOUNDED PRECEDING) AS current_and_all_before2
FROM sales
ORDER BY 2, price_per_item;




Ya hay 2 campos y el rango est谩 determinado por un rango con valores coincidentes para ambos campos,

y la opci贸n es cuando incluimos en el c谩lculo todas las l铆neas posteriores de la actual, que en el caso de la funci贸n SUMA coincide con el valor que se puede obtener mediante la ordenaci贸n inversa:

SELECT sales_id, customer_id, price_per_item, cnt, 
SUM(cnt) OVER (ORDER BY customer_id DESC, price_per_item DESC) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_before2
FROM sales
ORDER BY 2, price_per_item, sales_id desc;



Como puede ver, RANGE nuevamente captura todo el rango de pares coincidentes.

A pesar de que la funcionalidad de ROWS y RANGE est谩 lejos de ser nueva cada vez, surgen preguntas sobre c贸mo usarla. Espero que este art铆culo haya agregado una comprensi贸n de c贸mo ROWS y RANGE son diferentes, y ahora no dudar谩 en qu茅 caso se necesita este o aquel marco.

Ilustraci贸n Fuente RANGO sobre la diferencia y las FILAS
las funciones de la ventana Con el SQL Server 2016, Mark Tabladillo

estar谩 a tiempo para el curso

More articles:


All Articles