この記事は約409分59秒で読むことができます。

sql oracle MATCH_RECOGNIZE lessonn

テストデータの準備

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

DROP TABLE ticker PURGE;
CREATE TABLE ticker AS
WITH rec(
	rn,syb,tp,qty
	)AS(
		SELECT
			1 AS rn
			,CASE
				WHEN LEVEL = 1 THEN 'ACME'
				WHEN LEVEL = 2 THEN 'GLOBEX'
				WHEN LEVEL = 3 THEN 'OSCORP'
				ELSE NULL
			END AS syb
			,sysdate
			,TRUNC(ABS(dbms_random.VALUE(1,10)),0) AS qty
		FROM
			dual
		CONNECT BY
			LEVEL <= 3
		UNION ALL
		SELECT
			rn + 1
			,syb
			,tp + 1
			,TRUNC(ABS(dbms_random.VALUE(1,10)),0) AS qty
		FROM
			rec
		WHERE
			rn + 1 <= 20
	)
SELECT syb, tp, qty FROM rec
;
コード表示

SELECT * FROM ticker ORDER BY syb,tp;

SELECT
	*
FROM
	ticker
	PIVOT (MIN(qty) FOR syb IN ('ACME' AS acme,'GLOBEX' AS globex,'OSCORP' AS oscorp))
ORDER BY
	tp
;

ALL ROWS PER MATCHの動き

コード表示


ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';


SELECT
	*
FROM
	ticker MATCH_RECOGNIZE (
		 PARTITION BY syb
		 ORDER BY tp
		 MEASURES
			strt.tp AS start_tp
			,LAST(UP.tp) AS end_tp
			,match_number() AS mn
			,classifier() AS cls
		 ALL ROWS PER MATCH
		 PATTERN (strt down* UP)
		 DEFINE
			   down AS down.qty < prev(down.qty)
			   ,UP AS UP.qty > prev(UP.qty) 
		) mr
WHERE
	syb = 'ACME'
;


SYB    TP         START_TP   END_TP                MN CLS                  QTY
------ ---------- ---------- ---------- ------------- ---------- -------------
ACME   2019-02-24 2019-02-24                        1 STRT                   4
ACME   2019-02-25 2019-02-24 2019-02-25             1 UP                     6
ACME   2019-02-26 2019-02-26                        2 STRT                   1
ACME   2019-02-27 2019-02-26 2019-02-27             2 UP                     5
ACME   2019-03-02 2019-03-02                        3 STRT                   4
ACME   2019-03-03 2019-03-02                        3 DOWN                   1
ACME   2019-03-04 2019-03-02 2019-03-04             3 UP                     6
ACME   2019-03-08 2019-03-08                        4 STRT                   1
ACME   2019-03-09 2019-03-08 2019-03-09             4 UP                     2
ACME   2019-03-10 2019-03-10                        5 STRT                   5
ACME   2019-03-11 2019-03-10                        5 DOWN                   1
ACME   2019-03-12 2019-03-10 2019-03-12             5 UP                     5

12 rows selected.

Elapsed: 00:00:00.00


AFTER MATCH SKIP PAST LAST ROWの動き

これがデフォ。パタン句に定義した一連のパタンを検出した最終行の次の行からパタンマッチを再開する。省略したら、ALL ROWS PER MATCHの動きはこれにしたがう。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker MATCH_RECOGNIZE (
		 PARTITION BY syb
		 ORDER BY tp
		 MEASURES
			strt.tp AS start_tp
			,LAST(UP.tp) AS end_tp
			,match_number() AS mn
			,classifier() AS cls
		 ALL ROWS PER MATCH
		 AFTER MATCH SKIP PAST LAST ROW
		 PATTERN (strt down* UP)
		 DEFINE
			   down AS down.qty < prev(down.qty)
			   ,UP AS UP.qty > prev(UP.qty) 
		) mr
WHERE
	syb = 'ACME'
;


SYB    TP         START_TP   END_TP                MN CLS                  QTY
------ ---------- ---------- ---------- ------------- ---------- -------------
ACME   2019-02-24 2019-02-24                        1 STRT                   4
ACME   2019-02-25 2019-02-24 2019-02-25             1 UP                     6
ACME   2019-02-26 2019-02-26                        2 STRT                   1
ACME   2019-02-27 2019-02-26 2019-02-27             2 UP                     5
ACME   2019-03-02 2019-03-02                        3 STRT                   4
ACME   2019-03-03 2019-03-02                        3 DOWN                   1
ACME   2019-03-04 2019-03-02 2019-03-04             3 UP                     6
ACME   2019-03-08 2019-03-08                        4 STRT                   1
ACME   2019-03-09 2019-03-08 2019-03-09             4 UP                     2
ACME   2019-03-10 2019-03-10                        5 STRT                   5
ACME   2019-03-11 2019-03-10                        5 DOWN                   1
ACME   2019-03-12 2019-03-10 2019-03-12             5 UP                     5

12 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO NEXT ROWの動き

パタン句に定義した一連のパタンの開始行の次の行からパタンマッチを再開する。たとえば、mn1に該当する開始行の次の行のtpは2019-02-25のため、mn2はここからパタンマッチを続ける。mn2に該当する開始行の次の行のtpは2019-02-26のため、mn3はここからパタンマッチを続ける。という具合に。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker MATCH_RECOGNIZE (
		 PARTITION BY syb
		 ORDER BY tp
		 MEASURES
			strt.tp AS start_tp
			,LAST(UP.tp) AS end_tp
			,match_number() AS mn
			,classifier() AS cls
		 ALL ROWS PER MATCH
		 AFTER MATCH SKIP TO NEXT ROW
		 PATTERN (strt down* UP)
		 DEFINE
			   down AS down.qty < prev(down.qty)
			   ,UP AS UP.qty > prev(UP.qty) 
		) mr
WHERE
	syb = 'ACME'
;

SYB    TP         START_TP   END_TP                MN CLS                  QTY
------ ---------- ---------- ---------- ------------- ---------- -------------
ACME   2019-02-24 2019-02-24                        1 STRT                   4
ACME   2019-02-25 2019-02-24 2019-02-25             1 UP                     6
ACME   2019-02-25 2019-02-25                        2 STRT                   6
ACME   2019-02-26 2019-02-25                        2 DOWN                   1
ACME   2019-02-27 2019-02-25 2019-02-27             2 UP                     5
ACME   2019-02-26 2019-02-26                        3 STRT                   1
ACME   2019-02-27 2019-02-26 2019-02-27             3 UP                     5
ACME   2019-02-27 2019-02-27                        4 STRT                   5
ACME   2019-02-28 2019-02-27 2019-02-28             4 UP                     8
ACME   2019-03-02 2019-03-02                        5 STRT                   4
ACME   2019-03-03 2019-03-02                        5 DOWN                   1
ACME   2019-03-04 2019-03-02 2019-03-04             5 UP                     6
ACME   2019-03-03 2019-03-03                        6 STRT                   1
ACME   2019-03-04 2019-03-03 2019-03-04             6 UP                     6
ACME   2019-03-04 2019-03-04                        7 STRT                   6
ACME   2019-03-05 2019-03-04 2019-03-05             7 UP                     8
ACME   2019-03-08 2019-03-08                        8 STRT                   1
ACME   2019-03-09 2019-03-08 2019-03-09             8 UP                     2
ACME   2019-03-09 2019-03-09                        9 STRT                   2
ACME   2019-03-10 2019-03-09 2019-03-10             9 UP                     5
ACME   2019-03-10 2019-03-10                       10 STRT                   5
ACME   2019-03-11 2019-03-10                       10 DOWN                   1
ACME   2019-03-12 2019-03-10 2019-03-12            10 UP                     5
ACME   2019-03-11 2019-03-11                       11 STRT                   1
ACME   2019-03-12 2019-03-11 2019-03-12            11 UP                     5

25 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO FIRST(LAST) パタン変数の動き

パタンマッチした範囲のうち、パタン変数に定義したパタンが最初(最後)に登場した行からパタンマッチを開始する。パタン変数に定義する正規表現に「|,?,*」を指定していると、パタンマッチした範囲で見つかるかどうか分からないため、コンパイラはエラーをはくよ。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker MATCH_RECOGNIZE (
		 PARTITION BY syb
		 ORDER BY tp
		 MEASURES
			strt.tp AS start_tp
			,LAST(UP.tp) AS end_tp
			,match_number() AS mn
			,classifier() AS cls
		 ALL ROWS PER MATCH
		 AFTER MATCH SKIP TO FIRST up
--		 AFTER MATCH SKIP TO last up
--		 AFTER MATCH SKIP TO FIRST down
--		 AFTER MATCH SKIP TO last down
		PATTERN (strt down UP)
		 DEFINE
			   down AS down.qty < prev(down.qty)
			   ,UP AS UP.qty > prev(UP.qty) 
		) mr
WHERE
	syb = 'ACME'
;

SYB    TP         START_TP   END_TP                MN CLS                  QTY
------ ---------- ---------- ---------- ------------- ---------- -------------
ACME   2019-02-25 2019-02-25                        1 STRT                   6
ACME   2019-02-26 2019-02-25                        1 DOWN                   1
ACME   2019-02-27 2019-02-25 2019-02-27             1 UP                     5
ACME   2019-03-02 2019-03-02                        2 STRT                   4
ACME   2019-03-03 2019-03-02                        2 DOWN                   1
ACME   2019-03-04 2019-03-02 2019-03-04             2 UP                     6
ACME   2019-03-10 2019-03-10                        3 STRT                   5
ACME   2019-03-11 2019-03-10                        3 DOWN                   1
ACME   2019-03-12 2019-03-10 2019-03-12             3 UP                     5

