SQL Snippets ™: Tutorials for Oracle Developers

Assembling the MOVEMENT_STRING_% columns in the preceding page was the hardest part. After that, searching for patterns in the test data is now easily accomplished with REGEXP_% functions. For example, this query finds stocks exhibiting a pure W pattern.

clear breaks

select stock_symbol
from   stock_price_movement
where  day = 11
and    regexp_like( MOVEMENT_STRING_UDA, 'D+U+D+U+' ) ;
 

STOCK_SYMB
----------
W1
 

These queries show two different ways to find stocks exhibiting a fuzzy W pattern. (I will refer to patterns that tolerate Across movement as "fuzzy" patterns.)

select stock_symbol
from   stock_price_movement
where  day = 11
and    regexp_like( MOVEMENT_STRING_UDA, 'A*D[DA]*U[UA]*D[DA]*U[UA]*' ) ;
 

STOCK_SYMB
----------
W1
W2
 

select stock_symbol
from   stock_price_movement
where  day = 11
and    regexp_like( MOVEMENT_STRING_UD, 'D+U+D+U+' ) ;
 

STOCK_SYMB
----------
W1
W2
 

This query finds stocks exhibiting a fuzzy V pattern over 4 days.

select stock_symbol
from   stock_price_movement
where  day = 11
and    regexp_like( MOVEMENT_STRING_UDA, '((DA)|(AD)|(DD))((UA)|(AU)|(UU))' ) ;
 

STOCK_SYMB
----------
V1
V2
W1
W2
 

This query flags days with a "1" when stock prices have gone Up or Across in the prior 4 days.

break on stock_symbol skip 1

select
  stock_symbol,
  day,
  movement_uda ,
  sign( regexp_instr( MOVEMENT_STRING_UDA, '[UA]{4}$' ) ) flag
from stock_price_movement ;
 

STOCK_SYMB        DAY M       FLAG
---------- ---------- - ----------
FLAT                1 A          0
                    2 A          0
                    3 A          0
                    4 A          1
                    5 A          1
                    6 A          1
                    7 A          1
                    8 A          1
                    9 A          1
                   10 A          1
                   11 A          1

V1                  1 A          0
                    2 D          0
                    3 D          0
                    4 D          0
                    5 D          0
                    6 D          0
                    7 U          0
                    8 U          0
                    9 U          0
                   10 U          1
                   11 U          1

V2                  1 A          0
                    2 A          0
                    3 D          0
                    4 A          0
                    5 D          0
                    6 A          0
                    7 U          0
                    8 A          0
                    9 U          1
                   10 A          1
                   11 U          1


STOCK_SYMB        DAY M       FLAG
---------- ---------- - ----------
V3                  1 A          0
                    2 A          0
                    3 A          0
                    4 U          1
                    5 A          1
                    6 U          1
                    7 A          1
                    8 D          0
                    9 A          0
                   10 D          0
                   11 D          0

W1                  1 A          0
                    2 D          0
                    3 D          0
                    4 D          0
                    5 U          0
                    6 U          0
                    7 D          0
                    8 D          0
                    9 U          0
                   10 U          0
                   11 U          0

W2                  1 A          0
                    2 A          0
                    3 A          0
                    4 D          0
                    5 U          0
                    6 A          0
                    7 A          0
                    8 D          0
                    9 U          0
                   10 A          0
                   11 A          0


STOCK_SYMB        DAY M       FLAG
---------- ---------- - ----------