Alternatively, how would one write a query to return a column plus a running total column without window functions? What I’m looking for is a way to write a query to get records whose running total is between two values when running total is calculated ordered by date. I’m just getting familiar with SQL, so feel free to point out the obvious I may be missing!
This is on a table with >20k rows. Joining the table to itself times out. At this point I’m leaning towards retrieving the whole set and processing rows in a script transform to get what we need, though it seems like there should be a better way.
It turns out calculating a running total and selecting records within the desired running total window by scripting is quite responsive in Perspective even though it has to process 20k rows (usually selecting a window near the end). I’m guessing it was slow in the script console due to downloading the whole result set a slow connection before it could process it.
That all said, I think we’ll move this to a DB that supports windows.
I am still curious if there’s a way to get a newer version of SQLite in Ignition as it seems like a good solution for smaller amounts of data that is mostly static. I wouldn’t want to use it for anything that needs window functions unless we can get a current version that supports them.
Technically, you might be able to drop in a new sqlite JDBC driver (SQLite JDBC drivers are usually just pretty thin shims that call native SQLite code), but in practice that's probably not a good idea, if it works at all.
We had some issues with ARM when we last tried upgrading our bundled SQLite driver, but we can take another look and see if we can bump the version across all platforms.
