Spanner: Query for N child items (1-to-many) for each item in a set (without window functions)

Question

I have a chat type system stored in spanner, with tables like:

Table: Chat
 - ChatId STRING(64)
 - ... etc ...

Table: Message
 - MessageId  STRING(64)
 - ChatId     STRING(64)
 - CreateTime TIMESTAMP

I'm trying to figure out a decent-performing way to query for the most recent N messages for each chat in a set of ChatIds. So given 10 chat Ids, I'd like to query for 20 messages that includes the most recent 2 from each of the 10 chats.

I think window functions would normally be suitable for this, but apparently Spanner doesn't support those.

Is this possible without doing 10 queries for messages ordered by CreateTime with LIMIT N

Answer 1

Yes it is possible to do it in one query. I tried it on my end

Example code:

SELECT 
  * 
FROM 
  (
    SELECT 
      chatid, 
      messageid, 
      createtime, 
      RANK() OVER(
        PARTITION BY chatid 
        ORDER BY 
          createtime DESC
      ) AS mostrecent 
    FROM 
      Message
  ) 
WHERE 
  chatid IN (insert chatids) 
  AND mostrecent <= N

Hope it helps