Spanner：无需窗口函数为指定Chat集合查询每个Chat的N条最新消息

前提：创建性能优化索引 #

要保证查询效率，首先给Message表创建复合索引：

CREATE INDEX Message_ChatId_CreateTimeDesc ON Message(ChatId, CreateTime DESC)

这个索引能让Spanner快速定位每个Chat下按时间倒序排列的消息，避免全表扫描。

单查询实现方案 #

利用Spanner支持的带ORDER BY和LIMIT的ARRAY_AGG函数，结合UNNEST展开结果，只需一次查询就能获取所有指定Chat的最新N条消息：

假设要查询['chat_001', 'chat_002', ..., 'chat_010']这10个ChatId，每个取最新2条消息，SQL示例如下：

WITH ChatLatestMessages AS (
  SELECT
    ChatId,
    ARRAY_AGG(
      STRUCT(MessageId, CreateTime)  -- 可替换为你需要的所有消息字段，或直接用*
      ORDER BY CreateTime DESC
      LIMIT 2  -- 指定每个Chat要获取的最新消息数量N
    ) AS LatestMessages
  FROM Message
  WHERE ChatId IN ('chat_001', 'chat_002', 'chat_003', 'chat_004', 'chat_005',
                   'chat_006', 'chat_007', 'chat_008', 'chat_009', 'chat_010')
  GROUP BY ChatId
)
SELECT
  ChatId,
  Msg.MessageId,
  Msg.CreateTime
FROM ChatLatestMessages, UNNEST(LatestMessages) AS Msg
ORDER BY ChatId, Msg.CreateTime DESC;

方案说明 #

• ARRAY_AGG按ChatId分组，将每个Chat下的消息按CreateTime倒序聚合，通过LIMIT控制只保留最新的N条，生成每个Chat的消息数组。
• UNNEST将数组展开为行记录，最终得到每条消息的详情，同时保留对应的ChatId。
• 整个查询仅执行一次，且完全依赖预先创建的索引，性能远优于循环执行N次单Chat查询。

内容的提问来源于stack exchange，提问作者cwa76