9 rows selected.

Elapsed: 00:00:00.01

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker MATCH_RECOGNIZE (
		 PARTITION BY syb
		 ORDER BY tp
		 MEASURES
			strt.tp AS start_tp
			,LAST(UP.tp) AS end_tp
			,match_number() AS mn
			,classifier() AS cls
		 ALL ROWS PER MATCH
--		 AFTER MATCH SKIP TO FIRST up
		 AFTER MATCH SKIP TO last up
--		 AFTER MATCH SKIP TO FIRST down
--		 AFTER MATCH SKIP TO last down
		PATTERN (strt down UP)
		 DEFINE
			   down AS down.qty < prev(down.qty)
			   ,UP AS UP.qty > prev(UP.qty) 
		) mr
WHERE
	syb = 'ACME'
;

SYB    TP         START_TP   END_TP                MN CLS                  QTY
------ ---------- ---------- ---------- ------------- ---------- -------------
ACME   2019-02-25 2019-02-25                        1 STRT                   6
ACME   2019-02-26 2019-02-25                        1 DOWN                   1
ACME   2019-02-27 2019-02-25 2019-02-27             1 UP                     5
ACME   2019-03-02 2019-03-02                        2 STRT                   4
ACME   2019-03-03 2019-03-02                        2 DOWN                   1
ACME   2019-03-04 2019-03-02 2019-03-04             2 UP                     6
ACME   2019-03-10 2019-03-10                        3 STRT                   5
ACME   2019-03-11 2019-03-10                        3 DOWN                   1
ACME   2019-03-12 2019-03-10 2019-03-12             3 UP                     5

9 rows selected.

Elapsed: 00:00:00.01


コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker MATCH_RECOGNIZE (
		 PARTITION BY syb
		 ORDER BY tp
		 MEASURES
			strt.tp AS start_tp
			,LAST(UP.tp) AS end_tp
			,match_number() AS mn
			,classifier() AS cls
		 ALL ROWS PER MATCH
--		 AFTER MATCH SKIP TO FIRST up
--		 AFTER MATCH SKIP TO last up
		 AFTER MATCH SKIP TO FIRST down
--		 AFTER MATCH SKIP TO last down
		PATTERN (strt down UP)
		 DEFINE
			   down AS down.qty < prev(down.qty)
			   ,UP AS UP.qty > prev(UP.qty) 
		) mr
WHERE
	syb = 'ACME'
;

SYB    TP         START_TP   END_TP                MN CLS                  QTY
------ ---------- ---------- ---------- ------------- ---------- -------------
ACME   2019-02-25 2019-02-25                        1 STRT                   6
ACME   2019-02-26 2019-02-25                        1 DOWN                   1
ACME   2019-02-27 2019-02-25 2019-02-27             1 UP                     5
ACME   2019-03-02 2019-03-02                        2 STRT                   4
ACME   2019-03-03 2019-03-02                        2 DOWN                   1
ACME   2019-03-04 2019-03-02 2019-03-04             2 UP                     6
ACME   2019-03-10 2019-03-10                        3 STRT                   5
ACME   2019-03-11 2019-03-10                        3 DOWN                   1
ACME   2019-03-12 2019-03-10 2019-03-12             3 UP                     5

9 rows selected.

Elapsed: 00:00:00.00

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker MATCH_RECOGNIZE (
		 PARTITION BY syb
		 ORDER BY tp
		 MEASURES
			strt.tp AS start_tp
			,LAST(UP.tp) AS end_tp
			,match_number() AS mn
			,classifier() AS cls
		 ALL ROWS PER MATCH
--		 AFTER MATCH SKIP TO FIRST up
--		 AFTER MATCH SKIP TO last up
--		 AFTER MATCH SKIP TO FIRST down
		 AFTER MATCH SKIP TO last down
		PATTERN (strt down UP)
		 DEFINE
			   down AS down.qty < prev(down.qty)
			   ,UP AS UP.qty > prev(UP.qty) 
		) mr
WHERE
	syb = 'ACME'
;

SYB    TP         START_TP   END_TP                MN CLS                  QTY
------ ---------- ---------- ---------- ------------- ---------- -------------
ACME   2019-02-25 2019-02-25                        1 STRT                   6
ACME   2019-02-26 2019-02-25                        1 DOWN                   1
ACME   2019-02-27 2019-02-25 2019-02-27             1 UP                     5
ACME   2019-03-02 2019-03-02                        2 STRT                   4
ACME   2019-03-03 2019-03-02                        2 DOWN                   1
ACME   2019-03-04 2019-03-02 2019-03-04             2 UP                     6
ACME   2019-03-10 2019-03-10                        3 STRT                   5
ACME   2019-03-11 2019-03-10                        3 DOWN                   1
ACME   2019-03-12 2019-03-10 2019-03-12             3 UP                     5

9 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO パタン変数の動き

AFTER MATCH SKIP TO LAST パタン変数と同じ。

AFTER MATCH SKIPの動きもうちょいしらべる

まずはサマリ行のみ出力してAFTER MATCH SKIP PAST LAST ROWはデフォ。なるたけ、パタンが多くマッチするように振舞う。こういうのをたしか強欲greedyとかいいたりするんだよな。

所感としてはパタンの序盤に定義したパタン変数をパタンマッチの再開開始位置として定義すると出力される行数は増えるイメージ。それはそうか。。そうすると、パタンマッチした範囲にoverlapが出てくるから。overlapの扱いどうする的な仕様しだいでここの指定は変わるはず。まぁ基本はデフォルトでいいとおもわれ。あとはパタン句に定義した最後のパタン変数がパタンマッチした範囲において最後に現れた行から再開するばあいとかか。after match skip to last pattern_variable。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ONE ROW PER MATCH
		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
ORDER BY
	syb
	, mr.start_w
;

SYB               MN START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 2019-03-06 2019-03-14             7             1             5             1             5
GLOBEX             1 2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 2019-03-05 2019-03-11             7             1             2             1             9
OSCORP             1 2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 2019-03-05 2019-03-10             2             2             5             3             8

6 rows selected.

Elapsed: 00:00:00.00

次に明細行も出力して各行のマッチしたパタンを調べる。取得列とかclassifier()いれること。order by も時系列を加えること。partitio byは基本的に並列に振舞うから代表一つとして、シンボルがACMEのものを出力するようにした。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	, tp
;

SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-03-06             2 STRT       2019-03-06                        7                                                                     7
ACME   2019-03-07             2 X          2019-03-06                        7             1                                                       1
ACME   2019-03-08             2 X          2019-03-06                        7             1                                                       1
ACME   2019-03-09             2 Y          2019-03-06                        7             1             2                                         2
ACME   2019-03-10             2 Y          2019-03-06                        7             1             5                                         5
ACME   2019-03-11             2 W          2019-03-06                        7             1             5             1                           1
ACME   2019-03-12             2 Z          2019-03-06 2019-03-12             7             1             5             1             5             5
ACME   2019-03-13             2 Z          2019-03-06 2019-03-13             7             1             5             1             5             5
ACME   2019-03-14             2 Z          2019-03-06 2019-03-14             7             1             5             1             5             5

18 rows selected.

Elapsed: 00:00:00.00

AFTER MATCH SKIP TO LAST Xの場合。サマリ行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
ORDER BY
	syb
	, mr.start_w
;

SYB               MN START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 2019-02-28 2019-03-10             8             1             8             1             5
ACME               3 2019-03-05 2019-03-14             8             1             5             1             5
ACME               4 2019-03-10 2019-03-14             5             1             5             5             5
GLOBEX             1 2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 2019-02-27 2019-03-05             3             1             8             1             7
GLOBEX             3 2019-03-02 2019-03-07             8             1             7             1             2
GLOBEX             4 2019-03-05 2019-03-11             7             1             2             1             9
GLOBEX             5 2019-03-07 2019-03-15             2             1             9             1             4
GLOBEX             6 2019-03-11 2019-03-15             9             8             8             1             4
OSCORP             1 2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 2019-03-02 2019-03-07             7             3             8             2             5
OSCORP             3 2019-03-04 2019-03-10             8             2             5             3             8
OSCORP             4 2019-03-07 2019-03-15             5             3             8             5             9
OSCORP             5 2019-03-09 2019-03-15             8             7             7             5             9

15 rows selected.

Elapsed: 00:00:00.00

AFTER MATCH SKIP TO LAST Xの場合。明細行。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	,mn
--	,start_w
	, tp
;

SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-02-28             2 STRT       2019-02-28                        8                                                                     8
ACME   2019-03-01             2 X          2019-02-28                        8             4                                                       4
ACME   2019-03-02             2 X          2019-02-28                        8             4                                                       4
ACME   2019-03-03             2 X          2019-02-28                        8             1                                                       1
ACME   2019-03-04             2 Y          2019-02-28                        8             1             6                                         6
ACME   2019-03-05             2 Y          2019-02-28                        8             1             8                                         8
ACME   2019-03-06             2 W          2019-02-28                        8             1             8             7                           7
ACME   2019-03-07             2 W          2019-02-28                        8             1             8             1                           1
ACME   2019-03-08             2 W          2019-02-28                        8             1             8             1                           1
ACME   2019-03-09             2 Z          2019-02-28 2019-03-09             8             1             8             1             2             2
ACME   2019-03-10             2 Z          2019-02-28 2019-03-10             8             1             8             1             5             5
ACME   2019-03-05             3 STRT       2019-03-05                        8                                                                     8
ACME   2019-03-06             3 X          2019-03-05                        8             7                                                       7
ACME   2019-03-07             3 X          2019-03-05                        8             1                                                       1
ACME   2019-03-08             3 X          2019-03-05                        8             1                                                       1
ACME   2019-03-09             3 Y          2019-03-05                        8             1             2                                         2
ACME   2019-03-10             3 Y          2019-03-05                        8             1             5                                         5
ACME   2019-03-11             3 W          2019-03-05                        8             1             5             1                           1
ACME   2019-03-12             3 Z          2019-03-05 2019-03-12             8             1             5             1             5             5
ACME   2019-03-13             3 Z          2019-03-05 2019-03-13             8             1             5             1             5             5
ACME   2019-03-14             3 Z          2019-03-05 2019-03-14             8             1             5             1             5             5
ACME   2019-03-10             4 STRT       2019-03-10                        5                                                                     5
ACME   2019-03-11             4 X          2019-03-10                        5             1                                                       1
ACME   2019-03-12             4 Y          2019-03-10                        5             1             5                                         5
ACME   2019-03-13             4 W          2019-03-10                        5             1             5             5                           5
ACME   2019-03-14             4 Z          2019-03-10 2019-03-14             5             1             5             5             5             5

35 rows selected.

Elapsed: 00:00:00.00

AFTER MATCH SKIP TO LAST Yの場合。サマリ行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
--		ALL ROWS PER MATCH
		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
--WHERE
--	syb = 'ACME'
ORDER BY
	syb
--	,mn
	,start_w
--	, tp
;

SYB               MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 Z          2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 Z          2019-02-28 2019-03-10             8             1             8             1             5
ACME               3 Z          2019-03-05 2019-03-14             8             1             5             1             5
ACME               4 Z          2019-03-10 2019-03-14             5             1             5             5             5
GLOBEX             1 Z          2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 Z          2019-02-27 2019-03-05             3             1             8             1             7
GLOBEX             3 Z          2019-03-02 2019-03-07             8             1             7             1             2
GLOBEX             4 Z          2019-03-05 2019-03-11             7             1             2             1             9
GLOBEX             5 Z          2019-03-07 2019-03-15             2             1             9             1             4
GLOBEX             6 Z          2019-03-11 2019-03-15             9             8             8             1             4
OSCORP             1 Z          2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 Z          2019-03-02 2019-03-07             7             3             8             2             5
OSCORP             3 Z          2019-03-04 2019-03-10             8             2             5             3             8
OSCORP             4 Z          2019-03-07 2019-03-15             5             3             8             5             9
OSCORP             5 Z          2019-03-10 2019-03-15             8             7             7             5             9

15 rows selected.

Elapsed: 00:00:00.01


AFTER MATCH SKIP TO LAST Yの場合。明細行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	,mn
--	,start_w
	, tp
;

SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-02-28             2 STRT       2019-02-28                        8                                                                     8
ACME   2019-03-01             2 X          2019-02-28                        8             4                                                       4
ACME   2019-03-02             2 X          2019-02-28                        8             4                                                       4
ACME   2019-03-03             2 X          2019-02-28                        8             1                                                       1
ACME   2019-03-04             2 Y          2019-02-28                        8             1             6                                         6
ACME   2019-03-05             2 Y          2019-02-28                        8             1             8                                         8
ACME   2019-03-06             2 W          2019-02-28                        8             1             8             7                           7
ACME   2019-03-07             2 W          2019-02-28                        8             1             8             1                           1
ACME   2019-03-08             2 W          2019-02-28                        8             1             8             1                           1
ACME   2019-03-09             2 Z          2019-02-28 2019-03-09             8             1             8             1             2             2
ACME   2019-03-10             2 Z          2019-02-28 2019-03-10             8             1             8             1             5             5
ACME   2019-03-05             3 STRT       2019-03-05                        8                                                                     8
ACME   2019-03-06             3 X          2019-03-05                        8             7                                                       7
ACME   2019-03-07             3 X          2019-03-05                        8             1                                                       1
ACME   2019-03-08             3 X          2019-03-05                        8             1                                                       1
ACME   2019-03-09             3 Y          2019-03-05                        8             1             2                                         2
ACME   2019-03-10             3 Y          2019-03-05                        8             1             5                                         5
ACME   2019-03-11             3 W          2019-03-05                        8             1             5             1                           1
ACME   2019-03-12             3 Z          2019-03-05 2019-03-12             8             1             5             1             5             5
ACME   2019-03-13             3 Z          2019-03-05 2019-03-13             8             1             5             1             5             5
ACME   2019-03-14             3 Z          2019-03-05 2019-03-14             8             1             5             1             5             5
ACME   2019-03-10             4 STRT       2019-03-10                        5                                                                     5
ACME   2019-03-11             4 X          2019-03-10                        5             1                                                       1
ACME   2019-03-12             4 Y          2019-03-10                        5             1             5                                         5
ACME   2019-03-13             4 W          2019-03-10                        5             1             5             5                           5
ACME   2019-03-14             4 Z          2019-03-10 2019-03-14             5             1             5             5             5             5

35 rows selected.

Elapsed: 00:00:00.00


AFTER MATCH SKIP TO LAST Wの場合。サマリ行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
--		ALL ROWS PER MATCH
		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
--WHERE
--	syb = 'ACME'
ORDER BY
	syb
--	,mn
	,start_w
--	, tp
;

SYB               MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 Z          2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 Z          2019-03-05 2019-03-14             8             1             5             1             5
GLOBEX             1 Z          2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 Z          2019-03-02 2019-03-07             8             1             7             1             2
GLOBEX             3 Z          2019-03-07 2019-03-15             2             1             9             1             4
OSCORP             1 Z          2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 Z          2019-03-04 2019-03-10             8             2             5             3             8
OSCORP             3 Z          2019-03-09 2019-03-15             8             7             7             5             9

8 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO LAST Wの場合。明細行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	,mn
--	,start_w
	, tp
;


SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-03-05             2 STRT       2019-03-05                        8                                                                     8
ACME   2019-03-06             2 X          2019-03-05                        8             7                                                       7
ACME   2019-03-07             2 X          2019-03-05                        8             1                                                       1
ACME   2019-03-08             2 X          2019-03-05                        8             1                                                       1
ACME   2019-03-09             2 Y          2019-03-05                        8             1             2                                         2
ACME   2019-03-10             2 Y          2019-03-05                        8             1             5                                         5
ACME   2019-03-11             2 W          2019-03-05                        8             1             5             1                           1
ACME   2019-03-12             2 Z          2019-03-05 2019-03-12             8             1             5             1             5             5
ACME   2019-03-13             2 Z          2019-03-05 2019-03-13             8             1             5             1             5             5
ACME   2019-03-14             2 Z          2019-03-05 2019-03-14             8             1             5             1             5             5

19 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO LAST Zの場合。サマリ行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
--		ALL ROWS PER MATCH
		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
--WHERE
--	syb = 'ACME'
ORDER BY
	syb
--	,mn
	,start_w
--	, tp
;


SYB               MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 Z          2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 Z          2019-03-05 2019-03-14             8             1             5             1             5
GLOBEX             1 Z          2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 Z          2019-03-02 2019-03-07             8             1             7             1             2
GLOBEX             3 Z          2019-03-07 2019-03-15             2             1             9             1             4
OSCORP             1 Z          2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 Z          2019-03-04 2019-03-10             8             2             5             3             8
OSCORP             3 Z          2019-03-10 2019-03-15             8             7             7             5             9

8 rows selected.

Elapsed: 00:00:00.00

AFTER MATCH SKIP TO LAST Zの場合。明細行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
		AFTER MATCH SKIP TO LAST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	,mn
--	,start_w
	, tp
;

SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-03-05             2 STRT       2019-03-05                        8                                                                     8
ACME   2019-03-06             2 X          2019-03-05                        8             7                                                       7
ACME   2019-03-07             2 X          2019-03-05                        8             1                                                       1
ACME   2019-03-08             2 X          2019-03-05                        8             1                                                       1
ACME   2019-03-09             2 Y          2019-03-05                        8             1             2                                         2
ACME   2019-03-10             2 Y          2019-03-05                        8             1             5                                         5
ACME   2019-03-11             2 W          2019-03-05                        8             1             5             1                           1
ACME   2019-03-12             2 Z          2019-03-05 2019-03-12             8             1             5             1             5             5
ACME   2019-03-13             2 Z          2019-03-05 2019-03-13             8             1             5             1             5             5
ACME   2019-03-14             2 Z          2019-03-05 2019-03-14             8             1             5             1             5             5

19 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO FIRST Xの場合。サマリ行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
--		ALL ROWS PER MATCH
		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		AFTER MATCH SKIP TO FIRST X
--		AFTER MATCH SKIP TO FIRST Y
--		AFTER MATCH SKIP TO FIRST W
--		AFTER MATCH SKIP TO FIRST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
--WHERE
--	syb = 'ACME'
ORDER BY
	syb
--	,mn
	,start_w
--	, tp
;

