SQL Pattern Matching

View All Scripts Login to Run Script

Script Name SQL Pattern Matching
Description Taken from: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/misc/sql_pattern/sql_pattern.html
Area SQL General / Analytics
Contributor Bartje
Created Monday January 18, 2016

Statement 1

CREATE TABLE ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER)

Table created.

Statement 2

CREATE TABLE ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER)

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 3

INSERT INTO Ticker VALUES('ACME', '01-Apr-11', 12)

1 row(s) inserted.

Statement 4

INSERT INTO Ticker VALUES('ACME', '02-Apr-11', 17)

1 row(s) inserted.

Statement 5

INSERT INTO Ticker VALUES('ACME', '03-Apr-11', 19)

1 row(s) inserted.

Statement 6

INSERT INTO Ticker VALUES('ACME', '04-Apr-11', 21)

1 row(s) inserted.

Statement 7

INSERT INTO Ticker VALUES('ACME', '05-Apr-11', 25)

1 row(s) inserted.

Statement 8

INSERT INTO Ticker VALUES('ACME', '06-Apr-11', 12)

1 row(s) inserted.

Statement 9

INSERT INTO Ticker VALUES('ACME', '07-Apr-11', 15)

1 row(s) inserted.

Statement 10

INSERT INTO Ticker VALUES('ACME', '08-Apr-11', 20)

1 row(s) inserted.

Statement 11

INSERT INTO Ticker VALUES('ACME', '09-Apr-11', 24)

1 row(s) inserted.

Statement 12

INSERT INTO Ticker VALUES('ACME', '10-Apr-11', 25)

1 row(s) inserted.

Statement 13

INSERT INTO Ticker VALUES('ACME', '11-Apr-11', 19)

1 row(s) inserted.

Statement 14

INSERT INTO Ticker VALUES('ACME', '12-Apr-11', 15)

1 row(s) inserted.

Statement 15

INSERT INTO Ticker VALUES('ACME', '13-Apr-11', 25)

1 row(s) inserted.

Statement 16

INSERT INTO Ticker VALUES('ACME', '14-Apr-11', 25)

1 row(s) inserted.

Statement 17

INSERT INTO Ticker VALUES('ACME', '15-Apr-11', 14)

1 row(s) inserted.

Statement 18

INSERT INTO Ticker VALUES('ACME', '16-Apr-11', 12)

1 row(s) inserted.

Statement 19

INSERT INTO Ticker VALUES('ACME', '17-Apr-11', 14)

1 row(s) inserted.

Statement 20

INSERT INTO Ticker VALUES('ACME', '18-Apr-11', 24)

1 row(s) inserted.

Statement 21

INSERT INTO Ticker VALUES('ACME', '19-Apr-11', 23)

1 row(s) inserted.

Statement 22

INSERT INTO Ticker VALUES('ACME', '20-Apr-11', 22)

1 row(s) inserted.

Statement 23
```
DROP TABLE ticker
```
```
Table dropped.
```

Statement 24

CREATE TABLE ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER)

Table created.

Statement 25

INSERT INTO Ticker VALUES('ACME', '01-Apr-11', 12)

1 row(s) inserted.

Statement 26

INSERT INTO Ticker VALUES('ACME', '02-Apr-11', 17)

1 row(s) inserted.

Statement 27

INSERT INTO Ticker VALUES('ACME', '03-Apr-11', 19)

1 row(s) inserted.

Statement 28

INSERT INTO Ticker VALUES('ACME', '04-Apr-11', 21)

1 row(s) inserted.

Statement 29

INSERT INTO Ticker VALUES('ACME', '05-Apr-11', 25)

1 row(s) inserted.

Statement 30

INSERT INTO Ticker VALUES('ACME', '06-Apr-11', 12)

1 row(s) inserted.

Statement 31

INSERT INTO Ticker VALUES('ACME', '07-Apr-11', 15)

1 row(s) inserted.

Statement 32

INSERT INTO Ticker VALUES('ACME', '08-Apr-11', 20)

1 row(s) inserted.

Statement 33

INSERT INTO Ticker VALUES('ACME', '09-Apr-11', 24)

1 row(s) inserted.

Statement 34

INSERT INTO Ticker VALUES('ACME', '10-Apr-11', 25)

1 row(s) inserted.

Statement 35

INSERT INTO Ticker VALUES('ACME', '11-Apr-11', 19)

1 row(s) inserted.

Statement 36

INSERT INTO Ticker VALUES('ACME', '12-Apr-11', 15)

1 row(s) inserted.

Statement 37

INSERT INTO Ticker VALUES('ACME', '13-Apr-11', 25)

1 row(s) inserted.

Statement 38

INSERT INTO Ticker VALUES('ACME', '14-Apr-11', 25)

1 row(s) inserted.

Statement 39

INSERT INTO Ticker VALUES('ACME', '15-Apr-11', 14)

1 row(s) inserted.

Statement 40

INSERT INTO Ticker VALUES('ACME', '16-Apr-11', 12)

1 row(s) inserted.

Statement 41

INSERT INTO Ticker VALUES('ACME', '17-Apr-11', 14)

1 row(s) inserted.

Statement 42

INSERT INTO Ticker VALUES('ACME', '18-Apr-11', 24)

1 row(s) inserted.

Statement 43

INSERT INTO Ticker VALUES('ACME', '19-Apr-11', 23)

1 row(s) inserted.

Statement 44

INSERT INTO Ticker VALUES('ACME', '20-Apr-11', 22)

1 row(s) inserted.

Statement 45

SELECT * 
FROM Ticker MATCH_RECOGNIZE ( 
PARTITION BY symbol 
ORDER BY tstamp 
MEASURES STRT.tstamp AS start_tstamp, 
LAST(DOWN.tstamp) AS bottom_tstamp, 
LAST(UP.tstamp) AS end_tstamp 
ONE ROW PER MATCH 
AFTER MATCH SKIP TO LAST UP 
PATTERN (STRT DOWN+ UP+) 
DEFINE 
DOWN AS DOWN.price < PREV(DOWN.price), 
UP AS UP.price > PREV(UP.price) ) MR
WHERE symbol='ACME'
ORDER BY MR.symbol, MR.start_tstamp

 
SYMBOL START_TSTAMP BOTTOM_TSTAMP END_TSTAMP
ACME 05-APR-11 06-APR-11 10-APR-11
ACME 10-APR-11 12-APR-11 13-APR-11
ACME 14-APR-11 16-APR-11 18-APR-11

3 rows selected.

SYMBOL	START_TSTAMP	BOTTOM_TSTAMP	END_TSTAMP
ACME	05-APR-11	06-APR-11	10-APR-11
ACME	10-APR-11	12-APR-11	13-APR-11
ACME	14-APR-11	16-APR-11	18-APR-11

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database