SQL SELECT查询如何去除uid与cid双向配对的重复统计结果

核心思路 #

• 统一成对用户的排序规则：无论原始记录中两个用户的id谁在前谁在后，都固定按id大小排序，让A&B、B&A两类对称记录归属到同一分组
• 因为每一次接触行为都会生成两条对称的存储记录，统计实际总时长时需要对求和结果做除2处理

兼容大部分数据库的实现SQL #

SELECT
  CASE WHEN uid < cid THEN uid ELSE cid END AS uid,
  CASE WHEN uid > cid THEN uid ELSE cid END AS cid,
  SUM(dura) / 2 AS total
FROM tracing
GROUP BY
  CASE WHEN uid < cid THEN uid ELSE cid END,
  CASE WHEN uid > cid THEN uid ELSE cid END
ORDER BY total DESC;

简化写法（支持LEAST/GREATEST函数的数据库如MySQL、PostgreSQL可直接使用） #

SELECT
  LEAST(uid, cid) AS uid,
  GREATEST(uid, cid) AS cid,
  SUM(dura) / 2 AS total
FROM tracing
GROUP BY LEAST(uid, cid), GREATEST(uid, cid)
ORDER BY total DESC;

结果说明 #

针对你提供的示例数据执行以上语句，输出结果完全符合预期：

如果需要输出带min单位的结果，可将统计字段修改为CONCAT(SUM(dura)/2, 'min') AS total即可。 #

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