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

コード表示



docker fileにしたいこと

sudo package install

[oracle@centos7 ~]$ docker exec --interactive --tty --user root --workdir / orcl_18cr3 bash
bash-4.2# yum install -y sudo
Installed:
  sudo.x86_64 0:1.8.23-3.el7
Complete!
bash-4.2# visudo
visudo: /etc/sudoers.tmp unchanged
vim install

[oracle@centos7 ~]$ docker exec --interactive --tty --user root --workdir / orcl_18cr3 bash
bash-4.2# yum -y install vim
Installed:
  vim-enhanced.x86_64 2:7.4.160-5.el7
Complete!
passwdコマンド install

[oracle@centos7 ~]$ docker exec --interactive --tty --user root --workdir / orcl_18cr3 bash
bash-4.2# whereis passwd
passwd: /etc/passwd
bash-4.2# yum install -y passwd
Installed:
  passwd.x86_64 0:0.79-4.el7

Complete!
bash-4.2# whereis passwd
passwd: /usr/bin/passwd /etc/passwd

passwordの設定

bash-4.2# whoami
root
bash-4.2# passwd
Changing password for user root.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
bash-4.2# passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
oracle user sudo enable

bash-4.2# visudo
## Same thing without a password
# %wheel        ALL=(ALL)       NOPASSWD: ALL
oracle          ALL=(ALL)       ALL

[oracle@centos7 ~]$ docker exec --interactive --tty --user root --workdir / orcl_18cr3 bash
bash-4.2# exit
[oracle@centos7 ~]$ docker exec -it orcl_18cr3 bash
[oracle@c75932116d78 ~]$ sudo echo "hey"

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for oracle:
hey
[oracle@c75932116d78 ~]$

epel repo install


rlwrap install sqlplus

[oracle@centos7 ~]$ docker exec -it orcl_18cr3 bash
[oracle@c75932116d78 ~]$ whoami
oracle
[oracle@c75932116d78 ~]$ sudo curl -O https://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/e/epel-release-7-11.noarch.rpm
[sudo] password for oracle:
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 15080  100 15080    0     0  11824      0  0:00:01  0:00:01 --:--:-- 11827
[oracle@c75932116d78 ~]$ ls -lt
total 16
-rw-r--r--. 1 root root 15080 Jan 13 18:26 epel-release-7-11.noarch.rpm
lrwxrwxrwx. 1 root root    26 Jan  2 20:58 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@c75932116d78 ~]$ sudo rpm -ivh epel-release-7-11.noarch.rpm
Installed:
  rlwrap.x86_64 0:0.43-1.el7
Complete!
rlwrap dictionary install sqlplus

[oracle@c75932116d78 ~]$ ll
total 16
-rw-r--r--. 1 root root 15080 Jan 13 18:26 epel-release-7-11.noarch.rpm
lrwxrwxrwx. 1 root root    26 Jan  2 20:58 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@c75932116d78 ~]$ mkdir rlwrap-extensions
[oracle@c75932116d78 ~]$ ll
total 16
-rw-r--r--. 1 root   root     15080 Jan 13 18:26 epel-release-7-11.noarch.rpm
drwxr-xr-x. 2 oracle oinstall     6 Jan 13 18:29 rlwrap-extensions
lrwxrwxrwx. 1 root   root        26 Jan  2 20:58 setPassword.sh -> /opt/oracle/setPassword.sh
[oracle@c75932116d78 ~]$ cd rlwrap-extensions
[oracle@c75932116d78 rlwrap-extensions]$ pwd
/home/oracle/rlwrap-extensions
[oracle@c75932116d78 rlwrap-extensions]$ curl -O http://www.linuxification.at/download/rlwrap-extensions-V12-0.05.tar.gz
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 57847  100 57847    0     0  22940      0  0:00:02  0:00:02 --:--:-- 22946
[oracle@c75932116d78 rlwrap-extensions]$ tar xvfz rlwrap-extensions-V12-0.05.tar.gz
adrci
asm+
asmcmd
dgmgrl
README
README.english
rman
sql+
sql.functions
sqlplus
sqlplus.all
sqlplus.cdb
sqlplus.dba
sqlplus.dbms_packages
sqlplus.functions
sqlplus.gvdollar
sqlplus.init
sqlplus._init
sqlplus.packages
sqlplus.rman
sqlplus.tables
sqlplus.user
sqlplus.utl_packages
sqlplus.vdollar
[oracle@c75932116d78 rlwrap-extensions]$ cd ~
[oracle@c75932116d78 ~]$ vim .bashrc
[oracle@c75932116d78 ~]$ vi
[oracle@c75932116d78 ~]$
glogin modify

COLUMN OTHER_PLUS_EXP FORMAT A200
COLUMN PLAN_PLUS_EXP  FORMAT A200
SET NUMWIDTH 13
SET LINESIZE 1000
SET LONG 40000
SET LONGCHUNKSIZE 40000
SET PAGESIZE 50000
SET SERVEROUTPUT ON
SET TAB OFF
SET TRIMSPOOL ON
set feed on
--set autotrace traceonly
set timing on
--set hist on
DEFINE_EDITOR=nano
set sqlp "_USER'@'_CONNECT_IDENTIFIER> "

[oracle@c75932116d78 ~]$ find / -name *login*.sql -type f 2>/dev/null
/opt/oracle/product/18c/dbhome_1/sqlplus/admin/glogin.sql
[oracle@c75932116d78 ~]$ vi /opt/oracle/product/18c/dbhome_1/sqlplus/admin/glogin.sql
[oracle@c75932116d78 ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 13 18:38:42 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 13 2019 18:38:35 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1>

非パーティションからパーティションへの移行 オンライン再定義の手順

参考文献

https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/d-12-disk-1484778-ja.pdf

初期データ投入


PURGE RECYCLEBIN;
DROP TABLE CALENDAR PURGE;
CREATE TABLE CALENDAR AS
WITH sub AS (
SELECT
    to_char(ind,'yyyymmdd') AS yyyymmdd
    ,to_char(ind,'cc') AS century
    ,to_char(ind,'yyyy') AS yyyy
    ,to_char(ind,'mm') AS mm
    ,to_char(ind,'dd') AS dd
    ,to_char(ind,'q') AS quater
    ,to_char(ind,'mon') AS japan_mon
    ,to_char(ind,'rm') AS roman_mon
    ,to_char(ind,'ddd') AS day_per_year
    ,to_char(ind,'ww') AS week_per_year
    ,to_char(ind,'w') AS week_per_month
    ,to_char(ind,'d') AS day_of_week
    ,to_char(ind,'dy') AS jan_day_of_week
    ,to_char(ind,'day') AS jan_day_of_week_rich
FROM
    dual
MODEL
RETURN UPDATED ROWS
DIMENSION BY(sysdate AS ind)
MEASURES(
        0 AS val
        )
RULES(
        val[FOR ind FROM TO_DATE('2019-01-01') TO TO_DATE('2019-01-01') INCREMENT INTERVAL '1' DAY] = 0
        )
)
SELECT * FROM sub;

ALTER TABLE CALENDAR ADD CONSTRAINT CALENDAR_PK PRIMARY KEY(yyyymmdd) USING INDEX GLOBAL;
CREATE UNIQUE INDEX CALENDAR_ind ON CALENDAR (yyyy,mm,dd) GLOBAL;

仮表の作成


DROP MATERIALIZED VIEW TMP_CALENDAR;
DROP TABLE aine.TMP_CALENDAR PURGE;
CREATE TABLE aine.TMP_CALENDAR (
	yyyymmdd               VARCHAR2(8 BYTE)
	, century                VARCHAR2(2 BYTE)
	, yyyy                   VARCHAR2(4 BYTE)
	, mm                     VARCHAR2(2 BYTE)
	, dd                     VARCHAR2(2 BYTE)
	, quater                 VARCHAR2(1 BYTE)
	, japan_mon              VARCHAR2(8 BYTE)
	, roman_mon              VARCHAR2(4 BYTE)
	, day_per_year           VARCHAR2(3 BYTE)
	, week_per_year          VARCHAR2(2 BYTE)
	, week_per_month         VARCHAR2(1 BYTE)
	, day_of_week            VARCHAR2(1 BYTE)
	, jan_day_of_week        VARCHAR2(4 BYTE)
	, jan_day_of_week_rich   VARCHAR2(12 BYTE)
	)
	PARTITION BY RANGE(yyyymmdd)
       (
	   PARTITION part_q1 VALUES LESS THAN ('20190401')
		,PARTITION part_q2 VALUES LESS THAN ('20190701')
		,PARTITION part_q3 VALUES LESS THAN ('20191001')
		,PARTITION part_q4 VALUES LESS THAN (MAXVALUE)
);

オンライン処理を表現


BEGIN
	FOR I IN 1..364
	LOOP
		INSERT INTO CALENDAR
		WITH sub AS(
		SELECT
			to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'yyyymmdd') AS yyyymmdd
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'cc') AS century
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'yyyy') AS yyyy
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'mm') AS mm
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'dd') AS dd
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'q') AS quater
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'mon') AS japan_mon
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'rm') AS roman_mon
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'ddd') AS day_per_year
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'ww') AS week_per_year
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'w') AS week_per_month
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'd') AS day_of_week
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'dy') AS jan_day_of_week
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'day') AS jan_day_of_week_rich
		FROM
			CALENDAR
		)
		SELECT * FROM sub WHERE ROWNUM = 1
		;
		COMMIT;
	dbms_lock.sleep(1);
  END
