MySQL PARTITION BY child clause

Partition By clause can be used to improve query performance. It reduces storage requirements and improves data manageability. By partitioning a large table, queries that access only a small portion of the data can be executed faster. Partitioning also reduces backup and recovery times. In this article, we will discuss Partition By clause in MySQL with syntax and various examples.

Introduction

The purpose of the PARTITION BY clause is to group the rows of the table into separate partitions. This is particularly useful when performing calculations on specific rows within a partition using other rows in the same partition.

PARTITION BY clause must always be used within an OVER() clause. Partitions created by the PARTITION BY clause are also called windows. This clause operates specifically on window functions such as RANK(), LEAD(), and LAG().

If you exclude the PARTITION BY clause from the OVER() clause, the entire table will be treated as a single partition.

grammar

Window_function ( expression )
   Over ( partition by expr [order_clause] [frame_clause] )

order_clause and frame_clause are optional components of the grammar.

In MySQL, the expression in the Partition clause can be a column name or a built-in function. However, in standard SQL, only column names are allowed in expressions.

Example

Let’s take the “Hacker” table as an example -

h_id	h_name	challenge_id	Fraction
3	Raju	111	20
2	mislash	111	80
5	Rudra	112	40
5	Mohan	114	90
4	Rohan	112	30
1	Sohan	112	40

We need to determine the ranking of each hacker in each challenge. In other words, we have to list all the hackers who participated in the challenge and their respective rankings in that challenge.

To achieve this we use the following query:

select challenge_id, h_id, h_name, score,
dense_rank() over ( partition by challenge_id order by score desc )
as "rank", from hacker;

In this query, the partition by clause groups the table by challenge_id.

The order by clause sorts the hackers in each partition in descending order of score.

The over() clause specifies how to partition and sort table rows for the window function rank().

The window function dense_rank() assigns a rank to each hacker in the ordered partition of the challenge. If two hackers have the same score, they are assigned the same ranking.

The result output shows a list of all hackers and their respective rankings for each challenge -

challenge_id	h_id	h_name	Fraction	Ranking
111	2	mislash	80	1
111	3	Raju	20	2
112		Rudra	40	1
112	1	Sohan	40	1
112	4	Rohan	30	2
114	5	Mohan	90	1

Thus, we managed to get a list of all hackers and their ranking in each individual challenge.

Usage of PARTITION BY clause

• Group the rows of a table into separate partitions so that calculations can be performed on specific rows within the partitions.

• Reduce storage requirements and improve data manageability.

• Improving query performance by executing queries that access only a small portion of the data faster.

• Reduce backup and recovery time.

in conclusion

The PARTITION BY clause in MySQL is a useful tool for grouping rows of a table into separate partitions, thereby improving query performance and reducing storage requirements. This clause operates specifically on window functions such as RANK(), LEAD(), and LAG(). The syntax is simple and allows flexibility in the types of expressions used in clauses. The above example demonstrates the functionality of the PARTITION BY clause when calculating the total sales for each customer. By taking advantage of this powerful feature, users can optimize database performance and improve data manageability.

The above is the detailed content of MySQL PARTITION BY child clause. For more information, please follow other related articles on the PHP Chinese website!