PostgreSQL Get top-k minimum per range

Question

Assume I have the following data:

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

There are two f3 values maximum per each f1,f2 combination.

For this specific example, I want to get top-2 minimum values per f1 and range (f2 - max(f2) per f1).

Example output:

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

To create the field RNG is not required. I only added it to show that for f1=1, there are 3 ranges: 1-3, 2-3, 3-3 created by the distinct values of f2 for f1=1. For each such range I want to calculate the top-k minimum values per f1 and range.

SQL Fiddle here:

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

Building the Ranges may be done by:

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;

Is there any way to achieve that, without building intermediate DB tables?

ypercubeᵀᴹ · Accepted Answer · 2015-09-01T10:16:32.647

Another way, using the LATERAL syntax, available from 9.3+ versions::

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 ;

Test at SQLfiddle.

Or without the 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 ;

Alexandros · Answer 2 · 2015-09-01T09:39:02.177

I think this query is correct:

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;

And seems to give the correct output in 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 |

PostgreSQL Get top-k minimum per range

2 Answers2