LOOP;
END;
/

以降の処理は別セッションで実行。sqldeveloperをもう一個開くなり、teratermでもうひとつセッション複製するなり、コンソールもう一個開くなりする。

表がオンライン再定義の候補であることの確認


EXEC dbms_redefinition.can_redef_table(user,'CALENDAR',dbms_redefinition.cons_use_pk);

ディクショナリ情報の確認


SELECT table_name, partitioning_type, status FROM user_part_tables WHERE table_name LIKE '%CALENDAR%';
SELECT table_owner, table_name, index_name, uniqueness, status, global_stats FROM user_indexes WHERE table_name LIKE '%CALENDAR%';
SELECT OWNER, table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name LIKE '%CALENDAR%';

件数チェック


select count(*) from CALENDAR;
select count(*) from TMP_CALENDAR;

再定義プロセスの開始


EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'CALENDAR', 'TMP_CALENDAR');

仮表にローカル主キー索引を作成


ALTER TABLE TMP_CALENDAR ADD CONSTRAINT TMP_CALENDAR_PK PRIMARY KEY (yyyymmdd) USING INDEX LOCAL;

仮表にローカル索引を作成


CREATE INDEX TMP_CALENDAR_ind ON TMP_CALENDAR (yyyy,mm,dd) LOCAL;

ディクショナリ情報の確認


SELECT table_name, partitioning_type, status FROM user_part_tables WHERE table_name LIKE '%CALENDAR%';
SELECT table_owner, table_name, index_name, uniqueness, status, global_stats FROM user_indexes WHERE table_name LIKE '%CALENDAR%';
SELECT OWNER, table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name LIKE '%CALENDAR%';

DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECTプロシージャによる索引&主キー制約の関連付け


BEGIN
  dbms_redefinition.register_dependent_object(
      USER                         -- uname
    , 'CALENDAR'                      -- orig_table
    , 'TMP_CALENDAR'                  -- int_table
    , dbms_redefinition.cons_index -- dep_type
    , USER                         -- dep_owner
    , 'CALENDAR_PK'                   -- dep_orig_name
    , 'TMP_CALENDAR_PK'               -- dep_int_name
  );
END;
/


BEGIN
  dbms_redefinition.register_dependent_object(
      USER                         -- uname
    , 'CALENDAR'                      -- orig_table
    , 'TMP_CALENDAR'                  -- int_table
    , dbms_redefinition.cons_constraint -- dep_type
    , USER                         -- dep_owner
    , 'CALENDAR_PK'                   -- dep_orig_name
    , 'TMP_CALENDAR_PK'               -- dep_int_name
  );
END;
/


BEGIN
  dbms_redefinition.register_dependent_object(
      USER                         -- uname
    , 'CALENDAR'                      -- orig_table
    , 'TMP_CALENDAR'                  -- int_table
    , dbms_redefinition.cons_index -- dep_type
    , USER                         -- dep_owner
    , 'CALENDAR_IND'                   -- dep_orig_name
    , 'TMP_CALENDAR_IND'               -- dep_int_name
  );
END;
/

再定義プロセスの終了


BEGIN
	dbms_redefinition.finish_redef_table(
		'aine'
		, 'CALENDAR'
		, 'TMP_CALENDAR'
	);
END;
/

ディクショナリ情報の確認


SELECT table_name, partitioning_type, status FROM user_part_tables WHERE table_name LIKE '%CALENDAR%';
SELECT table_owner, table_name, index_name, uniqueness, status, global_stats FROM user_indexes WHERE table_name LIKE '%CALENDAR%';
SELECT OWNER, table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name LIKE '%CALENDAR%';

パーティション単位の検索


SELECT COUNT(*) FROM CALENDAR PARTITION (part_q1);
SELECT COUNT(*) FROM CALENDAR PARTITION (part_q2);
SELECT COUNT(*) FROM CALENDAR PARTITION (part_q3);
SELECT COUNT(*) FROM CALENDAR PARTITION (part_q4);

仮表の削除


DROP TABLE TMP_CALENDAR PURGE;

docker postfix ssmtp install gmail

root login


[oracle@centos7 ~]$ docker exec --interactive --tty --user root --workdir / orcl_12cr2 bash

postfix intall


bash-4.2# yum install -y postfix
Loaded plugins: ovl
epel/x86_64/metalink                                                                                                           | 9.6 kB  00:00:00     
epel                                                                                                                           | 3.2 kB  00:00:00     
ol7_UEKR4                                                                                                                      | 1.2 kB  00:00:00     
ol7_latest                                                                                                                     | 1.4 kB  00:00:00     
osquery-s3-centos7-repo                                                                                                        |  951 B  00:00:00     
(1/2): epel/x86_64/updateinfo                                                                                                  | 942 kB  00:00:02     
(2/2): epel/x86_64/primary                                                                                                     | 3.6 MB  00:00:04     
epel                                                                                                                                      12776/12776
Resolving Dependencies
--> Running transaction check
---> Package postfix.x86_64 2:2.10.1-7.el7 will be installed
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.60-1.el7_5 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================
 Package                             Arch                          Version                                    Repository                         Size
======================================================================================================================================================
Installing:
 postfix                             x86_64                        2:2.10.1-7.el7                             ol7_latest                        2.4 M
Installing for dependencies:
 mariadb-libs                        x86_64                        1:5.5.60-1.el7_5                           ol7_latest                        758 k

Transaction Summary
======================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 3.2 M
Installed size: 17 M
Downloading packages:
(1/2): mariadb-libs-5.5.60-1.el7_5.x86_64.rpm                                                                                  | 758 kB  00:00:05     
(2/2): postfix-2.10.1-7.el7.x86_64.rpm                                                                                         | 2.4 MB  00:00:07     
------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                 457 kB/s | 3.2 MB  00:00:07     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 1:mariadb-libs-5.5.60-1.el7_5.x86_64                                                                                               1/2 
  Installing : 2:postfix-2.10.1-7.el7.x86_64                                                                                                      2/2 
