如何在SQL Server中从不同时间戳的两张表计算累计重量？

需要在SQL Server中，从两个时间戳不匹配的表计算2023年1月10日08:00:00至20:00:00时段内的累计重量。两张表数据来自不可控设备：

• current_product_weight表：记录产品切换时的单袋重量，存在设备重复记录
• total_pouches表：记录持续递增的总袋数（无重置）

表数据 #

current_product_weight表 #

total_pouches表 #

预期结果 #

需要获取2023年1月10日08:00:00至20:00:00的累计重量（为近似值，因无袋数生产精确时间）：

*计算示例：第三行72.5 = (20-10)1.75 + 55

尝试的SQL（未得到正确结果） #

select t_stamp, ([pouches]-lag([pouches]) * weight over (order by [t_stamp]) as 
accumulated_weight
from current_product_weight
inner join total_pouches on current_product_weight.t_stamp = total_pouches.t_stamp
where t_stamp between '1/10/2023 08:00:00' and '1/10/2023 20:00:00'

问题分析 #

• 仅通过t_stamp精确关联两张表，忽略了重量切换的时间区间，大部分袋数记录无法匹配到对应重量
• 窗口函数语法错误：lag([pouches])缺少括号，且未正确关联重量字段到对应的时段

解决方案 #

步骤说明 #

1. 处理重量表：对current_product_weight去重，获取每个重量的生效起始时间和结束时间
2. 关联时段与重量：将total_pouches的每个时间点匹配到对应的有效重量
3. 计算累计重量：先计算每个时段的新增重量，再累加得到累计值

完整SQL代码 #

WITH CleanedWeights AS (
    -- 去重并获取每个重量的生效时间区间
    SELECT 
        t_stamp AS weight_start_time,
        weight,
        LEAD(t_stamp) OVER (ORDER BY t_stamp) AS weight_end_time
    FROM (
        -- 去重，保留每个重量的最早生效时间
        SELECT 
            t_stamp,
            weight,
            ROW_NUMBER() OVER (PARTITION BY weight ORDER BY t_stamp) AS rn
        FROM current_product_weight
    ) w
    WHERE rn = 1
),
PouchesWithWeight AS (
    -- 为每个袋数记录匹配对应的有效重量
    SELECT 
        p.t_stamp,
        p.pouches,
        -- 匹配当前时间点之前最近生效的重量
        COALESCE(
            (SELECT TOP 1 weight 
             FROM CleanedWeights 
             WHERE weight_start_time <= p.t_stamp 
               AND (weight_end_time IS NULL OR weight_end_time > p.t_stamp)),
            0 -- 初始时段无重量记录时设为0
        ) AS current_weight,
        -- 获取上一个时段的袋数
        LAG(p.pouches, 1, 0) OVER (ORDER BY p.t_stamp) AS prev_pouches
    FROM total_pouches p
    WHERE p.t_stamp BETWEEN '2023-01-10 08:00:00' AND '2023-01-10 20:00:00'
)
-- 计算累计重量
SELECT 
    t_stamp,
    SUM((pouches - prev_pouches) * current_weight) OVER (ORDER BY t_stamp) AS accumulated_weight
FROM PouchesWithWeight
ORDER BY t_stamp;

代码解释 #

• CleanedWeights：通过去重和LEAD函数，得到每个重量的生效时间范围，避免重复记录干扰
• PouchesWithWeight：为每个袋数时间点匹配对应的有效重量，同时获取上一个时段的袋数用于计算新增量
• 最后通过窗口函数SUM累加每个时段的新增重量，得到累计值

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