Data Science is in too much Hype these days. Every organization is now a day looking for data scientists. The Data science role includes the collection, retrieval, analyzing, and representation of data in pictorial format, including statistical information to the users.

The need for data scientists has been rise due to an increase in the use of the internet. Every layman use data these days. Which causes an increase in data in zillions and trillions.

So, here are some most useful and important Nonaggregate Windows or Analytic functions MySql functions.

Windows function Introduction

Window functions come in three flavors:

1. Aggregate functions i.e SUM(), MIN(), MAX(), COUNT() and AVG().
2. Nonaggregate Ranking functions i.e RANK(), DNSE_RANK(), ROW_NUMBER(), NTILE() AND PERCENT_RANK().
3. Nonaggregate Value Window functions i.e LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE(), CUME_DIST().

MySQL LEAD() function

It’s a Nonaggregate value Window function. The LEAD(), function is a window function that allows you to look forward a number of rows and access data of the specified number of row from the current row.

The LEAD() function is very useful for calculating the difference between the current row and the subsequent row within the same result set.

This function helps in accessing data of a subsequent row from the current row in the same result set.

It returns NULL in the last column value where the LEAD function is applied. The following shows the syntax of the LEAD() function:

LEAD(expression, jumprows, default) [null_treatment] over_clause

Returns the value of expression from the row that leads (follows) the current row by jumprows rows within its partition. If there is no such row, the return value is default. For example, if jumprows is 3, the return value is default for the last two rows. If jumprows or default are missing, the defaults are 1 and NULL, respectively.

jumprows must be a positive integer. If it is 0, expression will be evaluated for the current row.

Beginning with MySQL 8.0.22, jumprows cannot be NULL. In addition, it must now be an integer in the range 1 to 263, inclusive, in any of the following forms:

In MySQL 8.0.22 and later, the use of a negative value for the rows argument of this function is not permitted.

The LEAD() function is used to get value from the row that succeeds the current row.

Note: The LEAD() function is always used with OVER(). If it is missed then the over clause will raise an error.

Example 1 : In the below example we are using the LEAD() function to get the value of the next row data column (order_amount) value. That’s why we are using LEAD(order_amount,1).

mysql> select row_number() over (order by client_city) as ‘row_number()’, client_city, order_amount, LEAD(order_amount,1) over () as ‘Lead’ from tbl_toysorderdetails;

Example 2 : Passing 0(zero) as jumprows parameter value yields the same row value in the output.

mysql> select row_number() over (order by client_city) as ‘row_number()’, client_city, order_amount, LEAD(order_amount,0) over () as ‘Lead as 0’ from tbl_toysorderdetails;

Example 3 : Using the LEAD() to get the difference between current resultset order amount and next row order amount.

In the below example we are using LEAD() function to get the value of the next row data column (order_amount) value and using the same to get the difference between current order amount and next row order amount.

mysql> select row_number() over (order by client_city) as ‘row_number()’, client_city, order_amount, LEAD(order_amount,1) over () as ‘Lead’, order_amount – LEAD(order_amount,1) over() as ‘order_amount-lead’ from tbl_toysorderdetails;

Endnotes

Hope this article helps you bring out more from your dataset. And if you have any favorite MySql function that you find useful or use quite often, do comment below and share your experience!

Do see some important function here for data analysis in Mysql.

Some Useful aggregate and mathematical functions in MYSQL for data science

How to learn Strings and datetime analytic functions in MySql for data science?

See all MySQL String functions MySQL 8 String Functions.

See all PHP string functions PHP String Functions.