# How to learn Analytic or Windows functions in MySQL for data science – Part 2?

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:**

**Aggregate functions i.e SUM(), MIN(), MAX(), COUNT() and AVG().****Nonaggregate Ranking functions i.e RANK(), DNSE_RANK(), ROW_NUMBER(), NTILE() AND PERCENT_RANK().****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 `2`

, inclusive, in any of the following forms:^{63}

- an unsigned integer constant literal
- a positional parameter marker (
`?`

) - a user-defined variable
- a local variable in a stored routine

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.**