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