I have a table TrendData that contains 3 columns. ConfigId, timestamp, and value. My database is SQLite and I will be sending this data through Java JDBC.
I need a single sql query that will get the data with an overall startTime to endTime. But the hard part is: I need that split overall time into X number of equal time ‘slices’. And I need a min value and max value for each slice for configId = Y. I will be constructing the query dynamically so java can plug in X and Y.
So for example. We have 10 rows in TrendData.:
We want it split into 5 samples of min max so X = 5. And Y in this case is 1.
The results I would expect are (TimeStart inclusive, TimeEnd exclusive):
The values for min and max time stamp in the results image don't appear to be correct. The first record is for 2 rows so that the min is 0 and max is 1 not 2. It is the second row which has a min time stamp = 2, etc.
One of the ways to come up with the query you need is via ntile windowing function. Generally speaking, I am not advocating the usage of any keywords as column names, but based on the table definition in question, there is no choice because 2 out of 3 column in the table are actually named after keywords. Here is the sample script which uses 2 parameters (restate it as needed to feed these from the outside call):
Based on the sample data in question, this script produces the following result:
If you don't need the first column in the results, remove it from the bottom select list.
Hope this helps.
answered Jul 11, 2017 at 02:11 PM