Shulou(Shulou.com)06/01 Report--

Similar to the function of the analysis function, the matching can be judged and calculated between lines. The new pattern matching statement in SQL is "match_recognize".

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

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-01, 12)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-02, 17)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-03, 19)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-04, 21)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-05, 25)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-06, 12)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-07, 15)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-08, 20)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-09, 24)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-10, 25)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-11, 19)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-12, 15)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-1300, 25)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-14, 25)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-15, 14)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-1600, 12)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-17, 14)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-18, 24)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-19, 23)

INSERT INTO Ticker VALUES ('ACME', DATE' 2011-04-20, 22)

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 (UP.price)

) MR

ORDER BY MR.symbol, MR.start_tstamp

SYMBOL START_TST BOTTOM_TS END_TSTAM

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

What did this query do? Each line in the MATCH_RECOGNIZE clause is explained below:

The .PARTITION BY divides the Ticker table data into logical groups, each containing a stock symbol.

The .order BY sorts the data within each logical group by tstamp.

MEASURES defines three metrics: a V-shaped start timestamp (start_tstamp), a V-shaped bottom timestamp (bottom_tstamp), and a V-shaped end timestamp (end_tstamp). Bottom_tstamp and end_tstamp metrics use the LAST () function to ensure that the value read is the value of the last timestamp in each matching pattern.

.One ROW PER MATCH means that there is only one line of output for each pattern match found.

After MATCH SKIP TO LAST UP means that every time you find a match, you restart your search on the last line of the UP schema variable. A schema variable is a variable used in MATCH_RECOGNIZE, defined in the DEFINE clause.

Pattern (STRT DOWN+ UP+) says that the pattern you are searching for has three schema variables: STRT, DOWN, and UP. The plus sign (+) after DOWN and UP means that each of them has at least one line mapped. This pattern defines a regular expression, which is a very expressive search method.

.DEFINE gives us the conditions that should be met when a row is mapped to your row pattern variables STRT, DOWN, and UP. Because no condition is specified for STRT, any row can be mapped to STRT. Why does a schema variable have no conditions? You can use it as a starting point for testing matches. Both DOWN and UP take advantage of the PREV () function, which allows them to compare the price of the current line with the price of the previous line. The DOWN is matched when the price is lower than the previous line, so it defines the V-shaped downside (left leg). If the price is higher than the previous line, it is mapped to UP.

The following two diagrams can help you better understand the results returned by example 20-1. Figure 20-2 shows the date mapped to a specific schema variable (specified in the PATTERN clause). After the mapping of the schema variable to the date is visible, the MEASURES clause uses this information to calculate the measure. The results of the measurements are shown in figure 20-3.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.