主页 / dba / 问题 / 80311
Accepted
Alexandros
Asked: 2014-10-17 00:03:39 +0800 CST

带领带的 Postgres top-k

  • 772

假设我有以下包含 3 个整数字段的数据库表。

A | B | C
1 | 2 | 3
1 | 2 | 4
1 | 3 | 1
2 | 4 | 2
2 | 4 | 3

当我做：

SELECT * FROM dbTable ORDER BY A,B LIMIT 1;

我得到

1 | 2 | 3

这是预期的。但是第二条记录：

 1 | 2 | 4

dbFields A 和 B 也具有相同的值。是否有任何有效的方法来实际检索与 top-k 记录具有相同值的所有记录？例如，当我搜索前 100 条记录而不是 102 条记录时，如果后两条记录的值与第 100 条记录相同？是否有任何索引可以加速此类查询？如果实现有效，我不介意是否必须使用 pl/pgsql（而不是纯 SQL）来完成。

postgresql postgresql-9.3

2 个回答

  1. 您可以为此使用窗口函数：

    select a,b,c
from (
  select a,b,c,
         dense_rank() over (order by a,b) as rnk
  from dbTable
) t
where rnk = 1;

    rank()对于“第一”行，使用or并不重要dense_rank()。例如，当您想要“第二个”时，如果出现平局，rank()和dense_rank()将返回不同的结果。因为rank()数字会有“差距”，但dense_rank()不会。

    通过分两步执行此操作可以实现可能的加速，当然还有一个索引(a,b)

    with ranked as (
  select *
  from (
    select a,b,
           dense_rank() over (order by a,b) as rnk
    from dbTable
  ) t
  where r.rnk = 1  -- (or <= for "top-k")
)
select t.a, t.b, t.c
from dbTable t
   join ranked r on r.a = t.a and r.b = t.b;

    这个想法是让 Postgres 有机会对排名部分进行仅索引扫描，然后仅将扫描的结果连接到基表以获得剩余的列。排名过滤是在 CTE 内部完成的，因为 Postgres 不会将条件从外部查询下推到 CTE 本身（这就是为什么我在 CTE 内部有派生表）

    我不确定这是否真的提高了性能，但我想值得尝试并查看带有真实表（和数据）的执行计划。

    • 2
  2. Best Answer

    这就是我想出的：

    /* Get the records of original table that correspond to each value of A 
and for values of B better or the same for the top-k records */ 
SELECT n3.A,n3.B,n3.C
FROM dbTable n3,
(
/* n2 = Get the worst value of B per A for the top-k records */ 
SELECT A, MAX(B) AS B
FROM 
/* n1 = Count records per A, ordered by B */ 
(SELECT A, B, C,
        row_number() over (partition BY A ORDER BY B,C)  AS counter
  FROM dbTable) n1

WHERE n1.counter<=100 /* k=100 */
GROUP BY A) n2

WHERE n3.A=n2.A AND n3.B<=n2.B
ORDER BY n3.A,n3.B,n3.C;

    似乎是正确的，但请发现任何可能的疏忽。

    • 0

相关问题