SQL嵌套查询需求：指定日期范围按小时统计平均库存订单数

需要实现输入任意日期范围，返回该范围内每天各小时的平均订单数，要求包含无订单的小时（计为0），且无订单的日期也要纳入平均值计算。

基础表结构（MyInventoryOrdersTable） #

现有内层查询（按小时、日统计订单数） #

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)

之前的尝试及问题 #

用外层查询包裹时，仅返回最后一小时的数据，无法得到0-23小时的逐时平均值，且未处理无订单的日期/小时：

SELECT MAX([hr]) AS 'Hour', AVG([cnt]) AS 'Average'
FROM (
    -- 内层查询代码
) r;

期望结果示例 #

解决方案（SQL Server 适用） #

核心思路：生成完整的日期列表和0-23小时列表，然后与订单统计数据做左连接，确保所有日期+小时组合都被包含，最后按小时计算平均值。

完整代码 #

DECLARE @StartDate DATETIME = '2023-08-01 00:00:00.000';
DECLARE @EndDate DATETIME = '2023-08-03 00:00:00.000';

WITH DateRange AS (
    SELECT CAST(@StartDate AS DATE) AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM DateRange
    WHERE [Date] < CAST(@EndDate AS DATE)
),
HoursList AS (
    SELECT 0 AS [Hour]
    UNION ALL
    SELECT [Hour] + 1
    FROM HoursList
    WHERE [Hour] < 23
),
DateHourCombination AS (
    SELECT 
        dr.[Date],
        hl.[Hour]
    FROM DateRange dr
    CROSS JOIN HoursList hl
),
OrderStats AS (
    SELECT
        CAST(EnteredOn AS DATE) AS [Date],
        DATEPART(HOUR, EnteredOn) AS [Hour],
        COUNT(*) AS [OrderCount]
    FROM 
        MyInventoryOrdersTable
    WHERE
        Workcell = 'Workcell1' 
        AND EnteredOn BETWEEN @StartDate AND @EndDate
    GROUP BY
        CAST(EnteredOn AS DATE),
        DATEPART(HOUR, EnteredOn)
)
SELECT
    dhc.[Hour] AS [小时(Hour)],
    ROUND(AVG(ISNULL(os.OrderCount, 0)), 2) AS [平均值(Average)]
FROM DateHourCombination dhc
LEFT JOIN OrderStats os ON dhc.[Date] = os.[Date] AND dhc.[Hour] = os.[Hour]
GROUP BY dhc.[Hour]
ORDER BY dhc.[Hour];

关键说明 #

1. 递归CTE生成日期和小时：确保覆盖指定范围内的所有日期，以及0-23小时，避免遗漏无订单的时段。
2. CROSS JOIN生成所有组合：得到日期×小时的全量组合，保证每个日期的每个小时都被统计。
3. LEFT JOIN订单数据：将实际订单数与全量组合关联，无订单的时段用ISNULL补0。
4. 按小时分组求平均：基于全量的日期+小时组合计算平均值，自然包含无订单的日期/小时。

内容的提问来源于stack exchange，提问作者Bushmatic