failed to link /usr/share/man/man1/mailq.1.gz -> /etc/alternatives/mta-mailqman: No such file or directory
failed to link /usr/share/man/man1/newaliases.1.gz -> /etc/alternatives/mta-newaliasesman: No such file or directory
failed to link /usr/share/man/man8/sendmail.8.gz -> /etc/alternatives/mta-sendmailman: No such file or directory
failed to link /usr/share/man/man5/aliases.5.gz -> /etc/alternatives/mta-aliasesman: No such file or directory
  Verifying  : 2:postfix-2.10.1-7.el7.x86_64                                                                                                      1/2 
  Verifying  : 1:mariadb-libs-5.5.60-1.el7_5.x86_64                                                                                               2/2 

Installed:
  postfix.x86_64 2:2.10.1-7.el7                                                                                                                       

Dependency Installed:
  mariadb-libs.x86_64 1:5.5.60-1.el7_5                                                                                                                

Complete!
bash-4.2# 

mailx cyrus-sasl-plain install


bash-4.2# yum install -y mailx cyrus-sasl-plain
Loaded plugins: ovl
Package mailx-12.5-19.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package cyrus-sasl-plain.x86_64 0:2.1.26-23.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================
 Package                                 Arch                          Version                                Repository                         Size
======================================================================================================================================================
Installing:
 cyrus-sasl-plain                        x86_64                        2.1.26-23.el7                          ol7_latest                         38 k

Transaction Summary
======================================================================================================================================================
Install  1 Package

Total download size: 38 k
Installed size: 39 k
Downloading packages:
cyrus-sasl-plain-2.1.26-23.el7.x86_64.rpm                                                                                      |  38 kB  00:00:07     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : cyrus-sasl-plain-2.1.26-23.el7.x86_64                                                                                              1/1 
  Verifying  : cyrus-sasl-plain-2.1.26-23.el7.x86_64                                                                                              1/1 

Installed:
  cyrus-sasl-plain.x86_64 0:2.1.26-23.el7                                                                                                             

Complete!

/etc/postfix/main.cfの編集


bash-4.2# vim /etc/postfix/main.cf


# The relayhost parameter specifies the default host to send mail to
# when no entry is matched in the optional transport(5) table. When
# no relayhost is given, mail is routed directly to the destination.
#
# On an intranet, specify the organizational domain name. If your
# internal DNS uses no MX records, specify the name of the intranet
# gateway host instead.
#
# In the case of SMTP, specify a domain, host, host:port, [host]:port,
# [address] or [address]:port; the form [host] turns off MX lookups.
#
# If you're connected via UUCP, see also the default_transport parameter.
#
#relayhost = $mydomain
#relayhost = [gateway.my.domain]
#relayhost = [mailserver.isp.tld]
#relayhost = uucphost
#relayhost = [an.ip.add.ress]
relayhost = [smtp.gmail.com]:587

#sasl setting
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_sasl_security_options = noanonymous
smtp_sasl_tls_security_options = noanonymous
smtp_sasl_mechanism_filter = plain

#tls setting
smtp_use_tls = yes

root mailをgmailに転送設定する


bash-4.2# vim /etc/aliases
# Person who should get root's mail
#root:          marc
root:           メールアドレス@gmail.com

/etc/postfix/sasl_passwdの編集


bash-4.2# vim /etc/postfix/sasl_passwd
[smtp.gmail.com]:587 メールアドレス@gmail.com:password

/etc/postfix/sasl_passwdの権限設定


bash-4.2# cd /etc/postfix/
bash-4.2# pwd
/etc/postfix
bash-4.2# ls -lt
total 152
-rw-r--r--. 1 root root    62 Dec 30 00:38 sasl_passwd
-rw-r--r--. 1 root root 27462 Dec 30 00:33 main.cf
-rw-r--r--. 1 root root 20876 Aug 24 06:16 access
-rw-r--r--. 1 root root 11883 Aug 24 06:16 canonical
-rw-r--r--. 1 root root 10106 Aug 24 06:16 generic
-rw-r--r--. 1 root root 21545 Aug 24 06:16 header_checks
-rw-r--r--. 1 root root  6105 Aug 24 06:16 master.cf
-rw-r--r--. 1 root root  6816 Aug 24 06:16 relocated
-rw-r--r--. 1 root root 12549 Aug 24 06:16 transport
-rw-r--r--. 1 root root 12696 Aug 24 06:16 virtual
bash-4.2# chmod 600 /etc/postfix/sasl_passwd
bash-4.2# ls -lt
total 152
-rw-------. 1 root root    62 Dec 30 00:38 sasl_passwd
-rw-r--r--. 1 root root 27462 Dec 30 00:33 main.cf
-rw-r--r--. 1 root root 20876 Aug 24 06:16 access
-rw-r--r--. 1 root root 11883 Aug 24 06:16 canonical
-rw-r--r--. 1 root root 10106 Aug 24 06:16 generic
-rw-r--r--. 1 root root 21545 Aug 24 06:16 header_checks
-rw-r--r--. 1 root root  6105 Aug 24 06:16 master.cf
-rw-r--r--. 1 root root  6816 Aug 24 06:16 relocated
-rw-r--r--. 1 root root 12549 Aug 24 06:16 transport
-rw-r--r--. 1 root root 12696 Aug 24 06:16 virtual


bash-4.2# ls -lt
total 152
-rw-------. 1 root root    62 Dec 30 00:38 sasl_passwd
-rw-r--r--. 1 root root 27462 Dec 30 00:33 main.cf
-rw-r--r--. 1 root root 20876 Aug 24 06:16 access
-rw-r--r--. 1 root root 11883 Aug 24 06:16 canonical
-rw-r--r--. 1 root root 10106 Aug 24 06:16 generic
-rw-r--r--. 1 root root 21545 Aug 24 06:16 header_checks
-rw-r--r--. 1 root root  6105 Aug 24 06:16 master.cf
-rw-r--r--. 1 root root  6816 Aug 24 06:16 relocated
-rw-r--r--. 1 root root 12549 Aug 24 06:16 transport
-rw-r--r--. 1 root root 12696 Aug 24 06:16 virtual
bash-4.2# postmap /etc/postfix/sasl_passwd
postmap: fatal: parameter inet_interfaces: no local interface found for ::1
bash-4.2# vim /etc/hosts
bash-4.2# postmap /etc/postfix/sasl_passwd
bash-4.2# ls -lt
total 160
-rw-------. 1 root root 12288 Dec 30 00:44 sasl_passwd.db
-rw-------. 1 root root    62 Dec 30 00:38 sasl_passwd
-rw-r--r--. 1 root root 27462 Dec 30 00:33 main.cf
-rw-r--r--. 1 root root 20876 Aug 24 06:16 access
-rw-r--r--. 1 root root 11883 Aug 24 06:16 canonical
-rw-r--r--. 1 root root 10106 Aug 24 06:16 generic
-rw-r--r--. 1 root root 21545 Aug 24 06:16 header_checks
-rw-r--r--. 1 root root  6105 Aug 24 06:16 master.cf
-rw-r--r--. 1 root root  6816 Aug 24 06:16 relocated
-rw-r--r--. 1 root root 12549 Aug 24 06:16 transport
-rw-r--r--. 1 root root 12696 Aug 24 06:16 virtual
bash-4.2# 

/etc/hostsを修正する

変更前


127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.17.0.2      609a69bc0b21

変更後


127.0.0.1       localhost
#::1    localhost ip6-localhost ip6-loopback
#fe00::0        ip6-localnet
#ff00::0        ip6-mcastprefix
#ff02::1        ip6-allnodes
#ff02::2        ip6-allrouters
172.17.0.2      609a69bc0b21

postfixの起動


