MySQL查询生成打洞卡dataviz

Question

我有一个包含聊天历史记录的数据库，其中包含日期时间字段、消息的作者和消息。

我不想进行一个返回一个表的查询，该表将工作日与一天中的小时数相关联，计算发送的消息数量，使用 github 用于生成打洞卡图的相同格式:

[day, hour, number of messages]

[
[0,0,23],
[0,1,20],
[0,2,56],
[]...]

我尝试了这个 SQL 指令，但我认为不起作用。

select 
    dayofweek(date_time) as DAY, 
    hour(date_time) as `HOUR`, 
    sum(dayofweek(date_time)) as Msgs 
from chat
group by DAY(date_time)
order by `DAY`

我得到的返回是:

[[1,1,4734]
[1,20,4503]
[1,11,6510]
[1,0,7058]
[2,0,6518]
[2,8,3913]
[2,9,3885]
[2,0,2305]
[2,9,4471]
[2,0,3703]
[3,0,3315]
[3,0,9600]
[3,0,3910]
[3,9,4956]
[3,0,2692]
[4,0,5225]
[4,10,4815]
[4,0,7667]
[4,11,5249]
[4,0,5121]
[5,8,3148]
[5,10,4947]
[5,0,4109]
[5,0,4775]
[6,0,4970]
[6,0,4342]
[6,12,4488]
[6,0,7191]
[7,0,3790]
[7,9,9294]
[7,9,4749]]

我不知道发生了什么，但返回应该有 168 行，7 * 24，我在这里缺少什么？

谢谢!

Answer 1

问题是您仅按天分组，而不是按天和小时分组。所以而不是

select 
    dayofweek(date_time) as DAY, 
    hour(date_time) as `HOUR`, 
    sum(dayofweek(date_time)) as Msgs 
from chat
group by DAY(date_time)
order by `DAY`

做

select 
    dayofweek(date_time) as DAY, 
    hour(date_time) as `HOUR`, 
    sum(dayofweek(date_time)) as Msgs 
from chat
group by dayofweek(date_time), hour(date_time)
order by `DAY`,HOUR