SYB               MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 Z          2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 Z          2019-02-28 2019-03-10             8             1             8             1             5
ACME               3 Z          2019-03-01 2019-03-10             4             1             8             1             5
ACME               4 Z          2019-03-02 2019-03-10             4             1             8             1             5
ACME               5 Z          2019-03-05 2019-03-14             8             1             5             1             5
ACME               6 Z          2019-03-06 2019-03-14             7             1             5             1             5
ACME               7 Z          2019-03-07 2019-03-14             1             1             5             1             5
ACME               8 Z          2019-03-10 2019-03-14             5             1             5             5             5
GLOBEX             1 Z          2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 Z          2019-02-25 2019-03-02             3             1             3             1             8
GLOBEX             3 Z          2019-02-27 2019-03-05             3             1             8             1             7
GLOBEX             4 Z          2019-03-02 2019-03-07             8             1             7             1             2
GLOBEX             5 Z          2019-03-05 2019-03-11             7             1             2             1             9
GLOBEX             6 Z          2019-03-07 2019-03-15             2             1             9             1             4
GLOBEX             7 Z          2019-03-11 2019-03-15             9             8             8             1             4
OSCORP             1 Z          2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 Z          2019-02-26 2019-03-04             8             2             7             3             8
OSCORP             3 Z          2019-02-27 2019-03-04             6             2             7             3             8
OSCORP             4 Z          2019-03-02 2019-03-07             7             3             8             2             5
OSCORP             5 Z          2019-03-04 2019-03-10             8             2             5             3             8
OSCORP             6 Z          2019-03-05 2019-03-10             2             2             5             3             8
OSCORP             7 Z          2019-03-07 2019-03-15             5             3             8             5             9
OSCORP             8 Z          2019-03-09 2019-03-15             8             7             7             5             9
OSCORP             9 Z          2019-03-10 2019-03-15             8             7             7             5             9

24 rows selected.

Elapsed: 00:00:00.00


AFTER MATCH SKIP TO FIRST Xの場合。明細行。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
		AFTER MATCH SKIP TO FIRST X
--		AFTER MATCH SKIP TO FIRST Y
--		AFTER MATCH SKIP TO FIRST W
--		AFTER MATCH SKIP TO FIRST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	,mn
--	,start_w
	, tp
;

SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-02-28             2 STRT       2019-02-28                        8                                                                     8
ACME   2019-03-01             2 X          2019-02-28                        8             4                                                       4
ACME   2019-03-02             2 X          2019-02-28                        8             4                                                       4
ACME   2019-03-03             2 X          2019-02-28                        8             1                                                       1
ACME   2019-03-04             2 Y          2019-02-28                        8             1             6                                         6
ACME   2019-03-05             2 Y          2019-02-28                        8             1             8                                         8
ACME   2019-03-06             2 W          2019-02-28                        8             1             8             7                           7
ACME   2019-03-07             2 W          2019-02-28                        8             1             8             1                           1
ACME   2019-03-08             2 W          2019-02-28                        8             1             8             1                           1
ACME   2019-03-09             2 Z          2019-02-28 2019-03-09             8             1             8             1             2             2
ACME   2019-03-10             2 Z          2019-02-28 2019-03-10             8             1             8             1             5             5
ACME   2019-03-01             3 STRT       2019-03-01                        4                                                                     4
ACME   2019-03-02             3 X          2019-03-01                        4             4                                                       4
ACME   2019-03-03             3 X          2019-03-01                        4             1                                                       1
ACME   2019-03-04             3 Y          2019-03-01                        4             1             6                                         6
ACME   2019-03-05             3 Y          2019-03-01                        4             1             8                                         8
ACME   2019-03-06             3 W          2019-03-01                        4             1             8             7                           7
ACME   2019-03-07             3 W          2019-03-01                        4             1             8             1                           1
ACME   2019-03-08             3 W          2019-03-01                        4             1             8             1                           1
ACME   2019-03-09             3 Z          2019-03-01 2019-03-09             4             1             8             1             2             2
ACME   2019-03-10             3 Z          2019-03-01 2019-03-10             4             1             8             1             5             5
ACME   2019-03-02             4 STRT       2019-03-02                        4                                                                     4
ACME   2019-03-03             4 X          2019-03-02                        4             1                                                       1
ACME   2019-03-04             4 Y          2019-03-02                        4             1             6                                         6
ACME   2019-03-05             4 Y          2019-03-02                        4             1             8                                         8
ACME   2019-03-06             4 W          2019-03-02                        4             1             8             7                           7
ACME   2019-03-07             4 W          2019-03-02                        4             1             8             1                           1
ACME   2019-03-08             4 W          2019-03-02                        4             1             8             1                           1
ACME   2019-03-09             4 Z          2019-03-02 2019-03-09             4             1             8             1             2             2
ACME   2019-03-10             4 Z          2019-03-02 2019-03-10             4             1             8             1             5             5
ACME   2019-03-05             5 STRT       2019-03-05                        8                                                                     8
ACME   2019-03-06             5 X          2019-03-05                        8             7                                                       7
ACME   2019-03-07             5 X          2019-03-05                        8             1                                                       1
ACME   2019-03-08             5 X          2019-03-05                        8             1                                                       1
ACME   2019-03-09             5 Y          2019-03-05                        8             1             2                                         2
ACME   2019-03-10             5 Y          2019-03-05                        8             1             5                                         5
ACME   2019-03-11             5 W          2019-03-05                        8             1             5             1                           1
ACME   2019-03-12             5 Z          2019-03-05 2019-03-12             8             1             5             1             5             5
ACME   2019-03-13             5 Z          2019-03-05 2019-03-13             8             1             5             1             5             5
ACME   2019-03-14             5 Z          2019-03-05 2019-03-14             8             1             5             1             5             5
ACME   2019-03-06             6 STRT       2019-03-06                        7                                                                     7
ACME   2019-03-07             6 X          2019-03-06                        7             1                                                       1
ACME   2019-03-08             6 X          2019-03-06                        7             1                                                       1
ACME   2019-03-09             6 Y          2019-03-06                        7             1             2                                         2
ACME   2019-03-10             6 Y          2019-03-06                        7             1             5                                         5
ACME   2019-03-11             6 W          2019-03-06                        7             1             5             1                           1
ACME   2019-03-12             6 Z          2019-03-06 2019-03-12             7             1             5             1             5             5
ACME   2019-03-13             6 Z          2019-03-06 2019-03-13             7             1             5             1             5             5
ACME   2019-03-14             6 Z          2019-03-06 2019-03-14             7             1             5             1             5             5
ACME   2019-03-07             7 STRT       2019-03-07                        1                                                                     1
ACME   2019-03-08             7 X          2019-03-07                        1             1                                                       1
ACME   2019-03-09             7 Y          2019-03-07                        1             1             2                                         2
ACME   2019-03-10             7 Y          2019-03-07                        1             1             5                                         5
ACME   2019-03-11             7 W          2019-03-07                        1             1             5             1                           1
ACME   2019-03-12             7 Z          2019-03-07 2019-03-12             1             1             5             1             5             5
ACME   2019-03-13             7 Z          2019-03-07 2019-03-13             1             1             5             1             5             5
ACME   2019-03-14             7 Z          2019-03-07 2019-03-14             1             1             5             1             5             5
ACME   2019-03-10             8 STRT       2019-03-10                        5                                                                     5
ACME   2019-03-11             8 X          2019-03-10                        5             1                                                       1
ACME   2019-03-12             8 Y          2019-03-10                        5             1             5                                         5
ACME   2019-03-13             8 W          2019-03-10                        5             1             5             5                           5
ACME   2019-03-14             8 Z          2019-03-10 2019-03-14             5             1             5             5             5             5

71 rows selected.

Elapsed: 00:00:00.02

AFTER MATCH SKIP TO FIRST Yの場合。サマリ行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
--		ALL ROWS PER MATCH
		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
--		AFTER MATCH SKIP TO FIRST X
		AFTER MATCH SKIP TO FIRST Y
--		AFTER MATCH SKIP TO FIRST W
--		AFTER MATCH SKIP TO FIRST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
--WHERE
--	syb = 'ACME'
ORDER BY
	syb
--	,mn
	,start_w
--	, tp
;

SYB               MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 Z          2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 Z          2019-02-28 2019-03-10             8             1             8             1             5
ACME               3 Z          2019-03-05 2019-03-14             8             1             5             1             5
ACME               4 Z          2019-03-10 2019-03-14             5             1             5             5             5
GLOBEX             1 Z          2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 Z          2019-02-27 2019-03-05             3             1             8             1             7
GLOBEX             3 Z          2019-03-02 2019-03-07             8             1             7             1             2
GLOBEX             4 Z          2019-03-05 2019-03-11             7             1             2             1             9
GLOBEX             5 Z          2019-03-07 2019-03-15             2             1             9             1             4
GLOBEX             6 Z          2019-03-11 2019-03-15             9             8             8             1             4
OSCORP             1 Z          2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 Z          2019-03-02 2019-03-07             7             3             8             2             5
OSCORP             3 Z          2019-03-04 2019-03-10             8             2             5             3             8
OSCORP             4 Z          2019-03-07 2019-03-15             5             3             8             5             9
OSCORP             5 Z          2019-03-09 2019-03-15             8             7             7             5             9

15 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO FIRST Yの場合。明細行。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
--		AFTER MATCH SKIP TO FIRST X
		AFTER MATCH SKIP TO FIRST Y
--		AFTER MATCH SKIP TO FIRST W
--		AFTER MATCH SKIP TO FIRST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	,mn
--	,start_w
	, tp
;

SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-02-28             2 STRT       2019-02-28                        8                                                                     8
ACME   2019-03-01             2 X          2019-02-28                        8             4                                                       4
ACME   2019-03-02             2 X          2019-02-28                        8             4                                                       4
ACME   2019-03-03             2 X          2019-02-28                        8             1                                                       1
ACME   2019-03-04             2 Y          2019-02-28                        8             1             6                                         6
ACME   2019-03-05             2 Y          2019-02-28                        8             1             8                                         8
ACME   2019-03-06             2 W          2019-02-28                        8             1             8             7                           7
ACME   2019-03-07             2 W          2019-02-28                        8             1             8             1                           1
ACME   2019-03-08             2 W          2019-02-28                        8             1             8             1                           1
ACME   2019-03-09             2 Z          2019-02-28 2019-03-09             8             1             8             1             2             2
ACME   2019-03-10             2 Z          2019-02-28 2019-03-10             8             1             8             1             5             5
ACME   2019-03-05             3 STRT       2019-03-05                        8                                                                     8
ACME   2019-03-06             3 X          2019-03-05                        8             7                                                       7
ACME   2019-03-07             3 X          2019-03-05                        8             1                                                       1
ACME   2019-03-08             3 X          2019-03-05                        8             1                                                       1
ACME   2019-03-09             3 Y          2019-03-05                        8             1             2                                         2
ACME   2019-03-10             3 Y          2019-03-05                        8             1             5                                         5
ACME   2019-03-11             3 W          2019-03-05                        8             1             5             1                           1
ACME   2019-03-12             3 Z          2019-03-05 2019-03-12             8             1             5             1             5             5
ACME   2019-03-13             3 Z          2019-03-05 2019-03-13             8             1             5             1             5             5
ACME   2019-03-14             3 Z          2019-03-05 2019-03-14             8             1             5             1             5             5
ACME   2019-03-10             4 STRT       2019-03-10                        5                                                                     5
ACME   2019-03-11             4 X          2019-03-10                        5             1                                                       1
ACME   2019-03-12             4 Y          2019-03-10                        5             1             5                                         5
ACME   2019-03-13             4 W          2019-03-10                        5             1             5             5                           5
ACME   2019-03-14             4 Z          2019-03-10 2019-03-14             5             1             5             5             5             5

35 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO FIRST Wの場合。サマリ行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
--		ALL ROWS PER MATCH
		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
--		AFTER MATCH SKIP TO FIRST X
--		AFTER MATCH SKIP TO FIRST Y
		AFTER MATCH SKIP TO FIRST W
--		AFTER MATCH SKIP TO FIRST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
--WHERE
--	syb = 'ACME'
ORDER BY
	syb
--	,mn
	,start_w
--	, tp
;

SYB               MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 Z          2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 Z          2019-03-01 2019-03-10             4             1             8             1             5
ACME               3 Z          2019-03-06 2019-03-14             7             1             5             1             5
GLOBEX             1 Z          2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 Z          2019-03-02 2019-03-07             8             1             7             1             2
GLOBEX             3 Z          2019-03-07 2019-03-15             2             1             9             1             4
OSCORP             1 Z          2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 Z          2019-03-04 2019-03-10             8             2             5             3             8
OSCORP             3 Z          2019-03-09 2019-03-15             8             7             7             5             9

9 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO FIRST Wの場合。明細行。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
--		AFTER MATCH SKIP TO FIRST X
--		AFTER MATCH SKIP TO FIRST Y
		AFTER MATCH SKIP TO FIRST W
--		AFTER MATCH SKIP TO FIRST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	,mn
--	,start_w
	, tp
;

SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-03-01             2 STRT       2019-03-01                        4                                                                     4
ACME   2019-03-02             2 X          2019-03-01                        4             4                                                       4
ACME   2019-03-03             2 X          2019-03-01                        4             1                                                       1
ACME   2019-03-04             2 Y          2019-03-01                        4             1             6                                         6
ACME   2019-03-05             2 Y          2019-03-01                        4             1             8                                         8
ACME   2019-03-06             2 W          2019-03-01                        4             1             8             7                           7
ACME   2019-03-07             2 W          2019-03-01                        4             1             8             1                           1
ACME   2019-03-08             2 W          2019-03-01                        4             1             8             1                           1
ACME   2019-03-09             2 Z          2019-03-01 2019-03-09             4             1             8             1             2             2
ACME   2019-03-10             2 Z          2019-03-01 2019-03-10             4             1             8             1             5             5
ACME   2019-03-06             3 STRT       2019-03-06                        7                                                                     7
ACME   2019-03-07             3 X          2019-03-06                        7             1                                                       1
ACME   2019-03-08             3 X          2019-03-06                        7             1                                                       1
ACME   2019-03-09             3 Y          2019-03-06                        7             1             2                                         2
ACME   2019-03-10             3 Y          2019-03-06                        7             1             5                                         5
ACME   2019-03-11             3 W          2019-03-06                        7             1             5             1                           1
ACME   2019-03-12             3 Z          2019-03-06 2019-03-12             7             1             5             1             5             5
ACME   2019-03-13             3 Z          2019-03-06 2019-03-13             7             1             5             1             5             5
ACME   2019-03-14             3 Z          2019-03-06 2019-03-14             7             1             5             1             5             5

28 rows selected.

Elapsed: 00:00:00.00


AFTER MATCH SKIP TO FIRST Zの場合。サマリ行から。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
--		ALL ROWS PER MATCH
		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
--		AFTER MATCH SKIP TO FIRST X
--		AFTER MATCH SKIP TO FIRST Y
--		AFTER MATCH SKIP TO FIRST W
		AFTER MATCH SKIP TO FIRST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
--WHERE
--	syb = 'ACME'
ORDER BY
	syb
--	,mn
	,start_w
--	, tp
;

SYB               MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY
------ ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- -------------
ACME               1 Z          2019-02-25 2019-03-05             6             1             8             1             8
ACME               2 Z          2019-03-05 2019-03-14             8             1             5             1             5
GLOBEX             1 Z          2019-02-24 2019-03-02             4             1             3             1             8
GLOBEX             2 Z          2019-03-02 2019-03-07             8             1             7             1             2
GLOBEX             3 Z          2019-03-07 2019-03-15             2             1             9             1             4
OSCORP             1 Z          2019-02-25 2019-03-04             8             2             7             3             8
OSCORP             2 Z          2019-03-04 2019-03-10             8             2             5             3             8
OSCORP             3 Z          2019-03-09 2019-03-15             8             7             7             5             9

8 rows selected.

Elapsed: 00:00:00.01

AFTER MATCH SKIP TO FIRST Zの場合。明細行。

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker match_recognize (
	PARTITION BY syb
	ORDER BY tp
	MEASURES
		match_number() as mn
		, classifier() as cls
		, strt.tp AS start_w
		, LAST(z.tp) AS end_w
		, strt.qty as start_qty
		, x.qty as x_qty
		, y.qty as y_qty
		, w.qty as w_qty
		, z.qty as z_qty
		ALL ROWS PER MATCH
--		ONE ROW PER MATCH
--		AFTER MATCH SKIP PAST LAST ROW
--		AFTER MATCH SKIP TO LAST X
--		AFTER MATCH SKIP TO LAST Y
--		AFTER MATCH SKIP TO LAST W
--		AFTER MATCH SKIP TO LAST Z
--		AFTER MATCH SKIP TO FIRST X
--		AFTER MATCH SKIP TO FIRST Y
--		AFTER MATCH SKIP TO FIRST W
		AFTER MATCH SKIP TO FIRST Z
		PATTERN (STRT x+ y+ w+ z+)
		DEFINE
			x AS x.qty <= prev(x.qty)
			, y AS y.qty >= prev(y.qty)
			, w AS w.qty <= prev(w.qty)
			, z AS z.qty >= prev(z.qty) 
) mr
WHERE
	syb = 'ACME'
ORDER BY
	syb
	,mn
--	,start_w
	, tp
;

SYB    TP                    MN CLS        START_W    END_W          START_QTY         X_QTY         Y_QTY         W_QTY         Z_QTY           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6                                                                     6
ACME   2019-02-26             1 X          2019-02-25                        6             1                                                       1
ACME   2019-02-27             1 Y          2019-02-25                        6             1             5                                         5
ACME   2019-02-28             1 Y          2019-02-25                        6             1             8                                         8
ACME   2019-03-01             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-02             1 W          2019-02-25                        6             1             8             4                           4
ACME   2019-03-03             1 W          2019-02-25                        6             1             8             1                           1
ACME   2019-03-04             1 Z          2019-02-25 2019-03-04             6             1             8             1             6             6
ACME   2019-03-05             1 Z          2019-02-25 2019-03-05             6             1             8             1             8             8
ACME   2019-03-05             2 STRT       2019-03-05                        8                                                                     8
ACME   2019-03-06             2 X          2019-03-05                        8             7                                                       7
ACME   2019-03-07             2 X          2019-03-05                        8             1                                                       1
ACME   2019-03-08             2 X          2019-03-05                        8             1                                                       1
ACME   2019-03-09             2 Y          2019-03-05                        8             1             2                                         2
ACME   2019-03-10             2 Y          2019-03-05                        8             1             5                                         5
ACME   2019-03-11             2 W          2019-03-05                        8             1             5             1                           1
ACME   2019-03-12             2 Z          2019-03-05 2019-03-12             8             1             5             1             5             5
ACME   2019-03-13             2 Z          2019-03-05 2019-03-13             8             1             5             1             5             5
ACME   2019-03-14             2 Z          2019-03-05 2019-03-14             8             1             5             1             5             5

19 rows selected.

Elapsed: 00:00:00.01

SUBSETに関して

パタン句にサブセット句指定できるけど、これなにとなったので、調べた。パタン句に定義したパタン変数をグルーピングして定義し、それを集約単位、集計単位に利用できる模様。。移動平均とか、累積和とか言ってたりしたやつとかも、sum(case when
x in (hogehoge,togetoge) then lll else kkk end )…みたいに書かなくてもすむ。パタンとして明示的に定義してサブセットパタンごとに計算できるから。べんりだなー。なおサブセットはカンマ区切りで複数定義できる模様。

