如何获得PostgreSQL中最畅销的产品?

Question

给定一个表products

<表类> pid 名称 tbody><<tr>123牛奶456茶789蛋糕……

从Postgres 13开始，它已经支持带有ties的，所以你的查询可以简单如下:

select p.pId, p.name
from sales s
join products p on p.pid = s.pid
group by p.pId, p.name
order by Sum(units) desc
fetch first 1 rows with ties;

参见demo Fiddle

解决方案:

WITH cte1 AS
(
SELECT s.pid, p.name,
SUM(units) as total_units
FROM sales s
INNER JOIN products p
ON s.pid = p.pid
GROUP BY s.pid, p.name
),
cte2 AS
(
SELECT *,
DENSE_RANK() OVER(ORDER BY total_units DESC) as rn
FROM cte1
)
SELECT pid,name
FROM cte2
WHERE rn = 1
ORDER BY pid;

工作示例:db_fiddle link