bash-4.2# postfix restart
postfix/postfix-script: error: unknown command: 'restart'
postfix/postfix-script: fatal: usage: postfix start (or stop, reload, abort, flush, check, status, set-permissions, upgrade-configuration)
bash-4.2# postfix start
postfix/postfix-script: starting the Postfix mail system
bash-4.2# postfix status
postfix/postfix-script: the Postfix mail system is running: PID: 381
bash-4.2# 

メールのテスト送信


bash-4.2# echo "hoge" | mail -s "test mail from docker" ${自身のGmailメールアドレス}

postfixだとできなかった!
ssmtpで挑戦!

ssmtpのインストール


bash-4.2# yum install -y ssmtp
Loaded plugins: ovl
Resolving Dependencies
--> Running transaction check
---> Package ssmtp.x86_64 0:2.64-14.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================
 Package                           Arch                               Version                                  Repository                        Size
======================================================================================================================================================
Installing:
 ssmtp                             x86_64                             2.64-14.el7                              epel                              50 k

Transaction Summary
======================================================================================================================================================
Install  1 Package

Total download size: 50 k
Installed size: 74 k
Downloading packages:
ssmtp-2.64-14.el7.x86_64.rpm                                                                                                   |  50 kB  00:00:02     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : ssmtp-2.64-14.el7.x86_64                                                                                                           1/1 
failed to link /usr/share/man/man1/mailq.1.gz -> /etc/alternatives/mta-mailqman: No such file or directory
failed to link /usr/share/man/man1/newaliases.1.gz -> /etc/alternatives/mta-newaliasesman: No such file or directory
failed to link /usr/share/man/man5/aliases.5.gz -> /etc/alternatives/mta-aliasesman: No such file or directory
failed to link /usr/share/man/man8/sendmail.8.gz -> /etc/alternatives/mta-sendmailman: No such file or directory
warning: %post(ssmtp-2.64-14.el7.x86_64) scriptlet failed, exit status 2
Non-fatal POSTIN scriptlet failure in rpm package ssmtp-2.64-14.el7.x86_64
  Verifying  : ssmtp-2.64-14.el7.x86_64                                                                                                           1/1 

Installed:
  ssmtp.x86_64 0:2.64-14.el7                                                                                                                          

Complete!

/etc/ssmtp/ssmtp.confの編集


bash-4.2# sudo vim /etc/ssmtp/ssmtp.conf

編集内容


root=${自身のGmailメールアドレス}
mailhub=smtp.gmail.com:587
RewriteDomain=gmail.com
hostname=gmail.com
AuthUser=${自身のGmailメールアドレス}
AuthPass=${自分のGmailログインパスワード}
AuthMethod=LOGIN
UseTLS=YES
FromLineOverride=YES

root=${自身のGmailメールアドレス}


# The person who gets all mail for userids < 1000
# Make this empty to disable rewriting.
#root=postmaster
root=${自身のGmailメールアドレス}

mailhub=smtp.gmail.com:587


# The place where the mail goes. The actual machine name is required
# no MX records are consulted. Commonly mailhosts are named mail.domain.com
# The example will fit if you are in domain.com and your mailhub is so named.
#mailhub=mail
mailhub=smtp.gmail.com:587

RewriteDomain=gmail.com


# Where will the mail seem to come from?
#RewriteDomain=
RewriteDomain=gmail.com

Hostname=gmail.com


# The full hostname
#Hostname=
Hostname=gmail.com
AuthUser=${自身のGmailメールアドレス}
AuthPass=${自分のGmailログインパスワード}
AuthMethod=LOGIN

UseTLS=YES


# Use SSL/TLS to send secure messages to server.
#UseTLS=YES
UseTLS=YES

FromLineOverride=YES


# Set this to never rewrite the "From:" line (unless not given) and to
# use that address in the "from line" of the envelope.
#FromLineOverride=YES
FromLineOverride=YES

以下のURLから「安全性の低いアプリ」を許可する
https://myaccount.google.com/lesssecureapps
2段階認証の場合は許可できない。

メールのテスト送信


bash-4.2# echo "hoge" | mail -s "test mail from docker" ${自身のGmailメールアドレス}

できた!!

gmail sending settings centos7 cron

plugin install


[oracle@centos7 postfix]$ sudo yum install -y mailx cyrus-sasl-plain
読み込んだプラグイン:fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: centos.ustc.edu.cn
 * extras: ftp.nara.wide.ad.jp
 * updates: centos.ustc.edu.cn
パッケージ mailx-12.5-19.el7.x86_64 はインストール済みか最新バージョンです
パッケージ cyrus-sasl-plain-2.1.26-23.el7.x86_64 はインストール済みか最新バージョンです
何もしません
[oracle@centos7 postfix]$ 

postfix mail send env


[oracle@centos7 ~]$ sudo vim /etc/postfix/main.cf
[sudo] oracle のパスワード:

/etc/postfix/main.cf


# The relayhost parameter specifies the default host to send mail to
# when no entry is matched in the optional transport(5) table. When
# no relayhost is given, mail is routed directly to the destination.
#
# On an intranet, specify the organizational domain name. If your
# internal DNS uses no MX records, specify the name of the intranet
# gateway host instead.
#
# In the case of SMTP, specify a domain, host, host:port, [host]:port,
# [address] or [address]:port; the form [host] turns off MX lookups.
#
# If you're connected via UUCP, see also the default_transport parameter.
#
#relayhost = $mydomain
#relayhost = [gateway.my.domain]
#relayhost = [mailserver.isp.tld]
#relayhost = uucphost
#relayhost = [an.ip.add.ress]
relayhost = [smtp.gmail.com]:587

#sasl setting
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_sasl_security_options = noanonymous
smtp_sasl_tls_security_options = noanonymous
smtp_sasl_mechanism_filter = plain

#tls setting
smtp_use_tls = yes

root mail transport to gamil


[oracle@centos7 postfix]$ vim /etc/aliases
# Person who should get root's mail
#root:          marc
root:           メールアドレス@gmail.com

/etc/postfix/sasl_passwd


[oracle@centos7 ~]$ cd /etc/postfix/
[oracle@centos7 postfix]$ ll
合計 148
-rw-r--r--. 1 root root 20876  6月 10  2014 access
-rw-r--r--. 1 root root 11681  6月 10  2014 canonical
-rw-r--r--. 1 root root  9904  6月 10  2014 generic
-rw-r--r--. 1 root root 21545  6月 10  2014 header_checks
-rw-r--r--. 1 root root 27464 12月 29 07:46 main.cf
-rw-r--r--. 1 root root  6105  6月 10  2014 master.cf
-rw-r--r--. 1 root root  6816  6月 10  2014 relocated
-rw-r--r--. 1 root root 12549  6月 10  2014 transport
-rw-r--r--. 1 root root 12494  6月 10  2014 virtual
[oracle@centos7 postfix]$ sudo vim /etc/postfix/sasl_passwd
[smtp.gmail.com]:587 メールアドレス@gmail.com:password

grand to sasl_passwd


[oracle@centos7 postfix]$ ll
合計 152
-rw-r--r--. 1 root root 20876  6月 10  2014 access
-rw-r--r--. 1 root root 11681  6月 10  2014 canonical
-rw-r--r--. 1 root root  9904  6月 10  2014 generic
-rw-r--r--. 1 root root 21545  6月 10  2014 header_checks
-rw-r--r--. 1 root root 27464 12月 29 07:46 main.cf
-rw-r--r--. 1 root root  6105  6月 10  2014 master.cf
-rw-r--r--. 1 root root  6816  6月 10  2014 relocated
-rw-r--r--. 1 root root    62 12月 29 07:52 sasl_passwd
-rw-r--r--. 1 root root 12549  6月 10  2014 transport
-rw-r--r--. 1 root root 12494  6月 10  2014 virtual
[oracle@centos7 postfix]$ sudo chmod 600 /etc/postfix/sasl_passwd
[oracle@centos7 postfix]$ ll
合計 152
-rw-r--r--. 1 root root 20876  6月 10  2014 access
-rw-r--r--. 1 root root 11681  6月 10  2014 canonical
-rw-r--r--. 1 root root  9904  6月 10  2014 generic
-rw-r--r--. 1 root root 21545  6月 10  2014 header_checks
-rw-r--r--. 1 root root 27464 12月 29 07:46 main.cf
-rw-r--r--. 1 root root  6105  6月 10  2014 master.cf
-rw-r--r--. 1 root root  6816  6月 10  2014 relocated
-rw-------. 1 root root    62 12月 29 07:52 sasl_passwd
-rw-r--r--. 1 root root 12549  6月 10  2014 transport
-rw-r--r--. 1 root root 12494  6月 10  2014 virtual
[oracle@centos7 postfix]$ 

