如何在AspenTech infoplus.21 historian中查询信号超阈值时间区间

要解决本地处理数据慢的问题，核心是利用infoplus.21的服务器端查询能力，直接在数据库层面完成阈值过滤和区间聚合，避免拉取全量采样数据到MATLAB。以下是可直接落地的实现方案：

一、infoplus.21 查询语句（核心逻辑） #

infoplus.21支持类SQL查询，通过关联查询识别信号从“低于阈值”到“高于阈值”的切换点（t1），以及从“高于阈值”到“低于阈值”的切换点（t2），最终聚合为连续的超阈值区间。

SELECT
  t1.TimeStamp AS StartTime,
  MIN(t2.TimeStamp) AS EndTime
FROM
  (SELECT TimeStamp, Value
   FROM [YOUR_TAG_FULL_PATH]
   WHERE Value > YOUR_THRESHOLD
     AND TimeStamp BETWEEN 'START_DATETIME' AND 'END_DATETIME') t1
LEFT JOIN
  (SELECT TimeStamp, Value
   FROM [YOUR_TAG_FULL_PATH]
   WHERE Value <= YOUR_THRESHOLD
     AND TimeStamp BETWEEN 'START_DATETIME' AND 'END_DATETIME') t2
ON t2.TimeStamp > t1.TimeStamp
WHERE NOT EXISTS (
  SELECT 1
  FROM [YOUR_TAG_FULL_PATH] t3
  WHERE t3.TimeStamp > t1.TimeStamp
    AND t3.TimeStamp < t2.TimeStamp
    AND t3.Value <= YOUR_THRESHOLD)
GROUP BY t1.TimeStamp
HAVING EndTime IS NOT NULL
UNION ALL
-- 处理查询结束时仍处于超阈值状态的区间
SELECT
  MIN(TimeStamp) AS StartTime,
  'END_DATETIME' AS EndTime
FROM [YOUR_TAG_FULL_PATH]
WHERE Value > YOUR_THRESHOLD
  AND TimeStamp BETWEEN 'START_DATETIME' AND 'END_DATETIME'
  AND NOT EXISTS (
    SELECT 1
    FROM [YOUR_TAG_FULL_PATH] t4
    WHERE t4.TimeStamp > TimeStamp
      AND t4.TimeStamp <= 'END_DATETIME'
      AND t4.Value <= YOUR_THRESHOLD)

关键参数说明 #

• [YOUR_TAG_FULL_PATH]：标签的完整路径，比如[Production/Line1.PressureSensor]，需用方括号包裹
• YOUR_THRESHOLD：阈值数值，比如100.0
• START_DATETIME/END_DATETIME：查询时间区间，格式为'yyyy-MM-dd HH:mm:ss'

二、MATLAB 调用实现 #

通过ODBC连接infoplus.21，将上述查询参数化后执行，直接获取聚合后的区间数据：

% 1. 建立ODBC连接（需提前配置指向infoplus.21的ODBC数据源）
conn = database('Infoplus21_DSN', 'your_username', 'your_password');

% 2. 定义查询参数
tagFullPath = '[Production/Line1.PressureSensor]';
threshold = 100.0;
startDt = datetime(2024, 5, 1, 0, 0, 0);
endDt = datetime(2024, 5, 2, 0, 0, 0);

% 3. 格式化时间为infoplus.21兼容的字符串
startStr = datestr(startDt, 'yyyy-mm-dd HH:MM:SS');
endStr = datestr(endDt, 'yyyy-mm-dd HH:MM:SS');

% 4. 构建参数化查询语句
query = sprintf([...
    'SELECT ' ...
    '  t1.TimeStamp AS StartTime, ' ...
    '  MIN(t2.TimeStamp) AS EndTime ' ...
    'FROM ' ...
    '  (SELECT TimeStamp, Value ' ...
    '   FROM %s ' ...
    '   WHERE Value > %f ' ...
    '     AND TimeStamp BETWEEN ''%s'' AND ''%s'') t1 ' ...
    'LEFT JOIN ' ...
    '  (SELECT TimeStamp, Value ' ...
    '   FROM %s ' ...
    '   WHERE Value <= %f ' ...
    '     AND TimeStamp BETWEEN ''%s'' AND ''%s'') t2 ' ...
    'ON t2.TimeStamp > t1.TimeStamp ' ...
    'WHERE NOT EXISTS (' ...
    '  SELECT 1 ' ...
    '  FROM %s t3 ' ...
    '  WHERE t3.TimeStamp > t1.TimeStamp ' ...
    '    AND t3.TimeStamp < t2.TimeStamp ' ...
    '    AND t3.Value <= %f) ' ...
    'GROUP BY t1.TimeStamp ' ...
    'HAVING EndTime IS NOT NULL ' ...
    'UNION ALL ' ...
    'SELECT ' ...
    '  MIN(TimeStamp) AS StartTime, ' ...
    '  ''%s'' AS EndTime ' ...
    'FROM %s ' ...
    'WHERE Value > %f ' ...
    '  AND TimeStamp BETWEEN ''%s'' AND ''%s'' ' ...
    '  AND NOT EXISTS (' ...
    '    SELECT 1 ' ...
    '    FROM %s t4 ' ...
    '    WHERE t4.TimeStamp > TimeStamp ' ...
    '      AND t4.TimeStamp <= ''%s'' ' ...
    '      AND t4.Value <= %f)' ...
], tagFullPath, threshold, startStr, endStr, ...
   tagFullPath, threshold, startStr, endStr, ...
   tagFullPath, threshold, ...
   endStr, tagFullPath, threshold, startStr, endStr, ...
   tagFullPath, endStr, threshold);

% 5. 执行查询并获取结果
results = fetch(conn, query);

% 6. 关闭连接
close(conn);

% 7. 转换时间戳为MATLAB datetime格式
results.StartTime = datetime(results.StartTime, 'InputFormat', 'yyyy-MM-dd HH:mm:ss');
results.EndTime = datetime(results.EndTime, 'InputFormat', 'yyyy-MM-dd HH:mm:ss');

三、注意事项 #

1. ODBC数据源配置：确保ODBC数据源指向infoplus.21的historian服务，可通过Windows ODBC数据源管理器配置
2. 标签路径格式：若标签包含特殊字符或层级，必须用方括号包裹完整路径
3. 版本兼容性：部分旧版infoplus.21可能不支持NOT EXISTS子句，可改用DATEDIFF判断连续采样点的时间间隔，筛选连续超阈值的段
4. 性能优化：若查询时间跨度极大，可拆分多个小时间区间分批查询，避免服务器负载过高

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