PostgreSQL LATERAL JOIN用于限制GROUP BY查询结果

Question

很抱歉我无法进行lateral join操作！

我有一个像这样的表：

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

我尝试的代码如下： （来源：在PostgreSQL中分组限制：显示每个组的前N行？）

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 元素的所有行。

- BenWhite

Answer 1

利用Windows的分析函数row_number。 Rextester演示。

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，将为每个 number 组按 value 降序分配 rno。然后在外部查询中，您可以选择 rno <= 2 来获得输出结果。

- Utsav