mapping sasl_passwd to postfix


[oracle@centos7 postfix]$ ll
合計 160
-rw-r--r--. 1 root root 20876  6月 10  2014 access
-rw-r--r--. 1 root root 11681  6月 10  2014 canonical
-rw-r--r--. 1 root root  9904  6月 10  2014 generic
-rw-r--r--. 1 root root 21545  6月 10  2014 header_checks
-rw-r--r--. 1 root root 27464 12月 29 07:46 main.cf
-rw-r--r--. 1 root root  6105  6月 10  2014 master.cf
-rw-r--r--. 1 root root  6816  6月 10  2014 relocated
-rw-------. 1 root root    65 12月 29 08:57 sasl_passwd
-rw-------. 1 root root 12288 12月 29 07:53 sasl_passwd.db
-rw-r--r--. 1 root root 12549  6月 10  2014 transport
-rw-r--r--. 1 root root 12494  6月 10  2014 virtual
[oracle@centos7 postfix]$ sudo postmap /etc/postfix/sasl_passwd
[oracle@centos7 postfix]$ ll
合計 160
-rw-r--r--. 1 root root 20876  6月 10  2014 access
-rw-r--r--. 1 root root 11681  6月 10  2014 canonical
-rw-r--r--. 1 root root  9904  6月 10  2014 generic
-rw-r--r--. 1 root root 21545  6月 10  2014 header_checks
-rw-r--r--. 1 root root 27464 12月 29 07:46 main.cf
-rw-r--r--. 1 root root  6105  6月 10  2014 master.cf
-rw-r--r--. 1 root root  6816  6月 10  2014 relocated
-rw-------. 1 root root    65 12月 29 08:57 sasl_passwd
-rw-------. 1 root root 12288 12月 29 08:59 sasl_passwd.db
-rw-r--r--. 1 root root 12549  6月 10  2014 transport
-rw-r--r--. 1 root root 12494  6月 10  2014 virtual

sudo service postfix restart


[oracle@centos7 postfix]$ sudo service postfix restart
Redirecting to /bin/systemctl restart postfix.service
[oracle@centos7 postfix]$ 

test sending


echo "hoge" | mail -s "test email" メールアドレス@gmail.com

crontab -e


MAILTO='メールアドレス@gmail.com'
22 9 * * * echo "hey"

docker network

独自ネットワーク作成


[oracle@centos7 docker]$ docker network create --subnet=192.168.200.0/24 user_defined_nw
94d83b03fa3f790af5de17af4a6fb396ce8ea9e7153a3720bf663f9629362ffb
[oracle@centos7 docker]$ docker network ls
NETWORK ID          NAME                DRIVER              SCOPE
0e36d0bb7052        bridge              bridge              local
0bdba384a7be        host                host                local
483c017347fc        none                null                local
94d83b03fa3f        user_defined_nw     bridge              local

コンテナを作る


[oracle@centos7 docker]$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
oracle/database     12.2.0.1-ee         bd737fd89ef9        5 days ago          5.97GB
oraclelinux         7-slim              b19454a5f17a        7 weeks ago         117MB
hello-world         latest              4ab4c602aa5e        3 months ago        1.84kB

[oracle@centos7 docker]$ docker run -it --name test1 --net=user_defined_nw -d oraclelinux:7-slim /bin/bash
[oracle@centos7 docker]$ docker run -it --name test2 --net=user_defined_nw -d oraclelinux:7-slim /bin/bash
[oracle@centos7 docker]$ docker run -it --name test3 --net=user_defined_nw -d oraclelinux:7-slim /bin/bash


CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                     PORTS               NAMES
1c10308b5238        oraclelinux:7-slim            "/bin/bash"              28 minutes ago      Up 28 minutes                                  test1
6bc6cd79c469        oraclelinux:7-slim            "/bin/bash"              42 minutes ago      Up 42 minutes                                  test3
2e7fbb790b46        oraclelinux:7-slim            "/bin/bash"              43 minutes ago      Up 43 minutes                                  test2

割り当てられたipの確認


[oracle@centos7 ~]$ docker network inspect $(docker network ls -q) | grep -E "Subnet|Name|IPv4"
        "Name": "bridge",
                    "Subnet": "172.17.0.0/16",
        "Name": "host",
        "Name": "none",
        "Name": "user_defined_nw",
                    "Subnet": "192.168.200.0/24"
                "Name": "test1",
                "IPv4Address": "192.168.200.2/24",
                "Name": "test2",
                "IPv4Address": "192.168.200.3/24",
                "Name": "test3",
                "IPv4Address": "192.168.200.4/24",

デフォルトゲートウェイは192.168.200.1にわりあてられています。


[oracle@centos7 ~]$ docker network inspect user_defined_nw | grep -E "Subnet|Name|IPv4"
        "Name": "user_defined_nw",
                    "Subnet": "192.168.200.0/24"
                "Name": "test1",
                "IPv4Address": "192.168.200.2/24",
                "Name": "test2",
                "IPv4Address": "192.168.200.3/24",
                "Name": "test3",
                "IPv4Address": "192.168.200.4/24",

コマンドいろいろインストール


[oracle@centos7 docker]$ docker exec -it test1 /bin/bash
bash-4.2# yum install -y iputils
bash-4.2# yum -y install net-tools
bash-4.2# yum install -y iproute 
bash-4.2# yum install -y vim
bash-4.2# yum install -y traceroute

