SQL-Aggregate Timeseries Table (HourOfDay, Val) to Average Value of HourOfDay by Weeekday (fi. Avg of Mondays 10:00-11:00, 11:00-12:00,...,Tue...)

Question

So far I made an SQL query that provides me with a table containing the amount of customers handled for each hour of the day - given a arbitrary start and an end datetime value (from Grafana interface). The result might be over many weeks. My goal is to implement an hourly heatmap by weekday with averaged values.

How do I aggregate those customer per hour to show the average value of that hours per weekday?

So let's say I got 24 values per day over 19 days. How do I aggregate so I get 24 values for each mon, tue, wed, thu, fri, sat, sun - each hour representing the average value for those days?

Also only use data of full weeks, so strip leading and trailing days, that are not part of a fully represented week (so same amount of individual weekdays representing an average value).

Here is a segment on how the return of my SQL query looks so far. (hour of each day, number of customers):

...    
    2021-12-13 11:00:00 |   0
    2021-12-13 12:00:00 |   3
    2021-12-13 13:00:00 |   4
    2021-12-13 14:00:00 |   4
    2021-12-13 15:00:00 |   7
    2021-12-13 16:00:00 |   17
    2021-12-13 17:00:00 |   12
    2021-12-13 18:00:00 |   18
    2021-12-13 19:00:00 |   15
    2021-12-13 20:00:00 |   8
    2021-12-13 21:00:00 |   10
    2021-12-13 22:00:00 |   1
    2021-12-13 23:00:00 |   0
    2021-12-14 00:00:00 |   0
    2021-12-14 01:00:00 |   0
    2021-12-14 02:00:00 |   0
    2021-12-14 03:00:00 |   0
    2021-12-14 04:00:00 |   0
    2021-12-14 05:00:00 |   0
    2021-12-14 06:00:00 |   0
    2021-12-14 07:00:00 |   0
    2021-12-14 08:00:00 |   0
    2021-12-14 09:00:00 |   0
    2021-12-14 10:00:00 |   12
    2021-12-14 11:00:00 |   12
    2021-12-14 12:00:00 |   19
    2021-12-14 13:00:00 |   11
    2021-12-14 14:00:00 |   11
    2021-12-14 15:00:00 |   12
    2021-12-14 16:00:00 |   9
    2021-12-14 17:00:00 |   2
...

So (schematically, example data) startDate 2021-12-10 11:00 to endDate 2021-12-31 17:00

-------------------------------
...
Mon 2021-12-13 12:00  | 3
Mon 2021-12-13 13:00  | 4
Mon 2021-12-13 14:00  | 4
...
Mon 2021-12-20 12:00  | 1
Mon 2021-12-20 13:00  | 6
Mon 2021-12-20 13:00  | 2
...
Mon 2021-12-27 12:00  | 2
Mon 2021-12-27 13:00  | 2
Mon 2021-12-27 13:00  | 3
...
-------------------------------

into this: strip leading fri 10., sat 11., sun 12. strip trailing tue 28., wen 29., thu 30., fri 31. average hours per weekday

-------------------------------
...
Mon 12:00  | 2
Mon 13:00  | 4
Mon 14:00  | 3
...
Tue 12:00  | x
Tue 13:00  | y
Tue 13:00  | z
...
-------------------------------

My approach so far:

    WITH CustomersPerHour as (
      SELECT dateadd(hour, datediff(hour, 0, Systemdatum),0) as DayHour, Count(*) as C 
      FROM CustomerList
      WHERE CustomerID > 0
        AND Datum BETWEEN '2021-12-010T11:00:00Z' AND '2021-12-31T17:00:00Z'
        AND EntryID IN (62,65)
        AND CustomerID IN (SELECT * FROM udf_getActiveUsers())
        GROUP BY dateadd(hour, datediff(hour, 0, Systemdatum), 0)
    )
    
    -- add null values on missing data/insert missing hours
    SELECT DATEDIFF(second, '1970-01-01', dt.Date) AS time, C as Customers
    FROM dbo.udf_generateHoursTable('2021-12-03T18:14:56Z', '2022-03-13T18:14:56Z') as dt
        LEFT JOIN CustomersPerHour cPh ON dt.Date = cPh.DayHour
    ORDER BY
      time ASC

score 1 · Accepted Answer · answered Mar 13 '22 at 20:32

Hi simpliest solution is just do what you have written in example. Create custom base for aggregation.

So first step is to prepare your data in aggregated table with Date & Hour precision & customer count.

Then create base. This is example of basic idea:

-- EXAMPLE
SELECT 
DATENAME(WEEKDAY, GETDATE()) + ' ' + CAST(DATEPART(HOUR, GETDATE()) + ':00' AS varchar(8)) 

-- OUTPUT: Sunday 21:00

You can concatenate data and then use it in GROUP BY clause. Adjust this query for your use case:

SELECT 
DATENAME(WEEKDAY, <DATETIME_COL>) + ' ' + CAST(DATEPART(HOUR, <DATETIME_COL>) AS varchar(8)) + ':00' as base
,SUM(...) as sum_of_whatever
,AVG(...) as avg_of_whatever
FROM  <YOUR_AGG_TABLE>
GROUP BY DATENAME(WEEKDAY, <DATETIME_COL>) + ' ' + CAST(DATEPART(HOUR, <DATETIME_COL>) AS varchar(8)) + ':00'

This create base exactly as you wanted. You can use this logic to create other desired agg. bases.

SQL-Aggregate Timeseries Table (HourOfDay, Val) to Average Value of HourOfDay by Weeekday (fi. Avg of Mondays 10:00-11:00, 11:00-12:00,...,Tue...)

1 Answers1