SQL 中的distinct和row_number() over() 有什么區別

SQL 中的 DISTINCT 和 ROW_NUMBER() OVER() 有什么區別

在 SQL 中，DISTINCT 和 ROW_NUMBER() OVER() 是兩種常用的數據處理方式，但它們的功能和使用場景有很大的不同。本文將詳細探討這兩者的區別，并通過示例代碼幫助讀者更好地理解它們的應用場景。

1. DISTINCT 的作用與使用

1.1 DISTINCT 的基本概念

DISTINCT 是 SQL 中的一個關鍵字，用于從查詢結果中去除重復的行。它作用于整個查詢結果集，確保返回的每一行都是唯一的。

1.2 DISTINCT 的語法

SELECT DISTINCT column1, column2, ...
FROM table_name;

在這個語法中，DISTINCT 關鍵字會去除 column1, column2, ... 這些列的組合中重復的行。

1.3 DISTINCT 的示例

假設我們有一個 employees 表，結構如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

插入一些數據：

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'IT'),
(3, 'Charlie', 'HR'),
(4, 'David', 'IT'),
(5, 'Eve', 'HR');

如果我們想要查詢所有不同的部門，可以使用 DISTINCT：

SELECT DISTINCT department
FROM employees;

結果將是：

department
----------
HR
IT

1.4 DISTINCT 的局限性

DISTINCT 只能去除完全相同的行。如果我們需要根據某些條件去除重復的行，或者需要保留某些特定的行，DISTINCT 就無法滿足需求。

2. ROW_NUMBER() OVER() 的作用與使用

2.1 ROW_NUMBER() OVER() 的基本概念

ROW_NUMBER() OVER() 是 SQL 中的窗口函數（Window Function），它為每一行分配一個唯一的行號。這個行號是基于 OVER() 子句中指定的排序規則生成的。

2.2 ROW_NUMBER() OVER() 的語法

SELECT 
    column1, column2, ...,
    ROW_NUMBER() OVER (PARTITION BY columnX ORDER BY columnY) AS row_num
FROM table_name;

在這個語法中： - PARTITION BY columnX 是可選的，用于將數據分組，然后在每個組內進行編號。 - ORDER BY columnY 是必須的，用于指定排序規則。

2.3 ROW_NUMBER() OVER() 的示例

繼續使用 employees 表，假設我們想要為每個部門的員工編號：

SELECT 
    id, name, department,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY id) AS row_num
FROM employees;

結果將是：

id  name    department  row_num
1   Alice   HR          1
3   Charlie HR          2
5   Eve     HR          3
2   Bob     IT          1
4   David   IT          2

在這個結果中，row_num 列表示每個部門內員工的編號。

2.4 ROW_NUMBER() OVER() 的應用場景

ROW_NUMBER() OVER() 常用于以下場景： - 去重：通過為每一行分配一個唯一的行號，可以輕松去除重復的行。 - 分頁：在分頁查詢中，可以使用 ROW_NUMBER() 來生成行號，然后根據行號進行分頁。 - 排名：在某些情況下，需要為數據排名，ROW_NUMBER() 可以用于生成排名。

3. DISTINCT 和 ROW_NUMBER() OVER() 的區別

3.1 功能上的區別

• DISTINCT：用于去除查詢結果中的重復行，返回唯一的行。
• ROW_NUMBER() OVER()：為每一行分配一個唯一的行號，通常用于排序、去重或分頁。

3.2 使用場景的區別

• DISTINCT：適用于簡單的去重需求，當需要去除完全相同的行時使用。
• ROW_NUMBER() OVER()：適用于復雜的去重需求，例如根據某些條件去除重復的行，或者需要保留某些特定的行。

3.3 性能上的區別

• DISTINCT：在處理大數據集時，DISTINCT 可能會導致性能問題，因為它需要對整個結果集進行去重操作。
• ROW_NUMBER() OVER()：雖然 ROW_NUMBER() 也需要排序和編號，但通過合理的分區和排序，可以減少性能開銷。

3.4 示例對比

假設我們有一個 orders 表，結構如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

插入一些數據：

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(1, 1, '2023-01-01', 100.00),
(2, 1, '2023-01-02', 200.00),
(3, 2, '2023-01-01', 150.00),
(4, 2, '2023-01-02', 250.00),
(5, 3, '2023-01-01', 300.00);

3.4.1 使用 DISTINCT 去重

如果我們想要查詢所有不同的客戶 ID，可以使用 DISTINCT：

SELECT DISTINCT customer_id
FROM orders;

結果將是：

customer_id
-----------
1
2
3

3.4.2 使用 ROW_NUMBER() OVER() 去重

如果我們想要查詢每個客戶的最新訂單，可以使用 ROW_NUMBER() OVER()：

WITH ranked_orders AS (
    SELECT 
        order_id, customer_id, order_date, amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS row_num
    FROM orders
)
SELECT order_id, customer_id, order_date, amount
FROM ranked_orders
WHERE row_num = 1;

結果將是：

order_id  customer_id  order_date  amount
2         1            2023-01-02  200.00
4         2            2023-01-02  250.00
5         3            2023-01-01  300.00

在這個結果中，我們只保留了每個客戶的最新訂單。

4. 總結

DISTINCT 和 ROW_NUMBER() OVER() 是 SQL 中兩種不同的數據處理方式，它們的功能和使用場景有很大的不同。DISTINCT 適用于簡單的去重需求，而 ROW_NUMBER() OVER() 則適用于復雜的去重、排序和分頁需求。在實際應用中，應根據具體需求選擇合適的工具。

通過本文的詳細講解和示例代碼，相信讀者已經對 DISTINCT 和 ROW_NUMBER() OVER() 的區別有了更深入的理解。在實際的 SQL 查詢中，合理使用這兩種工具，可以大大提高數據處理的效率和準確性。