我目前正在获得按小时和工作日分组的计数:
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;
您可以使用按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;