我现在得到一个按小时和星期几分组的通话计数:
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;
1条答案
按热度按时间2uluyalo1#
您可以按dayname(creationtimestamp)、hour(creationtimestamp)分组,并将结果限制为60