Oracleでテキスト加工 ( Oracle + SQL*Loader + SELECT )

最近会社で聞かれて答えたことをまとめてみました。

こんなことを聞かれました。

あるテキストデータを使ってデータ分析を行いたい。分析はExcelで行っている。しかし元データとなるテキストファイルには余分な情報がたくさん含まれている。加えてエラー発生の有無で形式が大きく変わる。今は、テキストエディタで加工しているが、かなり大変。何とかならないだろうか。

この元データは、実は先日の記事に登場した定期処理です。以下のような状況になっていました。

• 定期処理の実行ログをテキストファイルに格納したものとなっている。
• 一回のサイクル処理が複数行を構成している。
• 一回のサイクル処理の中から、以下の部分を取り出して使用したい。
• 日時が記録されている行から、日時情報を抜き出したもの
• データ格納行から、数値データを抜き出し、平均値を算出したもの
• 処理に失敗したときは、エラーを表す特別な値としたい

grepで簡単にできる部分、awkやperlなら手軽にできそうかなという部分がありました。しかしawkやperlを知らない人に説明するのは大変です。そのため、"Oracle"＋"SQL*Loader"＋"SELECT"という方法を説明しました。

ここでは、その時に説明したことをまとめてみます。

今回、"SQLLoader"を使います。Oracle Instant Clientを使用する場合は、12.2以上を使う必要があります。それ以前のバージョンには"SQLLoader"は含まれていません。

元データ

会社の題材をそのまま使用するわけにはいきません。そのため、テストデータ作成用のバッチファイルを作成しました。

やっていることは、"PING"を複数回実行し、その結果を時刻とともにファイルに記録するというものです。短い時間で応答が返却される"127.0.0.1"、ある程度長い時間で応答が返却される"1.1.1.1"、応答が返ってこない"10.1.1.1"の3種にPINGを投げるようにしています。

サーバーが3種類あるのは単にバリエーションのあるテストデータを作り出すためだけのものです。選んだサーバーに深い意味はありません。

バッチファイルは以下のようになりました。

@ECHO OFF

SETLOCAL

CD /D %~dp0

SET FILE=%~n0
SET FILE=%FILE:run-=%
SET FILE=%FILE%.dat

DEL /F %FILE%

CALL :PROC_EACH 1.1.1.1
TIMEOUT /T 30
CALL :PROC_EACH 10.1.1.1
TIMEOUT /T 30
CALL :PROC_EACH 1.1.1.1
TIMEOUT /T 30
CALL :PROC_EACH 127.0.0.1
TIMEOUT /T 30
CALL :PROC_EACH 1.1.1.1

EXIT /B 0

:PROC_EACH
SETLOCAL
SET IP=%1
ECHO 時刻＝%TIME% >> %FILE%
PING %IP% >> %FILE%
EXIT /B 0

テストデータは以下のようになりました。

時刻＝15:36:07.44 

1.1.1.1 に ping を送信しています 32 バイトのデータ:
1.1.1.1 からの応答: バイト数 =32 時間 =31ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =42ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =38ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =54ms TTL=56

1.1.1.1 の ping 統計:
    パケット数: 送信 = 4、受信 = 4、損失 = 0 (0% の損失)、
ラウンド トリップの概算時間 (ミリ秒):
    最小 = 31ms、最大 = 54ms、平均 = 41ms
時刻＝15:36:40.79 

10.1.1.1 に ping を送信しています 32 バイトのデータ:
要求がタイムアウトしました。
要求がタイムアウトしました。
要求がタイムアウトしました。
要求がタイムアウトしました。

10.1.1.1 の ping 統計:
    パケット数: 送信 = 4、受信 = 0、損失 = 4 (100% の損失)、
時刻＝15:37:29.19 

1.1.1.1 に ping を送信しています 32 バイトのデータ:
1.1.1.1 からの応答: バイト数 =32 時間 =62ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =64ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =65ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =59ms TTL=56

1.1.1.1 の ping 統計:
    パケット数: 送信 = 4、受信 = 4、損失 = 0 (0% の損失)、
ラウンド トリップの概算時間 (ミリ秒):
    最小 = 59ms、最大 = 65ms、平均 = 62ms
時刻＝15:38:02.12 

127.0.0.1 に ping を送信しています 32 バイトのデータ:
127.0.0.1 からの応答: バイト数 =32 時間 =1ms TTL=128
127.0.0.1 からの応答: バイト数 =32 時間 =2ms TTL=128
127.0.0.1 からの応答: バイト数 =32 時間 <1ms TTL=128
127.0.0.1 からの応答: バイト数 =32 時間 <1ms TTL=128