bash-4.2# ifconfig
eth0: flags=4163  mtu 1500
        inet 192.168.200.2  netmask 255.255.255.0  broadcast 192.168.200.255
        ether 02:42:c0:a8:c8:02  txqueuelen 0  (Ethernet)
        RX packets 29283  bytes 41677592 (39.7 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 13729  bytes 919089 (897.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 43  bytes 5079 (4.9 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 43  bytes 5079 (4.9 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0



bash-4.2# ip route
default via 192.168.200.1 dev eth0 
192.168.200.0/24 dev eth0 proto kernel scope link src 192.168.200.2 


bash-4.2# ping -c 3 test2
PING test2 (192.168.200.3) 56(84) bytes of data.
64 bytes from test2.user_defined_nw (192.168.200.3): icmp_seq=1 ttl=64 time=0.432 ms
64 bytes from test2.user_defined_nw (192.168.200.3): icmp_seq=2 ttl=64 time=0.199 ms
64 bytes from test2.user_defined_nw (192.168.200.3): icmp_seq=3 ttl=64 time=0.204 ms

--- test2 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.199/0.278/0.432/0.109 ms
bash-4.2# ping -c 3 test3
PING test3 (192.168.200.4) 56(84) bytes of data.
64 bytes from test3.user_defined_nw (192.168.200.4): icmp_seq=1 ttl=64 time=0.394 ms
64 bytes from test3.user_defined_nw (192.168.200.4): icmp_seq=2 ttl=64 time=0.200 ms
64 bytes from test3.user_defined_nw (192.168.200.4): icmp_seq=3 ttl=64 time=0.189 ms

--- test3 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.189/0.261/0.394/0.094 ms


bash-4.2# iptables -t nat -L -n
iptables v1.4.21: can't initialize iptables table `nat': Permission denied (you must be root)
Perhaps iptables or your kernel needs to be upgraded.
bash-4.2# pwd                
/etc/sysconfig
bash-4.2# ls
64bit_strstr_via_64bit_strstr_sse2_unaligned  cbq  ip6tables-config  iptables-config  network  rdisc
bash-4.2# vim /etc/sysconfig/iptables-config





変更前


# Unload modules on restart and stop
#   Value: yes|no,  default: yes
# This option has to be 'yes' to get to a sane state for a firewall
# restart or stop. Only set to 'no' if there are problems unloading netfilter
# modules.
IPTABLES_MODULES_UNLOAD="yes"

変更後


# Unload modules on restart and stop
#   Value: yes|no,  default: yes
# This option has to be 'yes' to get to a sane state for a firewall
# restart or stop. Only set to 'no' if there are problems unloading netfilter
# modules.
IPTABLES_MODULES_UNLOAD="no"

bash-4.2# traceroute google.com
traceroute to google.com (172.217.26.110), 30 hops max, 60 byte packets
 1  gateway (192.168.200.1)  0.167 ms  0.085 ms  0.078 ms
 2  192.168.100.1 (192.168.100.1)  3.085 ms  5.109 ms  5.011 ms
 3  * * *
 4  * * *
 5  172.23.38.114 (172.23.38.114)  78.580 ms  78.440 ms  78.241 ms
 6  172.25.114.126 (172.25.114.126)  78.088 ms  33.786 ms  43.899 ms
 7  obpBBAC05.bb.kddi.ne.jp (27.93.199.137)  43.729 ms  56.664 ms  68.491 ms
 8  27.80.241.77 (27.80.241.77)  69.502 ms 27.85.133.125 (27.85.133.125)  69.375 ms 27.85.133.109 (27.85.133.109)  69.165 ms
 9  27.86.41.102 (27.86.41.102)  67.615 ms  67.501 ms  67.182 ms
10  72.14.202.237 (72.14.202.237)  67.110 ms  66.876 ms  59.708 ms
11  * * *
12  kix05s01-in-f110.1e100.net (172.217.26.110)  57.243 ms 108.170.235.44 (108.170.235.44)  59.557 ms 108.170.235.42 (108.170.235.42)  56.599 ms
bash-4.2# 

escape sequence play


#!/bin/bash


## color code const
readonly C_GREEN=32;
readonly C_YELLOW=33;
readonly C_BLUE=34;
readonly C_MAGENTA=35;
readonly C_CYAN=36;
readonly C_L_RED=91;
readonly C_L_GREEN=92;
readonly C_L_YELLOW=93;
readonly C_L_BLUE=94;
readonly C_L_MAGENTA=95;
readonly C_L_CYAN=96;

pre_color=$PS1

function f_c()
{
printf "\033[${1}m${2}\033[${1}m\n";
}

echo 'please input'
echo '1:GREEN 2:YELLOW 3:BLUE 4:MAGENTA 5:CYAN 6:L_RED 7:L_GREEN 8:L_YELLOW 9:L_BLUE 10:L_MAGENTA 11:L_CYAN'

read num

case "$num" in
1)
color=${C_GREEN}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
2)
color=${C_YELLOW}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
3)
color=${C_BLUE}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
4)
color=${C_MAGENTA}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
5)
color=${C_CYAN}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
6)
color=${C_L_RED}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
7)
color=${C_L_GREEN}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
8)
color=${C_L_YELLOW}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
9)
color=${C_L_BLUE}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
10)
color=${C_L_MAGENTA}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
11)
color=${C_L_CYAN}
f_c ${color} "#########################"
f_c ${color} "||     hello world!    ||"
f_c ${color} "#########################"
;;
*)
;;
esac

f_c ${pre_color} ""
[oracle@centos7 lesson]$

ssh centos7 enable


[oracle@centos7 ~]$ pwd
/home/oracle
[oracle@centos7 ~]$ ll
合計 4
drwxr-xr-x. 2 oracle docker    6 12月 23 14:59 Desktop
drwxr-xr-x. 2 oracle docker    6 12月 23 14:59 Documents
drwxr-xr-x. 2 oracle docker    6 12月 23 14:59 Downloads
drwxr-xr-x. 2 oracle docker    6 12月 23 14:59 Music
drwxr-xr-x. 2 oracle docker    6 12月 23 14:59 Pictures
drwxr-xr-x. 2 oracle docker    6 12月 23 14:59 Public
drwxr-xr-x. 2 oracle docker    6 12月 23 14:59 Templates
drwxr-xr-x. 2 oracle docker    6 12月 23 14:59 Videos
drwxr-xr-x. 3 oracle docker   66 12月 23 17:12 docker
drwxrwxrwx. 4 oracle docker   32 12月 23 15:31 doclan
-rwxr-xr-x. 1 oracle docker 1299 12月 23 15:31 mkdir.sh
drwxr-xr-x. 2 oracle docker   24 12月 23 17:12 share
[oracle@centos7 ~]$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa): 
Created directory '/home/oracle/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:uZjgdcQyJLlUXxdr1Z/bqnLmKQVWDwRF9LclCEUNDKo oracle@centos7
The key's randomart image is:
+---[RSA 2048]----+
|    .oo   =@@+.. |
|    oo o o o+=. .|
|   . .o =  .+oo =|
|    .  = .o.  .++|
|    . E S. .   .o|
|   . o + .  .  ..|
|    . o .  .   . |
|          o o..  |
|           *+.   |
+----[SHA256]-----+
[oracle@centos7 ~]$ 


[oracle@centos7 ~]$ ll -a
合計 40
drwx------. 21 oracle docker 4096 12月 27 20:43 .
drwxr-xr-x.  3 root   root     20 12月 23 14:28 ..
-rw-------.  1 oracle docker 1864 12月 23 15:45 .ICEauthority
-rw-------.  1 oracle docker 3392 12月 27 20:39 .bash_history
-rw-r--r--.  1 oracle docker   18  4月 11  2018 .bash_logout
-rw-r--r--.  1 oracle docker  193  4月 11  2018 .bash_profile
-rw-r--r--.  1 oracle docker  231  4月 11  2018 .bashrc
drwx------. 17 oracle docker 4096 12月 23 14:55 .cache
drwxr-xr-x. 19 oracle docker 4096 12月 24 00:02 .config
drwx------.  3 oracle docker   25 12月 23 14:41 .dbus
-rw-------.  1 oracle docker   16 12月 23 14:41 .esd_auth
drwx------.  3 oracle docker   18 12月 23 14:55 .gnome
drwx------.  3 oracle docker   19 12月 23 14:41 .local
drwxr-xr-x.  5 oracle docker   54 12月 23 14:48 .mozilla
drwx------.  3 oracle docker   19 12月 23 14:55 .pki
drwx------.  2 oracle docker   38 12月 27 20:43 .ssh
drwxr-xr-x.  2 oracle docker    6 12月 23 14:59 Desktop
drwxr-xr-x.  2 oracle docker    6 12月 23 14:59 Documents
drwxr-xr-x.  2 oracle docker    6 12月 23 14:59 Downloads
drwxr-xr-x.  2 oracle docker    6 12月 23 14:59 Music
drwxr-xr-x.  2 oracle docker    6 12月 23 14:59 Pictures
drwxr-xr-x.  2 oracle docker    6 12月 23 14:59 Public
drwxr-xr-x.  2 oracle docker    6 12月 23 14:59 Templates
drwxr-xr-x.  2 oracle docker    6 12月 23 14:59 Videos
drwxr-xr-x.  3 oracle docker   66 12月 23 17:12 docker
drwxrwxrwx.  4 oracle docker   32 12月 23 15:31 doclan
-rwxr-xr-x.  1 oracle docker 1299 12月 23 15:31 mkdir.sh
drwxr-xr-x.  2 oracle docker   24 12月 23 17:12 share
[oracle@centos7 ~]$ 


