mysql - 如何通过多重联接展平或正确分组

Question

我的结构

我有三个(假设的)表；用户、电影、 session 。

> SELECT * FROM users
+----+---------------------------+
| id | email                     |
+----+---------------------------+
|  5 | abcdefghijklmno@gmail.com |
+----+---------------------------+

> SELECT * FROM movies
+----+---------+---------+--------------+
| id |  title  | user_id | total_watches|
+----+---------+---------+--------------+
|  1 |  X-men  |    1    |     1        |
|  2 |  Blade  |    1    |     1        |
|  3 | Goonies |    1    |     1        |
+----+---------+---------+--------------+

> SELECT * FROM sessions
+----+---------+---------+------------+ 
| id | user_id | show_id | total_time |
+----+---------+---------+------------+
|  1 |       1 |       1 |          5 |
|  2 |       1 |       1 |         30 |
|  3 |       1 |       1 |          5 |
+----+---------+---------+------------+

我想要什么

我想在一次查询中了解用户的电影事件概览，因此希望以以下格式检索数据:

+----+---------------------------+---------------+----------------+
| id | email                     |  total_time   |  total_watches |
+----+---------------------------+---------------+----------------+
|  5 | abcdefghijklmno@gmail.com |       40      |        3       |
+----+---------------------------+---------------+----------------+

我尝试过的

SELECT users.id, users.email, SUM(movies.total_watches) AS total_watches, SUM(sessions.total_time) AS total_time
FROM users
JOIN movies ON users.id = movies.user_id 
JOIN sessions ON users.id = sessions.user_id 
GROUP BY users.id

这将返回(减去几列):

+------------------+---------------+---------------+
| email            | total_watches |   total_time  |
+------------------+---------------+---------------+
| abcdef@gmail.com |       9       |        120    | 
+------------------+---------------+---------------+

摘要

我知道额外的 session 连接会为每部电影创建三行，因此 SUM 结果增加了三倍，那么如何获得“扁平化”数据？我尝试过其他组的组合，但没有成功。

Answer 1

正如上面评论中所建议的，您当前的表结构需要进一步规范化。

现在，对于这个表结构，一种巧妙的方法是将 SUM 除以另一个表的行数(由于 JOIN 导致重复)，以抵消重复的影响。

因此，total_watches 的总和可以除以用户 ID 的 sessions 表中的行数。同样，total_time 的总和可以除以 movies 表中的行数。

SELECT users.id, 
       SUM(movies.total_watches)/COUNT(DISTINCT sessions.id) AS total_watches, 
       SUM(sessions.total_time)/COUNT(DISTINCT movies.id) AS total_time
FROM users
JOIN movies ON users.id = movies.user_id 
JOIN sessions ON users.id = sessions.user_id 
GROUP BY users.id

结果

| id  | total_watches | total_time |
| --- | ------------- | ---------- |
| 1   | 3             | 40         |

<小时/>

View on DB Fiddle