まずはサマリ行から

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker MATCH_RECOGNIZE(
		PARTITION BY syb
		ORDER BY tp
		MEASURES
			match_number() AS mn
			,classifier() AS cls
			,FIRST(strt.tp) AS strt_time
			,LAST(down.tp) AS bottom
			,TRUNC(AVG(stdn.qty),1) AS stdn_avg_qty
			,COUNT(stdn.*) AS stdn_cnt
			,SUM(stdn.qty) AS stdn_run_sum
			,TRUNC(AVG(dnup.qty),1) AS dnup_avg_qty
			,COUNT(dnup.*) AS dnup_cnt
			,SUM(dnup.qty) AS dnup_run_sum
     ONE ROW PER MATCH
--     ALL ROWS PER MATCH
     AFTER MATCH SKIP TO LAST UP
     PATTERN (strt down+ UP+)
     SUBSET stdn= (strt, down),dnup= (down, up)
     DEFINE
        UP AS UP.qty > prev(UP.qty)
		,down AS down.qty < prev (down.qty)
);

SYB               MN CLS        STRT_TIME  BOTTOM      STDN_AVG_QTY      STDN_CNT  STDN_RUN_SUM  DNUP_AVG_QTY      DNUP_CNT  DNUP_RUN_SUM
------ ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- -------------
ACME               1 UP         2019-02-25 2019-02-26           3.5             2             7           4.6             3            14
ACME               2 UP         2019-03-02 2019-03-03           2.5             2             5             5             3            15
ACME               3 UP         2019-03-10 2019-03-11             3             2             6             3             2             6
GLOBEX             1 UP         2019-02-24 2019-02-26           2.6             3             8           2.3             3             7
GLOBEX             2 UP         2019-02-27 2019-02-28             2             2             4           3.6             3            11
GLOBEX             3 UP         2019-03-02 2019-03-03           4.5             2             9           4.6             3            14
GLOBEX             4 UP         2019-03-05 2019-03-06             4             2             8           1.5             2             3
GLOBEX             5 UP         2019-03-07 2019-03-08           1.5             2             3           5.7             4            23
GLOBEX             6 UP         2019-03-13 2019-03-14           4.5             2             9           2.5             2             5
OSCORP             1 UP         2019-02-26 2019-02-28           5.3             3            16           5.2             4            21
OSCORP             2 UP         2019-03-02 2019-03-03             5             2            10           5.5             2            11
OSCORP             3 UP         2019-03-07 2019-03-08             4             2             8           5.5             2            11
OSCORP             4 UP         2019-03-12 2019-03-13             6             2            12             7             2            14

13 rows selected.

Elapsed: 00:00:00.01

次に明細行

コード表示

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT
	*
FROM
	ticker MATCH_RECOGNIZE(
		PARTITION BY syb
		ORDER BY tp
		MEASURES
			match_number() AS mn
			,classifier() AS cls
			,FIRST(strt.tp) AS strt_time
			,LAST(down.tp) AS bottom
			,TRUNC(AVG(stdn.qty),1) AS stdn_avg_qty
			,COUNT(stdn.*) AS stdn_cnt
			,SUM(stdn.qty) AS stdn_run_sum
			,TRUNC(AVG(dnup.qty),1) AS dnup_avg_qty
			,COUNT(dnup.*) AS dnup_cnt
			,SUM(dnup.qty) AS dnup_run_sum
--     ONE ROW PER MATCH
     ALL ROWS PER MATCH
     AFTER MATCH SKIP TO LAST UP
     PATTERN (strt down+ UP+)
     SUBSET stdn= (strt, down),dnup= (down, up)
     DEFINE
        UP AS UP.qty > prev(UP.qty)
		,down AS down.qty < prev (down.qty)
);

SYB    TP                    MN CLS        STRT_TIME  BOTTOM      STDN_AVG_QTY      STDN_CNT  STDN_RUN_SUM  DNUP_AVG_QTY      DNUP_CNT  DNUP_RUN_SUM           QTY
------ ---------- ------------- ---------- ---------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
ACME   2019-02-25             1 STRT       2019-02-25                        6             1             6                           0                           6
ACME   2019-02-26             1 DOWN       2019-02-25 2019-02-26           3.5             2             7             1             1             1             1
ACME   2019-02-27             1 UP         2019-02-25 2019-02-26           3.5             2             7             3             2             6             5
ACME   2019-02-28             1 UP         2019-02-25 2019-02-26           3.5             2             7           4.6             3            14             8
ACME   2019-03-02             2 STRT       2019-03-02                        4             1             4                           0                           4
ACME   2019-03-03             2 DOWN       2019-03-02 2019-03-03           2.5             2             5             1             1             1             1
ACME   2019-03-04             2 UP         2019-03-02 2019-03-03           2.5             2             5           3.5             2             7             6
ACME   2019-03-05             2 UP         2019-03-02 2019-03-03           2.5             2             5             5             3            15             8
ACME   2019-03-10             3 STRT       2019-03-10                        5             1             5                           0                           5
ACME   2019-03-11             3 DOWN       2019-03-10 2019-03-11             3             2             6             1             1             1             1
ACME   2019-03-12             3 UP         2019-03-10 2019-03-11             3             2             6             3             2             6             5
GLOBEX 2019-02-24             1 STRT       2019-02-24                        4             1             4                           0                           4
GLOBEX 2019-02-25             1 DOWN       2019-02-24 2019-02-25           3.5             2             7             3             1             3             3
GLOBEX 2019-02-26             1 DOWN       2019-02-24 2019-02-26           2.6             3             8             2             2             4             1
GLOBEX 2019-02-27             1 UP         2019-02-24 2019-02-26           2.6             3             8           2.3             3             7             3
GLOBEX 2019-02-27             2 STRT       2019-02-27                        3             1             3                           0                           3
GLOBEX 2019-02-28             2 DOWN       2019-02-27 2019-02-28             2             2             4             1             1             1             1
GLOBEX 2019-03-01             2 UP         2019-02-27 2019-02-28             2             2             4           1.5             2             3             2
GLOBEX 2019-03-02             2 UP         2019-02-27 2019-02-28             2             2             4           3.6             3            11             8
GLOBEX 2019-03-02             3 STRT       2019-03-02                        8             1             8                           0                           8
GLOBEX 2019-03-03             3 DOWN       2019-03-02 2019-03-03           4.5             2             9             1             1             1             1
GLOBEX 2019-03-04             3 UP         2019-03-02 2019-03-03           4.5             2             9           3.5             2             7             6
GLOBEX 2019-03-05             3 UP         2019-03-02 2019-03-03           4.5             2             9           4.6             3            14             7
GLOBEX 2019-03-05             4 STRT       2019-03-05                        7             1             7                           0                           7
GLOBEX 2019-03-06             4 DOWN       2019-03-05 2019-03-06             4             2             8             1             1             1             1
GLOBEX 2019-03-07             4 UP         2019-03-05 2019-03-06             4             2             8           1.5             2             3             2
GLOBEX 2019-03-07             5 STRT       2019-03-07                        2             1             2                           0                           2
GLOBEX 2019-03-08             5 DOWN       2019-03-07 2019-03-08           1.5             2             3             1             1             1             1
GLOBEX 2019-03-09             5 UP         2019-03-07 2019-03-08           1.5             2             3             3             2             6             5
GLOBEX 2019-03-10             5 UP         2019-03-07 2019-03-08           1.5             2             3           4.6             3            14             8
GLOBEX 2019-03-11             5 UP         2019-03-07 2019-03-08           1.5             2             3           5.7             4            23             9
GLOBEX 2019-03-13             6 STRT       2019-03-13                        8             1             8                           0                           8
GLOBEX 2019-03-14             6 DOWN       2019-03-13 2019-03-14           4.5             2             9             1             1             1             1
GLOBEX 2019-03-15             6 UP         2019-03-13 2019-03-14           4.5             2             9           2.5             2             5             4
OSCORP 2019-02-26             1 STRT       2019-02-26                        8             1             8                           0                           8
OSCORP 2019-02-27             1 DOWN       2019-02-26 2019-02-27             7             2            14             6             1             6             6
OSCORP 2019-02-28             1 DOWN       2019-02-26 2019-02-28           5.3             3            16             4             2             8             2
OSCORP 2019-03-01             1 UP         2019-02-26 2019-02-28           5.3             3            16           4.6             3            14             6
OSCORP 2019-03-02             1 UP         2019-02-26 2019-02-28           5.3             3            16           5.2             4            21             7
OSCORP 2019-03-02             2 STRT       2019-03-02                        7             1             7                           0                           7
OSCORP 2019-03-03             2 DOWN       2019-03-02 2019-03-03             5             2            10             3             1             3             3
OSCORP 2019-03-04             2 UP         2019-03-02 2019-03-03             5             2            10           5.5             2            11             8
OSCORP 2019-03-07             3 STRT       2019-03-07                        5             1             5                           0                           5
OSCORP 2019-03-08             3 DOWN       2019-03-07 2019-03-08             4             2             8             3             1             3             3
OSCORP 2019-03-09             3 UP         2019-03-07 2019-03-08             4             2             8           5.5             2            11             8
OSCORP 2019-03-12             4 STRT       2019-03-12                        7             1             7                           0                           7
OSCORP 2019-03-13             4 DOWN       2019-03-12 2019-03-13             6             2            12             5             1             5             5
OSCORP 2019-03-14             4 UP         2019-03-12 2019-03-13             6             2            12             7             2            14             9

