Key words: Ordered set     positions of records     position interval

There’s one computing scenario. Sort data, find two desired records according to a certain condition and calculate how many records there are between the two records.

It’s inconvenient to do this in SQL!

Because SQL doesn’t support ordered sets and t couldn’t explicitly express the position of a record. I say “couldn’t” because the language was improved in its 2003 standard with the introduction of window functions. So now we can number the records using the window function and then locate records with a series of subqueries. One example: we have STOCK table recording stocks’ closing prices and want to count the trading days between the last lowest closing price and the last highest closing price for stock 000062.

We do this in Oracle SQL, for example:

WITH T AS

    (SELECT *, ROW_NUMBER() OVER (ORDER BY STOCKDATE) RN

    FROM STOCK WHERE CODE='000062'),

T1 AS (SELECT * FROM T WHERE CLOSE = ( SELECT MIN(CLOSE) FROM T )),

T2 AS (SELECT * FROM T WHERE CLOSE = ( SELECT MAX(CLOSE) FROM T )),

T3 AS (SELECT * FROM T1 WHERE RN = ( SELECT MAX(RN) FROM T1 )),

T4 AS (SELECT * FROM T2 WHERE RN = ( SELECT MAX(RN) FROM T2 ))

SELECT ABS(MAX(T3.RN)-MAX(T4.RN)) FROM T3, T4;

It’s a difficult to read program as there are too many subqueries.

The code would be simple and clear if we could handle this in esProc SPL (Structured Process Language). Only two lines are sufficient.

SPL supports ordered set object and provides functions for getting positions of elements in a set. The code is intuitive and easy to read.

The SPL sets function library includes intersection, difference, union & concatenation, aggregates and loops. Refer to SPL Set and SPL Order to learn more.

SPL is integration-friendly with a Java program. Read How to Call an SPL Script in Java to learn details.

About how to work with esProc, read Getting Started with esProc.