sql - 使用 SUM 的 SQL 查询问题

Question

我的数据库模式的相关部分如下所示(Ruby on Rails 迁移代码，但应该易于阅读):

create_table "team_memberships" do |t|
  t.integer  "team_id"
  t.integer  "user_id"
end

create_table "users" do |t|
  t.integer "id"
  t.string  "slug"
end

create_table "performance_points" do |t|
  t.integer "id"
  t.integer "user_id",
  t.date    "date",
  t.integer "points",
  t.integer "team_id"
end

我想要一个查询，该查询返回一个用户列表，该列表按照他们自某个日期以来获得的性能点总数排序。请注意，一个“performance_points”行不等于一个点，我们需要对“点数”求和

到目前为止，我的查询如下所示:

SELECT u.id, u.slug, SUM(pp.points) AS total
FROM users u
JOIN performance_points pp ON pp.user_id = u.id
JOIN team_memberships tm ON tm.team_id = pp.team_id AND tm.user_id = pp.user_id
WHERE (pp.date > '2015-08-02 13:57:14.042221')
GROUP BY pp.id, u.id
ORDER BY total DESC
LIMIT 50

前三个结果是:

"id","slug","total"
32369,"andreas-jensen-9de10dec-0f88-427f-b135-62cebea611c8",245
23752,"kenneth-kjaerstad",95
34179,"marius-mork-rydal",93

为了检查结果是否正确，我计算了每个用户的分数。然而，第二个似乎是错误的。我使用 Kenneth 的 ID 运行此查询:

SELECT SUM(performance_points.points)
FROM performance_points
WHERE performance_points.user_id = 23752
  AND (date > '2015-08-02 13:57:14.042221')

我得到:84。查看 Kenneth 的所有性能点:

SELECT performance_points.points
FROM performance_points
WHERE performance_points.user_id = 23752
  AND (date > '2015-08-02 13:57:14.042221')

我们得到:

"points"
-10
1
-2
95

-10 + 1 - 2 + 95 确实是 84 所以我不知道第一个查询发生了什么。为什么总数是95？

我正在运行 PostgreSQL 版本 9.3.5

Answer 1

如果 slug 每个用户都是唯一的:

SELECT u.id, u.slug, SUM(pp.points) AS total
FROM users u
JOIN performance_points pp
ON u.id = pp.user_id
WHERE pp.date > '2015-08-02 13:57:14.042221'
GROUP BY u.id, u.slug
ORDER BY total DESC
LIMIT 50

否则你不能SELECT slug 因为它不是分组列，所以每个组中都有它的多个值。你想在 performance_points 中GROUP BY user_id 以获得每个 user_id 的 total 然后 JOIN使用 users 获取 slug。

SELECT id, slug, total
FROM users
JOIN (
    SELECT user_id, SUM(points) AS total
    FROM performance_points
    WHERE date > '2015-08-02 13:57:14.042221'
    GROUP BY user_id) t
ON id = user_id
ORDER BY total DESC
LIMIT 50

(不清楚您为什么要JOINteam_membership。大概performance_points (user_id,team_id) 是它的外键，即所有这样的对已经在里面了。)