Key words: SQL  Consecutively increasing  Order-based calculation

To get records containing a value consecutively increasing N times in SQL?

It’s hard!

The unordered-sets-based language didn’t even have the concept of order in its early days. Back then users had to create sequence numbers using JOIN to deal with scenarios such as the “consecutively increasing” problems. Later it introduced window function in 2003 standard to enable an easier but still inconvenient solution.

Here’s an instance. To find stocks that rises for consecutive n days according to database table stock:

The SQL solution:

WITH A AS

(SELECT CODE,STOCKDATE,(CLOSE-LAG(CLOSE) OVER (PARTITION BY CODE ORDER BY STOCKDATE) )CLOSE_UP FROM STOCK),

B AS

(SELECT CODE,

CASE WHEN CLOSE_UP>0 AND

LAG(CLOSE_UP) OVER (PARTITION BY CODE ORDER BY STOCKDATE)>0 AND

LAG(CLOSE_UP,2) OVER (PARTITION BY CODE ORDER BY STOCKDATE)>0

THEN 1 ELSE 0 END UPDAYS3 FROM A)

SELECT DISTINCT CODE FROM B WHERE UPDAYS3=1

Here 3 is static. It’s more complicated to query for 5 days and almost impossible to query for n days.

Yet there is a much easier alternative. You can handle such an order-based calculation in esProc SPL (Structured Process Language) with a two-line script:

To query for n days, you just need to change the days (here is 5) in A2 to n.

Since SPL is ordered-sets-based, it can provide an all-around support for handling order-relevant calculations. It locates an element in a set, a record for example, through the absolute or relevant position and expresses it simply and clearly. It thus can conveniently handle a scenario involving daily increase or decrease.

Besides location, esProc supports other order-relevant techniques, including untypical iterative aggregation, order-based loop and ranking. They are useful in simplifying the program. Find more examples in SPL Order and Inter-row Calculations.

esProc SPL is a scripting language good at offering simple and easy solutions to SQL headaches. It has simple syntax and is process-oriented with inherent support of step-by-step programming that agrees with human way of thinking. Its syntax is database independent but universal, which enables algorithms that can be seamlessly migrated between different database products. It is desktop based, ready to use, simple to configure and convenient to debug with breakpoint setting and step-by-step execution during which you can view the result of each step. See SQL Enhancer 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.