SQLite实现合并双向用户对话消息总数的统计查询

场景说明 #

使用SQLite数据库，有一张messages表存储消息数据，表结构如下：

• Id：整数类型
• body：字符串类型，消息内容
• from：UUID类型，发送者ID
• to：UUID类型，接收者ID
• inserted_at：日期时间类型，发送时间

需求是统计两个用户之间的总消息交互数，但使用初始查询：

SELECT "from", "to", COUNT(*) AS count
FROM messages
GROUP BY "from", "to";

会出现一个对话分两行的问题：比如用户117d8b9a-b089-45da-8e1c-879108bdbf1a给1646471d-63dc-40fc-b4c5-599f9f1e54d0发1条消息，对方回复3条，查询结果会分成两行，无法直接得到两人的总消息数（4条）。

当前查询结果示例：

From                                    To                                      Count
117d8b9a-b089-45da-8e1c-879108bdbf1a    1646471d-63dc-40fc-b4c5-599f9f1e54d0    1
1646471d-63dc-40fc-b4c5-599f9f1e54d0    117d8b9a-b089-45da-8e1c-879108bdbf1a    3
1646471d-63dc-40fc-b4c5-599f9f1e54d0    1d7a6815-91ab-4cc3-a1ba-b07e295945e2    1
1d7a6815-91ab-4cc3-a1ba-b07e295945e2    1646471d-63dc-40fc-b4c5-599f9f1e54d0    8

解决方案 #

使用CASE语句统一对话中两个用户的排序，将双向消息合并为一组统计：

SELECT 
  CASE 
    WHEN "from" < "to" THEN "from" 
    ELSE "to" 
  END AS user1,
  CASE 
    WHEN "from" < "to" THEN "to" 
    ELSE "from" 
  END AS user2,
  COUNT(*) AS count
FROM messages
GROUP BY user1, user2;

原理说明 #

通过比较两个UUID字符串的大小，将较小的UUID固定为user1，较大的为user2，不管消息是A发给B还是B发给A，都会被归到同一组(user1, user2)下，最终统计的count就是两人之间的总消息交互数。

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