Frame Clause in SQL

Prerequisites: Window functions in SQL

FRAME clause is used with window/analytic functions in SQL. Whenever we use a window function, it creates a ‘window’ or a ‘partition’ depending upon the column mentioned after the ‘partition by’ clause in the ‘over’ clause. And then it applies that window function to each of those partitions and inside these partitions, we can create a subset of records using the FRAME clause.

Therefore, the FRAME clause specifies a subset.

Step 1: Consider the following STATIONERY table.

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

Step 2: Insert values in the Stationery table.

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);

Output:

Step 3: Now let’s understand FRAME using ‘first_value’ and ‘last_value’ window functions. A ‘first_value’ and ‘last_value’ window functions giving us the first value and the last value in a window.

Suppose we have to display two extra columns which will display the most expensive  (‘Exp_Product’) and the least expensive (‘Che_Product’) product of a particular category with every row of the table using ‘first_value’ and ‘last_value’ window functions.

Now, there are other easy ways to solve this but since we have to use the ‘first_value’ and ‘last_value’ window functions only what we can do is sort the product name on the basis of price and display the first and the last value of the product name in the ‘Exp_Product’ and ‘Che_Product’ column.

Query:

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;

Output:

We can see the problem here, our query is doing good for ‘Exp_Product’ but not for ‘Che_Product’. It does not show the least expensive product in a given category and the reason is the default FRAME.

There is a default frame that SQL uses with every window function. The default FRAME is a  ‘range between unbounded preceding and current row’.

it means that it specifies the range our window function is supposed to consider while applying that particular window function. And by default it considers all the rows preceding the current row and also the current row itself in a particular partition (mentioned in the OVER clause).

That’s why in ‘Che_Product‘ (Least Expensive Product) since our ‘Price’ column was ordered in descending manner and the window function used was ‘last_value‘, at every point in a partition it formed a subset consisting of every row before the current row and the current row and the reason why it displayed the value of the current row itself is that the current row was the ‘last value’ at every point.

The below SQL code performs the same query as the above:

Query: 

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;

Note: We write the FRAME at the end of the OVER clause.

Now we want to get the correct values for the ‘b’ column. We can do so by tweaking the Default Frame clause in such a way that it considers the whole partition as the window for every partition and then apply the Window Function to it.

We can apply the FRAME as the ‘range between unbounded preceding and unbounded following’. Therefore using this we consider the whole partition as the unbounded following will not be bounded by the current row but will extend to the whole window.

Query: 

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;

Output:

Note:

1.  It is not required to use the FRAME clause in all WINDOW functions, it depends on specific case use.
2. Generally we should take care while using WINDOW functions like ‘nth_value’ , ‘ntile’ , ‘first_value’ and ‘last_value’.
3. The FRAME clause statement mentioned in the above example is not the only restricted way to use it, we can change it to fit our needs while maintaining the basic SQL rules.