Início / dba / Perguntas / 112738
Accepted
Alexandros
Asked: 2015-09-01 06:25:12 +0800 CST

PostgreSQL Obtenha o mínimo top-k por intervalo

  • 772

Suponha que eu tenha os seguintes dados:

| f1 | f2 | f3 |
|----|----|----|
|  1 |  1 |  1 |
|  1 |  1 |  5 |
|  1 |  2 |  3 |
|  1 |  2 |  6 |
|  1 |  3 |  4 |
|  1 |  3 |  7 |
|  2 |  1 |  2 |
|  2 |  1 | 22 |
|  2 |  2 |  3 |
|  2 |  2 |  4 |

Existem dois valores máximos de f3 para cada combinação de f1,f2.

Para este exemplo específico, desejo obter os 2 valores mínimos principais por f1 e intervalo (f2 - max(f2) por f1).

Saída de exemplo:

| f1 | RNG| f3 |
|----|----|----|
|  1 |1 -3|  1 |
|  1 |1 -3|  3 |
|  1 |2 -3|  3 |
|  1 |2 -3|  4 |
|  1 |3 -3|  4 |
|  1 |3 -3|  7 |
|  2 |1- 2|  2 |
|  2 |1- 2|  3 |
|  2 |2- 2|  3 |
|  2 |2- 2|  4 |

Para criar o campo RNG não é necessário. Eu apenas adicionei para mostrar que para f1=1, existem 3 intervalos: 1-3, 2-3, 3-3criados pelos valores distintos de f2for f1=1. Para cada intervalo, desejo calcular os valores mínimos top-k por f1 e intervalo.

SQL Fiddle aqui:

http://sqlfiddle.com/#!15/9ddbb/1

Construir os intervalos pode ser feito por:

SELECT DISTINCT s1.f1,s1.f2 AS range_from ,s2.f2 AS range_to
FROM dbTable s1,
(SELECT f1,MAX(f2) AS f2 FROM dbTable
GROUP BY f1) s2
WHERE s1.f1=s2.f1
ORDER BY s1.f1,s1.f2;

Existe alguma maneira de conseguir isso, sem construir tabelas intermediárias de banco de dados?

postgresql-9.3 greatest-n-per-group

2 respostas

  1. Best Answer

    Outra maneira, usando a LATERALsintaxe, disponível nas versões 9.3+::

    WITH t AS
  ( SELECT f1, f2,  
           MAX(f2) OVER (PARTITION BY f1) AS range_to
    FROM dbTable 
    GROUP BY f1, f2
  ) 
SELECT t.f1, 
       -- t.f2 AS range_from, t.range_to,
       t.f2 || ' - ' || t.range_to AS RNG,
       x.f3
FROM t
  CROSS JOIN LATERAL
    ( SELECT f3 
      FROM dbTable 
      WHERE f1 = t.f1 AND f2 >= t.f2 
      ORDER BY f3 LIMIT 2
    ) AS x (f3)
ORDER BY t.f1, t.f2, x.f3 ;

    Teste no SQLfiddle .

    Ou sem o CTE:

    SELECT t.f1, 
       -- t.f2 AS range_from, 
       -- MAX(t.f2) OVER (PARTITION BY t.f1) AS range_to,
       t.f2 || ' - ' || MAX(t.f2) OVER (PARTITION BY t.f1) AS RNG,
       x.f3
FROM dbTable AS t
  CROSS JOIN LATERAL
    ( SELECT f3 
      FROM dbTable 
      WHERE f1 = t.f1 AND f2 >= t.f2 
      ORDER BY f3 LIMIT 2
    ) AS x (f3)
GROUP BY t.f1, t.f2, x.f3
ORDER BY t.f1, t.f2, x.f3 ;
    • 6

  2. Acho que esta consulta está correta:

    SELECT f1,range_from,range_to,f3 FROM
(
/* s53 subquery joins original table and created ranges */
SELECT s.f1,s3.range_from,s3.range_to,s.f3, row_number() over (partition BY s.f1,s3.range_from ORDER BY f3) AS counter 
FROM dbTable s,
(
/* Created ranges are the s3 subquery */
SELECT DISTINCT s1.f1,s1.f2 AS range_from ,s2.f2 AS range_to
FROM dbTable s1,
(SELECT f1,MAX(f2) AS f2 FROM dbTable
GROUP BY f1) s2
WHERE s1.f1=s2.f1
ORDER BY s1.f1,s1.f2
) s3

WHERE s.f1=s3.f1 
AND (s.f2 >= s3.range_from AND s.f2<= s3.range_to)
ORDER BY s.f1,s3.range_from,s.f3
) s53
WHERE counter<=2
ORDER BY f1,range_from,range_to, counter;

    E parece dar a saída correta no SQL Fiddle :

    | f1 | range_from | range_to | f3 |
|----|------------|----------|----|
|  1 |          1 |        3 |  1 |
|  1 |          1 |        3 |  3 |
|  1 |          2 |        3 |  3 |
|  1 |          2 |        3 |  4 |
|  1 |          3 |        3 |  4 |
|  1 |          3 |        3 |  7 |
|  2 |          1 |        2 |  2 |
|  2 |          1 |        2 |  3 |
|  2 |          2 |        2 |  3 |
|  2 |          2 |        2 |  4 |
    • 2

relate perguntas