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 -

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 -

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 above is the detailed content of MySQL PARTITION BY child clause. For more information, please follow other related articles on the PHP Chinese website!