[oracle@centos7 ~]$ cd .ssh
[oracle@centos7 .ssh]$ pwd
/home/oracle/.ssh
[oracle@centos7 .ssh]$ ll
合計 8
-rw-------. 1 oracle docker 1766 12月 27 20:43 id_rsa
-rw-r--r--. 1 oracle docker  396 12月 27 20:43 id_rsa.pub
[oracle@centos7 .ssh]$ 

サーバー側


[oracle@centos7 ~]$ ip route
default via 192.168.100.1 dev wlp7s0 proto static metric 600 
172.17.0.0/16 dev docker0 proto kernel scope link src 172.17.0.1 
192.168.100.0/24 dev wlp7s0 proto kernel scope link src 192.168.100.107 metric 600 
192.168.122.0/24 dev virbr0 proto kernel scope link src 192.168.122.1 
[oracle@centos7 ~]$ ll
合計 4
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Desktop
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Documents
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Downloads
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Music
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Pictures
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Public
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Templates
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Videos
drwxr-xr-x. 3 oracle docker   66 12月 23 15:21 docker
drwxrwxrwx. 4 oracle docker   32 12月 23 14:47 doclan
-rwxr-xr-x. 1 oracle docker 1302 12月 23 14:46 mkdir.sh
drwxr-xr-x. 2 oracle docker   42 12月 23 16:40 share
[oracle@centos7 ~]$ 

クライアント側


[oracle@centos7 .ssh]$ scp id_rsa.pub oracle@192.168.100.107:~
The authenticity of host '192.168.100.107 (192.168.100.107)' can't be established.
ECDSA key fingerprint is SHA256:lAUjRXceZduuHmiWxudjvCaspDoZb+klg+K1g40p/sI.
ECDSA key fingerprint is MD5:1d:f6:2b:f5:7e:8e:2d:b1:17:1d:b6:1b:b5:5d:c1:5f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.100.107' (ECDSA) to the list of known hosts.
oracle@192.168.100.107's password: 
id_rsa.pub                                                                                                    100%  396    43.1KB/s   00:00    
[oracle@centos7 .ssh]$ 

サーバー側


[oracle@centos7 ~]$ ll
合計 8
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Desktop
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Documents
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Downloads
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Music
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Pictures
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Public
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Templates
drwxr-xr-x. 2 oracle docker    6 12月 23 10:57 Videos
drwxr-xr-x. 3 oracle docker   66 12月 23 15:21 docker
drwxrwxrwx. 4 oracle docker   32 12月 23 14:47 doclan
-rw-r--r--. 1 oracle docker  396 12月 28 20:07 id_rsa.pub
-rwxr-xr-x. 1 oracle docker 1302 12月 23 14:46 mkdir.sh
drwxr-xr-x. 2 oracle docker   42 12月 23 16:40 share
[oracle@centos7 ~]$ 


[oracle@centos7 .ssh]$ ip route
default via 192.168.100.1 dev wlp2s0 proto static metric 600 
172.17.0.0/16 dev docker0 proto kernel scope link src 172.17.0.1 
192.168.100.0/24 dev wlp2s0 proto kernel scope link src 192.168.100.108 metric 600 
192.168.122.0/24 dev virbr0 proto kernel scope link src 192.168.122.1 
--ホスト名一緒やとぜんぜんわかね
[oracle@centos7 .ssh]$ ssh oracle@192.168.100.107
oracle@192.168.100.107's password: 
Last login: Fri Dec 28 20:22:43 2018 from 192.168.100.108
[oracle@centos7 ~]$ 


[oracle@centos7 ~]$ cd ~ && cat id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDRelZ3eydODQTdFCzCZGd5DlH0SBUkWxkTodK30b1pk37D9WDsxDFF1iOTcFVZFsYlQzMUpn1R1fM2Dhjcu7WrBmwhxsfB09bpl+HK/ZlQNZgfysEnR1mOfilai30LioV84+VFKNdiQUP+4iG6pzXMNJaqyWytLFr9gYqWRgp0znakGB187XdOEhTkNWXcP4gQsHbrB02SSTPTn8Ern+bYAdzHfNEMm8yezJFOycF0vkWQbkYqg+uf5Ry/Cy3GB7Sf6H7q78vVR+6ambU0HDhYPY3dzQ02WQeab5clMy4A5zX8fDVqfV/mncNT1GxG1e8XInl4kHxqjWStm24S8sDr oracle@centos7
[oracle@centos7 ~]$ 


[oracle@centos7 ~]$ mkdir ~/.ssh
[oracle@centos7 ~]$ ll -at
合計 48
drwx------. 21 oracle docker 4096 12月 28 20:31 .
drwxr-xr-x.  2 oracle docker    6 12月 28 20:31 .ssh
-rw-------.  1 oracle docker 4925 12月 28 20:23 .bash_history
-rw-r--r--.  1 oracle docker  396 12月 28 20:07 id_rsa.pub
drwxr-xr-x. 19 oracle docker 4096 12月 23 21:56 .config
drwxr-xr-x.  2 oracle docker   42 12月 23 16:40 share
drwxr-xr-x.  3 oracle docker   66 12月 23 15:21 docker
-rw-------.  1 oracle docker 1554 12月 23 15:16 .ICEauthority
drwxrwxrwx.  4 oracle docker   32 12月 23 14:47 doclan
-rwxr-xr-x.  1 oracle docker 1302 12月 23 14:46 mkdir.sh
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Music
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Pictures
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Videos
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Documents
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Public
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Templates
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Desktop
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Downloads
drwx------.  3 oracle docker   18 12月 23 10:53 .gnome
drwx------.  3 oracle docker   19 12月 23 10:53 .pki
drwx------. 17 oracle docker 4096 12月 23 10:53 .cache
drwxr-xr-x.  5 oracle docker   54 12月 23 10:45 .mozilla
-rw-------.  1 oracle docker   16 12月 23 10:44 .esd_auth
drwx------.  3 oracle docker   19 12月 23 10:44 .local
drwx------.  3 oracle docker   25 12月 23 10:44 .dbus
drwxr-xr-x.  3 root   root     20 12月 23 10:39 ..
-rw-r--r--.  1 oracle docker   18  4月 11  2018 .bash_logout
-rw-r--r--.  1 oracle docker  193  4月 11  2018 .bash_profile
-rw-r--r--.  1 oracle docker  231  4月 11  2018 .bashrc
[oracle@centos7 ~]$ chmod 700 .ssh
[oracle@centos7 ~]$ ll -at
合計 48
drwx------. 21 oracle docker 4096 12月 28 20:31 .
drwx------.  2 oracle docker    6 12月 28 20:31 .ssh
-rw-------.  1 oracle docker 4925 12月 28 20:23 .bash_history
-rw-r--r--.  1 oracle docker  396 12月 28 20:07 id_rsa.pub
drwxr-xr-x. 19 oracle docker 4096 12月 23 21:56 .config
drwxr-xr-x.  2 oracle docker   42 12月 23 16:40 share
drwxr-xr-x.  3 oracle docker   66 12月 23 15:21 docker
-rw-------.  1 oracle docker 1554 12月 23 15:16 .ICEauthority
drwxrwxrwx.  4 oracle docker   32 12月 23 14:47 doclan
-rwxr-xr-x.  1 oracle docker 1302 12月 23 14:46 mkdir.sh
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Music
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Pictures
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Videos
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Documents
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Public
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Templates
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Desktop
drwxr-xr-x.  2 oracle docker    6 12月 23 10:57 Downloads
drwx------.  3 oracle docker   18 12月 23 10:53 .gnome
drwx------.  3 oracle docker   19 12月 23 10:53 .pki
drwx------. 17 oracle docker 4096 12月 23 10:53 .cache
drwxr-xr-x.  5 oracle docker   54 12月 23 10:45 .mozilla
-rw-------.  1 oracle docker   16 12月 23 10:44 .esd_auth
drwx------.  3 oracle docker   19 12月 23 10:44 .local
drwx------.  3 oracle docker   25 12月 23 10:44 .dbus
drwxr-xr-x.  3 root   root     20 12月 23 10:39 ..
-rw-r--r--.  1 oracle docker   18  4月 11  2018 .bash_logout
-rw-r--r--.  1 oracle docker  193  4月 11  2018 .bash_profile
-rw-r--r--.  1 oracle docker  231  4月 11  2018 .bashrc
[oracle@centos7 ~]$ 


