PostgreSQL NTH_VALUE用法及代碼示例

在PostgreSQL中，的NTH_VALUE()函數用於從n獲取值^th在結果集中的行。NTH_VALUE()函數從n返回一個值^th結果集的有序分區中的行。

這是NTH_VALUE()函數的語法：

用法：
NTH_VALUE(expression, offset) 
OVER (
    [PARTITION BY partition_expression]
    [ ORDER BY sort_expression [ASC | DESC]
    frame_clause ]
)

讓我們分析以上語法：

• 這個表達是目標列或表達式NTH_VALUE()函數運行。
• 這個抵消是一個正整數(大於零)，該整數確定相對於表達式要計算的窗口中第一行的行號。
• 這個分區依據子句將結果集的行分配到分區中，NTH_VALUE()函數適用。
• 這個排序子句對應用該函數的每個分區中的行進行排序。
• 這個frame_clause定義當前分區的子集(或框架)。

範例1：

首先，創建兩個名為product和product_groups的表：

CREATE TABLE product_groups (
    group_id serial PRIMARY KEY,
    group_name VARCHAR (255) NOT NULL
);

CREATE TABLE products (
    product_id serial PRIMARY KEY,
    product_name VARCHAR (255) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL,
    FOREIGN KEY (group_id) REFERENCES product_groups (group_id)
);

現在向其中添加一些數據：

INSERT INTO product_groups (group_name)
VALUES
    ('Smartphone'),
    ('Laptop'),
    ('Tablet');

INSERT INTO products (product_name, group_id, price)
VALUES
    ('Microsoft Lumia', 1, 200),
    ('HTC One', 1, 400),
    ('Nexus', 1, 500),
    ('iPhone', 1, 900),
    ('HP Elite', 2, 1200),
    ('Lenovo Thinkpad', 2, 700),
    ('Sony VAIO', 2, 700),
    ('Dell Vostro', 2, 800),
    ('iPad', 3, 700),
    ('Kindle Fire', 3, 150),
    ('Samsung Galaxy Tab', 3, 200);

以下聲明使用NTH_VALUE()函數將所有產品與最昂貴的產品一起退回：

SELECT 
    product_id,
    product_name,
    price,
    NTH_VALUE(product_name, 2) 
    OVER(
        ORDER BY price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    )
FROM 
    products;

輸出：

範例2：

以下語句使用NTH_VALUE()函數返回每個產品組中價格第二貴的所有產品：

SELECT 
    product_id,
    product_name,
    price,
    group_id,
    NTH_VALUE(product_name, 2) 
    OVER(
        PARTITION BY group_id
        ORDER BY price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    )
FROM 
    products;

輸出：