NTH_VALUE() 函數

NTH_VALUE() 是窗口函數中的位移函數，用來取得窗口範圍內「第 N 列」的值。它是 FIRST_VALUE() 和 LAST_VALUE() 的延伸版本，可以取得任意位置的值。

NTH_VALUE 語法

NTH_VALUE(column, n) OVER (
    [PARTITION BY column_name(s)]
    ORDER BY column_name(s)
    [frame_clause]
)

• column: 要取值的欄位
• n: 要取第幾列的值（從 1 開始）

NTH_VALUE 基本用法 (Example)

假設我們有一個 employees 資料表：

取得各部門薪資第 2 高的員工姓名：

SELECT 
    Dept,
    Name,
    Salary,
    NTH_VALUE(Name, 2) OVER (
        PARTITION BY Dept 
        ORDER BY Salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM employees;

查詢結果：

窗口框架的重要性

與 LAST_VALUE() 相同，NTH_VALUE() 預設的窗口框架只會看到「當前列及之前」的資料。如果要取得完整分區內的第 N 個值，需要明確指定窗口框架：

-- 正確寫法：指定完整的窗口範圍
NTH_VALUE(column, n) OVER (
    ORDER BY column
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

當 N 超出範圍時

如果指定的 N 大於窗口內的資料筆數，NTH_VALUE() 會返回 NULL。

-- 如果某部門只有 2 人，取第 3 名會返回 NULL
NTH_VALUE(Name, 3) OVER (PARTITION BY Dept ORDER BY Salary DESC)

實務應用：取得前三名

同時顯示第 1、2、3 名：

SELECT 
    Dept,
    Name,
    Salary,
    FIRST_VALUE(Name) OVER w AS first_place,
    NTH_VALUE(Name, 2) OVER w AS second_place,
    NTH_VALUE(Name, 3) OVER w AS third_place
FROM employees
WINDOW w AS (
    PARTITION BY Dept 
    ORDER BY Salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

實務應用：中位數近似值

取得排序後中間位置的值：

SELECT 
    Dept,
    NTH_VALUE(Salary, (COUNT(*) OVER (PARTITION BY Dept) + 1) / 2) OVER (
        PARTITION BY Dept 
        ORDER BY Salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS median_salary
FROM employees;

資料庫支援度