SQL Lag
The SQL lag() function allows you to access the previous row from the current row at a specific offset. In short, the lag() function allows you to access the previous row from the current one. By specifying the offset value, you can access the previous 1, 2, 3, etc., rows from the current one.
It is the opposite of the lead() function which allows you to access subsequent rows.
SQL Lag()
The function syntax is as follows:
OVER (
[PARTITION BY partition_expression] ORDER BY sort_expression [ASC | DESC] );
The following are the supported arguments:
- value_expression – It specifies the return value of the preceding row. The expression must evaluate to a single value.
- offset – It specifies how many rows backward from the current row to access.
- default – It sets the default value if the offset is outside the scope of the partition. By default, the value is set to NULL.
- Partition by – It specifies how to partition the data.
- Order by – It sets the order format for the rows in each partition.
Sample Data Setup
Before we dive into the workings of the lag() function, let us start by setting up a basic table for demonstration purposes.
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
category VARCHAR(255),
price DECIMAL(10, 2),
quantity INT,
expiration_date DATE,
barcode BIGINT
);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Chef Hat 25cm',
'bakery',
24.67,
57,
'2023-09-09',
2854509564204);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Quail Eggs - Canned',
'pantry',
17.99,
67,
'2023-09-29',
1708039594250);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Coffee - Egg Nog Capuccino',
'bakery',
92.53,
10,
'2023-09-22',
8704051853058);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Pear - Prickly',
'bakery',
65.29,
48,
'2023-08-23',
5174927442238);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Pasta - Angel Hair',
'pantry',
48.38,
59,
'2023-08-05',
8008123704782);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Wine - Prosecco Valdobiaddene',
'produce',
44.18,
3,
'2023-03-13',
6470981735653);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Pastry - French Mini Assorted',
'pantry',
36.73,
52,
'2023-05-29',
5963886298051);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Orange - Canned, Mandarin',
'produce',
65.0,
1,
'2023-04-20',
6131761721332);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Pork - Shoulder',
'produce',
55.55,
73,
'2023-05-01',
9343592107125);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Dc Hikiage Hira Huba',
'produce',
56.29,
53,
'2023-04-14',
3354910667072);
With the data creation and setup complete, let us proceed to discuss a few examples.
Example 1: Basic Usage
In this case, we have access to a “products” table that contains the product information. Suppose we want to get the previous barcode from the current row.
We can use the lag() function as follows:
product_name,
price,
lag(barcode) over (partition by category
order by
price asc) as previous_item
from
products p;
The given code partitions the data based on the category. It then fetches the previous barcode in the partition using the lag() function.
The resulting output is as follows:
Example 2: Setting the Default Value
If there is no previous row in a specific column (out of bound), the function sets the value to NULL as shown in the previous example.
To set a default value for any out-of-scope access, we can do the following:
product_name,
price,
lag(barcode, 1, 'N/A') over (partition by category
order by
price asc) as previous_item
from
products p;
We set the default value to “N/A”. This should replace any out-of-bound value as shown in the output:
Example 3: Custom Offset Value
Suppose you want to access the previous two rows from the current row. We can do that by setting the offset value to 2.
An example query is illustrated in the following:
product_name,
price,
lag(barcode, 2, 'N/A') over (partition by category
order by
price asc) as previous_item
from
products p;
Output:
This query returns the previous two rows in each partition.
Conclusion
In this tutorial, we learned how to work with the lag() function to get the previous item from the current row.
Source: linuxhint.com