MariaDB Window functions

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

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