We are using Ignition to plot a graph which represent a machine throughput in terms of pieces/min or pieces/hour. It’s a king of hour meter.
The graph works fine but the underlying data source is cumbersome and does not scale well as the dataset grow.
The plot moreover is bades on per shift timebase and the underlying data is basically a collection of rows with timestamp and each row represent a piece which has been processed by a machine. The graph refresh each 5 mins and plot a timespan of one shift and the graph start every shift from 0 as the meter is per shift based.
Currently we achieve this result via SQL creating using a PARTITION OVER clause which select a specific date and shift and creates a “sliding window” which group and calculate the machine speed by counting the number of rows (pieces) within a discrete timeframe (ususally minutes).
This allows any operator to query and display real-time data as well as historical by just chosing a specific date and shift. The same table is used to summarize the production per shift.
Does anybody knows a better way to achieve the same result without using an expensive SQL PARTITION clause? Eg. creating a different setup or using historian?