Oracle SQLの方言：例のモデル。 パート1

データベースからのレポートの生成を扱う開発者は、かさばるクエリの構築に定期的に遭遇します。 多くの場合、これはデータベース設計エラーが原因であり、さらに頻繁に、抽出されたデータの変換の複雑さが原因です。 後者には、反復計算法の適用、サブグループの小計の計算、隣接するサンプル行の値を使用する計算、文字列の複雑な書式設定、および同様のタスクが含まれます。 このような変換は、多くの場合、データベースレベルからアプリケーションサーバーまたはクライアントレベルに実行され、コードのパフォーマンスとメンテナンスの容易さに影響します。 これらのSQLの問題を解決するために、Oracle DBMSは分析機能とMODEL演算子を提供します。これについては、この記事で説明します。

SELECTコンストラクトのこの拡張は、DBMSバージョン10gで利用可能になりました。 MODELを使用すると、サンプルデータに多次元配列としてアクセスしたり、要素を変更および追加したり、複雑な集計を実行したり、以前はPL / SQLを使用する必要があった多くのタスクを解決したりできます。 同時に、言語構成は読みやすく宣言的です。 一言で言えば、Excelのようなものに加えて、負荷全体がデータベースサーバーの負担になります。

構文

MODEL [ IGNORE NAV ] [ RETURN UPDATED ROWS ]
[ PARTITION BY (partition_column_1, ...)]
DIMENSION BY (dimension_column_1, ...)
MEASURES (measured_column_1, ...)
RULES [ AUTOMATIC ORDER | ITERATE (value) [ UNTIL (expression)]] (
rule_1, ...
);

MODELステートメントは後者の間で処理され、その後にDISTINCTとORDER BYのみが続きます。 アプリケーションの結果として、サンプル列は、 dimension_column_ * dimensionsの measured_column_ *配列にマップされます。 オプションのPARTITION BYパラメーターは、分析関数で使用されるパーティションと同様のパーティションを定義します（それぞれがrule_ *ルールによって独立した配列として扱われます）。 ルールが列挙されます。

最も単純な例

まず、数値1、2、および3のサンプルをシミュレートします。

SELECT *
FROM dual
MODEL DIMENSION BY (0 dimension)
MEASURES (dummy)
RULES (
dummy[5] = 1,
dummy[6] = 2,
dummy[7] = 3
);

DIMENSION R
---------- -
0 X
7 3
6 2
5 1

この場合、3つのルールに従って、次元次元を持つダミー配列が埋められます。 エイリアス0ディメンションは 、新しい列を追加するために定義されています。 変換をさらに詳しく分析してみましょう。 最初のステップは、選択列（DIMENSIONの0次元 、MEASURESのダミー ）を決定して表示し、列を選択して（ ダミー= X、次元= 0を返す）、ルールが実行された後のみです。 最初に、 次元= 5の文字列が検索されますが、 見つからない場合、新しいルールが作成され、 ダミー= 1が入力されます。残りの2つのルールも同様です。 必要に応じて、RETURN UPDATED ROWSディレクティブを使用して、更新された行のみを表示できます。

SELECT result, dummy
FROM dual
MODEL RETURN UPDATED ROWS
DIMENSION BY (dummy)
MEASURES (0 result)
RULES (
result[5] = 1,
result[6] = 2,
result[7] = 3
);

RESULT DUMMY
---------- ------
3 7
2 6
1 5

ルールセットをループで適用することもできます。 次のクエリは、フィボナッチ数列のいくつかの要素を計算します。

SELECT sequence
FROM dual
MODEL DIMENSION BY (0 dimension)
MEASURES (0 sequence)
RULES ITERATE (100500) UNTIL (sequence[iteration_number] > 10) (
sequence[iteration_number] =
CASE iteration_number
WHEN 0 THEN 0
WHEN 1 THEN 1
ELSE sequence[iteration_number - 2] + sequence[iteration_number - 1]
END
);

SEQUENCE
----------
0
1
1
2
3
5
8
13

ITERATEはループの反復回数（0から開始）を設定し、オプションのUNTILディレクティブは終了するための条件を設定します（Ctrl + Endをまだ押していないという事実によって判断されます）。 カウンターへのアクセスには、 iteration_number変数を使用します。

範囲と集計

上記では、個々の要素の値を計算する例を見ました。 さらに、グループにルールを設定し、指定した条件でフィルタリングできます。 このために、2番目のタイプのリンクが使用されます-シンボリック（以前は位置のみでした）。 シンボリックリンクのインデックスには、次のような検証条件が含まれる場合があります。

• cnt [日<6、タイプLIKE 'latt％']
• cnt [日IN （3、6）、cv（タイプ）]
• cnt [1日と 16日の間、regexp_like（type、 '^。+（sso | tte）$'）]

位置リンクでは、インデックス値は一意に定義されます：

• cnt [2、 '黒']
• cnt [7、「latte」]