127.0.0.1 の ping 統計:
    パケット数: 送信 = 4、受信 = 4、損失 = 0 (0% の損失)、
ラウンド トリップの概算時間 (ミリ秒):
    最小 = 1ms、最大 = 2ms、平均 = 1ms
時刻＝15:38:35.18 

1.1.1.1 に ping を送信しています 32 バイトのデータ:
1.1.1.1 からの応答: バイト数 =32 時間 =49ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =37ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =45ms TTL=56
1.1.1.1 からの応答: バイト数 =32 時間 =66ms TTL=56

1.1.1.1 の ping 統計:
    パケット数: 送信 = 4、受信 = 4、損失 = 0 (0% の損失)、
ラウンド トリップの概算時間 (ミリ秒):
    最小 = 37ms、最大 = 66ms、平均 = 49ms

このテストデータの中で、時刻情報と応答所要時間の部分を使用して集計するという題材にします。但し"応答時間"の集計には"時間～ms"という部分を使用し、"平均=～ms"という部分を使用しないことにします。

集計の際、"<1ms"は"0.5ms"、応答のなかったものは"1000ms"という扱いにします。

大まかな手順

SQL*Loaderは、一定の書式で記されたファイルを効率的にOracle DBに取り込む仕組みです。

WEBでよく見かける例では、固定長ファイルやCSVファイルなどを取り込む例が多いです。

しかし、今回はそのような定型的なものではありません。しかも、加工に試行錯誤が付きまとうような状況です。このようなときには、まずテキスト全体を単一カラムに取り込んで、SQLを試行錯誤しながら情報分割や集計や加工をするようにしています。つまり、以下のような手順としています。

• OracleDB環境の準備
• 取り込み先テーブルの作成
• SQL*Loaderを用いたテキストファイルの取り込み
• 取り込んだ情報をSQLで加工、集計

実際にやってみた結果

OracleDB環境の準備

今回は"Oracle11g(on Docker)"を使用しました。環境準備方法は省略します。但し、先日の記事とほぼ同じ手順です。

取り込み先テーブルの作成

1行をまるごと単一カラムに取り込む前提でテーブルを作成します。

いつもは、COL001～COL050まで設けてある汎用読み込みテーブルを使っています。即席でCSVなどを取り込むときは、汎用読み込みテーブルを使っています。今回は1カラムと決まっているため、COL001だけを定義しています。

SEQ_NOは行番号を表します。PKは省略しています。

CREATE TABLE TBL_SQLLDR (
		SEQ_NO	NUMBER
	,	COL001	VARCHAR2(4000)
);
COMMENT ON COLUMN TBL_SQLLDR.SEQ_NO IS '行番号';
COMMENT ON COLUMN TBL_SQLLDR.COL001 IS 'テキストデータ';

SQL*Loaderを用いたテキストファイルの取り込み

制御ファイルの作成

制御ファイルは、データファイルをDBにどのように取り込むかを定義するためのファイルです。

今回は、1行をまるごと特定のカラムに取込みます。取込み先のテーブルもカラムも決まっています。そのため、制御ファイルを固定的に書くことができます。

データファイルは処理対象ごとに異なります。そのため、制御ファイルでは指定していません。

LOAD DATA
CHARACTERSET JA16SJISTILDE
TRUNCATE INTO TABLE TBL_SQLLDR
(
        SEQ_NO  SEQUENCE(1, 1)
    ,   COL001  POSITION(1:4000) CHAR
)

SQL*Loaderの実行

データファイルを実行時に指定してDBに取り込みます。BADファイルやLOGファイルの指定は省略しています。省略すると、拡張子は自動的に".BAD"や".LOG"になります。

SqlLdr ^
    TEST/TEST@127.0.0.1:1521/XE ^
    CONTROL=SqlLoader-1-column.ctl ^
    DATA=CheckData.dat ^

取り込み後にログファイルを確認します。以下のような内容になっていれば全データがDBに取り込まれていることになります。"skipped"や"rejected"や"discarded"の行がないことから、エラーなく取り込まれたことが分かります。

    :
Table TBL_SQLLDR:
  58 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
    :
Total logical records skipped:          0
Total logical records read:            58
Total logical records rejected:         0
Total logical records discarded:        0
    :

取り込み結果

取り込んだ結果、TBL_SQLLDRは以下のような状況になりました。先頭10件のみを抜粋しています。