48 rows selected.

Elapsed: 00:00:00.01

正規表現に関して

だいたいつかえる。POSIXの文法に従う。

PATTERN: Defining the Row Pattern to Be Matched  

prev,next,first,lastとかいうnaviについて

ちょっとしたサンプルで確認。

コード表示

DROP TABLE test___$_____ PURGE;
CREATE TABLE test___$_____ AS
SELECT
	LEVEL AS rn
	,LEVEL*10 AS qty
FROM
	dual
CONNECT BY
	LEVEL <= 5
;

SELECT * FROM test___$_____;

           RN           QTY
------------- -------------
            1            10
            2            20
            3            30
            4            40
            5            50

5 rows selected.

Elapsed: 00:00:00.00

firstの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
			,FIRST(UP.qty) AS f___qty
			,FIRST(UP.qty,0) AS f_0_qty
			,FIRST(UP.qty,1) AS f_1_qty
			,FIRST(UP.qty,2) AS f_2_qty
			,FIRST(UP.qty,3) AS f_3_qty
			,FIRST(UP.qty,4) AS f_4_qty
			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

           MN CLS              F___QTY       F_0_QTY       F_1_QTY       F_2_QTY       F_3_QTY       F_4_QTY       F_5_QTY            RN           QTY
------------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
            1 UP                    10            10                                                                                   1            10
            1 UP                    10            10            20                                                                     2            20
            1 UP                    10            10            20            30                                                       3            30
            1 UP                    10            10            20            30            40                                         4            40
            1 UP                    10            10            20            30            40            50                           5            50

5 rows selected.

Elapsed: 00:00:00.01

running firstの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
			,running FIRST(UP.qty) AS r_f___qty
			,running FIRST(UP.qty,0) AS r_f_0_qty
			,running FIRST(UP.qty,1) AS r_f_1_qty
			,running FIRST(UP.qty,2) AS r_f_2_qty
			,running FIRST(UP.qty,3) AS r_f_3_qty
			,running FIRST(UP.qty,4) AS r_f_4_qty
			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

           MN CLS            R_F___QTY     R_F_0_QTY     R_F_1_QTY     R_F_2_QTY     R_F_3_QTY     R_F_4_QTY     R_F_5_QTY            RN           QTY
------------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
            1 UP                    10            10                                                                                   1            10
            1 UP                    10            10            20                                                                     2            20
            1 UP                    10            10            20            30                                                       3            30
            1 UP                    10            10            20            30            40                                         4            40
            1 UP                    10            10            20            30            40            50                           5            50

5 rows selected.

Elapsed: 00:00:00.01

final firstの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
			,final FIRST(UP.qty) AS f_f___qty
			,final FIRST(UP.qty,0) AS f_f_0_qty
			,final FIRST(UP.qty,1) AS f_f_1_qty
			,final FIRST(UP.qty,2) AS f_f_2_qty
			,final FIRST(UP.qty,3) AS f_f_3_qty
			,final FIRST(UP.qty,4) AS f_f_4_qty
			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

           MN CLS            F_F___QTY     F_F_0_QTY     F_F_1_QTY     F_F_2_QTY     F_F_3_QTY     F_F_4_QTY     F_F_5_QTY            RN           QTY
------------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
            1 UP                    10            10            20            30            40            50                           1            10
            1 UP                    10            10            20            30            40            50                           2            20
            1 UP                    10            10            20            30            40            50                           3            30
            1 UP                    10            10            20            30            40            50                           4            40
            1 UP                    10            10            20            30            40            50                           5            50

5 rows selected.

Elapsed: 00:00:00.00

lastの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
			,LAST(UP.qty) AS l___qty
			,LAST(UP.qty,0) AS l_0_qty
			,LAST(UP.qty,1) AS l_1_qty
			,LAST(UP.qty,2) AS l_2_qty
			,LAST(UP.qty,3) AS l_3_qty
			,LAST(UP.qty,4) AS l_4_qty
			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

           MN CLS              L___QTY       L_0_QTY       L_1_QTY       L_2_QTY       L_3_QTY       L_4_QTY       L_5_QTY            RN           QTY
------------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
            1 UP                    10            10                                                                                   1            10
            1 UP                    20            20            10                                                                     2            20
            1 UP                    30            30            20            10                                                       3            30
            1 UP                    40            40            30            20            10                                         4            40
            1 UP                    50            50            40            30            20            10                           5            50

5 rows selected.

Elapsed: 00:00:00.00

running lastの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
			,running LAST(UP.qty) AS r_l___qty
			,running LAST(UP.qty,0) AS r_l_0_qty
			,running LAST(UP.qty,1) AS r_l_1_qty
			,running LAST(UP.qty,2) AS r_l_2_qty
			,running LAST(UP.qty,3) AS r_l_3_qty
			,running LAST(UP.qty,4) AS r_l_4_qty
			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

           MN CLS            R_L___QTY     R_L_0_QTY     R_L_1_QTY     R_L_2_QTY     R_L_3_QTY     R_L_4_QTY     R_L_5_QTY            RN           QTY
------------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
            1 UP                    10            10                                                                                   1            10
            1 UP                    20            20            10                                                                     2            20
            1 UP                    30            30            20            10                                                       3            30
            1 UP                    40            40            30            20            10                                         4            40
            1 UP                    50            50            40            30            20            10                           5            50

5 rows selected.

Elapsed: 00:00:00.01


final lastの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
			,final LAST(UP.qty) AS f_l___qty
			,final LAST(UP.qty,0) AS f_l_0_qty
			,final LAST(UP.qty,1) AS f_l_1_qty
			,final LAST(UP.qty,2) AS f_l_2_qty
			,final LAST(UP.qty,3) AS f_l_3_qty
			,final LAST(UP.qty,4) AS f_l_4_qty
			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;


           MN CLS            F_L___QTY     F_L_0_QTY     F_L_1_QTY     F_L_2_QTY     F_L_3_QTY     F_L_4_QTY     F_L_5_QTY            RN           QTY
------------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
            1 UP                    50            50            40            30            20            10                           1            10
            1 UP                    50            50            40            30            20            10                           2            20
            1 UP                    50            50            40            30            20            10                           3            30
            1 UP                    50            50            40            30            20            10                           4            40
            1 UP                    50            50            40            30            20            10                           5            50

5 rows selected.

Elapsed: 00:00:00.00


prevの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
			,prev(UP.qty) AS p___qty
			,prev(UP.qty,0) AS p_0_qty
			,prev(UP.qty,1) AS p_1_qty
			,prev(UP.qty,2) AS p_2_qty
			,prev(UP.qty,3) AS p_3_qty
			,prev(UP.qty,4) AS p_4_qty
			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

           MN CLS              P___QTY       P_0_QTY       P_1_QTY       P_2_QTY       P_3_QTY       P_4_QTY       P_5_QTY            RN           QTY
------------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
            1 UP                                  10                                                                                   1            10
            1 UP                    10            20            10                                                                     2            20
            1 UP                    20            30            20            10                                                       3            30
            1 UP                    30            40            30            20            10                                         4            40
            1 UP                    40            50            40            30            20            10                           5            50

5 rows selected.

Elapsed: 00:00:00.01

running prevの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
			,running prev(UP.qty) AS r_p___qty
			,running prev(UP.qty,0) AS r_p_0_qty
			,running prev(UP.qty,1) AS r_p_1_qty
			,running prev(UP.qty,2) AS r_p_2_qty
			,running prev(UP.qty,3) AS r_p_3_qty
			,running prev(UP.qty,4) AS r_p_4_qty
			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

ERROR at line 58:
ORA-62509: illegal use of RUNNING or FINAL in MATCH_RECOGNIZE clause


Elapsed: 00:00:00.01

final prevの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
			,final prev(UP.qty) AS f_p___qty
			,final prev(UP.qty,0) AS f_p_0_qty
			,final prev(UP.qty,1) AS f_p_1_qty
			,final prev(UP.qty,2) AS f_p_2_qty
			,final prev(UP.qty,3) AS f_p_3_qty
			,final prev(UP.qty,4) AS f_p_4_qty
			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

ERROR at line 65:
ORA-62509: illegal use of RUNNING or FINAL in MATCH_RECOGNIZE clause


Elapsed: 00:00:00.00


nextの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
			,NEXT(UP.qty) AS n___qty
			,NEXT(UP.qty,0) AS n_0_qty
			,NEXT(UP.qty,1) AS n_1_qty
			,NEXT(UP.qty,2) AS n_2_qty
			,NEXT(UP.qty,3) AS n_3_qty
			,NEXT(UP.qty,4) AS n_4_qty
			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

           MN CLS              N___QTY       N_0_QTY       N_1_QTY       N_2_QTY       N_3_QTY       N_4_QTY       N_5_QTY            RN           QTY
------------- ---------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
            1 UP                    20            10            20            30            40            50                           1            10
            1 UP                    30            20            30            40            50                                         2            20
            1 UP                    40            30            40            50                                                       3            30
            1 UP                    50            40            50                                                                     4            40
            1 UP                                  50                                                                                   5            50

5 rows selected.

Elapsed: 00:00:00.00

running nextの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
			,running NEXT(UP.qty) AS r_n___qty
			,running NEXT(UP.qty,0) AS r_n_0_qty
			,running NEXT(UP.qty,1) AS r_n_1_qty
			,running NEXT(UP.qty,2) AS r_n_2_qty
			,running NEXT(UP.qty,3) AS r_n_3_qty
			,running NEXT(UP.qty,4) AS r_n_4_qty
			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

