MySQL:如何查詢出每個分組中的topn條記錄?

NO IMAGE

問題描述

MySQL:如何查詢出每個分組中的topn條記錄?

需求:

查詢出每月 order_amount(訂單金額) 排行前3的記錄。

例如對於2019-02,查詢結果中就應該是這3條:

MySQL:如何查詢出每個分組中的topn條記錄?

解決方法

MySQL 5.7 和 MySQL 8.0 有不同的處理方法。

1. MySQL 5.7

我們先寫一個查詢語句。

根據 order_date 中的年、月,和order_amount進行降序排列。

然後,添加一個新列:order_amount(本條記錄在本月中的名次)。

MySQL:如何查詢出每個分組中的topn條記錄?

執行結果:

MySQL:如何查詢出每個分組中的topn條記錄?

可以看到,根據年、月、訂單金額排序了,還多了一列order_rank,顯示出了本條記錄在本月的訂單金額排名情況。

上面SQL中比較個性的是這部分:

MySQL:如何查詢出每個分組中的topn條記錄?

@current_month@order_rank 是我們自定義的變量。

使用 := 可以動態創建一個變量,而不需要使用 set 命令。

MySQL:如何查詢出每個分組中的topn條記錄?

這句的含義:

取得order_date中的月份值,賦值給current_month,這樣就可以跟蹤每個月份。

MySQL:如何查詢出每個分組中的topn條記錄?

這句的含義:

比較 current_month 和本條記錄中的月份,如果一樣,order_rank 自增1,否則,置為1

注意@current_month 是在 @order_rank 的後面,例如執行到這條記錄時:

MySQL:如何查詢出每個分組中的topn條記錄?

if 判斷中,MONTH(order_date) 值為 2,而 current_month 值為 1,還是上條記錄設置的。

接下來,把上面的SQL語句作為一個子查詢,然後使用一個 where 條件就可以輕鬆拿到每組的 top 3。

最終語句:

MySQL:如何查詢出每個分組中的topn條記錄?

執行結果:

MySQL:如何查詢出每個分組中的topn條記錄?

2. MySQL 8

MySQL 8 引入了一個 rank() 函數,可以更簡便的實現排行的功能。

MySQL:如何查詢出每個分組中的topn條記錄?

執行結果:

MySQL:如何查詢出每個分組中的topn條記錄?

效果和 5.7 中的方法是一致的。

我們看下語句中的 rank() 方法:

MySQL:如何查詢出每個分組中的topn條記錄?

  • PARTITION BY 是指定分區依據,這裡是根據訂單的年、月進行分區。

  • ORDER BY 指定了分區內的排序依據,這裡是根據訂單的 年、月、金額 進行降序排列。

這樣就會自動計算出排行數值。

需要注意的是,這個地方和 5.7 的方法不一樣:

MySQL:如何查詢出每個分組中的topn條記錄?

就是參與排序的幾個值一樣的時候,rank 值是一樣的。

最終的SQL語句:

MySQL:如何查詢出每個分組中的topn條記錄?

翻譯整理自:

towardsdatascience.com/mysql-how-t…

如果您有興趣實踐一下,在公眾號“性能與架構”中發送消息:200106,會回覆實踐筆記的下載地址,包含建表語句、測試數據、MySQL5.7和8.0的這2個查詢語句。

推薦閱讀:

MySQL:如何查詢出每個分組中的topn條記錄?

相關文章

[前端]實戰年終盤點

一杯茶的時間,上手Docker

【canvas】箭頭跟隨鼠標移動的動畫原理

ReentrantLock源碼分析從入門到入土