mysql - 如何在 MYSQL 中按列值分隔这些分组数据？

Question

我有一个具有以下结构的表格:

SELECT * FROM logs WHERE data = "Map load" 
ORDER BY timestamp DESC LIMIT 5

user_id timestamp            data
3       2017-03-29 18:11:25  Map load
2       2017-03-29 18:10:11  Map load
5       2017-03-29 18:02:07  Map load
5       2017-03-29 17:48:03  Map load
3       2017-03-29 17:38:48  Map load

我希望按 15 分钟间隔对行进行分组，我已经做到了。但是，我还需要按 user_id 分割这些数据。

按间隔分组:

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp)/900)*900) AS `Time`,
COUNT(*) AS `Map Load` FROM logs 
WHERE data = "Map load"
GROUP BY `Time` ORDER BY `Time` DESC LIMIT 3

Time                Map Load
2017-03-29 18:00:00 3
2017-03-29 17:45:00 1
2017-03-29 17:30:00 1

所需的结果布局:

Time                user_id=3 user_id=2 user_id=5
2017-03-29 18:00:00 1         1         1
2017-03-29 17:45:00 0         0         1
2017-03-29 17:30:00 1         0         0

我还有其他 60 个用户，因此除了在 15 分钟间隔时间戳上为每个 user_id 连接该表的副本之外，还有其他方法可以做到这一点吗？

Answer 1

您可以为此使用条件聚合:

select FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 900) * 900) as time,
    sum(user_id = 3) as User_id_3,
    sum(user_id = 2) as User_id_2,
    sum(user_id = 5) as User_id_5
from logs
where data = "Map load"
group by time
order by time desc LIMIT 3

如果您有更多的用户，最好不要在 SQL 中透视值，而是在应用程序代码中进行:

select FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 900) * 900) as time,
    user_id,
    count(*) as cnt
from logs l
join (
    select FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 900) * 900) as time
    from logs
    group by time
    order by time desc limit 3
    ) t on FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 900) * 900) = t.time
where data = 'Map load'
group by time,
    user_id
order by time desc;