Postgresql：如何从每个组/类别中选择前n个％（％）条目 [英] Postgresql : How do I select top n percent(%) entries from each group/category

问题描述

我们是Postgres的新手，我们通过以下查询可以从每个类别中选择前N条记录。

We are new to postgres, we have following query by which we can select top N records from each category.

 create table temp (
     gp char,
     val int
 );

 insert into temp values ('A',10);
 insert into temp values ('A',8);
 insert into temp values ('A',6);
 insert into temp values ('A',4);
 insert into temp values ('B',3);
 insert into temp values ('B',2);
 insert into temp values ('B',1);

 select a.gp,a.val
 from   temp a
 where  a.val in (
              select b.val
              from   temp b
              where  a.gp=b.gp
              order by b.val desc
             limit 2);

以上查询的输出是这样的

Output of above query is something like this

 gp   val
 ----------
 A    10
 A    8
 B    3
 B    2

但是我们的要求不同，我们想从每个类别中选择前n％个记录，其中n是不是固定的，n是每个组中某些元素的百分比。

But our requirement is different, we want to select top n% records from each category where n is not fixed, n is based of some percent of elements in each group.

推荐答案

根据行的百分比检索行每个组中的行数可以使用两个窗口函数：一个用于计数行，另一个用于为行赋予唯一编号。

To retrieve the rows based on the percentage of the number of rows in each group you can use two window functions: one to count the rows and one to give them a unique number.

select gp,
       val
from (
  select gp, 
         val,
         count(*) over (partition by gp) as cnt,
         row_number() over (partition by gp order by val desc) as rn
  from temp
) t
where rn / cnt <= 0.75;

SQLFiddle示例：http://sqlfiddle.com/#!15/94fdd/1

SQLFiddle example: http://sqlfiddle.com/#!15/94fdd/1

Btw ：使用 char 几乎总是一个坏主意，因为它是固定长度的数据类型，被填充为定义的长度。希望您只是为了设置示例而已，不要在实际表中使用它。

Btw: using char is almost always a bad idea because it is a fixed-length data type that is padded to the defined length. I hope you only did that for setting up the example and don't use it in your real table.

这篇关于Postgresql：如何从每个组/类别中选择前n个％（％）条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！