sql - 如何查询用户与所有其他用户之间的评分平均差异

Question

我有一个 Postgres 图书评级表，其结构如下:

id 串行主键，
用户标识整数不为空，
bookid 整数不为空，
评级整数不为空

我想提取给定用户的评分与其他所有用户的评分之间的平均差异，分别考虑每一个。换句话说，作为一个用户，我想知道我的评分和一些 x 用户的评分之间的平均差异，对于所有 x。最终，我希望能够将这个平均数与给定用户和被比较用户的 ID 一起放入新的 SQL 表中。

我对 SQL 过去的简单查询非常陌生，我已经实现了一个在 Javascript 循环和 SQL 查询之间来回跳动的解决方案。如果有人愿意提供帮助，我正在寻找尽可能干净的东西。

编辑:这是一个简短的数据示例和理想输出。

id,userid,bookid,rating

1,1,1,5
2,1,2,2
3,1,3,3
4,1,4,3
5,1,5,1
6,2,1,5
7,2,2,2
8,3,1,1
9,3,2,5
10,3,3,3

这是理想的输出，结构为另一个 sql 表:

id serial primary key,
currentuser integer not null,
compareuser integer not null,
averagediff float not null

id,currentuser,compareuser,averagediff

1,1,2,0
2,1,3,2.33333

Answer 1

这可以通过自连接来完成。

select t1.userid as current_user,t2.userid as compare_user
,avg(abs(t1.rating-t2.rating)) as average_difference
from tbl t1
join tbl t2 on t1.userid<t2.userid and t1.bookid=t2.bookid
group by t1.userid,t2.userid