[oracle@centos7 ~]$ cd .ssh
[oracle@centos7 .ssh]$ ll
合計 0
[oracle@centos7 .ssh]$ touch ~/.ssh/authorized_keys
[oracle@centos7 .ssh]$ ll
合計 0
-rw-r--r--. 1 oracle docker 0 12月 28 20:35 authorized_keys
[oracle@centos7 .ssh]$ chmod 600 ~/.ssh/authorized_keys
[oracle@centos7 .ssh]$ ll
合計 0
-rw-------. 1 oracle docker 0 12月 28 20:35 authorized_keys
[oracle@centos7 .ssh]$ 


[oracle@centos7 .ssh]$ ログアウト
Connection to 192.168.100.107 closed.
[oracle@centos7 .ssh]$ ip route
default via 192.168.100.1 dev wlp2s0 proto static metric 600 
172.17.0.0/16 dev docker0 proto kernel scope link src 172.17.0.1 
192.168.100.0/24 dev wlp2s0 proto kernel scope link src 192.168.100.108 metric 600 
192.168.122.0/24 dev virbr0 proto kernel scope link src 192.168.122.1 
[oracle@centos7 .ssh]$ 


[oracle@centos7 .ssh]$ ll
合計 12
-rw-------. 1 oracle docker 1766 12月 27 20:43 id_rsa
-rw-r--r--. 1 oracle docker  396 12月 27 20:43 id_rsa.pub
-rw-r--r--. 1 oracle docker  177 12月 28 20:07 known_hosts
[oracle@centos7 .ssh]$ cat known_hosts
192.168.100.107 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBCVDRp3jReHwU4wAxBVo4Aat+UHtKLl4VVpKA3x8k+nTygk4odsn01Zrj8PDzSGd1zGT1DTXFZqYF2C+kYzkd3s=
[oracle@centos7 .ssh]$ 


[oracle@centos7 .ssh]$ ssh oracle@192.168.100.107
oracle@192.168.100.107's password: 
Last login: Fri Dec 28 20:23:22 2018 from 192.168.100.108
[oracle@centos7 ~]$ ifconfig
docker0: flags=4163  mtu 1500
        inet 172.17.0.1  netmask 255.255.0.0  broadcast 172.17.255.255
        inet6 fe80::42:e6ff:fe51:a6d6  prefixlen 64  scopeid 0x20 	 	 	 	
        ether 02:42:e6:51:a6:d6  txqueuelen 0  (Ethernet)
        RX packets 72756  bytes 34705340 (33.0 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 133133  bytes 158851749 (151.4 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

enp25s0: flags=4099  mtu 1500
        ether fc:61:98:4f:19:5f  txqueuelen 1000  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        device interrupt 17  

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 8138  bytes 614263 (599.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 8138  bytes 614263 (599.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

veth24ff817: flags=4163  mtu 1500
        inet6 fe80::707d:b1ff:fe06:274b  prefixlen 64  scopeid 0x20 	 	 	 	
        ether 72:7d:b1:06:27:4b  txqueuelen 0  (Ethernet)
        RX packets 24877  bytes 18606254 (17.7 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 39457  bytes 38601382 (36.8 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:78:5b:56  txqueuelen 1000  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

wlp7s0: flags=4163  mtu 1500
        inet 192.168.100.107  netmask 255.255.255.0  broadcast 192.168.100.255
        inet6 2001:268:c031:29e9:98e7:f562:4ec4:3  prefixlen 128  scopeid 0x0
        inet6 fe80::4bff:9d03:eef7:9a10  prefixlen 64  scopeid 0x20 	 	 	 	
        inet6 2001:268:c031:29e9:1a2b:1bda:f699:4627  prefixlen 64  scopeid 0x0
        ether 74:e5:0b:ba:d1:ac  txqueuelen 1000  (Ethernet)
        RX packets 917681  bytes 975680107 (930.4 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 599486  bytes 167522088 (159.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[oracle@centos7 ~]$ 


[oracle@centos7 .ssh]$ ll
合計 12
-rw-------. 1 oracle docker 1766 12月 27 20:43 id_rsa
-rw-r--r--. 1 oracle docker  396 12月 27 20:43 id_rsa.pub
-rw-r--r--. 1 oracle docker  177 12月 28 20:07 known_hosts
[oracle@centos7 .ssh]$ ssh -i id_rsa.pub oracle@192.168.100.107
oracle@192.168.100.107's password: 
Last login: Fri Dec 28 20:38:26 2018 from 192.168.100.108
[oracle@centos7 ~]$ ログアウト
Connection to 192.168.100.107 closed.

sudo user add docker oracle


[oracle@centos7 ~]$ docker exec -it orcl_12cr2 /bin/bash
[oracle@609a69bc0b21 ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)
[oracle@609a69bc0b21 ~]$ 

[oracle@centos7 ~]$ docker exec --interactive --tty --user root --workdir / orcl_12cr2 bash
bash-4.2# visudo
bash-4.2# exit

#--これはいけない
## Allows people in group wheel to run all commands
#%wheel ALL=(ALL)       ALL
oinstall ALL=(ALL)      ALL
#--これはいけるはず
## Same thing without a password
# %wheel        ALL=(ALL)       NOPASSWD: ALL
oracle          ALL=(ALL)       ALL

[oracle@centos7 ~]$ docker exec -it orcl_12cr2 /bin/bash
[oracle@609a69bc0b21 ~]$ whoami
oracle
[oracle@609a69bc0b21 ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)
[oracle@609a69bc0b21 ~]$ sudo echo "hey"
[sudo] password for oracle: 
hey
[oracle@609a69bc0b21 ~]$ 

docker oracle passwd install


[oracle@centos7 ~]$ docker exec --interactive --tty --user root --workdir / orcl_12cr2 bash
bash-4.2# whereis passwd
passwd: /etc/passwd
bash-4.2# passwd
bash: passwd: command not found
bash-4.2# yum install -y passwd
Loaded plugins: ovl
Resolving Dependencies
--> Running transaction check
---> Package passwd.x86_64 0:0.79-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================
 Package                          Arch                             Version                                 Repository                            Size
======================================================================================================================================================
Installing:
 passwd                           x86_64                           0.79-4.el7                              ol7_latest                           104 k

Transaction Summary
======================================================================================================================================================
Install  1 Package

Total download size: 104 k
Installed size: 420 k
Downloading packages:
passwd-0.79-4.el7.x86_64.rpm                                                                                                   | 104 kB  00:00:03     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : passwd-0.79-4.el7.x86_64                                                                                                           1/1 
  Verifying  : passwd-0.79-4.el7.x86_64                                                                                                           1/1 

Installed:
  passwd.x86_64 0:0.79-4.el7                                                                                                                          

Complete!
bash-4.2# whereis passwd
passwd: /usr/bin/passwd /etc/passwd
bash-4.2#