Oracleのグループ化とウィンドウ機能

こんにちは、Habr！私が働いている会社では、ミートアップが頻繁に行われます（失礼します）。これらの1つは、私の同僚によるOracle Windowing andGroupingについての講演を特集しました。このトピックについて投稿する価値があるように思えました。







最初から、この場合、Oracleは集合的なSQL言語として提示されていることを明確にしたいと思います。グループ化とその適用方法は、SQLファミリ全体（ここでは構造化クエリ言語として理解されています）に適用され、各言語の構文が修正されたすべてのクエリに適用されます。



必要な情報をすべて簡潔かつ簡単に2つのパートに分けて説明します。この投稿は、初心者の開発者にとっておそらく役立つでしょう。誰が気にする-猫へようこそ。

パート1：オファリングの注文、グループ化、所有

ここでは、並べ替え-並べ替え、グループ化-グループ化、フィルタリング-所有、およびクエリプランについて説明します。しかし、まず最初に。

注文する

Order by演算子は、出力値を並べ替えます。取得した値を特定の列で並べ替えます。並べ替えは、演算子を使用して定義された列エイリアスによって適用することもできます。



Order byの利点は、数値列と文字列列の両方に適用できることです。文字列列は通常、アルファベット順にソートされます。



昇順の並べ替えがデフォルトで適用されます。列を降順で並べ替える場合は、追加のDESC演算子を使用します。



構文：



SELECT column1、column2、 …（名前を示します）

FROM table_name

ORDER BY column1、column2 … ASC | DESC ;



例を挙げてすべてを見てみましょう。





最初の表では、すべてのデータを取得し、ID列の昇順で並べ替えています。



2番目では、すべてのデータも取得します。DESCキーワードを使用して、ID列で降順で並べ替えます。



3番目のテーブルは、いくつかの並べ替えフィールドを使用します。まず、部門別の並べ替えです。同じ部門のフィールドで最初の演算子が等しい場合、2番目のソート条件が適用されます。私たちの場合、これは給与です。



とても簡単です。複数の並べ替え条件を設定できるため、出力リストをよりインテリジェントに並べ替えることができます。

グループ化

SQLでは、Group by句は、特定のグループのデータベースから取得されたデータを収集します。グループ化により、すべてのデータが論理セットに分割されるため、統計計算を各グループで個別に実行できます。



この演算子は、1つ以上の列による選択の結果を組み合わせるために使用されます。グループ化した後、列で使用される値ごとに1つのエントリのみがあります。



SQL Group byステートメントの使用は、集約関数の使用およびSQLHavingステートメントと密接に関連しています。 SQLの集約関数は、一連の列値に対して単一の値を返す関数です。例：COUNT（）、MIN（）、MAX（）、AVG（）、SUM（）



構文：



SELECT column_name（s）

FROM table_name

WHERE 条件

GROUP BY COLUMN_NAME（S）

BY ORDER COLUMN_NAME（S）;



グループ化は、SELECTクエリの条件付きWHERE句の後に表示されます。オプションで、ORDERBYを使用して出力値を並べ替えることができます。 したがって、前の例の表に基づいて、各部門の従業員の最大給与を見つける必要があります。最終的なサンプルには、部門の名前と最高給与を含める必要があります。 解決策1（グループ化を使用しない）：

SELECT DISTINCT
    ie.department
    ie.slary
    FROM itx_employee ie
    WHERE ie.salary = (
             SELECT
             max(ie1.salary)
             FROM itx_employee ie1
             WHERE ie.department = ie1.department
             )

解決策2（グループ化を使用）：

SELECT
department,
max(salary)
FROM itx_employee
GROUP BY department

最初の例では、グループ化を使用せずに、副選択を使用して問題を解決します。1つの選択に2番目を入れます。2番目のソリューションでは、グループ化を使用します。



2番目の例は、最初の例と同じ機能を実行しますが、短くて読みやすくなっています。



Group byの仕組み：最初に2つの部門をqaグループとdevグループに分割します。それから彼はそれらのそれぞれの最大給与を探します。

持っている

持つことはフィルタリングツールです。集計機能を実行した結果を示します。持つ句は、WHEREを使用できないSQLで使用されます。



WHERE句で行をフィルタリングするための述語が定義されている場合、グループ化の後に「Having」を使用して、集計関数の値でグループをフィルタリングする論理述語を定義します。この句は、行グループの集計関数を使用して取得した値をテストするために必要です。



構文：



SELECT column_name（s）

FROM table_name

WHERE condition

GROUP BY column_name（s）

HAVING condition



最初に、平均給与が4000を超える部門を表示します。次に、フィルタリングを使用して最大給与を表示します。



解決策1（GROUP BYおよびHAVINGを使用しない）：

SELECT DISTINCT
ie.department AS "DEPARTMENT",
(
     (SELECT
     AVG(ie1.salary)
     FROM itx_employee ie1
     WHERE ie1.department = ie.department)
) AS "AVG SALARY"

FROM itx_employee ie
where (SELECT
     AVG(ie1.salary)
     FROM itx_employee ie1
     WHERE ie1.department = ie.department) > 4000

解決策2（GROUP BYおよびHAVINGを使用）：

SELECT
department, 
AVG(salary)

