Average of volume by weekday and hour over range

Database
Enthusiast

Average of volume by weekday and hour over range

I'm trying to calculate the average number of calls for a given weekday by hour of the day.  I've tried nesting aggregates byt it always comes out to 1, rather than the actual average.

Here's what the data looks like

creation_ts    weekday   hour
2013-04-01 20:27:41 Mon  8:00 PM
2013-04-02 15:56:55 Tue  3:00 PM
2013-04-03 11:54:33 Wed  11:00 AM
2013-04-03 23:51:22 Wed  11:00 PM
2013-04-04 17:53:45 Thu  5:00 PM
2013-04-05 10:56:53 Fri  10:00 AM
2013-04-05 15:19:25 Fri  3:00 PM
2013-04-06 03:56:32 Sat  3:00 AM
2013-04-06 14:58:46 Sat  2:00 PM

I'm needing to generate a result set that has an average number of calls for the hour for each weekday.

Something like

Weekday  00:00  01:00   02:00   03:00   ....  22:00   23:00
Sun          0   2       4       5         18      16
Mon
Tue
Wed
Thu
Fri
Sat

I tried using the following statement (by an example for dnoeth in previous similar post) but it only totals accross all days hour, rather than average

**Weekday and "hour" are columns from my temp table where I've extracted both from the timestamp

SELECT
weekday,
"hour",
avg(cnt) as CountByDayOfWeek
from
  (SELECT
   weekday,
   "hour",
   count(*) as cnt
  from
  calls_table
  group by 1,2
  )calls
group by 1,2

Any help is greatly appreciated!

2 REPLIES
Senior Apprentice

Re: Average of volume by weekday and hour over range

You group both times by the same columns, if i understodd you correctly you have to group by date/hour first and then by weekday:

SELECT 
creation_dt (FORMAT 'eee') (CHAR(3))
,AVG(CASE WHEN "hour" = 0 THEN cnt END) AS "00:00"
,AVG(CASE WHEN "hour" = 1 THEN cnt END) AS "01:00"
,AVG(CASE WHEN "hour" = 2 THEN cnt END) AS "02:00"
...
FROM
(
SELECT
CAST(creation_ts AS DATE) AS creation_dt
,EXTRACT(HOUR FROM creation_ts) AS "hour"
,COUNT(*) AS cnt
FROM calls_table
GROUP BY 1,2
) AS dt
GROUP BY 1

Dieter

Enthusiast

Re: Average of volume by weekday and hour over range

Thank you Dieter! I wound up using the calendar to divide by the the number of days in the date range, rather than only days with data. You've helped me tremendously!

SELECT
dt.dayofweek,
calls."hour",
 cast(calls.CountbyDayofWeek as decimal(15,3)) / dt.num_of_days as AVG_COUNT
 from

 (select
       CASE   day_of_week
            WHEN 1 THEN 'Sun'
            WHEN 2 THEN 'Mon'
            WHEN 3 THEN 'Tue'
            WHEN 4 THEN 'Wed'
            WHEN 5 THEN 'Thu'
            WHEN 6 THEN 'Fri'
            WHEN 7 THEN 'Sat'
        END as DayofWeek,
       count(*) as num_of_days
from sys_calendar.calendar
where calendar_date Between '2013-03-07' AND '2013-05-07'
group by 1
)dt
join

(SELECT
 EXTRACT(HOUR FROM case_creation_ts) AS "hour" ,
Count (*) as CountbyDayofWeek, 
CASE ((cast(case_creation_ts as date) - Date '1900-01-01') Mod 7) +1
WHEN 7 THEN 'Sun'
WHEN 1 THEN 'Mon'
WHEN 2 THEN 'Tue'
WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu'
WHEN 5 THEN 'Fri'
WHEN 6 THEN 'Sat' END as DayofWeek
FROM calls_table
 Group by 1,3
 )calls on dt.dayofweek = calls.dayofweek
 
order by 1,2

I used the suggestion from Ulrich in a previous post, from: 

http://forums.teradata.com/forum/database/determine-average-count-by-day-of-week