長い間postgresqlを使用してきた多くの開発者がウィンドウ関数を理解しておらず、それらがエリートにとってある種の特別な魔法であると考えて驚いた。 まあ、またはせいぜいStackOverflowを使用して、詳細を説明することなく「row_number（）OVER（）」のような「コピーアンドペースト」式です。 しかし、ウィンドウ関数は最も便利なPostgreSQL機能です。
それらを使用する方法を簡単な方法で説明しようとします。
まず、ウィンドウ関数は選択を変更せず、追加情報のみを追加することをすぐに明確にしたいと思います。 つまり 理解を容易にするために、postgresは最初にリクエスト全体（ソートと制限を除く）を実行し、その後ウィンドウ式のみを計算すると仮定できます。
構文は次のようなものです。
OVER
ウィンドウは、関数が処理する行のセットとこの処理の順序を記述する式です。
さらに、ウィンドウは空の括弧（）で簡単に設定できます。 ウィンドウはクエリ結果のすべての行です。
たとえば、この選択では、通常のフィールドID、ヘッダー、スコアに行番号が単に追加されます。
SELECT id, section, header, score, row_number() OVER () AS num FROM news; id | section | header | score | num ----+---------+-----------+-------+----- 1 | 2 | | 23 | 1 2 | 1 | | 6 | 2 3 | 4 | | 79 | 3 4 | 3 | | 36 | 4 5 | 2 | | 34 | 5 6 | 2 | | 95 | 6 7 | 4 | | 26 | 7 8 | 3 | | 36 | 8
ウィンドウ式にORDER BYを追加してから、処理順序を変更できます。
SELECT id, section, header, score, row_number() OVER (ORDER BY score DESC) AS rating FROM news ORDER BY id; id | section | header | score | rating ----+---------+-----------+-------+-------- 1 | 2 | | 23 | 7 2 | 1 | | 6 | 8 3 | 4 | | 79 | 2 4 | 3 | | 36 | 4 5 | 2 | | 34 | 5 6 | 2 | | 95 | 1 7 | 4 | | 26 | 6 8 | 3 | | 36 | 3
評価はすべて同じように計算されますが、予約全体の最後にORDER BY idも追加したことに注意してください。 つまり sgresは、結果をウィンドウ関数の結果と共にソートしただけで、1つの順序が他の順序に干渉することはまったくありません。
もっともっと。 単語PARTITION BY [expression]をウィンドウ式に追加できます。
たとえば、 row_number（）OVER（PARTITION BY section）の場合、計算は各グループで個別に行われます。
SELECT id, section, header, score, row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section FROM news ORDER BY section, rating_in_section; id | section | header | score | rating_in_section ----+---------+-----------+-------+------------------- 2 | 1 | | 6 | 1 6 | 2 | | 95 | 1 5 | 2 | | 34 | 2 1 | 2 | | 23 | 3 4 | 3 | | 36 | 1 8 | 3 | | 36 | 2 3 | 4 | | 79 | 1 7 | 4 | | 26 | 2
パーティションを指定しない場合、パーティションはリクエスト全体になります。
ここでは、非常に重要なニュアンスがあるため、使用できる機能について少し説明する必要があります。
関数として、いわば、マニュアルの真のウィンドウ関数を使用できます-これはrow_number（）、rank（）、lead（）などであり、次のような集約関数を使用できます：sum（）、count（）など そのため、これは重要です。集計関数の動作は少し異なります。ウィンドウでORDER BYが指定されていない場合、計算はパーティション全体に対して1回実行され、結果はすべての行に書き込まれます（すべてのパーティション行で同じ）。 ORDER BYが指定されている場合、各行のカウントはパーティションの先頭からこの行に移動します。
例を見てみましょう。 たとえば、特定の（真空で球形の）バランス補充表があります。
SELECT transaction_id, change FROM balance_change ORDER BY transaction_id; transaction_id | change ----------------+-------- 1 | 1.00 2 | -2.00 3 | 10.00 4 | -4.00 5 | 5.50
この場合、残高の残高がどのように変化したかを同時に調べたいと思います。
SELECT transaction_id, change, sum(change) OVER (ORDER BY transaction_id) as balance FROM balance_change ORDER BY transaction_id; transaction_id | change | balance ----------------+--------+--------- 1 | 1.00 | 1.00 2 | -2.00 | -1.00 3 | 10.00 | 9.00 4 | -4.00 | 5.00 5 | 5.50 | 10.50
つまり 各行のために別々のフレームで数えています。 この場合、フレームは、先頭から現在の行までの一連の行です（PARTITION BYの場合は、パーティションの先頭から）。
集計関数の合計でウィンドウでORDER BYを使用しない場合、合計金額を計算してすべての行に表示します。 つまり 各行のフレームは行のセット全体になります
パーティションの最初から最後まで。
SELECT transaction_id, change, sum(change) OVER () as result_balance FROM balance_change ORDER BY transaction_id; transaction_id | change | result_balance ----------------+--------+---------------- 1 | 1.00 | 10.50 2 | -2.00 | 10.50 3 | 10.00 | 10.50 4 | -4.00 | 10.50 5 | 5.50 | 10.50
ウィンドウ関数として使用する場合の集約関数の機能は次のとおりです。 私の意見では、これはSQL標準のかなり奇妙で直感的に明らかな瞬間です。
一度に複数のウィンドウ関数を使用できますが、それらは互いに干渉しないので、そこに記述しません。
SELECT transaction_id, change, sum(change) OVER (ORDER BY transaction_id) as balance, sum(change) OVER () as result_balance, round( 100.0 * sum(change) OVER (ORDER BY transaction_id) / sum(change) OVER (), 2 ) AS percent_of_result, count(*) OVER () as transactions_count FROM balance_change ORDER BY transaction_id; transaction_id | change | balance | result_balance | percent_of_result | transactions_count ----------------+--------+---------+----------------+-------------------+-------------------- 1 | 1.00 | 1.00 | 10.50 | 9.52 | 5 2 | -2.00 | -1.00 | 10.50 | -9.52 | 5 3 | 10.00 | 9.00 | 10.50 | 85.71 | 5 4 | -4.00 | 5.00 | 10.50 | 47.62 | 5 5 | 5.50 | 10.50 | 10.50 | 100.00 | 5
OVERの後に多くの同一の式がある場合、それらに名前を付けて、WINDOWキーワードを使用して別々にレンダリングし、コードの重複を回避できます。 マニュアルの例を次に示します。
SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
ここで、 OVERという単語の後にwが括弧なしで表示されます。
ウィンドウ関数はすべてのフィルタリングとグループ化の後に実行されるため、ウィンドウ関数の結果はWHEREを使用したクエリでフィルタリングできません。 何が起こったのか。 したがって、たとえば、各グループの上位5つのニュースアイテムを選択するには、サブクエリを使用する必要があります。
SELECT * FROM ( SELECT id, section, header, score, row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section FROM news ORDER BY section, rating_in_section ) counted_news WHERE rating_in_section <= 5;
統合する別の例。 row_number（）の他に、いくつかの関数があります。 たとえば、フレームの最終行の前の行を検索するラグ。 たとえば、ニュースがランキングの前のポイントよりどれだけ遅れているかを見つけることができます。
SELECT id, section, header, score, row_number() OVER w AS rating, lag(score) OVER w - score AS score_lag FROM news WINDOW w AS (ORDER BY score DESC) ORDER BY score desc; id | section | header | score | rating | score_lag ----+---------+-----------+-------+--------+----------- 6 | 2 | | 95 | 1 | 3 | 4 | | 79 | 2 | 16 8 | 3 | | 36 | 3 | 43 4 | 3 | | 36 | 4 | 0 5 | 2 | | 34 | 5 | 2 7 | 4 | | 26 | 6 | 8 1 | 2 | | 23 | 7 | 3 2 | 1 | | 6 | 8 | 17
ウィンドウ関数を使用することが特に便利な場合は、コメントに例を追加してください。 そして、もしあれば、それらにどのような問題が発生する可能性があります。
Zinc Prod開発ポッドキャストを購読してください。ここでは、データベース、プログラミング言語、その他すべてについて議論しています。