...

Order-based Calculations – Get Specified Records and their Next Ones

 
SQL(179)

【Question】

Date

Property1

Property2

2014/11/12

4

88

2014/11/13

5

33

2014/11/14

7

200

2014/11/15

15

50

2014/11/16

2

66

2014/11/17

4

22

2014/11/18

2

70

2014/11/19

58

1

2014/11/20

51

53

2014/11/21

4

2

2014/11/22

4

142

2014/11/23

58

8

I want a MySQL query to find out the records where Property2 value is greater 100 and their next records. The expected result:

2014/11/14

7

200

2014/11/15

15

50

2014/11/16

4

142

2014/11/17

58

8

 

【Answer】

MySQL doesn’t support window functions. Subqueries are needed to complete this order-based calculation. The query can be like this: select * from t0055 t1 where Property 2 >100 or Date in (select Date +1 from t0055 where Property 2>100).

This query is based on continuous dates. If dates are not continuous, we need to create sequence numbers for them with a sub-subquery.

SPL (Structured Process Language) supports order-based calculations. It’s simple to handle both continuous and discontinuous dates. For your problem, here’s the SPL script:

A

1

$select * from t0055 order by Date

2

=A1.pselect@a(Propety 2>100)

3

=A1(A2.conj([~,~+1]))

Below is the final result:

 undefined

The SPL script can be embedded into an application through esProc JDBC interface. See How to Call an SPL Script in Java.


SQL(179)
 
 •  353 View  •