How to calculate moving-window values

Hello all,
I’m gonna try and generalize my quesition so the answers will be useful for more than just me.

The situation I’m facing is this: I have a machine, from which I can extract the current production count. I’ve enabled the historian on this tag, but I’m struggling to figure out how I can readily calculate the production rate for the timeframe in a way that doesn’t seem fragile, prone to poor performance and hard to comprehend for maintenance.

The current version I’m looking at basically has a script querying the tag historian for data in the timeframe and then selecting the largest from the resulting dataset. Then it’s querying for the calculated minimum in the window and comparing the values to get production over the timeframe, then that value is divided by the number time-units in the time frame(e.g. minutes in the hour) to get production per unit for the timeframe.

This script seems to be running on a timer in order to refresh regularly, Given fact that the count updates in near-realtime, it feels fundamentally wrong to be doing this chain of queries and slicing datasets in order to get such a basic value.

I’m given to think I might be missing something, but so far I can’t find a great alternative.

Even my attempts to condense the queries by looking for the maximum and minimum counts in the timeframe into one queryTagCalculations call, but the maximum value is significantly smaller than the highest value I get if I look up all the values in the timeframe and select the largest myself.

So the question is basically this: How do you do this without running a hefty script on every change and/or tick on the timer? Can this value be calculated when the count updates, without being too heavy to safely run at that kind of speed?

Consider scripting a FIFO of timestamps and values. Hold the FIFO in a list in your python script module for efficiency. On script reloads, preload your FIFO from the DB. Otherwise, when the tag changes or on some timer event, append to the FIFO list (timestamp and new value), and compute against the oldest entry. Remove the oldest from the list if it falls before your sliding window.

If you don’t want to script it, this post has a similar operation done with expression tags and functions from my free add-on module:

I don’t have any advice for doing it within python scripts, but this is a common problem in database programming. SQL Server, Oracle (and likely Postgres) have windowing functions built in. Are you running on any of these?

Oh, yes. I switched to Postgres ~15 years ago when they introduced window functions. Previously only available in Oracle. MySQL and MS SQL Server were both absurdly and disappointingly late to that party.

I still do FIFO solutions for these sorts of problems to avoid hitting the DB unnecessarily. And it can be really hard to use windowing functions against Ignition’s historian.

It is really hard to do quite a number of things against Ignition’s historian – we rolled our own.

2 Likes

When you say FIFO, are you suggesting basically a stack of key-value pairs? Then just do timestamp lookups from that to calculate the production rate?

Yes, but keep them in order. Insert new samples at the head. At every insert, pop off the tail (possibly multiple) to keep the oldest within the desired time window. Then do the delta math with head versus tail.

(If the fifo will be very large, consider using jython’s deque datatype instead of list.)

BTW, these sorts of analyses are easiest and most precise if the counters being monitored are odometers that are never reset. Then any quantity for a time period is simply the odometer value at the end of the period minus the odometer value at the start of the period.

Unfortunately based on the existing code, I’m suspecting that I can’t make such an assumption. The code to try and catch a reset is already in place