Seleccione las N filas principales en PostgreSQL

1. Crear una tabla en PostgreSQL
2. Utilice la cláusula FETCH para seleccionar las N filas superiores en PostgreSQL
3. Use la cláusula LIMIT para seleccionar las N filas superiores en PostgreSQL
4. Utilice la función ROW_NUMBER() para seleccionar las N filas superiores en PostgreSQL
5. Use la función RANK() para seleccionar las N filas principales en PostgreSQL
6. Utilice la función DENSE_RANK() para seleccionar las N filas superiores en PostgreSQL
7. Indexación de tablas en PostgreSQL
8. Análisis de rendimiento

Obtener los valores más altos en una tabla es una consulta importante que debe saber. Una de sus áreas de aplicación es en la generación de informes.

Este tutorial lo guía a través de diferentes formas de obtener los N puntajes más altos en una tabla de PostgreSQL.

Crear una tabla en PostgreSQL

Considere una tabla student_score con N estudiantes y sus respectivos puntajes como se muestra aquí:

Consulta:

CREATE TABLE public.student_score
(
    id bigint NOT NULL
        GENERATED ALWAYS AS IDENTITY
        ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    studentid bigint NOT NULL,
    score integer NOT NULL,
    CONSTRAINT student_score_pkey PRIMARY KEY (id)
)

Aquí está la declaración INSERTAR para llenar la tabla con 1 millón de filas de datos de prueba:

INSERT INTO student_score (studentid, score)
SELECT i, floor(random()*(100-50+1))+50
FROM generate_series(1,1000000) i

Utilice la cláusula FETCH para seleccionar las N filas superiores en PostgreSQL

La cláusula FETCH devuelve solo el número especificado de puntuaciones. La tabla está ordenada en orden descendente, con el valor más alto en la parte superior y la puntuación disminuye a medida que avanza en la tabla.

Luego, la cláusula se usa para devolver solo 10 de los puntajes más altos, como se especifica en la consulta a continuación.

SELECT * FROM student_score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY

Producción :

id   | studentid | score
-----|---------- |--------
324  | 324       | 100
379  | 379       | 100
282  | 282       | 100
79   | 79        | 100
335  | 335       | 100
91   | 91        | 100
13   | 13        | 100
108  | 108       | 100
52   | 52        | 100
414  | 414       | 100

Esta consulta, sin embargo, devuelve los 10 mejores estudiantes con las puntuaciones más altas. En este ejemplo, 100 es la puntuación más alta que cualquier estudiante puede lograr y el resultado de la consulta muestra que al menos 10 estudiantes obtuvieron 100.

No da los 10 valores más altos diferentes que cualquier estudiante ha alcanzado. Si existe la necesidad de obtener los 10 puntajes únicos más altos adquiridos por cualquier estudiante, se usa la cláusula DISTINCT como se muestra aquí:

SELECT DISTINCT score FROM student_score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY

Además, si existe el requisito de saber cuántos estudiantes obtuvieron las 10 puntuaciones más altas registradas, esta consulta se puede utilizar para lograrlo:

SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY

Producción :

score | count
----- |-------
100   | 19518
99    | 19719
98    | 19412
97    | 19588
96    | 19652
95    | 19396
94    | 19649
93    | 19427
92    | 19880
91    | 19580

Use la cláusula LIMIT para seleccionar las N filas superiores en PostgreSQL

La cláusula LIMIT devuelve el número máximo de filas especificadas. En este caso, 10 es el número máximo de filas que se devolverán.

El uso de esta cláusula también requiere que las filas se clasifiquen en orden descendente. Aquí está la consulta:

SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
LIMIT 10

Utilice la función ROW_NUMBER() para seleccionar las N filas superiores en PostgreSQL

Se puede utilizar la función ROW_NUMBER() para obtener el mismo resultado. La consulta se da a continuación.

SELECT score, student_count
FROM (
    SELECT DISTINCT score AS score,
    COUNT(studentid) AS student_count,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS score_rank
    FROM student_score
    GROUP BY score
) subquery
WHERE score_rank <= 10
ORDER BY score_rank

Use la función RANK() para seleccionar las N filas principales en PostgreSQL

Aquí hay una consulta usando la función RANK():

SELECT score, student_count
FROM (
    SELECT DISTINCT score AS score,
    COUNT(studentid) AS student_count,
    RANK() OVER (ORDER BY score DESC) AS score_rank
    FROM student_score
    GROUP BY score
) subquery
WHERE score_rank <= 10
ORDER BY score_rank

Utilice la función DENSE_RANK() para seleccionar las N filas superiores en PostgreSQL

Aquí hay una consulta usando la función DENSE_RANK():

SELECT score, student_count
FROM (
    SELECT DISTINCT score AS score,
    COUNT(studentid) AS student_count,
    DENSE_RANK() OVER (ORDER BY score DESC) AS score_rank
    FROM student_score
    GROUP BY score
) subquery
WHERE score_rank <= 10
ORDER BY score_rank

Indexación de tablas en PostgreSQL

Para mejorar el rendimiento en una base de datos grande, puede usar índices. La columna de puntuación ordenada en orden descendente se puede indexar en este ejemplo.

Se hace creando un índice y agregando condiciones específicas, en este caso, ordenando la columna de puntuación en orden descendente, como se muestra a continuación.

CREATE INDEX score_desc_idx ON student_score(score DESC)

Como se mencionó anteriormente, la indexación mejora el rendimiento de las consultas para tablas con registros más grandes de manera más efectiva. Pero no es aconsejable usarlo si la tabla se modifica con frecuencia (con inserciones y actualizaciones frecuentes).

Además, PostgreSQL no usará índices en ciertos casos, como cuando el escaneo de una tabla es más rápido.

Análisis de rendimiento

Antes de crear el índice score_desc_idx, el tiempo de ejecución de cada operación se daba como,

Sin embargo, esto fue justo después de una sola carrera. Ejecutarlo varias veces dio tiempos variables que no excedieron un rango.

Puede utilizar la cláusula EXPLAIN ANALYZE para determinar qué enfoque es menos costoso para su base de datos.

EXPLAIN ANALYZE SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
LIMIT 10

Producción :

Limit  (cost=13636.35..13636.42 rows=10 width=12) (actual time=763.166..797.358 rows=10 loops=1)
->  Unique  (cost=13636.35..13636.73 rows=51 width=12) (actual time=763.163..797.351 rows=10 loops=1)"
        ->  Sort  (cost=13636.35..13636.48 rows=51 width=12) (actual time=763.161..797.342 rows=10 loops=1)"
             Sort Key: score DESC, (count(studentid))"
              Sort Method: quicksort  Memory: 27kB"
             ->  Finalize GroupAggregate  (cost=13621.98..13634.90 rows=51 width=12) (actual time=762.733..797.231 rows=51 loops=1)"
                   Group Key: score"
                   ->  Gather Merge  (cost=13621.98..13633.88 rows=102 width=12) (actual time=762.715..797.047 rows=153 loops=1)"
                          Workers Planned: 2"
                          Workers Launched: 2"
                          ->  Sort  (cost=12621.96..12622.09 rows=51 width=12) (actual time=632.708..632.724 rows=51 loops=3)
                                Sort Key: score DESC
                                Sort Method: quicksort  Memory: 27kB"
                                Worker 0:  Sort Method: quicksort  Memory: 27kB"
                                Worker 1:  Sort Method: quicksort  Memory: 27kB"
                                ->  Partial HashAggregate  (cost=12620.00..12620.51 rows=51 width=12) (actual time=632.509..632.546 rows=51 loops=3)"
                                      Group Key: score
                                      ->  Parallel Seq Scan on student_score  (cost=0.00..10536.67 rows=416667 width=12) (actual time=0.067..176.426 rows=333333 loops=3)
Planning Time: 3.228 ms
Execution Time: 799.928 ms

Se puede ver que PostgreSQL no usó el índice creado anteriormente, ya que escanear la tabla es relativamente rápido en este caso. Dado que agregar un índice no genera ningún cambio significativo en el tiempo de ejecución, el índice se puede eliminar ejecutando este comando:

DROP INDEX score_desc_idx;

Depende de usted decidir qué enfoque funciona mejor, y cualquier enfoque puede usarse para lograr el mismo resultado.