You need to enable JavaScript to run this app. SQL Server Historian系统跨月度分区表动态SQL查询实现方案
优惠活动
大模型
产品
解决方案
定价
更多

SQL Server Historian系统跨月度分区表动态SQL查询实现方案

动态分区历史数据查询存储过程实现

核心实现逻辑

完全匹配需求的执行流程:

  • 传入标签名称先匹配对应标签ID
  • 传入起止时间戳匹配所有覆盖区间的分区表
  • 自动拼接所有匹配分区的查询语句,通过UNION ALL整合结果返回t_stamp、floatvalue字段

存储过程完整代码

CREATE OR ALTER PROCEDURE SP_GetHistorianData
    @tagName NVARCHAR(100),
    @end_time BIGINT, -- 参数顺序严格匹配调用要求:EXEC 存储过程名 '标签名', 结束时间戳, 开始时间戳
    @start_time BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 匹配获取标签ID
    DECLARE @tagID INT;
    SELECT @tagID = id 
    FROM sqlth_te 
    WHERE tagpath LIKE '%' + @tagName + '%';

    IF @tagID IS NULL
    BEGIN
        RAISERROR('未匹配到对应标签,请检查标签名称是否正确', 16, 1);
        RETURN;
    END

    -- 2. 匹配所有覆盖查询时间范围的分区表
    DECLARE @partitionTables TABLE (pname NVARCHAR(128));
    INSERT INTO @partitionTables (pname)
    SELECT pname 
    FROM sqlth_partitions 
    WHERE end_time >= @start_time 
      AND start_time <= @end_time;

    -- 校验是否存在匹配的分区
    IF NOT EXISTS (SELECT 1 FROM @partitionTables)
    BEGIN
        RAISERROR('指定时间范围内无对应分区数据', 16, 1);
        RETURN;
    END

    -- 3. 拼接动态SQL
    DECLARE @sql NVARCHAR(MAX) = N'';

    SELECT @sql = @sql + 
        N'SELECT t_stamp, floatvalue 
          FROM ' + QUOTENAME(pname) + N' 
          WHERE tagid = @tagIDParam 
            AND t_stamp >= @startParam 
            AND t_stamp <= @endParam 
          UNION ALL '
    FROM @partitionTables;

    -- 移除最后多余的UNION ALL关键字
    SET @sql = LEFT(@sql, LEN(@sql) - LEN('UNION ALL '));

    -- 包裹外层查询并按时间排序
    SET @sql = N'SELECT t_stamp, floatvalue 
                 FROM (' + @sql + N') AS t 
                 ORDER BY t_stamp;';

    -- 参数化执行动态SQL,避免注入风险
    EXEC sp_executesql 
        @stmt = @sql,
        @params = N'@tagIDParam INT, @startParam BIGINT, @endParam BIGINT',
        @tagIDParam = @tagID,
        @startParam = @start_time,
        @endParam = @end_time;
END

调用示例

完全匹配要求的调用格式:

-- 查询标签test_tag 对应时间范围内的历史值
EXEC SP_GetHistorianData 
    'test_tag', 
    1612127027358, -- 结束时间戳
    1611489115070; -- 开始时间戳

关键设计说明

  • 所有分区表名通过QUOTENAME处理,避免表名带特殊字符引发语法错误
  • 动态SQL执行时采用参数化传值,不直接拼接标签ID、时间戳到SQL字符串中,兼顾安全性和执行性能
  • 每个分区表查询都增加t_stamp范围过滤,避免全分区扫描,提升大数据量下的查询效率
  • 内置标签不存在、无匹配分区的异常抛出逻辑,方便排查问题

内容的提问来源于stack exchange,提问作者tamer salem

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.03 21:15:05