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

在 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

假设你 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;

如果你在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()部分,它会给你一个“几乎随机”的部分。