Navicat 部落格

按分組列出 Top N 查詢 2020 年 5 月 14 日,由 Robert Gravelle 撰寫

Top N 查詢是按數值遞減排序的前幾筆記錄的查詢。通常,這些是使用 TOP 或 LIMIT 子句完成的。問題是,Top N 結果集被限制為資料表中最前的值,而沒有任何分組。GROUP BY 子句可以幫助解決這個問題,但僅限於每個分組的單項最前結果。如果你想要每個類別的前 5 筆記錄,GROUP BY 本身將無濟於事。這並不意味著它無法完成。實際上,在今天的文章中,我們正正要學習如何按分組建構 Top N 查詢。

Top N 查詢的基礎知識

為了更好地理解「Top N 查詢」,我們會從 Sakila 範例資料庫中選取最長的前 5 部電影。如果你不熟悉 Sakila 資料庫,它是一個 MySQL 資料庫,其中包含許多與虛構的影片出租商店有關的許多資料表、檢視和查詢。資料表有 actor、film、customer、rentals 等。

top_n (77K)

按類別將結果分組

GROUP BY 子句將彙總函式套用於一個或多個欄位,以便將資料按你指定的欄位分組。就分組結果而言,這是向前邁出的一步,但是 GROUP BY 仍然有兩個限制:

  1. 1. 它僅提供每組的第一個結果(即列),而略過其他結果,
  2. 2. 僅限於分組條件和彙總欄位中包含的欄。所有其他欄均不可存取。

此查詢使用 GROUP BY 來顯示每個評級中最長的電影:

group_by (47K)

請注意,我們不能包含電影標題,因為它既不是 GROUP BY 也不是彙總欄位的一部分。

視窗函式速成課程

視窗函式(Window Functions)中的術語「視窗」是指函式在其上運行的列集,因為函式使用視窗中列的數值來計算傳回的數值。視窗內的列集被彙總為一個數值。

若要在查詢中使用視窗函式,必須使用 OVER() 子句定義視窗。它會做這兩件事情:

  1. 1. 透過 PARTITION BY 子句定義視窗分割區以形成列組。
  2. 2. 透過 ORDER BY 子句排序分割區中的列。

一個查詢可以包含多個具有相同或不同視窗定義的視窗函式。

我們的查詢使用 ROW_NUMBER() 視窗函式。它為查詢的內部視窗結果集中的每一列資料分配一個連續的整數。我們可以使用該數值將每個評級的結果限制在前 5 個。這是將電影長度按遞減排序來完成的。

row_number (116K)

總結

在今天的文章中,我們學習了如何在 Navicat Premium 構建一個查詢,以取得每個類別的前 5 列資料。 Navicat Premium 版本 15 增加了 100 多項增強功能,其中還包括幾個新功能,可為你提供更多建置、管理和維護資料庫的方式!

Navicat 文章
頻道記錄
分享
部落格封存檔