ERROR at line 79:
ORA-62509: illegal use of RUNNING or FINAL in MATCH_RECOGNIZE clause


Elapsed: 00:00:00.00

final nextの場合

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
--			,FIRST(UP.qty) AS f___qty
--			,FIRST(UP.qty,0) AS f_0_qty
--			,FIRST(UP.qty,1) AS f_1_qty
--			,FIRST(UP.qty,2) AS f_2_qty
--			,FIRST(UP.qty,3) AS f_3_qty
--			,FIRST(UP.qty,4) AS f_4_qty
--			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
			,final NEXT(UP.qty) AS f_n___qty
			,final NEXT(UP.qty,0) AS f_n_0_qty
			,final NEXT(UP.qty,1) AS f_n_1_qty
			,final NEXT(UP.qty,2) AS f_n_2_qty
			,final NEXT(UP.qty,3) AS f_n_3_qty
			,final NEXT(UP.qty,4) AS f_n_4_qty
			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;
ERROR at line 86:
ORA-62509: illegal use of RUNNING or FINAL in MATCH_RECOGNIZE clause


Elapsed: 00:00:00.01

おまけで以下のデータでやってもいいかも

コード表示

DROP TABLE test___$_____ PURGE;
CREATE TABLE test___$_____ AS
SELECT
	LEVEL AS rn
	,case
		when level <=5 then LEVEL*10
		else 20 + level*10
	end AS qty
FROM
	dual
CONNECT BY
	LEVEL <= 10
;

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
			,FIRST(UP.qty) AS f___qty
			,FIRST(UP.qty,0) AS f_0_qty
			,FIRST(UP.qty,1) AS f_1_qty
			,FIRST(UP.qty,2) AS f_2_qty
			,FIRST(UP.qty,3) AS f_3_qty
			,FIRST(UP.qty,4) AS f_4_qty
			,FIRST(UP.qty,5) AS f_5_qty
--			,running FIRST(UP.qty) AS r_f___qty
--			,running FIRST(UP.qty,0) AS r_f_0_qty
--			,running FIRST(UP.qty,1) AS r_f_1_qty
--			,running FIRST(UP.qty,2) AS r_f_2_qty
--			,running FIRST(UP.qty,3) AS r_f_3_qty
--			,running FIRST(UP.qty,4) AS r_f_4_qty
--			,running FIRST(UP.qty,5) AS r_f_5_qty
--			,final FIRST(UP.qty) AS f_f___qty
--			,final FIRST(UP.qty,0) AS f_f_0_qty
--			,final FIRST(UP.qty,1) AS f_f_1_qty
--			,final FIRST(UP.qty,2) AS f_f_2_qty
--			,final FIRST(UP.qty,3) AS f_f_3_qty
--			,final FIRST(UP.qty,4) AS f_f_4_qty
--			,final FIRST(UP.qty,5) AS f_f_5_qty
--			,LAST(UP.qty) AS l___qty
--			,LAST(UP.qty,0) AS l_0_qty
--			,LAST(UP.qty,1) AS l_1_qty
--			,LAST(UP.qty,2) AS l_2_qty
--			,LAST(UP.qty,3) AS l_3_qty
--			,LAST(UP.qty,4) AS l_4_qty
--			,LAST(UP.qty,5) AS l_5_qty
--			,running LAST(UP.qty) AS r_l___qty
--			,running LAST(UP.qty,0) AS r_l_0_qty
--			,running LAST(UP.qty,1) AS r_l_1_qty
--			,running LAST(UP.qty,2) AS r_l_2_qty
--			,running LAST(UP.qty,3) AS r_l_3_qty
--			,running LAST(UP.qty,4) AS r_l_4_qty
--			,running LAST(UP.qty,5) AS r_l_5_qty
--			,final LAST(UP.qty) AS f_l___qty
--			,final LAST(UP.qty,0) AS f_l_0_qty
--			,final LAST(UP.qty,1) AS f_l_1_qty
--			,final LAST(UP.qty,2) AS f_l_2_qty
--			,final LAST(UP.qty,3) AS f_l_3_qty
--			,final LAST(UP.qty,4) AS f_l_4_qty
--			,final LAST(UP.qty,5) AS f_l_5_qty
--			,prev(UP.qty) AS p___qty
--			,prev(UP.qty,0) AS p_0_qty
--			,prev(UP.qty,1) AS p_1_qty
--			,prev(UP.qty,2) AS p_2_qty
--			,prev(UP.qty,3) AS p_3_qty
--			,prev(UP.qty,4) AS p_4_qty
--			,prev(UP.qty,5) AS p_5_qty
--			,running prev(UP.qty) AS r_p___qty
--			,running prev(UP.qty,0) AS r_p_0_qty
--			,running prev(UP.qty,1) AS r_p_1_qty
--			,running prev(UP.qty,2) AS r_p_2_qty
--			,running prev(UP.qty,3) AS r_p_3_qty
--			,running prev(UP.qty,4) AS r_p_4_qty
--			,running prev(UP.qty,5) AS r_p_5_qty
--			,final prev(UP.qty) AS f_p___qty
--			,final prev(UP.qty,0) AS f_p_0_qty
--			,final prev(UP.qty,1) AS f_p_1_qty
--			,final prev(UP.qty,2) AS f_p_2_qty
--			,final prev(UP.qty,3) AS f_p_3_qty
--			,final prev(UP.qty,4) AS f_p_4_qty
--			,final prev(UP.qty,5) AS f_p_5_qty
--			,NEXT(UP.qty) AS n___qty
--			,NEXT(UP.qty,0) AS n_0_qty
--			,NEXT(UP.qty,1) AS n_1_qty
--			,NEXT(UP.qty,2) AS n_2_qty
--			,NEXT(UP.qty,3) AS n_3_qty
--			,NEXT(UP.qty,4) AS n_4_qty
--			,NEXT(UP.qty,5) AS n_5_qty
--			,running NEXT(UP.qty) AS r_n___qty
--			,running NEXT(UP.qty,0) AS r_n_0_qty
--			,running NEXT(UP.qty,1) AS r_n_1_qty
--			,running NEXT(UP.qty,2) AS r_n_2_qty
--			,running NEXT(UP.qty,3) AS r_n_3_qty
--			,running NEXT(UP.qty,4) AS r_n_4_qty
--			,running NEXT(UP.qty,5) AS r_n_5_qty
--			,final NEXT(UP.qty) AS f_n___qty
--			,final NEXT(UP.qty,0) AS f_n_0_qty
--			,final NEXT(UP.qty,1) AS f_n_1_qty
--			,final NEXT(UP.qty,2) AS f_n_2_qty
--			,final NEXT(UP.qty,3) AS f_n_3_qty
--			,final NEXT(UP.qty,4) AS f_n_4_qty
--			,final NEXT(UP.qty,5) AS f_n_5_qty
		ALL ROWS PER MATCH
		PATTERN (uni UP+)
		DEFINE
			UP AS UP.qty = nvl(prev(UP.qty),0) + 10 --これで同一mnにしておく
)
;

lastはパタンマッチした範囲における最終行から前に指定した番号分さかのぼるのに対して、firstはパタンマッチした範囲における先頭行から後ろに指定した番号分進む。lastは番号分↑。firstは番号分↓。のイメージ。prevはlastのイメージ。nextはfirstのイメージ。違いとしてはlast firstはrange betweenでprev nextはrows betweenのイメージに近いのかな。

PREV NEXTのなかにFIRST,LASTがネスト??

調べた。サンプル少し用意。

コード表示

DROP TABLE test___$_____ PURGE;
CREATE TABLE test___$_____ AS
SELECT
	LEVEL AS rn
	,level*10 AS price
	,level as tax
FROM
	dual
CONNECT BY
	LEVEL <=6
;
           RN         PRICE           TAX
------------- ------------- -------------
            1            10             1
            2            20             2
            3            30             3
            4            40             4
            5            50             5
            6            60             6

6 rows selected.

Elapsed: 00:00:00.02


ふつうのやつ

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.price = nvl(prev(UP.price),0) + 10 --これで同一mnにしておく
)
;

           MN CLS                   RN         PRICE           TAX
------------- ---------- ------------- ------------- -------------
            1 UP                     1            10             1
            1 UP                     2            20             2
            1 UP                     3            30             3
            1 UP                     4            40             4
            1 UP                     5            50             5
            1 UP                     6            60             6

6 rows selected.

Elapsed: 00:00:00.01


PREV (LAST (up.Price + up.Tax, 1), 3)はlastのオフセット動いた位置からprevのオフセット動いた行でup.Price + up.Taxを計算する。

コード表示

SELECT
	*
FROM
	test___$_____
	MATCH_RECOGNIZE (
		MEASURES
			match_number() AS mn
			,classifier() AS cls
			,PREV (LAST (UP.Price + UP.Tax, 1), 3) as ns
			,PREV (final LAST (UP.Price + UP.Tax, 1), 3) as final_ns
		ALL ROWS PER MATCH
		PATTERN (UP+)
		DEFINE
			UP AS UP.price = nvl(prev(UP.price),0) + 10 --これで同一mnにしておく
)
;

           MN CLS                   NS      FINAL_NS            RN         PRICE           TAX
------------- ---------- ------------- ------------- ------------- ------------- -------------
            1 UP                                  22             1            10             1
            1 UP                                  22             2            20             2
            1 UP                                  22             3            30             3
            1 UP                                  22             4            40             4
            1 UP                    11            22             5            50             5
            1 UP                    22            22             6            60             6

6 rows selected.

Elapsed: 00:00:00.01

コード表示



Leave a Reply

Your email address will not be published. Required fields are marked *