Pattern Matching Over Rows

String Aggregation

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
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12472.html]SQL Snippets: Pattern Matching Over Rows - String Aggregation[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12472.html">SQL Snippets: Pattern Matching Over Rows - String Aggregation</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Pattern Matching Over Rows - String Aggregation
  • URL (href): http://www.sqlsnippets.com/en/topic-12472.html