如何在SQL时序查询中获取每日最后一个有效（非空非零）值

你的需求是从时序数据中提取每日最后一个非空且非零的有效数值，即使当日后续有0或空值的填充记录，同时要支持任意日期范围的查询。

现有存储过程的问题 #

你当前的代码只排除了NULL值，但没有处理0值，导致当日最后一条记录如果是0，会被错误选为当日的有效数据，不符合你对"有效值"的定义。

修改后的解决方案 #

下面是调整后的存储过程，主要做了两处关键优化：

1. 新增日期范围参数，支持传入任意开始/结束日期，不再固定为当前月
2. 修正有效值的判断逻辑，同时排除NULL和0，确保排序时优先保留有效数据

CREATE OR ALTER PROCEDURE dbo.Weekly_CSS1_LT_Coasting
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
SET NOCOUNT ON;

    DECLARE @DummyDate DATE = DATEADD(DAY, -1, @StartDate);  -- 上月最后一天（可选行）
    
    -- 生成指定日期范围内的所有日期
    ;WITH DateList AS (
        SELECT @StartDate AS [Date]
        UNION ALL
        SELECT DATEADD(DAY, 1, [Date])
        FROM DateList
        WHERE DATEADD(DAY, 1, [Date]) < @EndDate
    ),
    
    -- 标记有效数据并按规则排序
    ValidHistory AS (
        SELECT 
            CAST(H.DateTime AS DATE) AS [Date],
            H.Value,
            H.DateTime,
            ROW_NUMBER() OVER (
                PARTITION BY CAST(H.DateTime AS DATE)
                ORDER BY 
                    -- 优先排序有效值（非空且非零），再按时间倒序
                    CASE WHEN H.Value IS NOT NULL AND H.Value <> 0 THEN 0 ELSE 1 END,
                    H.DateTime DESC
            ) AS rn
        FROM Runtime.dbo.History H
        WHERE 
            H.TagName = 'CSS1_Streetlight.LT_KWH_PerDay_Costing'
            AND H.wwRetrievalMode = 'Cyclic'
            AND H.wwCycleCount = 100
            AND H.wwQualityRule = 'Extended'
            AND H.wwVersion = 'Latest'
            AND H.DateTime >= @StartDate
            AND H.DateTime < @EndDate
    ),
    
    -- 提取每日最后一个有效值
    LastValidValue AS (
        SELECT [Date], Value
        FROM ValidHistory
        WHERE rn = 1
        -- 过滤掉当日没有有效值的情况（避免rn=1是无效数据）
        AND Value IS NOT NULL AND Value <> 0
    )
    
    -- 最终结果：包含可选的上月最后一天，确保所有日期都有输出
    SELECT @DummyDate AS [Date], 0 AS TotalCombinedValue
    UNION ALL
    SELECT 
        D.[Date],
        ISNULL(L.Value, 0) AS TotalCombinedValue
    FROM DateList D
    LEFT JOIN LastValidValue L ON D.[Date] = L.[Date]
    ORDER BY [Date]
    OPTION (MAXRECURSION 0);

END;

关键逻辑说明 #

1. 参数化日期范围：新增@StartDate和@EndDate参数，调用时可以传入任意日期区间（比如整月'2024-01-01'到'2024-02-01'，或者全年'2024-01-01'到'2025-01-01'）
2. 有效值排序规则：在ROW_NUMBER的ORDER BY中，用CASE语句把非空且非零的记录标记为0，其他（空或0）标记为1，这样有效值会排在前面，再按时间倒序，确保每个日期分区里的第一条（rn=1）就是当日最后一个有效记录
3. 过滤无效数据：在LastValidValue中额外过滤掉无效数据，避免当日没有有效值时，把最后一条0或空值当成有效数据返回
4. DateList CTE：生成连续日期，确保即使某天没有任何数据，也能返回一行0值，保证结果集的完整性

调用示例 #

如果要查询2024年3月的数据，执行：

EXEC dbo.Weekly_CSS1_LT_Coasting @StartDate = '2024-03-01', @EndDate = '2024-04-01';

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