时间戳

首页 > 解决方案 > 如何为postgresql中的每个用户返回具有最大值的类别？

sql - 如何为postgresql中的每个用户返回具有最大值的类别？

问题描述

这是桌子

ID 类别 价值
1 一个 40
1 乙 20
1 C 10
2 一个 4
2 乙 7
2 C 7
3 一个 32
3 乙 21
3 C 2

我想要这样的结果

ID 类别
1 一个
2 乙
2 C
3 一个

标签： sqlpostgresql

解决方案

对于小表或每个用户只有很少的行，带有窗口函数的子查询rank()（如The Impaler 所示）就可以了。对整个表进行顺序扫描，然后进行排序将是最有效的查询计划。

但是，对于每个用户多于几行的情况，这会变得越来越低效。

通常，您还有一个users表，每个用户都有一个不同的行。如果您没有，请创建它！看：

我们可以将其用于可扩展性更好的替代查询——WITH TIES在LATERAL JOIN. 需要Postgres 13或更高版本。

SELECT u.id, t.*
FROM   users u
CROSS  JOIN LATERAL (
   SELECT t.category
   FROM   tbl t
   WHERE  t.id = u.id
   ORDER  BY t.value DESC
   FETCH  FIRST 1 ROWS WITH TIES  -- !
   ) t;

db<>在这里摆弄

看：

这可以使用多列索引来产生很好的效果 - 当然，它必须存在：

CREATE INDEX ON tbl (id, value);

或者：

CREATE INDEX ON tbl (id, value DESC);

通过以下方式可以实现更快的仅索引扫描：

CREATE INDEX ON tbl (id, value DESC, category);

或者（手头查询的最佳选择）：

CREATE INDEX ON tbl (id, value DESC) INCLUDE (category);

假设value是定义NOT NULL的，或者我们必须使用DESC NULLS LAST. 看：

为了将用户保留在 table 中没有任何行的结果中tbl， user LEFT JOIN LATERAL (...) ON true。看：

推荐阅读