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;

输出：