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
相关产品推荐
相关产品推荐