SQL_NO	COL001
1	時刻＝15:36:07.44
2
3	1.1.1.1 に ping を送信しています 32 バイトのデータ:
4	1.1.1.1 からの応答: バイト数 =32 時間 =31ms TTL=56
5	1.1.1.1 からの応答: バイト数 =32 時間 =42ms TTL=56
6	1.1.1.1 からの応答: バイト数 =32 時間 =38ms TTL=56
7	1.1.1.1 からの応答: バイト数 =32 時間 =54ms TTL=56
8
9	1.1.1.1 の ping 統計:
10	パケット数: 送信 = 4、受信 = 4、損失 = 0 (0% の損失)、

加工

やり方は色々とあると思います。今回は説明上の各プロセスを分かりやすくするため、プロセスを細分化して説明しています。

• 必要な行のみに限定
• 抽出したい個所を抜き出して別項目に出力
• 特殊値の変換
• TM列の補完
• 単一行化
• 集計

必要な行のみに限定

これは単純です。"時刻／応答／タイムアウト"を含む行を抜き出せばOKです。余分な行を抽出していないこと、必要な行をもれなく抽出していることを確認します。

事前に確認のためのSQLを使って確認しておきます。"MARK列"にマークのある行が抽出される行です。

SELECT	T.SEQ_NO
	,	T.COL001
	,	CASE WHEN T.COL001 LIKE '%時刻%' OR T.COL001 LIKE '%応答%' OR T.COL001 LIKE '%タイムアウト%' THEN '■' END AS MARK
FROM	TBL_TEST T
ORDER BY
		T.SEQ_NO

確認OKなら、CASE式で指定した条件式をWHERE句に移して結果抽出用SQLとします。

SELECT	T.SEQ_NO
	,	T.COL001
FROM	TBL_TEST T
WHERE   WHEN T.COL001 LIKE '%時刻%' OR T.COL001 LIKE '%応答%' OR T.COL001 LIKE '%タイムアウト%' THEN '■' END AS MARK
ORDER BY
		T.SEQ_NO

SQL_NO	COL001
1	時刻＝15:36:07.44
4	1.1.1.1 からの応答: バイト数 =32 時間 =31ms TTL=56
5	1.1.1.1 からの応答: バイト数 =32 時間 =42ms TTL=56
6	1.1.1.1 からの応答: バイト数 =32 時間 =38ms TTL=56
7	1.1.1.1 からの応答: バイト数 =32 時間 =54ms TTL=56
13	時刻＝15:36:40.79
16	要求がタイムアウトしました。
17	要求がタイムアウトしました。
18	要求がタイムアウトしました。
19	要求がタイムアウトしました。

抽出したい個所を抜き出して別項目に出力(時刻、算出値)

"時刻"と"時間="を抜き出すことにします。ここでは"CASE式"と"REPLACE/REGEXP_REPLACE"を組み合わせることにしました。

SELECT	T.SEQ_NO
	,	T.COL001
	,	CASE WHEN T.COL001 LIKE '%時刻%' THEN REPLACE ( T.COL001, '時刻＝' ) END AS TM
	,	CASE WHEN T.COL001 LIKE '%時間%' OR T.COL001 LIKE 'タイムアウト' THEN REGEXP_REPLACE ( T.COL001, '^.*時間 (.*) TTL=.*$', '\1' ) END AS RT
FROM	TBL_TEST T
WHERE	T.COL001 LIKE '%時刻%'
	OR	T.COL001 LIKE '%応答%'
	OR	T.COL001 LIKE '%タイムアウト%'

SQL_NO	COL001	TM	RT
1	時刻＝15:36:07.44	15:36:07.44
4	1.1.1.1 からの応答: バイト数 =32 時間 =31ms TTL=56		=31ms
5	1.1.1.1 からの応答: バイト数 =32 時間 =42ms TTL=56		=42ms
6	1.1.1.1 からの応答: バイト数 =32 時間 =38ms TTL=56		=38ms
7	1.1.1.1 からの応答: バイト数 =32 時間 =54ms TTL=56		=54ms
13	時刻＝15:36:40.79	15:36:40.79
16	要求がタイムアウトしました。
17	要求がタイムアウトしました。
18	要求がタイムアウトしました。
19	要求がタイムアウトしました。

特殊値の変換

項目"RT"には、以下の3種類の形式があります。集計方法に従い、変換しておきます。

• "=～ms" ⇒ "～"部分を抽出
• "<1ms" ⇒ 0.5
• NULL ⇒ 1000

