Cláusula marco en SQL

Requisitos previos: funciones de ventana en SQL

La cláusula FRAME se usa con funciones de ventana/analíticas en SQL. Cada vez que usamos una función de ventana, crea una ‘ventana’ o una ‘partición’ dependiendo de la columna mencionada después de la cláusula ‘partición por’ en la cláusula ‘sobre’. Y luego aplica esa función de ventana a cada una de esas particiones y dentro de estas particiones, podemos crear un subconjunto de registros usando la cláusula FRAME.

Por lo tanto, la cláusula FRAME especifica un subconjunto.

Paso 1: Considere la siguiente tabla de PAPELERÍA .

SQL> Create Table STATIONERY(Category VARCHAR(20),
     Brand VARCHAR(20),Product_Name VARCHAR(20),
     Price int,Primary Key(Product_Name));

Paso 2: Inserte valores en la tabla Papelería.

INSERT INTO STATIONERY VALUES('Pen','Alpha','Alpen',280);
INSERT INTO STATIONERY VALUES('Pen','Fabre','Fapen',250);
INSERT INTO STATIONERY VALUES('Pen','Camel','Capen',220);
INSERT INTO STATIONERY VALUES('Board','Alpha','Alord',550);
INSERT INTO STATIONERY VALUES('Board','Fabre','Faord',400);
INSERT INTO STATIONERY VALUES('Board','Camel','Carod',250);
INSERT INTO STATIONERY VALUES('Notebook','Alpha','Albook',250);
INSERT INTO STATIONERY VALUES('Notebook','Fabre','Fabook',230);
INSERT INTO STATIONERY VALUES('Notebook','Camel','Cabook',210);

Producción:

Paso 3: Ahora entendamos FRAME usando las funciones de ventana ‘first_value’ y ‘last_value’. Las funciones de ventana ‘first_value’ y ‘last_value’ nos dan el primer valor y el último valor en una ventana.

Supongamos que tenemos que mostrar dos columnas adicionales que mostrarán el producto más caro (‘Exp_Product’) y el menos caro (‘Che_Product’) de una categoría particular con cada fila de la tabla usando las funciones de ventana ‘first_value’ y ‘last_value’.

Ahora, hay otras formas fáciles de resolver esto, pero como tenemos que usar las funciones de ventana ‘first_value’ y ‘last_value’, solo lo que podemos hacer es ordenar el nombre del producto según el precio y mostrar el primero y el último valor de el nombre del producto en las columnas ‘Exp_Product’ y ‘Che_Product’.

Consulta:

SQL> Select * ,first_value(Product_Name) over
(partition by Category order by Price desc) as Exp_Product,
last_value(Product_Name) over(partition by Category 
order by Price desc) as Che_Product, from STATIONERY;

Producción:

Podemos ver el problema aquí, nuestra consulta funciona bien para ‘Exp_Product’ pero no para ‘Che_Product’. No muestra el producto menos costoso en una categoría determinada y la razón es el MARCO predeterminado.

Hay un marco predeterminado que utiliza SQL con cada función de ventana. El MARCO predeterminado es un ‘rango entre la fila actual y la anterior sin límites’.

significa que especifica el rango que se supone que debe considerar nuestra función de ventana al aplicar esa función de ventana en particular. Y por defecto considera todas las filas que preceden a la fila actual y también la fila actual en una partición particular (mencionada en la cláusula OVER).

Es por eso que en ‘ Che_Product ‘ (Producto menos costoso) dado que nuestra columna ‘Precio’ estaba ordenada en forma descendente y la función de ventana utilizada era ‘ último_valor ‘, en cada punto de una partición formaba un subconjunto que constaba de cada fila antes de la fila actual y la fila actual y la razón por la que muestra el valor de la fila actual es que la fila actual era el ‘último valor’ en cada punto.

El siguiente código SQL realiza la misma consulta que el anterior:

Consulta: 

SQL> Select *,first_value(Product_Name) over
(partition by Category order by Price desc range 
between unbounded preceding and current row) as Exp_Product,
last_value(Product_Name) over(partition by 
Category order by Price desc range between 
unbounded preceding and current row) as Che_Product,from STATIONERY;

Nota: Escribimos el MARCO al final de la cláusula OVER .

Ahora queremos obtener los valores correctos para la columna ‘b’. Podemos hacerlo ajustando la cláusula del marco predeterminado de tal manera que considere toda la partición como la ventana para cada partición y luego aplicarle la función de ventana.

Podemos aplicar el MARCO como el ‘rango entre el precedente sin límites y el siguiente sin límites’. Por lo tanto, al usar esto, consideramos la partición completa, ya que lo siguiente ilimitado no estará limitado por la fila actual, sino que se extenderá a toda la ventana.

Consulta: 

SQL> Select *,first_value(Product_Name) over
(partition by Category order by Price desc range 
between unbounded preceding and unbounded following) as Exp_Product,
last_value(Product_Name) over(partition by 
Category order by Price desc range between 
unbounded preceding and unbounded following) as Che_Product,from STATIONERY;

Producción:

Nota:

1.  No es necesario utilizar la cláusula FRAME en todas las funciones de VENTANA, depende del uso de casos específicos.
2. En general, debemos tener cuidado al usar funciones de VENTANA como ‘nth_value’, ‘ntile’, ‘first_value’ y ‘last_value’.
3. La declaración de la cláusula FRAME mencionada en el ejemplo anterior no es la única forma restringida de usarla, podemos cambiarla para que se ajuste a nuestras necesidades manteniendo las reglas básicas de SQL.