MySQL中的ROW_NUMBER視窗函式簡單瞭解下

落叶微风發表於2024-11-24
MySql函式

ROW_NUMBER() 是 MySQL8引入的視窗函式之一，它為查詢結果集中的每一行分配一個唯一的順序號（行號）。這個順序號是基於視窗函式的 ORDER BY 子句進行排序的，可以根據指定的排序順序生成連續的整數值。

ROW_NUMBER() 在分頁、去重、分組內排序等場景中非常有用。

本文涉及到的指令碼測試請在個人測試庫進行。

使用場景

  • 分頁查詢：使用 ROW_NUMBER() 可以生成每行的序號，結合 WHERE 或 LIMIT 子句實現高效的分頁查詢。尤其是在沒有 OFFSET 支援的情況下，ROW_NUMBER() 允許你在分頁時進行靈活的排序。
  • 去除重複資料：可以利用 ROW_NUMBER() 來給每一行打上唯一標識，之後選擇每組的第一行，從而有效地去除重複資料。
  • 分組內排序：可以按組對資料進行排序，併為每個組中的行分配一個行號。這個場景通常用於比如給每個訂單中的商品按價格排序，併為每個訂單挑選排名第一的商品。
  • 資料排名：使用 ROW_NUMBER() 可以為查詢結果中的資料進行排名，適用於例如學生成績排名、銷售業績排名等場景。

語法

ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY order_expression) AS row_num
  • PARTITION BY：可選，按指定欄位分組。相同分組內的行號會重新從 1 開始。
  • ORDER BY：指定排序欄位，行號的生成順序由此決定。

示例

假設有一個電商資料庫，包含 orders 和 order_items 表，使用 ROW_NUMBER() 來展示幾種常見場景。

示例 1：為每個訂單中的商品按價格排名

可以為每個訂單中的商品按價格進行排序，併為每個商品分配一個排名。

-- 建立 orders 表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE
);

-- 建立 order_items 表
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 插入資料
INSERT INTO orders (customer_name, order_date) VALUES
('Alice', '2024-10-01'),
('Bob', '2024-10-02'),
('Charlie', '2024-10-03');

INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
(1, 'Laptop', 1, 1000.00),
(1, 'Phone', 2, 500.00),
(1, 'Tablet', 1, 300.00),
(2, 'Headphones', 2, 100.00),
(2, 'Mouse', 1, 50.00),
(3, 'Smartwatch', 1, 150.00),
(3, 'Laptop', 1, 800.00);

查詢：為每個訂單中的商品按 unit_price 排序，給出排名

SELECT 
    oi.order_id, 
    oi.product_name, 
    oi.unit_price,
    ROW_NUMBER() OVER (PARTITION BY oi.order_id ORDER BY oi.unit_price DESC) AS `rank`
FROM order_items oi;

結果

order_id product_name unit_price rank
1 Laptop 1000.00 1
1 Phone 500.00 2
1 Tablet 300.00 3
2 Headphones 100.00 1
2 Mouse 50.00 2
3 Laptop 800.00 1
3 Smartwatch 150.00 2

在這個例子中，使用 ROW_NUMBER() 按照每個 order_id 對商品按 unit_price 從高到低排序，併為每個商品分配了一個行號（排名）。

如果只想獲取每個訂單中價格最高的商品，可以在查詢外層再加一個 WHERE rank = 1 來篩選。

示例 2：去除重複資料

假設 order_items 表中有重複的記錄，可以利用 ROW_NUMBER() 給每一行編號，然後只保留每組中第一個出現的記錄（行號為 1）。

插入重複資料

INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
(1, 'Laptop', 1, 1000.00),  -- 重複記錄
(2, 'Mouse', 1, 50.00),     -- 重複記錄
(3, 'Smartwatch', 1, 150.00);

查詢：去除重複記錄

WITH ranked_items AS (
    SELECT 
        oi.order_item_id, 
        oi.order_id, 
        oi.product_name, 
        oi.unit_price,
        ROW_NUMBER() OVER (PARTITION BY oi.order_id, oi.product_name ORDER BY oi.order_item_id) AS rn
    FROM order_items oi
)
SELECT 
    order_item_id, 
    order_id, 
    product_name, 
    unit_price
FROM ranked_items
WHERE rn = 1;
order_item_id order_id product_name unit_price
1 1 Laptop 1000.00
2 1 Phone 500.00
3 1 Tablet 300.00
4 2 Headphones 100.00
5 2 Mouse 50.00
7 3 Laptop 800.00
6 3 Smartwatch 150.00

在這個查詢中，ROW_NUMBER() 根據 order_id 和 product_name 為每一組商品打上編號，PARTITION BY 確保每個訂單中同一個商品只保留一次。WHERE rn = 1 確保每個分組只保留第一條記錄，從而去除了重複的商品條目。

示例 3：分頁查詢

假設需要分頁展示訂單項，每頁展示 2 條資料。可以使用 ROW_NUMBER() 來為查詢結果生成行號，並結合 WHERE 子句限制顯示特定頁的資料。

查詢：分頁顯示第二頁資料（每頁顯示 2 條）

WITH ranked_items AS (
    SELECT 
        oi.order_item_id, 
        oi.order_id, 
        oi.product_name, 
        oi.unit_price,
        ROW_NUMBER() OVER (ORDER BY oi.order_item_id) AS rn
    FROM order_items oi
)
SELECT 
    order_item_id, 
    order_id, 
    product_name, 
    unit_price
FROM ranked_items
WHERE rn BETWEEN 3 AND 4;

結果

order_item_id order_id product_name unit_price
3 1 Tablet 300.00
4 2 Headphones 100.00

在這個分頁查詢中，ROW_NUMBER() 為查詢結果集中的每一行分配了一個行號，然後透過 WHERE rn BETWEEN 3 AND 4 獲取第 2 頁的結果（假設每頁 2 條資料）。

總結

ROW_NUMBER() 在 MySQL 中是一個強大的視窗函式，具有以下幾個主要用途：

  • 分頁查詢：透過生成行號來實現高效分頁。
  • 去重：利用分組和行號，可以去除重複資料。
  • 分組排序：對每個分組內的資料進行排序並生成排名。
  • 資料排名：計算排名或為資料按某種規則分配順序。

MySQL 8.0 引入的視窗函式使得許多複雜的查詢變得更加簡潔和高效，特別是在處理排名、去重和分頁等場景時。

關於作者

來自全棧程式設計師nine的探索與實踐，持續迭代中。（技術交流codetrend）

相關文章