mysql - Averaging based on week count in mysql

Question

I'm trying to finalize a query I have that is wanting an average of two metrics, inbound calls and missed calls. But I've never worked this granularly with the day of week and each hour block so I'm not sure if I'm even totalling correctly, let alone get the right average.

Basically, starting at 1/1/18, I want an average of inbound calls and missed calls for each hour from 7am to 6pm monday to friday. We were closed 7 days, and I'm getting 48 rows, so that's what I expect.

So if the last 3 mondays looked like:

creationtimestamp   | Legtype1  |  answered  

07/23/18 08:15:00   |     2     |     0             
07/23/18 08:25:00   |     2     |     1           
07/23/18 08:35:00   |     2     |     1               
07/30/18 08:15:00   |     2     |     0           
07/30/18 08:25:00   |     2     |     0           
07/30/18 08:35:00   |     2     |     0           
07/30/18 08:45:00   |     2     |     1           
07/30/18 08:55:00   |     2     |     0           
08/06/18 08:15:00   |     2     |     0           
08/06/18 08:25:00   |     2     |     1           
08/06/18 08:35:00   |     2     |     0           
08/06/18 08:45:00   |     2     |     0  

That's a total of 12 calls, 4 missed, for monday between 8 and 9 am. If I were querying those three mondays from 8 to 9 I would expect:

Monday | 8 | 4 | 1.3

But I can't figure out how to take the sum of all calls for each individual week day, sum those and divide by the number of that weekday? My query below I currently have ```SUM''' instead of average but I'm not sure how to take the average I needs since it's hinging on the number of each individual weekday.

SELECT 
    dayname(s.creationtimestamp) as day, -- weekdays
    HOUR(s.creationtimestamp) as Hour, -- Hours
    sum(case when legtype1 = 2 then 1 else 0 end) as total_calls, -- total inbound
    sum(case when  legtype1 = 2 and answered = 0 then 1 else 0 end)as total_missed

FROM session s
WHERE (s.creationtimestamp >= '2018-01-01' AND creationtimestamp < now())
and WEEKDAY(s.creationtimestamp) BETWEEN 0 AND 4 -- Monday through friday
AND HOUR(s.creationtimestamp) between  7 and 18 -- 7am to 6pm
GROUP BY dayname(s.creationtimestamp),  HOUR(s.creationtimestamp)
order by dayofweek(s.creationtimestamp), hour asc;

To reiterate: The query works but I'm not sure if I'm aggregating correctly based on each weekday and hour block from the 1st of the year to now.

here's a fiddle: http://sqlfiddle.com/#!9/7b6b72

标签： mysql

Answer 1

I think all things are fine for avg you just a bit more, hope below query will help you

select day,Hour,Avg(total_calls) as avg_total_calls,
Avg(total_missed) as avg_total_missed from
(
SELECT 
    dayname(s.creationtimestamp) as day, -- weekdays
    HOUR(s.creationtimestamp) as Hour, -- Hours
    sum(case when legtype1 = 2 then 1 else 0 end) as total_calls, -- total inbound
    sum(case when  legtype1 = 2 and answered = 0 then 1 else 0 end)as total_missed

FROM session s
WHERE (s.creationtimestamp >= '2018-01-01' AND creationtimestamp < now())
and WEEKDAY(s.creationtimestamp) BETWEEN 0 AND 4 -- Monday through friday
AND HOUR(s.creationtimestamp) between  7 and 18 -- 7am to 6pm
GROUP BY dayname(s.creationtimestamp),  HOUR(s.creationtimestamp)

) as T group by day,Hour 
order by day,Hour asc