Test Data
The test data in the STOCK_PRICES table (created in the Setup topic) looks like this.
STOCK_SYMB PRICE GRAPH_LINE ---------- ----- ----------- FLAT 10 9 8 7 *********** 6 5 V1 10 * * 9 * * 8 * * 7 * * 6 * * 5 * V2 10 9 ** * 8 ** ** 7 ** 6 ** 5 V3 10 ** 9 ** 8 * 7 ** 6 * 5 *** W1 10 * * 9 * * * 8 * * * * 7 * * 6 5 W2 10 9 *** *** 8 *** 7 * 6 5 *
Atomic Patterns
Before we write any code to identify patterns in this test data we must first define what kind of atomic patterns we are interested in. In our case we are interested in price movement relative to the previous day's price. We will use the letters "U", "D", and "A" to represent prices that move Up, Down, or Across.
The next step is to aggregate the atomic patterns into a single string. We will create one string called MOVEMENT_STRING_UDA that tracks Up, Down, and Across movement. We will create another string called MOVEMENT_STRING_UD that ignores Across and only tracks Up and Down movement. For this step we use a technique described at Rows to String: Hierarchical Method.
Fortunately, both steps can be accomplished using a single hierarchical query.
create or replace view stock_price_movement as select stock_symbol, day, price, case when prior sp.price < sp.price then 'U' when prior sp.price > sp.price then 'D' else 'A' end as MOVEMENT_UDA , replace ( sys_connect_by_path ( case when prior price < price then 'U' when prior price > price then 'D' else 'A' end, ' ' ), ' ', null ) as MOVEMENT_STRING_UDA , case when prior sp.price < sp.price then 'U' when prior sp.price > sp.price then 'D' else null end as movement_ud , replace ( sys_connect_by_path ( case when prior price < price then 'U' when prior price > price then 'D' else null end, ' ' ), ' ', null ) as MOVEMENT_STRING_UD from stock_prices sp start with day = 1 connect by stock_symbol = prior stock_symbol and day = prior day + 1 ;
column STOCK_SYMBOL heading "Stock|Symbol" column DAY heading "Day" column PRICE heading "Price" column MOVEMENT_UDA format a8 heading "Movement|UDA" column MOVEMENT_STRING_UDA format a15 heading "String|UDA" column MOVEMENT_UD format a8 heading "Movement|UD" column MOVEMENT_STRING_UD format a15 heading "String|UD" select * from stock_price_movement ;
Stock Movement String Movement String Symbol Day Price UDA UDA UD UD ---------- --- ----- -------- --------------- -------- --------------- FLAT 1 7 A A 2 7 A AA 3 7 A AAA 4 7 A AAAA 5 7 A AAAAA 6 7 A AAAAAA 7 7 A AAAAAAA 8 7 A AAAAAAAA 9 7 A AAAAAAAAA 10 7 A AAAAAAAAAA 11 7 A AAAAAAAAAAA V1 1 10 A A 2 9 D AD D D 3 8 D ADD D DD 4 7 D ADDD D DDD 5 6 D ADDDD D DDDD 6 5 D ADDDDD D DDDDD 7 6 U ADDDDDU U DDDDDU 8 7 U ADDDDDUU U DDDDDUU 9 8 U ADDDDDUUU U DDDDDUUU 10 9 U ADDDDDUUUU U DDDDDUUUU 11 10 U ADDDDDUUUUU U DDDDDUUUUU V2 1 9 A A 2 9 A AA 3 8 D AAD D D 4 8 A AADA D 5 6 D AADAD D DD 6 6 A AADADA DD 7 7 U AADADAU U DDU 8 7 A AADADAUA DDU 9 8 U AADADAUAU U DDUU 10 8 A AADADAUAUA DDUU 11 9 U AADADAUAUAU U DDUUU V3 1 5 A A 2 5 A AA 3 5 A AAA 4 7 U AAAU U U 5 7 A AAAUA U 6 10 U AAAUAU U UU 7 10 A AAAUAUA UU 8 9 D AAAUAUAD D UUD 9 9 A AAAUAUADA UUD 10 8 D AAAUAUADAD D UUDD 11 6 D AAAUAUADADD D UUDDD W1 1 10 A A 2 9 D AD D D 3 8 D ADD D DD 4 7 D ADDD D DDD 5 8 U ADDDU U DDDU 6 9 U ADDDUU U DDDUU 7 8 D ADDDUUD D DDDUUD 8 7 D ADDDUUDD D DDDUUDD 9 8 U ADDDUUDDU U DDDUUDDU 10 9 U ADDDUUDDUU U DDDUUDDUU 11 10 U ADDDUUDDUUU U DDDUUDDUUU W2 1 9 A A 2 9 A AA 3 9 A AAA 4 7 D AAAD D D 5 8 U AAADU U DU 6 8 A AAADUA DU 7 8 A AAADUAA DU 8 5 D AAADUAAD D DUD 9 9 U AAADUAADU U DUDU 10 9 A AAADUAADUA DUDU 11 9 A AAADUAADUAA DUDU