SQL Partition By的完整指南

一、什麼是SQL Partition By

SQL Partition By是一種SQL函數，它可以根據所定義的分區來計算或統計函數的值。分區指定的方式可以是列、條件、時間等，SQL Partition By可以在分區內對每個組進行計算並為每個組返回一個結果。

下面是SQL Partition By函數的一般語法：

SELECT 
    column1, 
    function(column2) OVER (
        PARTITION BY column3 
        ORDER BY column4
    )
    FROM table_name

在這裡，column1是未分組的列，function()是要執行的函數（例如SUM()、AVG()等），column2是要進行計算的列，column3是所使用的分組列，column4指定用於組內排序的列。

二、SQL Partition By函數的幾種用法

1. SQL Partition By的基本用法

SQL Partition By最基本的用法是在分組列上進行分區，並對每個組內的數據應用指定的函數。

下面是一個例子：

SELECT 
    employee, 
    department, 
    SUM(sales) OVER (
        PARTITION BY department
    ) as total_sales
FROM sales_table;

在這裡，我們將sales_table表中的數據按部門分組，然後對每個分組計算總銷售額（total_sales）。

2. SQL Partition By函數的排序功能

要在分組內排序，可以選擇使用ORDER BY子句。在下面的例子中，我們將sales_table表中的數據按照創造的時間和部門進行排序，然後對每個分區計算每個員工在他們各自的部門內排名。

SELECT 
    employee, 
    department, 
    SUM(sales) OVER (
        PARTITION BY department
        ORDER BY create_date, sales DESC
    ) as rank
FROM sales_table;

在這裡，我們使用了ORDER BY子句指定了組內的排序方式。其中，create_date是創建日期的列，sales是銷售的總數。

3. SQL Partition By針對多個列的分區

可以選擇為一個或多個列分區。在下面的例子中，我們將sales_table表中的數據按employee和department分組，並計算每個員工部門內的銷售總額。

SELECT 
    employee, 
    department, 
    SUM(sales) OVER (
        PARTITION BY employee, department
    ) as total_sales
FROM sales_table;

在這裡，我們使用employee和department兩列進行分組。

4. SQL Partition By的行號()

ROW_NUMBER()是一種窗口函數，在SQL Partition By中使用它可以為每個分區內的行分配一個唯一的編號。

下面的例子演示了如何為每個部門內的員工分配一個唯一的編號：

SELECT 
    employee, 
    department, 
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY employee
    ) as row_num
FROM sales_table;

在這裡，我們使用ROW_NUMBER()函數為每個部門內的員工分配一個唯一的編號。

5. SQL Partition By的透視表和交叉表功能

SQL Partition By還可以用於創建透視表和交叉表。在下面的例子中，我們使用了SUM()函數來計算不同日期內的銷售額，並將它們轉換為透視表。

SELECT 
    department,
    SUM(CASE WHEN sales_date = '2021-01-01' THEN sales END) OVER (
        PARTITION BY department
    ) AS '2021-01-01',
    SUM(CASE WHEN sales_date = '2021-01-02' THEN sales END) OVER (
        PARTITION BY department
    ) AS '2021-01-02',
    SUM(CASE WHEN sales_date = '2021-01-03' THEN sales END) OVER (
        PARTITION BY department
    ) AS '2021-01-03'
FROM sales_table
GROUP BY department;

在這裡，我們使用了SUM()函數按日期計算銷售額，然後將它們轉換為透視表。

三、總結

SQL Partition By是一種強大的SQL函數，可以用於各種目的。通過使用分區、排序、行號和透視表等功能，可以將SQL Partition By函數用於各種查詢和報告中，從而更好地了解數據。