将表汇总并拆分为MySQL中的不同表

So I have this table which looks like this:

所以我有这个表看起来像这样:

user_id  location  duration(s)
-------  --------  -----------
1        room1     75
2        room1     289
1        room2     630
1        room1     287

The table above shows how long a user has been in a room, and the number of rows indicates how many times the user has been in a specific room. So for example in the above data, there are 2 rows in which user 1 was in location room1, meaning he has been in room1 twice. How would I go about taking this data in turning it into the table below:

上表显示了用户在房间中的时间长度,行数表示用户在特定房间中的次数。因此,例如在上面的数据中,有2行,其中用户1在位置room1中,这意味着他已经在room1中两次。我如何将这些数据转化为下表:

user_id  room_1_freq  room_1_duration  room_2_freq  room_2_duration
-------  -----------  ---------------  -----------  ---------------
1        2            181              1            630
2        1            289              0            0

In which room_1_freq and room_2_freq is the number of times the user has been in the respective rooms, and room_1_duration and room_2_duration is the average time that the user spends in each room. Can this be done in a single query?

room_1_freq和room_2_freq是用户在相应房间中的次数,room_1_duration和room_2_duration是用户在每个房间中花费的平均时间。这可以在一个查询中完成吗?

2 个解决方案

#1

If I understand correctly, this is just conditional aggregation:

如果我理解正确,这只是条件聚合:

select user_id,
       sum(location = 'room1') as room1_freq,
       sum(case when location = 'room1' then duration else 0 end) as room1_dur,
       sum(location = 'room2') as room2_freq,
       sum(case when location = 'room2' then duration else 0 end) as room1_dur
from t
group by user_id;

#2

There is a simpler (and probably more flexible way) of answering your question. Your secondary (results) table will extend exponentially with the number of columns for every room and every user that you need to report on and would, in my opinion, become difficult to manage. Plus, using Gordon's solution, you would have to rewrite your query every time a user or room were added.

有一种更简单(可能更灵活的方式)回答你的问题。您的辅助(结果)表将按照每个会议室和每个需要报告的用户的列数呈指数级扩展,并且在我看来会变得难以管理。另外,使用Gordon的解决方案,每次添加用户或房间时都必须重写查询。

It would be much easier to maintain if you summarised the data using standard sum, count and average functions within SQL with standard grouping clauses:

如果使用标准的sum,count和average函数在SQL中使用标准分组子句汇总数据,那么维护起来要容易得多:

SELECT user_id, location, count(location), sum(duration), avg(duration) 
FROM visits
group by user_id, location

This would give you the results you wish, but in a slightly different format:

这将为您提供所需的结果,但格式略有不同:

Doing it this way, you can add however many rooms and users you wish, and the summary info will always work. Adding date or time columns to filter results would also be very easy.

这样做,你可以添加你想要的许多房间和用户,总结信息将始终有效。添加日期或时间列以过滤结果也非常容易。

#1