SQL - 嵌套查询问题 - 需要对指定日期范围内的计数进行平均-解网

问：

我无法让嵌套查询在 Microsoft SQL Server Management Studio 19 * 中按照我想要的方式运行。下面是基表的样子。每个条目代表一件库存的内部订单。

编号	工作单元	部分编号	进入	徽章编号
1	工作单元1	511111一安	2023-08-01 13:28:04.240	1234
2	工作单元2	邮编：591222B	2023-08-01 14:10:05.100	1234
3	工作单元1	邮编：591222B	2023-08-02 13:09:51.855	0505
4	工作单元1	610000安培	2023-08-02 14:19:20.050	9876

这是“内部”查询，它给出了这样的结果。

SELECT
    DATEPART(HOUR, EnteredOn) AS [hr],
    COUNT(*) AS [cnt],
    DATEPART(DAY, EnteredOn) AS 'Day',
    DATEPART(MONTH, EnteredOn) AS 'Month',
    DATEPART(YEAR, EnteredOn) AS 'Year'
FROM 
    MyInventoryOrdersTable
WHERE
    Workcell = 'Workcell1' 
    AND EnteredOn BETWEEN '2023-08-01 00:00:00.000' AND '2023-08-03 00:00:00.000'
GROUP BY
    DATEPART(HOUR, EnteredOn),
    DATEPART(DAY, EnteredOn),
    DATEPART(MONTH, EnteredOn),
    DATEPART(YEAR, EnteredOn)

我希望能够输入任何日期范围（这将通过 Python / Ignition 8.1 单独完成 - 已经弄清楚了这部分），以便让 SQL 返回该日期范围内每小时的平均“计数”——这将是上面的“内部”查询找到的天数的平均值。因此，我们可以看到一天中通常的“繁忙时间”......

这是我走了多远：

我一开始尝试用这个包装整个查询......

SELECT MAX([hr]) AS 'Hour', AVG([cnt]) AS 'Average'
FROM (

...最后......

)
r;

...但这只是给了我过去一个小时的数据，而不是从 0 到 23 的每个小时的平均值。然后，当我意识到：

在计算此平均值时，我将需要几天没有任何数据来提供值“0”。例如，如果我抓取了一周的数据块，其中一个或多个天的输出为 0，我需要查询来识别该数据，并在平均该周的输出时将其考虑在内，即使它没有抓取任何条目来反映这一点。我不知道最好的方法。

想知道是否有任何 SQL 专家可以帮助我指出正确的方向。对于本文顶部提供的示例数据，我的预期结果表如下所示：

小时	平均
0	0.00
1	0.00
2	0.00
3	0.00
4	0.00
5	0.00
6	0.00
7	0.00
8	0.00
9	0.00
10	0.00
11	0.00
12	0.00
13	1.00
14	0.50
15	0.00
16	0.00
17	0.00
18	0.00
19	0.00
20	0.00
21	0.00
22	0.00
23	0.00

SQL Server 计数嵌套平均

SELECT HOUR, FORMAT(SUM(Average),'N2') Average FROM
( SELECT DATEPART(HOUR, EnteredOn) Hour,
CAST(COUNT(1) AS FLOAT)/DATEDIFF(day,'2023-08-01 00:00:00.000', '2023-08-03 00:00:00.000') Average 
FROM MyInventoryOrdersTable
WHERE Workcell = 'Workcell1' AND EnteredOn BETWEEN '2023-08-01 00:00:00.000' AND '2023-08-03 00:00:00.000'
GROUP BY DATEPART(HOUR, EnteredOn)
UNION ALL
SELECT Hour, 0.00 Average from
(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)) as HourList(Hour)
) OutputTable
GROUP BY Hour
ORDER BY Hour;

CAST(COUNT(1) AS FLOAT)将获取特定小时内的订单总数并转换为浮点数以获得浮动除法结果。 - 它计算日期范围内的天数。特定小时内的订单数/天数将等于给定日期范围内特定小时内特定小时内的订单平均值。 HourList 为我们提供了一天中的所有小时和 0.00 的平均值，因为将 0 添加到实际平均值不会产生任何差异。
通过使用 UNION ALL 和 GROUP BY，我们可以获得一天中所有时间的平均值。DATEDIFF(day,'2023-08-01 00:00:00.000', '2023-08-03 00:00:00.000')

我们可以在 SQLFiddle 上检查我的执行和输出

排除的代码：- 我们可以使用缩放器变量来存储开始日期和结束日期。

DECLARE @startdate DATETIME = '2023-08-01 00:00:00.000';  
DECLARE @enddate   DATETIME = '2023-08-03 00:00:00.000';

SQL - 嵌套查询问题 - 需要对指定日期范围内的计数进行平均

SQL - Trouble with nested query - Need to average counts over a specified date range

评论

评论