WITH
T1 AS (
SELECT	T.SEQ_NO
	,	T.COL001
	,	CASE WHEN T.COL001 LIKE '%時刻%' THEN REPLACE ( T.COL001, '時刻＝' ) END AS TM
	,	CASE WHEN T.COL001 LIKE '%時間%' OR T.COL001 LIKE 'タイムアウト' THEN REGEXP_REPLACE ( T.COL001, '^.*時間 (.*) TTL=.*$', '\1' ) END AS RT
FROM	TBL_TEST T
WHERE	T.COL001 LIKE '%時刻%'
	OR	T.COL001 LIKE '%応答%'
	OR	T.COL001 LIKE '%タイムアウト%'
)

SELECT	T.SEQ_NO
	,	T.TM
	,	T.RT	AS	RT_ORIGINAL
	,	CASE
			WHEN T.TM IS NOT NULL THEN NULL
			WHEN T.RT LIKE '=%' THEN TO_NUMBER ( REGEXP_REPLACE ( T.RT, '^=(.*)ms$', '\1' ) )
			WHEN T.RT LIKE '<1ms' THEN 0.5
			WHEN T.RT IS NULL THEN 1000
		END		AS	RT
FROM	T1 T
ORDER BY
		T.SEQ_NO

SQL_NO	COL001	TM	RT_ORIGINAL	RT
1	時刻＝15:36:07.44	15:36:07.44
4	1.1.1.1 からの応答: バイト数 =32 時間 =31ms TTL=56		=31ms	31
5	1.1.1.1 からの応答: バイト数 =32 時間 =42ms TTL=56		=42ms	42
6	1.1.1.1 からの応答: バイト数 =32 時間 =38ms TTL=56		=38ms	38
7	1.1.1.1 からの応答: バイト数 =32 時間 =54ms TTL=56		=54ms	54
13	時刻＝15:36:40.79	15:36:40.79
16	要求がタイムアウトしました。			1000
17	要求がタイムアウトしました。			1000
18	要求がタイムアウトしました。			1000
19	要求がタイムアウトしました。			1000

TM列の補完

この手の処理では、テキストファイルをいくつかのグループに分けることがよくあります。今回の場合、ワンサイクル分のデータで分けます。"時刻＝"で始まる行から、次の"時刻＝"の手前までがワンサイクル分のデータとなっています。

この手の補完を行う定型的な方法があります。"MAX"と"ROWS UNBOUNDED PRECEDING"を使う方法です。"ROWS UNBOUNDED PRECEDING"は過去の記事で登場した"現在行よりも上の範囲だけを使って分析関数を施す"というものです。"現在行よりも上の範囲で、一番大きな値"を取得すれば、それが直近の"時刻＝"の値を指し示すことになります。"時刻＝"の値が時系列に沿って増加していくからです。

補完したい列の値が昇順に並んでいない場合には"MAX"を使うことはできません。しかし、代わりに"LAST_VALUE"を使うことができます。"LAST_VALUE(～ IGNORE NULLS) OVER (ORDER BY ～ ROWS UNBOUNDED PRECEDING)"という形で分析関数を使うと、"NULL以外の一番後ろの値"つまり"直近の値"を取得することができます。これなら昇順に並んでいるかどうかを気にせず必ず使えます。(2021/04/15追記)

WITH
T1 AS (
SELECT	T.SEQ_NO
	,	T.COL001
	,	CASE WHEN T.COL001 LIKE '%時刻%' THEN REPLACE ( T.COL001, '時刻＝' ) END AS TM
	,	CASE WHEN T.COL001 LIKE '%時間%' OR T.COL001 LIKE 'タイムアウト' THEN REGEXP_REPLACE ( T.COL001, '^.*時間 (.*) TTL=.*$', '\1' ) END AS RT
FROM	TBL_TEST T
WHERE	T.COL001 LIKE '%時刻%'
	OR	T.COL001 LIKE '%応答%'
	OR	T.COL001 LIKE '%タイムアウト%'
)

SELECT	T.SEQ_NO
	,	T.TM
	,	MAX ( T.TM ) OVER ( ORDER BY T.SEQ_NO ROWS UNBOUNDED PRECEDING )	AS	TM_NEW
	,	CASE
			WHEN T.TM IS NOT NULL THEN NULL
			WHEN T.RT LIKE '=%' THEN TO_NUMBER ( REGEXP_REPLACE ( T.RT, '^=(.*)ms$', '\1' ) )
			WHEN T.RT LIKE '<1ms' THEN 0.5
			WHEN T.RT IS NULL THEN 1000
		END		AS	RT
