代码之家 › 专栏 › 技术社区 › Gacek

PostgreSQL:按查询分组选择n个结果

postgresql sql

Gacek · 技术社区 · 15 年前

我使用以下SQL查询从表中选择一些结果:

select  avg(c3), count(c3), std  
from ssims where obraz = 'lena' group by std order by std

但是我对不同的std值执行了不同数量的测试,所以它返回如下结果:

0.906176136363636;44;5
0.881669302325582;43;10
0.855873409090909;44;15
0.829195813953488;43;20
0.802071590909091;44;25
0.774523720930233;43;30
0.747213636363636;44;35
0.720115581395349;43;40
0.694712954545455;44;45
0.668683255813953;43;50

我要做的是为每个标准值选择结果的常量(即20)个数的平均值。所以在这样的查询之后,第二列将是每行20个。

如何做到这一点?我尝试了极限和顶级,但没有成功

3 回复 | 直到 14 年前

Quassnoi 15 年前

在 PostgreSQL 8.3 :

SELECT  a[1] AS avg_std, a[2] AS cnt_std, std
FROM    (
        SELECT  (
                SELECT  ARRAY[AVG(c3) , COUNT(*)]
                FROM    (
                        SELECT  c3
                        FROM    ssims si
                        WHERE   obraz = 'lena'
                                AND si.std = so.std
                        ORDER BY
                                id
                        LIMIT 20
                        ) q
                ) a
        FROM    (
                SELECT  DISTINCT std
                FROM    ssims
                WHERE   obraz = 'lena'
                ) so
        ) q

这两个都算 AVG 和 COUNT 对每个标准进行单索引扫描。

在上创建复合索引 (obraz, std, id) 让它快速工作。

在 PostgreSQL 8.4 :

SELECT  AVG(c3), COUNT(*), std
FROM    (
        SELECT  std, c3, ROW_NUMBER() OVER (PARTITION BY std ORDER BY id) AS rn
        FROM    ssims
        WHERE   obraz = 'lena'
        ) q
WHERE   rn <= 20
GROUP BY
        std

Tendayi Mawushe 15 年前

假设你 ssims 表有一个我调用过的唯一ID列 id 在我的示例中,您可以执行以下操作:

select avg(c3), count(c3), std from ssims where id in 
   (select id from ssims where obraz = 'lena' LIMIT 20)
   group by std order by std;

Magnus Hagander 15 年前

如果你在8.4上,你应该可以用一个窗口功能来完成。(不确定std部分是什么,但我相信你可以把它加回去)类似这样的东西(未测试,所以你可能需要调整一些东西):

SELECT std,avg(c3), count(c3)
FROM (
 SELECT std, c3, row_number() OVER (
  PARTITION BY std ORDER BY random())
 ) foo
WHERE row_number <= 20
GROUP BY std
ORDER BY std

如果你不关心你是否真的得到了一个随机的子集,你可以删除order by random()部分,它会给你一个“几乎随机”的部分。