它可能,但是仅使用sql非常难看.但是,如果可以使用存储的函数,那么它的外观也很漂亮.
从您在问题中链接的SO问题,我们知道以下表达式可以计算两个日期之间的工作日数:
5 * (DATEDIFF(@E, @S) DIV 7) +
MID('0123455501234445012333450122234501101234000123450',
7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
如果我们将此表达式乘以9,即每个工作日#个工作时间,则会得到营业时间差异.将两个时间戳之间的小时调整相加得出最终表达式,然后我们可以求平均
45 * (DATEDIFF(@E, @S) DIV 7) +
9 * MID('0123455501234445012333450122234501101234000123450',
7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1) +
TIMESTAMPDIFF(HOUR, DATE(@E), @E) -
TIMESTAMPDIFF(HOUR, DATE(@S), @S)
因此,难看但有效的查询是:
SELECT
clients.name
, AVG(45 * (DATEDIFF(jobs.time_updated, jobs.time_created) DIV 7) +
9 * MID('0123455501234445012333450122234501101234000123450',
7 * WEEKDAY(jobs.time_created) + WEEKDAY(jobs.time_updated) + 1, 1) +
TIMESTAMPDIFF(HOUR, DATE(jobs.time_updated), jobs.time_updated) -
TIMESTAMPDIFF(HOUR, DATE(jobs.time_created), jobs.time_created)) AS average_response
, AVG(45 * (DATEDIFF(jobs.time_closed, jobs.time_created) DIV 7) +
9 * MID('0123455501234445012333450122234501101234000123450',
7 * WEEKDAY(jobs.time_created) + WEEKDAY(jobs.time_closed) + 1, 1) +
TIMESTAMPDIFF(HOUR, DATE(jobs.time_closed), jobs.time_closed) -
TIMESTAMPDIFF(HOUR, DATE(jobs.time_created), jobs.time_created)) AS average_closure
, COUNT(jobs.id) AS ticket_count
, SUM(time_total) AS time_spent
FROM jobs
LEFT JOIN clients ON jobs.client = clients.id
WHERE jobs.status = 'closed'
GROUP BY jobs.client
更好的选择是创建一个存储的函数来处理营业时间差异逻辑.
DROP FUNCTION IF EXISTS BUSINESSHOURSDIFF;
DELIMITER $$
CREATE FUNCTION BUSINESSHOURSDIFF(start_time TIMESTAMP, end_time TIMESTAMP)
RETURNS INT UNSIGNED
BEGIN
RETURN 45 * (DATEDIFF(end_time, start_time) DIV 7) +
9 * MID('0123455501234445012333450122234501101234000123450',
7 * WEEKDAY(start_time) + WEEKDAY(end_time) + 1, 1) +
TIMESTAMPDIFF(HOUR, DATE(end_time), end_time) -
TIMESTAMPDIFF(HOUR, DATE(start_time), start_time);
END $$
DELIMITER ;
然后根据需要调用它.
SELECT
clients.name
, avg(BUSINESSHOURSDIFF(jobs.time_created, jobs.time_updated)) AS average_response
, avg(BUSINESSHOURSDIFF(jobs.time_created, jobs.time_closed)) AS average_closure
, count(jobs.id) AS ticket_count
, SUM(time_total) AS time_spent
FROM jobs
LEFT JOIN clients ON jobs.client = clients.id
WHERE jobs.status = 'closed'
GROUP BY jobs.client;