sql - PostgreSQL - “十个最常见的条目”

Question

• 我们有一个包含两个列的表：USER 和 MESSAGE
• 一个 USER 可以有多个消息。
• 该表经常更新为更多的 USER-MESSAGE 对。

我想经常检索发送最多消息的前 X 个用户。它的最佳（DX 和性能方面）解决方案是什么？

我自己看到的解决方案：

• 我可以 GROUP BY 和 COUNT，但它似乎不是最高效也不是最干净的解决方案。
• 我可以保留一个额外的表格来记录每个用户的消息。在每次向主表插入消息时，我还可以在此处更新相关行。可以自动更新吗？也许我可以为它写一个程序？
• 对于主表，我可以创建一个 VIEW，它有一个额外的“计算”列——它是 GROUP BY 和 COUNT，但同样，它可能不是最高效的解决方案。我会查询视图。

请告诉我您认为可能是最佳解决方案的任何内容。

标签： sqlpostgresql

Answer 1

一些数据库具有增量更新的视图，您可以在示例 3 中创建一个视图，并且它会像示例 2 中那样自动保持更新。PostgreSQL 没有此功能。

对于您的选项 1，对我来说这似乎很干净。很难比这简单得多。是的，它可能存在性能问题，但是您真正需要它多快？在担心解决问题之前，您应该确保自己确实遇到了问题。

对于您的选项 2，您正在寻找的是触发器。对于每次插入，它将增加用户表中的计数。如果您曾经删除，您还需要减少计数。此外，如果更新以更改现有条目的用户，触发器将需要减少旧用户的计数并增加新用户的计数。这会降低并发性，就好像两个进程试图同时插入来自同一个用户的消息，一个会阻塞直到另一个完成。这对你来说可能并不重要。此外，触发器的存在会带来一些 CPU 开销，以及触发器本身实际执行的任何操作。但除非我们的服务器已经超载，否则这可能无关紧要。

您的选项 3 对我来说没有多大意义，至少在 PostgreSQL 中没有。没有性能优势，它会掩盖而不是澄清正在发生的事情。任何无法理解 GROUP BY 的人可能会在理解仅用于执行 GROUP BY 的视图时遇到更多问题。

另一种选择是物化视图。但是您会在两次刷新之间看到来自它们的陈旧数据。对于某些用途是可以接受的，对于某些用途则不是。