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