PostgreSQL：同一表中每个项目的前n个条目 [英] PostgreSQL: top n entries per item in same table

问题描述

| uId |   title   |  amount  |  makers  |  widgets  |
   1     richard      998       xcorp     sprocket
   2     swiss        995       ycorp     framitz
   3     ricky         90       zcorp     flobber
   4     ricky2       798       xcorp     framitz
   1     lilrick      390       xcorp     sprocket
   1     brie         200       mcorp     gullywok
   1     richard      190       rcorp     flumitz
   1     brie         490       bcorp     sprocket

etc ...

我尝试每个个制造商只检索3条记录，前3个金额和它们产生的小工具

I am trying to retrieve only 3 records per makers, the top 3 amounts and the widgets they produced

这是我所拥有的：

SELECT amount, makers FROM (SELECT amount, makers, (SELECT count(*) FROM  entry  as t2
WHERE t2.amount = t1.amount and t2.makers >= t1.makers) AS RowNum
FROM entry as t1
) t3
WHERE t3.RowNum<4 order by amount;

这是否是我真正需要的东西？有没有更好的方法来解决这个问题？我见过的大多数方法都是在不同的表上执行联接等操作，我需要的所有信息都在一个表上。

Is this returning what I actually need? Is there a better way to go about this? Most of the ways I have seen to do this kind of thing are doing joins etc on disparate tables, all the info I need is on one table.

预期输出：

| uId |   title   |  amounts  |  makers  |  widgets  |
  1      richard      998        xcorp     sprocket
  41     swiss        995        xcorp     widget
  989    richard      989        xcorp     sprocket
  22     swiss        995        ycorp     framitz
  92     swiss        990        ycorp     widget
  456    swiss        895        ycorp     flobber
  344    ricky        490        zcorp     flobber
  32     tricky       480        zcorp     flobber
  13     ricky        470        zcorp     flobber

等...

制造商的订单重要的是获得每个制造商和 widgets的前3个金额 他们提供了。设置了个制造商的数量，总会有 x 个制造商

The order of the makers doesn't matter so much as getting the top 3 amounts for each makers, and the widgets they provided. The number of makers is set, there will always be x makers

推荐答案

SELECT *
FROM (
   SELECT uid,
          title, 
          amount, 
          maker, 
          widgets,
          rank() over (partition by maker order by amount desc) as rank
   FROM entry  
) t
WHERE rank <= 3

这篇关于PostgreSQL：同一表中每个项目的前n个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！