PostgreSQL – Función NTH_VALUE

En , NTH_VALUE() ^th

Esta es la sintaxis de la función NTH_VALUE():

Syntax:
NTH_VALUE(expression, offset) 
OVER (
    [PARTITION BY partition_expression]
    [ ORDER BY sort_expression [ASC | DESC]
    frame_clause ]
)

Analicemos la sintaxis anterior:

• expresiónNTH_VALUE()
• compensar
• PARTICIÓN BYNTH_VALUE()
• ORDENAR POR
• cláusula_marco

CREATE TABLE items(
    group_id serial PRIMARY KEY,
    group_name VARCHAR (100) NOT NULL
);

CREATE TABLE groceries(
    gro_id serial PRIMARY KEY,
    gro_name VARCHAR (100) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL,
    FOREIGN KEY (group_id) REFERENCES grocery (group_id)
);

Ahora añádele algunos datos:

INSERT INTO groceries (group_name)
VALUES
    ('Cereal'),
    ('Fruit'),
    ('Vegetable');

INSERT INTO groceries (group_name, group_id, price)
VALUES
    ('Wheat', 1, 30),
    ('Rice', 1, 40),
    ('Barley', 1, 50),
    ('Corn', 1, 90),
    ('Apple', 2, 120),
    ('Banana', 2, 70),
    ('Pear', 2, 70),
    ('Mango', 2, 80),
    ('Brinjal', 3, 70),
    ('Capsicum', 3, 150),
    ('Potato', 3, 20);

La siguiente declaración 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;

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;

Producción: