sql - PostgreSQL LATERAL JOIN 以限制 GROUP BY

Question

抱歉，我只是没能进行横向连接!

我有一张这样的 table :

ID | NUMBER | VALUE
-------------------
20 | 12     | 0.7
21 | 12     | 0.8
22 | 13     | 0.8
23 | 13     | 0.7
24 | 13     | 0.9
25 | Null   | 0.9

现在我想获取每个 NUMBER 的前 2 行，按 VALUE 的降序排列。

ID | NUMBER | VALUE
-------------------
21 | 12     | 0.8
20 | 12     | 0.7
24 | 13     | 0.9
22 | 13     | 0.8

到目前为止，我尝试过的代码如下所示:(发现:Grouped LIMIT in PostgreSQL: show the first N rows for each group?)

SELECT DISTINCT t_outer.id, t_top.number, t_top.value
FROM table t_outer
JOIN LATERAL (
     SELECT * FROM table t_inner
    WHERE t_inner.number NOTNULL
    AND t_inner.id = t_outer.id
    AND t_inner.number = t_outer.number
    ORDER BY t_inner.value DESC
    LIMIT 2
     ) t_top ON TRUE
order by t_outer.value DESC;

到目前为止一切都很好，只是 LIMIT 2 似乎无法正常工作。我得到了所有 NUMBER 个元素的所有行。

Answer 1

利用windows解析功能row_number

Rextester Demo

select "ID", "NUMBER", "VALUE" from 
(select t.*
 ,row_number() over (partition by "NUMBER" 
                     order by "VALUE" desc
                    ) as rno
from table1 t
) t1
where t1.rno <=2;

输出

ID  NUMBER  VALUE
21  12      0,8000
20  12      0,7000
24  13      0,9000
22  13      0,8000
25  NULL    0,9000

解释:

内部查询 t1，将分配 rno按 value 订购每个 number 的 desc团体。然后在外部查询中，您可以选择 rno <= 2得到你的输出。