Problem description

Requirements:

Query the top 3 records of order_AMOUNT per month.

For example, for 2019-02, the query result should be these 3 items:

The solution

MySQL 5.7 and MySQL 8.0 have different processing methods.

1. MySQL 5.7

Let’s start with a query.

Descending order by year, month, and order_amount in order_DATE.

Then, add a new column: order_AMOUNT (the ranking of this entry for the month).

Execution Result:

As you can see, order_rank is ordered by year, month and order amount, and there is also a column order_rank that shows the order amount ranking of this record in this month.

SQL > alter table SQL > alter table SQL > alter table SQL

@current_month and @order_rank are our custom variables.

Use := to create a variable dynamically without using the set command.

What this means:

Get the month value in order_date and assign it to current_month so that you can keep track of each month.

What this means:

Compare current_month to the month in this record, and if they are the same, order_rank increments by 1; otherwise, set to 1.

Note that @current_month comes after @order_rank, as when executing this record:

If the value of MONTH(order_date) is 2 and current_month is 1, as set in the previous record.

Next, treat the above SQL statement as a subquery and use a WHERE condition to easily get the top 3 for each group.

Final statement:

Execution Result:

2. MySQL 8

MySQL 8 has introduced a rank() function to make ranking easier.

Execution Result:

The effect is consistent with the method in 5.7.

Let’s look at the rank() method in the statement:

  • PARTITION BY indicates the specified PARTITION based on the year and month of the order.

  • ORDER BY specifies the ORDER BY which the ORDER is sorted in descending ORDER BY year, month, and amount.

This will automatically calculate the ranking value.

Note that this is not the same as 5.7:

That is, the rank value is the same when several values are the same.

Final SQL statement:

Translation from:

Towardsdatascience.com/mysql-how-t…

If you are interested in trying this out, send a message to the public account “Performance and Architecture” : 200106, which replies to the download address of the practice notes, including the table builder statements, test data, and the two queries of MySQL5.7 and 8.0.

Recommended reading:

  • High concurrency cases – Inventory overissue
  • Remote live architecture
  • How does MySQL Order by work?
  • How do you determine if an element exists in a billionth data set?
  • Zookeeper vs Etcd
  • MySQL8 Hash Join algorithm