Oracle SQLの方言：例のモデル。 パート2
これは、SELECTステートメント拡張機能（モデル構成）の使用に関する記事の続きです。 最初の部分から、アプリケーションの目的といくつかの機能についてのアイデアが既にあり、構文の半分に精通しています。 次に、範囲とパフォーマンスの分析だけでなく、いくつかの複雑な例を分析します。
MODEL構文を使用すると、他のSELECTブロック内での集計関数の使用が禁止されます（一般に、選択可能な列はすべて、MODELで言及された列から派生する必要があります）-代わりに、PARTITION BY、DIMENSION BY、またはMEASURES内でこれらの関数を宣言します。たとえば、次の簡単なクエリ
SELECT employee_id, sum(amount)
FROM sales
GROUP BY employee_id;
同等になります
SELECT employee_id, amt
FROM sales
GROUP BY employee_id
MODEL PARTITION BY (employee_id)
DIMENSION BY (0 dummy)
MEASURES (sum(amount) amt)
RULES ();
ルールによる変更を制御するための3つのセマンティクスがあります：UPDATE、UPSERT、UPSERT ALL。 UPDATEはアイテムの更新のみを可能にし、UPSERT（デフォルトで機能する）は位置リンクを使用して変更および追加でき、UPSERT ALLではシンボリックリンクを使用してアイテムを作成できます。 セマンティクスは、個々のルール（この場合、ディレクティブはその前に記述されます）とブロック全体のレベル（ディレクティブはRULESの後に指定する必要があります）の両方で変更できます。
UPSERT ALLはFORループのようには機能しないことを知っておくことが重要です。 この場合の新しいアイテムの作成は、4つのステップで行われます。
- シンボリックリンクに一致するすべての要素が見つかりました。
- 各ディメンションには、一意のインデックス値のセットがあります。
- これらのセットのデカルト積が計算されます。
- 作品に存在しないすべての要素が作成されます。
複雑な例
最後に、MODELを使用してさまざまなタスクを解決する方法を示すいくつかの例を示します。 始めに、1日に飲んだコーヒーを1行でリストします。
SELECT day, substr(type, 2) listing
FROM coffee
MODEL RETURN UPDATED ROWS
PARTITION BY (day)
DIMENSION BY (row_number() OVER ( PARTITION BY day ORDER BY type) position)
MEASURES (type, cnt)
RULES ITERATE (100500) UNTIL (presentv(type[iteration_number + 2], 1, 0) = 0) (
type[0] = type[0] || ', ' || cnt[iteration_number + 1] || ' x ' || type[iteration_number + 1]
)
ORDER BY day;
DAY LISTING
---------- ----------------------------------------
1 1 x espresso, 1 x turkish
2 1 x black, 1 x espresso, 2 x turkish
3 3 x latte
4 2 x black, 1 x ice
ここで新しい関数presentv（ref、arg1、arg2）から、 ref参照がNOT NULL値を持つ既存の要素を指す場合はarg1を返し、そうでない場合はarg2を返します。 行番号付けと名前によるソートには、分析関数（row_number（）OVER（PARTITION BY day ORDER BY type））が使用されます。 モデルと分析機能の両方でパーティション分割（PARTITION BY）を行うことにより、1日ごとに個別に処理されます。
そして最後に、幾何学的な例です。 交差するセグメントのセット[p1、p2]は直線上にあります;セグメントで完全にカバーされている範囲のソートされたリストを表示する必要があります。
SELECT * FROM lines;
P1 P2
---------- ----------
1 6
5 7
2 4
8 20
11 28
30 32
ランダムに保存されたセグメントの解は、p2> = p1であるため、関心のある人々がウォームアップすることができます。 サンプルコード：
SELECT p1, max(p2) p2
FROM (
SELECT p1, p2 FROM lines
MODEL DIMENSION BY (row_number() OVER ( ORDER BY p1, p2) rn)
MEASURES (p1, p2)
RULES AUTOMATIC ORDER (
p1[rn > 1] ORDER BY rn = CASE sign(p2[cv(rn) - 1] - p1[cv(rn)])
WHEN 1 THEN p1[cv(rn) - 1]
ELSE p1[cv(rn)]
END ,
p2[rn > 1] ORDER BY rn = greatest(p2[cv(rn) - 1], p2[cv(rn)])
)
)
GROUP BY p1
ORDER BY p1;
P1 P2
---------- ----------
1 7
8 28
30 32
モデル内のセグメントは、最初に左ポイントでソートされ、番号が付けられ（row_number（）OVER（ORDER BY p1、p2））、次に番号で上から下に表示されます（ORDER BY rnディレクティブ）。 初めて、等式の左側でシンボリックリンクが使用されるため、要素が処理される順序を指定する必要があることに注意してください。 表示されたセグメントのポイントp1が前のセグメントに属している場合、それは前のセグメントのp1に置き換えられます。 p2は、現在のセグメントと前のセグメントの右端のp2に置き換えられます）。 したがって、下に行くと、セグメントでカバーされる範囲を拡大します。 AUTOMATIC ORDERディレクティブを使用して、 現在のセグメントのp1とp2を順番に読み取ります。 ディレクティブを削除すると、 すべての行の最初のルールが最初に実行され、次に2番目のルールのみが実行されます。 一般的な場合、このディレクティブは、ルールを処理するときに要素間の依存関係を考慮に入れ、それらの回避の順序に違反する可能性があります（上記を参照）。
実用的なアプリケーションとパフォーマンス
一般に、MODELはほとんど使用されません。 これは、本当に複雑なものの90％が分析関数によって解決され、モデル用に残っている10％が伝統的にデータベースレベルから取り出されるためです。 それにもかかわらず、主なアプリケーションシナリオは次のとおりです。
- 真に複雑なレポートを生成する必要があります（読みやすさと保守の容易さが優先されます）。
- 分析関数を使用してもサンプリングは実行できません。
- 要求は高水準言語で動的に生成されます（分析よりもMODELでルールを生成する方が簡単です）。
- MODELで十分な場合はリソースを大量に消費する反復計算が使用され、PL / SQLの移行によりパフォーマンスが低下します。
- 学問的関心（手続き的ではなく宣言的アプローチ）。
- SQL MODEL ORDERED [FAST]-ルールがクエリで指定された順序で処理されることを意味します（デフォルト）。
- SQL MODEL ACYCLIC [FAST]-セルの依存関係は自動的に計算されます。
- SQL MODEL CYCLICは、セル間に複雑な関係がある最も遅いオプションです。
文学
- Oracle®Database Data Warehousing Guide 11gリリース2-第22章「モデリング用SQL」。
- Oracle Database 10gのSQLモデル句。
All Articles