mysql记录工作日和每小时的平均数据

bybem2ql  于 2021-06-23  发布在  Mysql
关注(0)|答案(1)|浏览(301)

我现在得到一个按小时和星期几分组的通话计数:

SELECT HOUR(creationtimestamp), dayname(creationtimestamp), count(*)
FROM cdrdb.session
WHERE DATE_SUB(creationtimestamp,INTERVAL 1 HOUR) And 
creationtimestamp > DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY HOUR(creationtimestamp);

返回:

0   Monday  23
0   Tuesday 96
0   Wednesday   85
0   Thursday    76
0   Friday  114
0   Saturday    47
0   Sunday  32
1   Monday  2
1   Tuesday 20
1   Wednesday   19
1   Thursday    16
1   Friday  31
1   Saturday    9
1   Sunday  6
2   Monday  13
2   Tuesday 18
2   Wednesday   5
2   Thursday    5
2   Friday  8

这一直持续到23小时,但这并不是我想要的。
我希望有60个左右的结果,毕竟是说和做,平均记录小时7上午至下午6点,所以我会有一个星期一到星期五的每一个小时的记录,在该时间框架的平均数量排。
也许看起来更像

Monday | 7:00 | 89
Monday | 8:00 | 53
Monday | 9:00 | 53
...
Tuesday | 8:00 | 53

我该如何为此更改分组
更新查询:
正确的小时数和天数,但平均数可能不正确。争取每周一早上8点到859点等的平均通话时间。

SELECT 
dayname(creationtimestamp) as day, 
HOUR(creationtimestamp) as Hour, 
(sum(callsIN/ 60) * 100 as averageCalls,
(sum(callsMissed)/sum(callsIN) * 100 as averageMissedCalls
  FROM session s
    WHERE (creationtimestamp >= '2018-01-01' AND creationtimestamp < now())
  and WEEKDAY(creationtimestamp) BETWEEN 0 AND 4
  AND HOUR(creationtimestamp) between  7 and 18 
  AND finallycalledpartyno IN ( 7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
  GROUP BY dayname(creationtimestamp),  HOUR(creationtimestamp)
  order by dayofweek(creationtimestamp), hour asc;
2uluyalo

2uluyalo1#

您可以按dayname(creationtimestamp)、hour(creationtimestamp)分组,并将结果限制为60

SELECT HOUR(creationtimestamp), dayname(creationtimestamp), count(*)
  FROM cdrdb.session
  WHERE DATE_SUB(creationtimestamp,INTERVAL 1 HOUR) And 
  creationtimestamp > DATE_SUB(NOW(), INTERVAL 1 DAY)
  AND HOUR(creationtimestamp) between  7 and 18 
  GROUP BY dayname(creationtimestamp),  HOUR(creationtimestamp) 
  ORDER BY dayname(creationtimestamp),  HOUR(creationtimestamp)  limit 60;

相关问题