FROM	T1 T

ORDER BY
		T.SEQ_NO

SQL_NO	TM_ORIGINAL	TM	RT
1	15:36:07.44	15:36:07.44
4		15:36:07.44	31
5		15:36:07.44	42
6		15:36:07.44	38
7		15:36:07.44	54
13	15:36:40.79	15:36:40.79
16		15:36:40.79	1000
17		15:36:40.79	1000
18		15:36:40.79	1000
19		15:36:40.79	1000

単一行化

今回のケースでは、"GROUP BY T.TM_NEW"して"AVG ( T.RT )"によって算出することができます。

以下、集計方法が単純ではない場合を想定して説明します。

LEADやLAGを用いて前後の行の情報を横並びに持ってくるという方法があります。

WITH
T1 AS (
SELECT	T.SEQ_NO
	,	T.COL001
	,	CASE WHEN T.COL001 LIKE '%時刻%' THEN REPLACE ( T.COL001, '時刻＝' ) END AS TM
	,	CASE WHEN T.COL001 LIKE '%時間%' OR T.COL001 LIKE 'タイムアウト' THEN REGEXP_REPLACE ( T.COL001, '^.*時間 (.*) TTL=.*$', '\1' ) END AS RT
FROM	TBL_TEST T
WHERE	T.COL001 LIKE '%時刻%'
	OR	T.COL001 LIKE '%応答%'
	OR	T.COL001 LIKE '%タイムアウト%'
)
,
T2 AS (
SELECT	T.SEQ_NO
	,	T.TM
	,	MAX ( T.TM ) OVER ( ORDER BY T.SEQ_NO ROWS UNBOUNDED PRECEDING )	AS	TM_NEW
	,	CASE
			WHEN T.TM IS NOT NULL THEN NULL
			WHEN T.RT LIKE '=%' THEN TO_NUMBER ( REGEXP_REPLACE ( T.RT, '^=(.*)ms$', '\1' ) )
			WHEN T.RT LIKE '<1ms' THEN 0.5
			WHEN T.RT IS NULL THEN 1000
		END		AS	RT
FROM	T1 T
)

SELECT	T.SEQ_NO
	,	T.TM
	,	T.TM_NEW
	,	T.RT
	,	LEAD ( T.RT, 1 ) OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )	AS	RT_01
	,	LEAD ( T.RT, 2 ) OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )	AS	RT_02
	,	LEAD ( T.RT, 3 ) OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )	AS	RT_03
	,	LEAD ( T.RT, 4 ) OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )	AS	RT_04
FROM	T2 T

ORDER BY
		T.SEQ_NO

SQL_NO	TM	TM_NEW	RT	RT_01	RT_02	RT_03	RT_04
1	15:36:07.44	15:36:07.44		31	42	38	54
4		15:36:07.44	31	42	38	54
5		15:36:07.44	42	38	54
6		15:36:07.44	38	54
7		15:36:07.44	54
13	15:36:40.79	15:36:40.79		1000	1000	1000	1000
16		15:36:40.79	1000	1000	1000	1000
17		15:36:40.79	1000	1000	1000
18		15:36:40.79	1000	1000
19		15:36:40.79	1000

計算に必要な情報"RT_01～RT_04"が、同じ行内にすべて揃いました。この状態になれば、後は"TM IS NOT NULL"の行の"RT_01～RT_04"の平均値を算出すればよいことが分かるでしょう。明細データの行数が4行固定という点が、"RT_01～RT_04"の平均値でよい理由となっています。

行数が可変なら、もう少し工夫する必要があります。

LEADやLAGを用いると、前後数行の情報を同じ行に持ってくることができます。判断や計算に必要な情報を同じ行に持ってくることができれば、CASE式で何とでも計算できます。RT列以外の情報も持ってくることができます。SELECT句に入れれば、元々のテキストデータ"T.COL001"やその前後行も持ってくることもできます。

なお"PARTITION BY T.TM_NEW"を付けないようにすれば、グループの垣根を越えて前後の行を参照することも可能です。

また、LEADやLAG以外の分析関数を使うこともできます。

