Linux Hint Posted March 15 Share Posted March 15 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: LAG(value_expression, offset [, default]) 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. CREATE TABLE products ( 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: select 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: select 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: select 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. View the full article Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.