x

SQL Query Help

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.:

alt text

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):

alt text

untitled.png (3.8 kB)
untitled2.png (5.0 kB)
more ▼

asked Jul 11 at 01:30 PM in Default

avatar image

Tacitus86
10 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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):

 declare @x int = 5, @y int = 1;
 
 ; with data as (
     select 
         ntile(@x) over (order by [TimeStamp]) RecordNumber, * 
         from dbo.TrendData
         where ConfigID = @y
 )
     select
         RecordNumber, min([TimeStamp]) TimeStart, max([TimeStamp]) TimeEnd,
         min([Value]) MinValue, max([Value]) MaxValue
         from data
         group by RecordNumber
         order by RecordNumber;

Based on the sample data in question, this script produces the following result:

 RecordNumber         TimeStart   TimeEnd     MinValue    MaxValue
 -------------------- ----------- ----------- ----------- -----------
 1                    0           1           3           500
 2                    2           3           31          40
 3                    4           5           1           1
 4                    6           7           0           19
 5                    8           9           199         234

If you don't need the first column in the results, remove it from the bottom select list.

Hope this helps.

Oleg

more ▼

answered Jul 11 at 02:11 PM

avatar image

Oleg
17.8k 3 7 28

Hey Oleg,

You are indeed correct with the timestamps and your result is correct but would I be able to use this with SQLite? I seem to have forgotten to mention that in the original post and I have updated it accordingly. The inputs are no biggy because I will change them through java before sending the query. And no I don't need the first column. If you think this will work with SQLite I can try it out. Let me know! Thanks

Jul 11 at 02:18 PM Tacitus86

@Tacitus86 According to SQLite unsupported analytical functions list, ntile is not supported yet. I am not sure about the CTE either, so please restate the query to use the sub-select:

 select
     min([TimeStamp]) TimeStart, max([TimeStamp]) TimeEnd,
     min([Value]) MinValue, max([Value]) MaxValue
     from (
         select 
         ntile(@x) over (order by [TimeStamp]) RecordNumber, * 
         from dbo.TrendData
         where ConfigID = @y
     ) d
     group by RecordNumber;

Search for ntile equivalent, I am sure that the solution exists. From what I read online, many people are seriously disappointed by the ntile unavailability, and I am sure someone already came up with the workaround.

Jul 11 at 04:10 PM Oleg

Thanks @Oleg, I will look into it. I appreciate your assistance!

Jul 11 at 04:12 PM Tacitus86

@Oleg, I haven't been able to find an appropriate ntile replacement for SQLite. Any suggestions on where to look?

Jul 13 at 12:09 PM Tacitus86
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x67
x9
x7

asked: Jul 11 at 01:30 PM

Seen: 22 times

Last Updated: Jul 13 at 12:09 PM

Copyright 2017 Redgate Software. Privacy Policy