• 明細件数
• COUNT(*) OVER ( PARTITION BY T.TM_NEW )
• 正常動作の場合と異常動作の場合で明細件数が異なるなど
• グループ内での明細の序数
• ROW_NUMBER() OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )
• 1件目と2件目以降で扱いが異なるなど
• グループ内でのタイムアウト発生有無
• SUM ( CASE WHEN T.COL001 LIKE '%タイムアウト%' THEN 1 END ) OVER ( PARTITION BY T.TM_NEW )
• タイムアウトが1回でもあったら特別な扱いとしたい場合など

処理対象行の前後の数行、処理対象グループの明細行全ての情報を、簡単に判断に含めることができる。これがSQLをテキスト加工に利用する際の大きなメリットです。

集計

前記のとおり"GROUP BY"だけでもできます。しかしここでは、RT列を"LEAD"で横並びにして、横並びになった項目で平均値を計算する方法を使ってみます。途中経過の"RT_01～RT_04"を並べて見せることができるためです。

WITH
T1 AS (
SELECT	T.SEQ_NO
	,	T.COL001
	,	CASE WHEN T.COL001 LIKE '%時刻%' THEN REPLACE ( T.COL001, '時刻＝' ) END AS TM
	,	CASE WHEN T.COL001 LIKE '%時間%' OR T.COL001 LIKE 'タイムアウト' THEN REGEXP_REPLACE ( T.COL001, '^.*時間 (.*) TTL=.*$', '\1' ) END AS RT
FROM	TBL_TEST T
WHERE	T.COL001 LIKE '%時刻%'
	OR	T.COL001 LIKE '%応答%'
	OR	T.COL001 LIKE '%タイムアウト%'
)
,
T2 AS (
SELECT	T.SEQ_NO
	,	T.TM
	,	MAX ( T.TM ) OVER ( ORDER BY T.SEQ_NO ROWS UNBOUNDED PRECEDING )	AS	TM_NEW
	,	CASE
			WHEN T.TM IS NOT NULL THEN NULL
			WHEN T.RT LIKE '=%' THEN TO_NUMBER ( REGEXP_REPLACE ( T.RT, '^=(.*)ms$', '\1' ) )
			WHEN T.RT LIKE '<1ms' THEN 0.5
			WHEN T.RT IS NULL THEN 1000
		END		AS	RT
FROM	T1 T
)
,
T3 AS (
SELECT	T.SEQ_NO
	,	T.TM
	,	T.TM_NEW
	,	T.RT
	,	LEAD ( T.RT, 1 ) OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )	AS	RT_01
	,	LEAD ( T.RT, 2 ) OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )	AS	RT_02
	,	LEAD ( T.RT, 3 ) OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )	AS	RT_03
	,	LEAD ( T.RT, 4 ) OVER ( PARTITION BY T.TM_NEW ORDER BY T.SEQ_NO )	AS	RT_04
FROM	T2 T
)

SELECT	T.SEQ_NO
	,	T.TM
	,	T.RT_01
	,	T.RT_02
	,	T.RT_03
	,	T.RT_04
	,	( T.RT_01 + T.RT_02 + T.RT_03 + T.RT_04 ) / 4 AS RESULT
FROM	T3 T
WHERE	T.TM IS NOT NULL
ORDER BY
		T.SEQ_NO

SQL_NO	TM	RT_01	RT_02	RT_03	RT_04	RESULT
1	15:36:07.44	31	42	38	54	41.25
13	15:36:40.79	1000	1000	1000	1000	1000
23	15:37:29.19	62	64	65	59	62.5
35	15:38:02.12	1	2	.5	.5	1
47	15:38:35.18	49	37	45	66	49.25

まとめ

使っている手法は大したことはありません。書き出しておきます。

• 必要な情報を別カラムとして抜き出す
• 抜き出す対象行のみから抜き出すために"CASE式"を使用
• 抜き出しが複雑で分岐が必要なら"CASE式"を使用
• 抜き出し個所が複雑な場合には"REGEXP_REPLACE"を使用
• 別の行の値を使用する
• 時刻用のカラムを補完する定型式 "MAX(～) OVER ( ORDER BY ～ ROWS UNBOUNDED PRECEDING )"
• 昇順に並んでいないカラムであっても補完できる定型式 "LAST_VALUE( ～ IGNORE NULLS ) OVER ( ORDER BY ～ ROWS UNBOUNDED PRECEDING )"
• 前後の列に含まれるカラムを取得する"LEAD"や"LAG"
• 時刻用のカラムを補完した後で、分析関数を使用する
• SQLが複雑になってきたらWITHで固める
• "CASE式"や分析関数の結果を、式などで使いたい場合

後は、これらの組み合わせ方、使いどころのパターンを増やしていくだけです。