jmakineni

Help writing a query to get prior and next points of a range.

Discussion created by jmakineni on Apr 8, 2013
Latest reply on Apr 12, 2013 by michaelh

I have a requirement to query a PI data source where I accept the following arguments:

 

a set of tags

 

a start date

 

an end date

 

I want to return the results as tag, time, and value; but I want to also (for each tag) determine the very first point that lies before the start of the range and the very first point that lies after the end of the range. If the prior point does not exist I don't fetch a row. If the latter point does not exist (the one after the range end), then I'd like to create a row where the tag and value are the prior row's tag and value, and the time is equal to the end date.

 

If I could do this in a single query that would be great!

 

But my current approach relies on something programmatic (employing JDBC API) with at best an O(2n + 1) performance (where n represents a tag).  Hardly acceptable for a large number of tags.

 

Here are the queries I use

 

private static final String DEFAULT_QUERY = "select tag, time, value from "
            + DEFAULT_TABLE + " where time between ? and ?";

    private static final String FIRST_POINT_QUERY = "select top 1 tag, time, value from " + DEFAULT_TABLE + " where time < ? and tag = ? order by tag, time desc";

    private static final String LAST_POINT_QUERY = "select top 1 tag, time, value from " + DEFAULT_TABLE + " where time > ? and tag = ? order by tag, time";

    private static final String QUERY_WITH_TAG_RESTRICTIONS = DEFAULT_QUERY
            + " and (%s)";

 

Note the ? occurrences above are parameter substitutions.

 

 

 

I generate queries like (with the latter two being issued for each tag in a set)

 

 

 

select tag, time, value from picomp where time between '2012-09-03 00:14:00' and '2012-09-03 00:17:00' and (tag in ('RJUTGB-+1426P')) order by tag, time

select top 1 tag, time, value from picomp where time < ''2012-09-03 00:14:00' and (tag in ('RJUTGB-+1426P')) order by tag, time desc;

select top 1 tag, time, value from picomp where time > '2012-09-03 00:17:00' and (tag in ('RJUTGB-+1426P')) order by tag, time;

 

 

 

I'm very new to PI and the JDBC driver.  So, some assistance on tuning or outright replacing the above would be great.

 

Ultimately, I want construct period data (out of picomp) where

 

start =  2012-09-01 00:00:00

 

end = 2012-09-01 00:03:00

 

tags = { MTUSCU-+0878P, RJUTGB-+1426P }

 

 

 

MTUSCU-+0878P    2012-09-01 00:00:00    493.0    null
MTUSCU-+0878P    2012-09-01 00:01:00    493.0    null
MTUSCU-+0878P    2012-09-01 00:02:00    493.0    null
MTUSCU-+0878P    2012-09-01 00:03:00    493.0    null
RJUTGB-+1426P    2012-09-01 00:00:00    1031.9399414    null
RJUTGB-+1426P    2012-09-01 00:01:00    1032.5799561    null
RJUTGB-+1426P    2012-09-01 00:02:00    1032.0899658    null
RJUTGB-+1426P    2012-09-01 00:03:00    1032.0198975    null

 

becomes

 

[MTUSCU-+0878P] | 2012-09-01 00:00:00-0700-2012-09-01 00:01:00-0700 | 493
[MTUSCU-+0878P] | 2012-09-01 00:01:00-0700-2012-09-01 00:02:00-0700 | 493
[MTUSCU-+0878P] | 2012-09-01 00:02:00-0700-2012-09-01 00:03:00-0700 | 493

 

[RJUTGB-+1426P] | 2012-09-01 00:00:00-0700-2012-09-01 00:01:00-0700 | 1031.9399413999999524094164371490478515625
[RJUTGB-+1426P] | 2012-09-01 00:01:00-0700-2012-09-01 00:02:00-0700 | 1032.57995609999989028437994420528411865234375
[RJUTGB-+1426P] | 2012-09-01 00:02:00-0700-2012-09-01 00:03:00-0700 | 1032.089965800000072704278863966464996337890625

 

Thanks!

Outcomes