In this** **MariaDB tutorial, we will learn about the “**MariaDB window functions**” using some examples. Moreover, we will cover the following topics.

- MariaDB window functions
- MariaDB window function rank
- MariaDB window function count
- MariaDB columnstore window functions

Table of Contents

## MariaDB window functions

The term **WINDOW **is referred to the set of rows in a MariaDB. The window function performs a calculation on the window. The **OVER **clause defines the window by partitioning and grouping the rows with the window functions.

MariaDB has three types of window functions:

**Rank Functions:**

**RANK():**It is the window function that returns the number for each and every row. The function assigns the same number to the same values in a row and skips the next number.**DENSE():**It is the window function that returns the number for each and every row. The function assigns the same number to the same values in a row but doesn’t skip the next number.**ROW NUMBER():**It is the window function that generates the unique number for all kinds of rows in the table.

These are the most used ranking function in MariaDB but there are others like **NTILE()**, etc.

**Value Functions:**

**LEAD():**It is the window function that fetches the value from the next row in the same result set without using the self-join.**LAG():**It is the window function that fetches the value from the previous row in the same result set without using the self-join.**FIRST_VALUE():**It is the window function that fetches the first value from the ordered set.**LAST_VALUE():**It is the window function that fetches the last value from the ordered set.

**Aggregate functions:**

**MAX():**It is the window function that returns the maximum value of a column in a table.**MIN():**It is the window function that returns the minimum value of a column in a table.**AVG():**It is the window function that returns the average value of a column in a table.**SUM():**It is the window function that returns the average value of a column in a table.**COUNT():**It is the window function that counts the number of rows in a table.

There are many more aggerate functions in a MariaDB like **BIT_AND**, **STD, **and **STDDEV**.

The syntax of the window function is given below.

```
Window_function (column_name) OVER (
[ PARTITION BY column_name,.. ]
[ ORDER BY column_name,... ] )
```

Here,

**Window_function:**It can be any three window functions that we have learned above.**column_name:**It can be any column of the table on which function performs the calculation.**OVER:**It is used to specify the window.**PARTITION BY:**It defines the set of rows (window) on which the window function performs the calculation.**ORDER BY:**This clause sorts the rows in ascending and descending order within each partition.

Let’s take an example by creating the new table Student_Score using the below code.

`CREATE TABLE Student_Score(std_id INT,std_name VARCHAR(30),std_subject VARCHAR(30),std_score INT,std_address VARCHAR(30));`

In the above example, we are using the following values.

**CREATE TABLE:**It is the MariaDB command to create a new table. So here we are creating the table as Student_Score.**std_name VARCHAR(30):**It is the column of type character of Studen_Score table for storing the name of students.**std_subject VARCHAR(30):**It is the column of type character of Studen_Score table for storing the subject of students.**std_score INT:**It is the column of type integer of Studen_Score table for storing the score of students.**std_address:**It is the column of type character of Studen_Score table for storing the address of students.

Insert the following records by typing the below codes.

```
INSERT INTO Student_Score(std_id,std_name,std_subject,std_score,std_address) VALUES(1,'Jason','Math',30,'United Kindom'),
(2,'Alison','Science',43,'United States'),(3,'James','Math',48,'Canada'),(4,'Celia','Science',46,'United States'),
(5,'Robert','Math',38,'United Kindom'),(6,'Linda','Science',29,'United States'),(7,'Linda','Science',29,'United States');
```

We have inserted the **7 **records using the above code.

View the table by typing the below code.

`SELECT * FROM Student_Score;`

Run the query to find the average partitioned by std_subject using the below code.

```
SELECT std_name,std_subject,std_score, AVG(std_score) OVER (PARTITION BY std_subject)
AS average_by_address FROM Student_Score;
```

The output shows that the average score is partitioned by the **std_subject**.

Read: MariaDB Comment

## MariaDB window function rank

From the above section, we know that **RANK()** is a window function that ranks each record in a MariaDB table.

- The
**RANK**function assigns the same rank to a row containing the same value. If the row contains the value 3, then the rank function will assign the rank as 1. Again, if the next record or row contains the same value as 3, then again rank will assign the rank as 1. - It skips the next rank which is 2 for the next unique record and assigns the rank as 3.

Let’s have an example on the same table **Student_Person **which we have used above.

```
SELECT std_name,std_subject,std_score, RANK() OVER (ORDER BY std_score)
AS score_rank FROM Student_Score;
```

Look at the above output,

The student **Linda **and **Jhon **have the same value or score for the column **std_score **which is **29**. So **RANK **function assigns the rank as 1 to both of them.

After assigning the rank to both the records, look at the next record value in a column **std_score **of a student **Jason **which is the **30**. The **RANK **function assigns the rank as **3 **because rank skips the rank value to the next value after assigning the same rank to more than one record.

Read: MariaDB Row_Number tutorial

## MariaDB window function count

The **COUNT **is the aggregate window function in MariaDB that counts the number of rows in a table.

Use the below code to count the number of students.

```
SELECT std_name,std_subject,std_score, std_address, COUNT(std_name) OVER (PARTITION BY std_address)
AS count_score FROM Student_Score;
```

In the above code,

- Loading the table with columns
**std_name, std_subject, std_address**and**std_socre**using the**SELECT**statement. - Counting the number of student using the window function as
**COUNT(std_name)**which is partitioned by the student address using the**OVER (PARTITION BY std_address)**. - At last, counted value is store in a column named count_score using the
**AS count_score**statement.

From the output, we can see in the column **count_score **which contains the record number which is partitioned by the student address.

The **count_score **columns the values as 2 and 3, which means there are 2 students from **Canada **and **United Kindom**. But 3 students are found who belong to the **United States**.

Read: MariaDB DateTime Tutorial

## MariaDB columnstore windows functions

Window function support is also provided by the MariaDB column store. As we know the window function performs the calculation on window (set of rows) that related the current row in a result set.

- These window functions can be the Aggregate functions or the aggerate function is used for window function. Its behaviour is different from the query that contains the group by clause, due to the rows remaining ungrouped.
- The Partition and Frame are the two concepts that are for the window functions. We have already know about the Partition, let’s know about the FRAME, this FRAME represents the range of the window functions. It is also the subset of the rows Partition for each row.
- The windows functions is used after the having clause,joins and group by.

We can use the above window function syntax to perform calculations on a set of rows. the difference is that columns store is used for big data processing or for the massive amount of the data in a parallel way.

You may also like to read the following MariaDB tutorials.

- MariaDB Date + Examples
- MariaDB Foreign Key
- What is MariaDB Sharding
- MariaDB DATEDIFF Function
- MariaDB Create Sequence
- MariaDB AUTO_INCREMENT
- MariaDB Case Statement
- MariaDB on Duplicate Key Update

So, in this tutorial, we have learned about the “**MariaDB window functions**” and we also covered the following topics.

- MariaDB window functions
- MariaDB window function rank
- MariaDB window function count
- MariaDB columnstore window functions

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.