主页 / dba / 问题 / 112738
Accepted
Alexandros
Asked: 2015-09-01 06:25:12 +0800 CST

PostgreSQL 获取每个范围的 top-k 最小值

  • 772

假设我有以下数据：

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

每个 f1、f2 组合有两个最大的 f3 值。

对于这个特定示例，我想获得每个 f1 和范围（f2 - 每个 f1 的 max(f2)）的前 2 个最小值。

示例输出：

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

不需要创建字段 RNG。我添加它只是为了表明 forf1=1有 3 个范围：1-3, 2-3, 3-3由 for 的不同值f2创建f1=1。对于每个这样的范围，我想计算每个 f1 和范围的前 k 个最小值。

SQL小提琴在这里：

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

构建范围可以通过以下方式完成：

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;

有没有办法在不构建中间数据库表的情况下实现这一目标？

postgresql-9.3 greatest-n-per-group

2 个回答

  1. Best Answer

    另一种方式，使用LATERAL9.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 ;

    在SQLfiddle测试。

    或者没有 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. 我认为这个查询是正确的：

    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;

    并且似乎在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

相关问题