FROM itx_employee 
GROUP BY department
HAVING AVG(salary) > 4000

最初の例では、2つの副選択を使用し、1つは最大給与を検索し、もう1つは平均給与をフィルタリングします。2番目の例も、はるかに単純で簡潔になりました。

プランのリクエスト

リクエストに時間がかかり、メモリとディスクの大量のリソースを消費する場合がよくあります。クエリが長く非効率的に実行されている理由を理解するために、クエリプランを確認できます。



クエリプランは、クエリの意図された実行プランです。 DBMSがそれを実行する方法。 DBMSは、サブクエリ内で実行されるすべての操作を記述します。すべてを分析した後、クエリの弱点がどこにあるかを理解し、クエリプランを使用してそれらを最適化することができます。



OracleでSQLステートメントを実行すると、いわゆる「実行計画」が取得されます。このクエリ実行計画は、実行中のSQLステートメントに従ってOracleがデータをフェッチする方法の説明です。プランは、ステップの順序とそれらの間の関係を含むツリーです。



クエリの推定実行計画を取得できるツールには、Toad、SQL Navigator、PL / SQL Developerなどがあります。これらのツールは、クエリのリソース消費を示すいくつかの指標を提供します。その主なものは、コスト-実行コストとカーディナリティ（または行）-カーディナリティ（または数量）です。行）。



これらのインジケーターの値が高いほど、クエリの効率は低下します。



以下に、クエリプランの分析を示します。最初のソリューションは副選択を使用し、2番目のソリューションはグループ化を使用します。最初のソリューションは22行を処理し、2番目のソリューションは15行を処理したことに注意してください。



クエリプラン分析：







2つの副選択を使用する別のクエリプラン分析：





この例は、SQLツールの非効率的な使用の変形として提示されているため、クエリで使用することはお勧めしません。



上記のすべての機能により、クエリを作成する際の作業が楽になり、コードの品質と読みやすさが向上します。

パート2：ウィンドウ機能

ウィンドウ関数は、Microsoft SQL Server 2005にまでさかのぼります。これらは、Select句内の特定の範囲の行に対して計算を実行します。要するに、「ウィンドウ」は、計算が行われる一連の行です。 「ウィンドウ」を使用すると、データを削減してより適切に処理できます。この機能を使用すると、データセット全体をウィンドウに分割できます。



ウィンドウ処理には大きな利点があります。計算用のデータセットを作成する必要はありません。これにより、セットのすべての行を一意のIDで保存できます。ウィンドウ関数の結果は、もう1つのフィールドの結果の選択に追加されます。



構文：



SELECT column_name（s）

集計関数（計算される列）

OVER（[ PARTITION BY列からグループへ]

FROM table_name

[ ORDERBY列からソート]

[グループ内の行を制限するためのROWSまたはRANGE式]）



OVER PARTITION BYは、ウィンドウサイズを設定するためのプロパティです。ここでは、追加情報を指定したり、サービスコマンドを指定したりできます。たとえば、行番号を追加できます。ウィンドウ関数の構文は、列の選択にぴったり合います。



例を挙げてすべてを見てみましょう。別の部門がテーブルに追加され、テーブルに15行が追加されました。私たちは、従業員、その給与、および組織の最大給与の引き出しを試みます。





最初のフィールドでは名前を取り、2番目のフィールドでは給与を取ります。次に、（）上でウィンドウ関数を使用します..。 「ウィンドウ」のサイズが示されていないため、組織全体で最大の給与を取得するために使用します。空の括弧付きのオーバー（）は、選択全体に適用されます。したがって、どこでも最大給与は10,000です。ウィンドウ関数のアクションの結果が各行に追加されます。



クエリの4行目からウィンドウ関数の言及を削除すると、max（salary）のみが残り、リクエストは機能しません。最大給与は単純に計算できませんでした。データは1行ずつ処理されるため、max（salary）を呼び出す時点では、現在の行には1つの番号しかありません。現在の従業員。ここで、ウィンドウ機能の利点を確認できます。通話時には、ウィンドウ全体と利用可能なすべてのデータで機能します。



各部門の最大給与を表示する必要がある別の例を見てみましょう







。実際、「ウィンドウ」のフレームを設定して、部門に分割しています。ランキングの例として部門を使用します。dev、qa、salesの3つの部門があります。



「ウィンドウ」は、各部門の最大給与を検索します。選択の結果、最初にdev、次にqa、次にsalesの最大給与が見つかったことがわかります。前述のように、ウィンドウ関数の結果は、各行のフェッチ結果に書き込まれます。



前の例では、overの後の括弧は指定されていません。ここでは、ウィンドウのサイズを設定できるPARTITIONBYを使用しました。ここでは、いくつかの追加情報を指定したり、行番号などのサービスコマンドを送信したりできます。

結論

SQLは、一見したほど単純ではありません。上記のすべてがウィンドウ機能の基本機能です。彼らの助けを借りて、あなたは私たちの要求を「単純化」することができます。ただし、それらにはさらに多くの可能性が隠されています。クエリに機能を追加するために組み合わせることができるユーティリティ演算子（ROWSやRANGEなど）があります。



この投稿がこのトピックに関心のあるすべての人に役立つことを願っています。