如何计算指定时间区间内每12小时节点的总流量？SQL实现求助

问题背景 #

有10000条水订单数据，每条包含Start（datetime格式）、End（datetime格式）和Flow_rate字段，需计算2023年11月1日至8日期间每12小时节点（11:00和23:00）的总流量。

示例数据 #

t_flow表 #

期望输出 #

t_flow_total表 #

已完成步骤 #

已通过CTE生成指定日期内11:00和23:00的时间序列，代码如下：

;WITH CTE1 AS (
SELECT DATEADD(hh, 11, '2023-11-01 00:00:00') as Interval
UNION ALL
SELECT CAST(DATEADD(hh,12,Interval) as datetime)
FROM CTE1
WHERE Interval <= '2023-11-08 11:00:00'
)

SELECT * FROM CTE1

核心需求 #

需将生成的时间序列与t_flow表关联，逻辑为：当Start < Interval且End > Interval时，累加对应的Flow_rate，类似Excel的SUMIFS(Flow_rate, Start, "<"&Interval, End, ">"&Interval)。

解决方法 #

通过左连接+聚合函数即可实现需求，完整SQL代码如下：

;WITH CTE1 AS (
SELECT DATEADD(hh, 11, '2023-11-01 00:00:00') as Interval
UNION ALL
SELECT CAST(DATEADD(hh,12,Interval) as datetime)
FROM CTE1
WHERE Interval <= '2023-11-08 11:00:00'
)
SELECT 
    c.Interval,
    ISNULL(SUM(f.Flow_rate), 0) AS Total_flow
FROM CTE1 c
LEFT JOIN t_flow f 
    ON f.Start < c.Interval 
    AND f.End > c.Interval
GROUP BY c.Interval
ORDER BY c.Interval

逻辑说明 #

• 左连接确保所有时间节点都会被保留，即使没有匹配的订单数据
• ON子句过滤出覆盖当前时间节点的订单（订单开始时间早于节点，结束时间晚于节点）
• SUM(f.Flow_rate)累加符合条件的流量，ISNULL将空值转为0，匹配期望输出格式
• GROUP BY按时间节点分组，ORDER BY保证结果按时间顺序排列

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