SQL 聚合时间序列表（HourOfDay、Val）到工作日的 HourOfDay 平均值（例如周一 10:00-11:00、11:00-12:00、...、星期二...的平均值）

Question

到目前为止，我做了一个 SQL 查询，它为我提供了一个表，其中包含一天中每个小时处理的客户数量 - 给定任意开始和结束日期时间值（来自 Grafana 界面）。结果可能要持续数周时间。我的目标是按工作日实现每小时热图的平均值。

如何每小时汇总这些客户以显示每个工作日该小时的平均价值？

假设我在 19 天内每天获得 24 个值。如何聚合以便获得每个周一、周二、周三、周四、周五、周六、周日的 24 个值 - 每个小时代表这些天的平均值？

另外，仅使用整周的数据，因此去掉不属于完全代表周的一部分的前导日和尾随日（因此代表平均值的各个工作日的数量相同）。

下面是关于到目前为止我的 SQL 查询的返回结果的一段内容。 （每天的小时，客户数量）：

...    
    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
...

因此（示意性地，示例数据）startDate 2021-12-10 11:00 到 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
...
-------------------------------

为： 周五 10 点、周六 11 点、周日 12 点领先。 周二 28 日、周三 29 日、周四 30 日、周五 31 日。 每周平均工作时间

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

到目前为止我的方法是：

    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

Answer 1

您好，最简单的解决方案就是按照您在示例中编写的内容进行操作。创建自定义聚合基础。

因此，第一步是使用日期和日期在聚合表中准备数据。小时精度&客户数量。

然后创建基地。
这是基本思想的示例：

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

-- OUTPUT: Sunday 21:00

您可以连接数据，然后在 GROUP BY 子句中使用它。
根据您的用例调整此查询：

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'

这将完全按照您的需要创建基础。
您可以使用此逻辑来创建其他所需的聚合。基地。