シンボリックリンクを使用して、新しい要素を作成することはできません。既存の要素のみを更新します（本当に必要な場合はもちろん、できますが、次のパートでさらに詳しく説明します）。 定位置のものを使用すると、更新して作成できます。 シンボリックリンクを使用して指定された範囲を持つ集計関数を使用できます。 ルール内の分析機能は禁止されています。

1週間に飲んだコーヒーに関する情報を保存するテーブルを考えてみましょう。

SELECT * FROM coffee;

TYPE CNT DAY
-------------------- ---------- ----------
turkish 1 1
espresso 1 1
turkish 2 2
black 1 2
espresso 1 2
latte 3 3
black 2 4
ice 1 4

毎日何杯のコーヒーを飲んだかについてのレポートを取得したいとします。一般的に、木曜日にはブラックコーヒーが2倍だったことを考慮します。 リクエストは次のとおりです。

SELECT *
FROM coffee
MODEL DIMENSION BY (day, type)
MEASURES (cnt)
RULES (
cnt[4, 'black' ] = cnt[cv(day), 'black' ] * 2,
cnt[ FOR day FROM 1 TO 4 INCREMENT 1, ' total for day' ] = sum<(cnt)[cv(day), ANY ],
cnt[ NULL , 'GRAND TOTAL' ] = sum(cnt)[ ANY , ' total for day' ]
)
ORDER BY day, type DESC ;

DAY TYPE CNT
--------- -------------------- ---------
1 turkish 1
1 espresso 1
1 total for day 2
2 turkish 2
2 espresso 1
2 black 1
2 total for day 4
3 latte 3
3 total for day 3
4 ice 1
4 black 4
4 total for day 5
GRAND TOTAL 14

ルールをより詳細に分析します。 最初は木曜日に飲むコーヒーの量を2倍にします。 cv（dimension_name）関数は、処理中の要素のdimension_name ディメンションの現在のインデックス値を返します（つまり、この場合、 cv（day）の代わりにday = 4を指定するか、必要に応じてday = cvとして前日を参照できます） 日）-1） 。 2番目のルールは、月曜日から木曜日までの小計を計算します。 ループ（既に説明されている）に加えて、ここでは等式の右側の要素の範囲への参照が使用されます。 範囲は、WHERE句のチェックと同じ方法で指定でき、追加のキーワードANYを使用してインデックス値を選択します。 方程式の右側の範囲へのリンクを集約する必要があります。この場合、 合計関数が使用されます。 最後に、3番目のルールは小計の合計を考慮します。

続けて。 コーヒーの種類ごとの飲み物の合計、火曜日に飲むラテの量、および水曜日にエスプレッソを飲むという今週の目標に達したかどうかに関するメッセージを表示するクエリを考えてみましょう。

SELECT *
FROM coffee
MODEL DIMENSION BY (day, type)
MEASURES (cnt)
RULES (
cnt[ NULL , FOR type IN ( SELECT DISTINCT type FROM coffee)] = sum(cnt)[ ANY , cv(type)],
cnt[ NULL , 'GRAND TOTAL' ] = sum(cnt)[ NULL , ANY ],
cnt[ NULL , ' drank ' || cnt[2, 'latte' ] || ' cups of latte on 2 day' ] = NULL ,
cnt[ NULL , CASE
WHEN cnt[3, 'espresso' ] IS PRESENT THEN ' ACHIEVED'
ELSE ' FAILED'
END || ': drank espresso on 3 day' ] = NULL
)
ORDER BY day, type DESC ;

DAY TYPE CNT
---------- ---------------------------------------- ----------
1 turkish 1
1 espresso 1
2 turkish 2
2 espresso 1
2 black 1
3 latte 3
4 ice 1
4 black 2
turkish 3
latte 3
ice 1
espresso 2
black 3
GRAND TOTAL 12
drank cups of latte on 2 day
FAILED: drank espresso on 3 day

最初のルールは、サンプル値を反復するループを使用します。 MODEL内で使用されるネストされたクエリは、無相関でなければなりません。 次のルールは合計金額を計算します。 「2日にラテを飲んだ」という行に注意してください。 なぜなら 要素cnt [2、 'latte']が見つかりませんでした。参照によりNULLになりました。 この動作は、IGNORE NAVディレクティブ（MODELという単語の後に追加）を使用して変更できます。次に、見つからない要素とNULLの代わりに、計算が置換されます。 最後に、4番目のルールは式IS PRESENTの使用を示します。指定された要素が存在する場合はtrueを返しますが、残念ながら水曜日にエスプレッソは飲まれませんでした。

この入門は完了しましたが、一般的なアイデアを得て、構文に慣れることを願っています。 第2部では、要素の更新と作成の管理、MODEL出力の制御、メソッドの適用性とそのパフォーマンスの問題について説明します。 そして、もちろん、より複雑な例についても説明します。

続きを読む

文学

1. Oracle®Database Data Warehousing Guide 11gリリース2-第22章「モデリング用SQL」。
2. Oracle Database 10gのSQLモデル句。