PostgreSQL – Función PERCENT_RANK

En PERCENT_RANK()

La sintaxis de la función PERCENT_RANK():

Syntax:
PERCENT_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Analicemos la sintaxis anterior:

• PARTICIÓN POR
• ORDENAR POR
• PERCENT_RANK()

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)
);

Ahora añádele algunos datos:

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);

PERCENT_RANK()

SELECT 
    name,
    amount,
    PERCENT_RANK() OVER (
        ORDER BY amount
    )
FROM 
    sales_stats
WHERE 
    year = 2019;

Producción:

Ejemplo 2:

La siguiente declaración PERCENT_RANK()

SELECT 
    name,
    amount,
    PERCENT_RANK() OVER (
        PARTITION BY year
        ORDER BY amount
    )
FROM 
    sales_stats;

Producción: