Início / dba / Perguntas / 303297
Accepted
RonJohn
Asked: 2021-12-01 06:56:34 +0800 CST

Como obter o segundo maior valor por grupo?

  • 772

Conseguir o segundo valor mais alto de uma tabela já foi resolvido muitas vezes, mas estou procurando o segundo valor mais alto em cada grupo .

Dada esta tabela:

+----+-----+
| A  |  10 |
| A  |  20 |
| A  |  35 |  <-- This record
| A  |  42 |
| B  |  12 |
| B  |  21 |  <-- This record
| B  |  33 |
| C  |  14 |
| C  |  23 |
| C  |  38 |
| C  |  41 |  <-- This record
| C  |  55 |
+----+-----+

Eu gostaria de obter as linhas marcadas.
Pseudo-código:

select col_a, penultimate(col_b)
from foo
group by col_a;
postgresql select

2 respostas

  1. Best Answer

    Você pode usar funções de janela para isso.

    select col_a, col_b
from (
  select col_a, 
         col_b, 
         dense_rank() over (partition by col_a order by col_b desc) as rnk
  from the_table
) t
where rnk = 2
    • 6

  2. Assumindo valores distintos por grupo. Portanto, não precisamos romper laços.
    Assumindo pelo menos 2 linhas por grupo - ou as seguintes quebras de consulta. (Você precisaria fazer mais, começando definindo o "2º valor mais alto" para esses casos.)

    Com mais do que algumas linhas por grupo (e embora esse recurso não seja implementado diretamente, ainda, a partir da página 14), uma varredura de salto de índice emulado será (muito) mais rápida. Um pouco complicado para obter o segundo valor mais alto:

    WITH RECURSIVE cte AS (
   (
   SELECT col_a, col_b
   FROM   tbl
   ORDER  BY col_a, col_b DESC
   OFFSET 1
   LIMIT  1
   )
   UNION ALL
   (
   SELECT t.col_a, t.col_b
   FROM   cte c
   JOIN   tbl t ON t.col_a > c.col_a
   ORDER  BY t.col_a, t.col_b DESC
   OFFSET 1
   LIMIT  1
   )
   )
TABLE cte;

    db<>fique aqui

    Ver:

    Requer um índice aplicável para ser rápido. Curti:

    CREATE UNIQUE INDEX ON tbl (col_a, col_b DESC);

    O Postgres pode varrer um índice para trás praticamente na velocidade máxima. Mas a ordem de classificação combinada das colunas não pode discordar da consulta. Ver:

    • 4

relate perguntas