この記事では、2つのシステムでカスタム集計関数およびウィンドウ（Oracleの用語では分析）関数を作成する方法を説明します。 構文の違いと一般に拡張性のアプローチに違いがあるにもかかわらず、これらの関数のメカニズムは非常に似ています。 しかし、違いもあります。



確かに、組み込みの集計関数とウィンドウ関数は非常にまれです。 一般に、ウィンドウ関数は、何らかの理由で「高度な」SQLとして伝統的に分類されており、理解および習得が難しいと考えられています。 ここでは、DBMSで既に使用可能な機能を処理します。



なぜこの問題を掘り下げるのでしょうか？ いくつかの理由を挙げることができます。

• ウィンドウ関数は通常の集約関数よりも客観的に複雑ですが、それらに超越的なものはありません。 SQL開発者にとって絶対に不可欠なツールです。 また、独自のウィンドウ関数を作成することで、非常に単純な場合でも、標準のウィンドウ関数がどのように機能するかをよりよく理解できます。
  
  
• ウィンドウ関数と集計関数は、手続き型処理と宣言型ロジックを組み合わせるのに最適な方法です。 場合によっては、1つのSQLクエリで問題を解決するためのパラダイムのフレームワーク内にとどまり、複雑なアクションを実行することが判明します。
  
  
• はい、興味深いトピックであり、2つのシステムを比較するのはさらに興味深いトピックです。
  
  

トレーニングの例は、数値のタイプ（Oracleの数値）の標準的なavg関数の類似物である平均の計算です。 このような関数を作成し、集約モードおよびウィンドウモードでどのように機能するか、複数の並列プロセスで計算できるかどうかを確認します。 結論として、実生活の例を見てみましょう。

集計関数

単純なものから複雑なものに移行し、PostgreSQLとOracleを切り替えます。



まず、いくつかの一般的な考慮事項。 集計関数は、テーブルの各行に対して順番に呼び出され、最終的にすべてを処理します。 呼び出し間で、彼女は実行のコンテキストを定義する内部状態を維持する必要があります。 作業の最後に、彼女は最終値を返す必要があります。



したがって、4つのコンポーネントが必要です。

• 状態（コンテキスト）、
• 次の行を処理する機能、
• 最終結果を発行する機能、
• 前の3つの段落が集約関数を構成することの表示。

PostgreSQL

状態を保存するには、適切なデータ型を選択する必要があります。 標準を使用できますが、独自に定義することもできます。 平均を計算する関数の場合、値を個別に合計し、その数を個別に計算する必要があります。 したがって、2つのフィールドを持つ複合型を作成します。



CREATE TYPE average_state AS (

accum numeric,

qty numeric

);







次の値を処理する関数を定義します。 PostgreSQLでは、遷移関数と呼ばれます：



CREATE OR REPLACE FUNCTION average_transition (

state average_state,

val numeric

) RETURNS average_state AS $$

BEGIN

RAISE NOTICE '%(%) + %', state.accum, state.qty, val;

RETURN ROW(state.accum+val, state.qty+1)::average_state;

END;

$$ LANGUAGE plpgsql;







この関数は現在の状態と次の値を取得し、新しい状態を返します。値が加算され、数量に1が加算されます。



さらに、（RAISE NOTICE）関数パラメーターを出力します-これにより、作業がどのように行われるかを確認できます。 古き良きデバッグPRINT、あなたより良いものはありません。



次の機能は、最終値を返すことです。



CREATE OR REPLACE FUNCTION average_final (

state average_state

) RETURNS numeric AS $$

BEGIN

RAISE NOTICE '= %(%)', state.accum, state.qty;

RETURN CASE WHEN state.qty > 0 THEN

trim(trailing '0' from ( state.accum/state.qty )::text)::numeric

END;

END;

$$ LANGUAGE plpgsql;







関数は状態を取得し、結果の数値を返します。 これを行うには、累積金額を金額で割ります。 しかし、量がゼロの場合、NULLを返します（平均も返します）。



トリム機能を使用した接地は、正確な出力のためにのみ必要です。この方法では、さもなければ画面を混乱させ、知覚を妨げるような重要でないゼロを取り除きます。 このようなもの：



SELECT 1::numeric / 2::numeric;

?column?

------------------------

0.50000000000000000000

(1 row)







実際には、これらのトリックはもちろん必要ありません。



最後に、集計関数自体を決定します。 これを行うには、特別なCREATE AGGREGATEコマンドを使用します。



CREATE AGGREGATE average(numeric) (

sfunc = average_transition ,

stype = average_state ,

finalfunc = average_final ,

initcond = '(0,0)'

);







このコマンドは、状態のデータ型（stype）、2つの関数（sfuncおよびfinalfunc）、および初期状態値（initcond）を文字列定数の形式で示します。



試すことができます。 この記事のほとんどすべての例では、5行（1、2、3、4、5）の単純なテーブルを使用します。 テストデータを生成するために不可欠なツールであるgenerate_series関数を使用して、その場でテーブルを作成します。



SELECT average(gx) FROM generate_series(1,5) AS g(x);

NOTICE: 0(0) + 1

NOTICE: 1(1) + 2

NOTICE: 3(2) + 3

NOTICE: 6(3) + 4

NOTICE: 10(4) + 5

NOTICE: = 15(5)

average

---------

3

(1 row)







結果は正しく、関数の出力により進行状況を追跡できます。

• 状態は（0,0）に設定され、
• average_transition関数は5回連続して呼び出され、徐々に状態が変化し、
• 最終的に、average_final関数が呼び出され、3 = 15/5になりました。

別のチェックは空のセットに対して行われます：



SELECT average(gx) FROM generate_series(1,0) AS g(x);

NOTICE: = 0(0)

average

---------



(1 row)



オラクル

Oracleでは、すべての拡張性がデータカートリッジエンジンによって提供されます。 簡単に言えば、集計に必要なインターフェイスを実装するオブジェクトタイプを作成する必要があります。 コンテキストは、このオブジェクトの属性によって自然に表されます。



CREATE OR REPLACE TYPE AverageImpl AS OBJECT(

accum number,

qty number,

STATIC FUNCTION ODCIAggregateInitialize (actx IN OUT AverageImpl)

RETURN number,

MEMBER FUNCTION ODCIAggregateIterate (self IN OUT AverageImpl, val IN number

RETURN number,

MEMBER FUNCTION ODCIAggregateMerge (self IN OUT AverageImpl, ctx2 IN AverageImpl)

RETURN number,

MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT AverageImpl, returnValue OUT number, flags IN number)

RETURN number

);

/







ここでは、コンテキストの初期値は定数ではなく、個別の（静的、つまりオブジェクトの特定のインスタンスに関連付けられていない）関数ODCIAggregateInitializeによって決定されます。



各行に対して呼び出される関数はODCIAggregateIterateです。



結果はODCIAggregateTerminate関数によって返され、いくつかのフラグが渡されることに注意してください。これについては後で扱います。



インターフェイスには、ODCIAggregateMergeという別の必須機能が含まれています。 私たちはそれを定義します-どこへ行くべきですが、今のところはそれについての会話を延期します。



次に、リストされたメソッドの実装を使用してオブジェクト本体を作成します。



CREATE OR REPLACE TYPE BODY AverageImpl IS

STATIC FUNCTION ODCIAggregateInitialize (actx IN OUT AverageImpl)

RETURN number IS

BEGIN

actx := AverageImpl(0,0);

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateIterate (self IN OUT AverageImpl, val IN number)

RETURN number IS

BEGIN

dbms_output.put_line(self.accum||'('||self.qty||') + '||val);

self.accum := self.accum + val;

self.qty := self.qty + 1;

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateMerge (self IN OUT AverageImpl, ctx2 IN AverageImpl)

RETURN number IS

BEGIN

dbms_output.put_line(self.accum||'('||self.qty||') & '||ctx2.accum||'('||ctx2.qty||')');

self.accum := self.accum + ctx2.accum;

self.qty := self.qty + ctx2.qty;

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT AverageImpl, returnValue OUT number, flags IN number)

RETURN number IS

BEGIN

dbms_output.put_line('= '||self.accum||'('||self.qty||') flags:'||flags);

returnValue := CASE WHEN self.qty > 0 THEN self.accum / self.qty END;

RETURN ODCIConst.Success;

END;

END;

/







実装は、ほとんどの部分で、PostgreSQLに対して行ったすべてを繰り返しますが、構文はわずかに異なります。



戻り値の周りのトリムダンスは不​​要です。Oracleは、値を出力するときに、重要でないゼロを個別にカットします。



すべての関数は成功を示す値（ODCIConst.Success値）を返し、セマンティック値はOUTおよびIN OUTパラメーターを介して渡されることに注意してください（PL / SQLでは、PL / pgSQLのように実際の戻り値に関連付けられません）。 特に、ODCIAggregateTerminateを含むすべての関数は、オブジェクトの属性を変更できます。最初のパラメーター（self）で渡されるリンク。



集約関数の定義は次のとおりです。



CREATE OR REPLACE FUNCTION average(val number) RETURN number

AGGREGATE USING AverageImpl;

/







確認します。 値を生成するには、再帰クエリCONNECT BYレベルで慣用的な構成を使用します。



SELECT average(level) FROM dual CONNECT BY level <= 5;

AVERAGE(LEVEL)

--------------

3

0(0) + 1

1(1) + 2

3(2) + 3

6(3) + 4

10(4) + 5

= 15(5) flags:0







PostgreSQLのメッセージ出力が結果の前に表示され、Oracleの後に出力されることに注意してください。 これは、RAISE NOTICEが非同期で動作し、dbms_outputパケットが出力をバッファリングするためです。



ご覧のとおり、ODCIAggregateTerminate関数にnullフラグが渡されました。 つまり、コンテキストは不要になり、必要に応じて忘れることができます。



そして、空のセットを確認します。



SELECT average(rownum) FROM dual WHERE 1 = 0;

AVERAGE(ROWNUM)

---------------



= 0(0) flags:0



ウィンドウ関数：OVER（）

幸いなことに、私たちが書いた集約関数は、ウィンドウ（分析）として変更することなく機能します。



ウィンドウ関数は、選択を1つの（集約）行に折りたたまないという点で集約と異なりますが、行ごとに個別に計算されます。 構文的には、ウィンドウ関数呼び出しは、処理のための多くの行を定義するフレームの指示を伴うOVER構造の存在によって区別されます。 最も単純なケースでは、OVER（）のように記述されます。これは、関数がすべての行を処理する必要があることを意味します。 結果は、通常の集計関数を計算し、各サンプル行の反対側に結果（同じもの）を書き込んだかのようになります。



つまり、フレームは静的であり、すべての行にまたがっています。

 1. 2. 3. 4. 5。
 + --- + + --- + + --- + + --- --- + + --- +
 |  1 |  |  1 |  |  1 |  |  1 |  |  1 |
 |  2 |  |  2 |  |  2 |  |  2 |  |  2 |
 |  3 |  |  3 |  |  3 |  |  3 |  |  3 |
 |  4 |  |  4 |  |  4 |  |  4 |  |  4 |
 |  5 |  |  5 |  |  5 |  |  5 |  |  5 |
 + --- + + --- + + --- + + --- --- + + --- +

PostgreSQL

試してみましょう：



SELECT gx, average(gx) OVER ()

FROM generate_series(1,5) as g(x);

NOTICE: 0(0) + 1

NOTICE: 1(1) + 2

NOTICE: 3(2) + 3

NOTICE: 6(3) + 4

NOTICE: 10(4) + 5

NOTICE: = 15(5)

x | average

---+---------

1 | 3

2 | 3

3 | 3

4 | 3

5 | 3

(5 rows)







NOTICEの結論は、通常の集約関数を計算するとき、すべてが以前とまったく同じように発生することを示しています。 average_final関数から結果を受け取った後、PostgreSQLは各行に結果を配置します。

オラクル

SELECT average(level) OVER() average

FROM dual CONNECT BY level <= 5;

LEVEL AVERAGE

---------- -----------

1 3

2 3

3 3

4 3

5 3

0(0) + 1

1(1) + 2

3(2) + 3

6(3) + 4

10(4) + 5

= 15(5) flags:1

= 15(5) flags:1

= 15(5) flags:1

= 15(5) flags:1

= 15(5) flags:1

= 15(5) flags:0







意外と。 結果を1回計算する代わりに、OracleはODCIAggregateTerminate関数をN + 1回呼び出します。最初はフラグ1の各行に対して（コンテキストがまだ有効であることを意味します）、最後にもう一度呼び出します。 最後の呼び出しから取得した値は単に無視されます。



結論は次のとおりです。ODCIAggregateTerminate関数が計算的に複雑なロジックを使用する場合、同じジョブを数回実行しないことを考える必要があります。

ウィンドウ関数：OVER（PARTITION BY）

フレーム定義のPARTITION BY句は、通常のGROUP BY集計構造に似ています。 PARTITION BYを示すウィンドウ関数は行のグループごとに個別に計算され、結果は選択範囲の各行に割り当てられます。



この実施形態では、フレームも静的であるが、グループごとに異なる。 たとえば、2つのグループのラインが定義されている場合（1番目から2番目、3番目から5番目）、フレームは次のように想像できます。

 1. 2. 3. 4. 5。
 + --- + + --- +
 |  1 |  |  1 |
 |  2 |  |  2 |  + --- + + --- + + --- +
 + --- + + --- + |  3 |  |  3 |  |  3 |
                 |  4 |  |  4 |  |  4 |
                 |  5 |  |  5 |  |  5 |
                 + --- + + --- + + --- +

PostgreSQL

SELECT gx/3 part,

gx,

average(gx) OVER (PARTITION BY gx/3)

FROM generate_series(1,5) as g(x);

NOTICE: 0(0) + 1

NOTICE: 1(1) + 2

NOTICE: = 3(2)

NOTICE: 0(0) + 3

NOTICE: 3(1) + 4

NOTICE: 7(2) + 5

NOTICE: = 12(3)

part | x | average

------+---+---------

0 | 1 | 1.5

0 | 2 | 1.5

1 | 3 | 4

1 | 4 | 4

1 | 5 | 4

(5 rows)







計算は再び連続して行われますが、現在、別のグループの行に移動すると、状態は初期値（initcond）にリセットされます。

オラクル

SELECT trunc(level/3) part,

level,

average(level) OVER(PARTITION BY trunc(level/3)) average

FROM dual CONNECT BY level <= 5;

PART LEVEL AVERAGE

---------- ---------- ----------

0 2 1.5

0 1 1.5

1 4 4

1 5 4

1 3 4

0(0) + 2

2(1) + 1

= 3(2) flags:1

= 3(2) flags:1

0(0) + 4

4(1) + 5

9(2) + 3

= 12(3) flags:1

= 12(3) flags:1

= 12(3) flags:1

= 12(3) flags:0







興味深いことに、Oracleはラインを交換することにしました。 これは実装の詳細について何かを言うかもしれませんが、どんな場合でも権利を持っています。

ウィンドウ関数：OVER（ORDER BY）

ソート順を示すORDER BY句がフレームの定義に追加された場合、関数は昇順モードで動作を開始します（sum関数の場合、 累積合計で ）。



最初の行では、フレームはこの1行で構成されます。 第二-第一と第二から; 3番目の場合-1番目、2番目、3番目などから。 つまり、フレームには最初から現在までの行が含まれます。



実際、次のように書くことができます：OVER（ORDER BY ...未結合の前行と現在行の間の行）。ただし、この冗長性はデフォルトで暗示されているため、通常は省略されます。



そのため、フレームは静止しなくなります。頭が下に移動し、尾が所定の位置に残ります。

 1. 2. 3. 4. 5。
 + --- + + --- + + --- + + --- --- + + --- +
 |  1 |  |  1 |  |  1 |  |  1 |  |  1 |
 + --- + |  2 |  |  2 |  |  2 |  |  2 |
         + --- + |  3 |  |  3 |  |  3 |
                 + --- + |  4 |  |  4 |
                         + --- + |  5 |
                                 + --- +

PostgreSQL

SELECT gx, average(gx) OVER (ORDER BY gx)

FROM generate_series(1,5) as g(x);

NOTICE: 0(0) + 1

NOTICE: = 1(1)

NOTICE: 1(1) + 2

NOTICE: = 3(2)

NOTICE: 3(2) + 3

NOTICE: = 6(3)

NOTICE: 6(3) + 4

NOTICE: = 10(4)

NOTICE: 10(4) + 5

NOTICE: = 15(5)

x | average

---+---------

1 | 1

2 | 1.5

3 | 2

4 | 2.5

5 | 3

(5 rows)







ご覧のとおり、行は一度に1つずつコンテキストに追加されますが、各追加後にaverage_final関数が呼び出され、中間結果が得られます。

オラクル

SELECT level, average(level) OVER(ORDER BY level) average

FROM dual CONNECT BY level <= 5;

LEVEL AVERAGE

---------- ----------

1 1

2 1.5

3 2

4 2.5

5 3

0(0) + 1

= 1(1) flags:1

1(1) + 2

= 3(2) flags:1

3(2) + 3

= 6(3) flags:1

6(3) + 4

= 10(4) flags:1

10(4) + 5

= 15(5) flags:1

= 15(5) flags:0







今回は、両方のシステムが同じように機能します。

ウィンドウ関数：OVER（PARTITION BY ORDER BY）

PARTITION BY句とORDER BY句は組み合わせることができます。 次に、各行グループ内で、関数は上昇モードで動作し、グループからグループに移動すると、状態は初期状態にリセットされます。

 1. 2. 3. 4. 5。
 + --- + + --- +
 |  1 |  |  1 |
 + --- + |  2 |  + --- + + --- + + --- +
         + --- + |  3 |  |  3 |  |  3 |
                 + --- + |  4 |  |  4 |
                         + --- + |  5 |
                                 + --- +

PostgreSQL

SELECT gx/3 part,

gx,

average(gx) OVER (PARTITION BY gx/3 ORDER BY gx)

FROM generate_series(1,5) as g(x);

NOTICE: 0(0) + 1

NOTICE: = 1(1)

NOTICE: 1(1) + 2

NOTICE: = 3(2)

NOTICE: 0(0) + 3

NOTICE: = 3(1)

NOTICE: 3(1) + 4

NOTICE: = 7(2)

NOTICE: 7(2) + 5

NOTICE: = 12(3)

part | x | average

------+---+---------

0 | 1 | 1

0 | 2 | 1.5

1 | 3 | 3

1 | 4 | 3.5

1 | 5 | 4

(5 rows)



オラクル

SELECT trunc(level/3) part,

level,

average(level) OVER(PARTITION BY trunc(level/3) ORDER BY level) average

FROM dual CONNECT BY level <= 5;

PART LEVEL AVERAGE

---------- ---------- ----------

0 1 1

0 2 1.5

1 3 3

1 4 3.5

1 5 4

0(0) + 1

= 1(1) flags:1

1(1) + 2

= 3(2) flags:1

0(0) + 3

= 3(1) flags:1

3(1) + 4

= 7(2) flags:1

7(2) + 5

= 12(3) flags:1

= 12(3) flags:0



スライディングフレームを備えたウィンドウ機能

私たちが見たすべての例で、フレームは静的であるか、頭のみが移動していました（ORDER BY句を使用している場合）。 これにより、状態を順番に計算し、1行ずつコンテキストに追加することができました。



ただし、ウィンドウ関数のフレームは、テールもシフトするように設定することもできます。 この例では、これは移動平均の概念に対応します。 たとえば、OVER（ROWS BETWEEN 2 PRECEDING AND CURRENT ROW）を指定すると、現在の値と2つの前の値が結果の行ごとに平均化されます。

 1. 2. 3. 4. 5。
 + --- +
 |  |  + --- +
 |  |  |  |  + --- +
 |  1 |  |  1 |  |  1 |  + --- +
 + --- + |  2 |  |  2 |  |  2 |  + --- +
         + --- + |  3 |  |  3 |  |  3 |
                 + --- + |  4 |  |  4 |
                         + --- + |  5 |
                                 + --- +

この場合、窓関数を計算できますか？ 効果的ではありませんが、彼はできることがわかりました。 しかし、さらにコードを書くことで、状況を改善できます。

PostgreSQL

見てみましょう：



SELECT gx,

average(gx) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

FROM generate_series(1,5) as g(x);

NOTICE: 0(0) + 1

NOTICE: = 1(1)

NOTICE: 1(1) + 2

NOTICE: = 3(2)

NOTICE: 3(2) + 3

NOTICE: = 6(3)

NOTICE: 0(0) + 2

NOTICE: 2(1) + 3

NOTICE: 5(2) + 4

NOTICE: = 9(3)

NOTICE: 0(0) + 3

NOTICE: 3(1) + 4

NOTICE: 7(2) + 5

NOTICE: = 12(3)

x | average

---+---------

1 | 1

2 | 1.5

3 | 2

4 | 3

5 | 4

(5 rows)







3行目までは、テールが実際に移動しないため、すべてがうまくいきます。既存のコンテキストに次の値を追加するだけです。 しかし、コンテキストから値を削除する方法がわからないため、4行目と5行目では、すべてが完全に再カウントされ、毎回初期状態に戻ります。



そのため、次の値を追加する機能だけでなく、状態から値を削除する機能もあると便利です。 実際、そのような関数は作成できます：



CREATE OR REPLACE FUNCTION average_inverse (state average_state, val numeric)

RETURNS average_state AS $$

BEGIN

RAISE NOTICE '%(%) - %', state.accum, state.qty, val;

RETURN ROW(state.accum-val, state.qty-1)::average_state;

END;

$$ LANGUAGE plpgsql;









ウィンドウ関数で使用できるようにするには、次のように集約を再作成する必要があります。



DROP AGGREGATE average(numeric);

CREATE AGGREGATE average(numeric) (

--

sfunc = average_transition ,

stype = average_state ,

finalfunc = average_final ,

initcond = '(0,0)',

-- “”

msfunc = average_transition ,

minvfunc = average_inverse ,

mstype = average_state ,

mfinalfunc = average_final ,

minitcond = '(0,0)'

);







チェック：



SELECT gx,

average(gx) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

FROM generate_series(1,5) as g(x);

NOTICE: 0(0) + 1

NOTICE: = 1(1)

NOTICE: 1(1) + 2

NOTICE: = 3(2)

NOTICE: 3(2) + 3

NOTICE: = 6(3)

NOTICE: 6(3) - 1

NOTICE: 5(2) + 4

NOTICE: = 9(3)

NOTICE: 9(3) - 2

NOTICE: 7(2) + 5

NOTICE: = 12(3)

x | average

---+---------

1 | 1

2 | 1.5

3 | 2

4 | 3

5 | 4

(5 rows)







これですべてが整いました。4行目と5行目では、状態からテール値を削除し、新しい値を追加します。

オラクル

ここでも状況は似ています。 作成されたバージョンの分析関数は機能しますが、非効率的です。



SELECT level,

average(level) OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) average

FROM dual CONNECT BY level <= 5;

LEVEL AVERAGE

---------- ----------

1 1

2 1.5

3 2

4 3

5 4

0(0) + 1

= 1(1) flags:1

1(1) + 2

= 3(2) flags:1

3(2) + 3

= 6(3) flags:1

0(0) + 2

2(1) + 3

5(2) + 4

= 9(3) flags:1

0(0) + 3

3(1) + 4

7(2) + 5

= 12(3) flags:1

= 12(3) flags:0







コンテキストから値を削除する関数は、次のように定義されます。



MEMBER FUNCTION ODCIAggregateDelete (self IN OUT AverageImpl, val IN number)

RETURN number IS

BEGIN

dbms_output.put_line(self.accum||'('||self.qty||') - '||val);

self.accum := self.accum - val;

self.qty := self.qty - 1;

RETURN ODCIConst.Success;

END;









関数自体を再作成する必要はありません。 チェック：



SELECT level,

average(level) OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) average

FROM dual CONNECT BY level <= 5;

LEVEL AVERAGE

---------- ----------

1 1

2 1.5

3 2

4 3

5 4

0(0) + 1

= 1(1) flags:1

1(1) + 2

= 3(2) flags:1

3(2) + 3

= 6(3) flags:1

6(3) - 1

5(2) + 4

= 9(3) flags:1

9(3) - 2

7(2) + 5

= 12(3) flags:1

= 12(3) flags:0



平行度

PostgreSQLとOracle（Enterprise Edition）の両方は、集計関数を並行して計算できます。 さらに、各並列プロセスは作業の一部を実行し、中間状態を形成します。 次に、メインの調整プロセスがこれらのいくつかの状態を受け取り、それらを1つの最終状態に結合する必要があります。



これを行うには、もう1つのunion関数が必要です。 この場合、単純に合計と値の数を合計します。

PostgreSQL

機能は次のとおりです。



CREATE OR REPLACE FUNCTION average_combine (state1 average_state, state2 average_state)

RETURNS average_state AS $$

BEGIN

RAISE NOTICE '%(%) & %(%)', state1.accum, state1.qty, state2.accum, state2.qty;

RETURN ROW(state1.accum+state2.accum, state1.qty+state2.qty)::average_state;

END;

$$ LANGUAGE plpgsql;







また、average_transition関数からデバッグ出力を削除します。 並列実行では、5つの値を集計するのではなく、さらに多くの値を集計するため、これを行わないと、無駄な情報が大量に得られます。



出力を削除するため、手続き型言語を使用する必要もありません。関数を純粋なSQLで記述します。



CREATE OR REPLACE FUNCTION average_transition (state average_state, val numeric)

RETURNS average_state AS $$

SELECT ROW(state.accum+val, state.qty+1)::average_state;

$$ LANGUAGE sql;







新しい機能を考慮してユニットを再作成し、並列モードで安全に使用できることを示すことは残ります。



DROP AGGREGATE average(numeric);

CREATE AGGREGATE average(numeric) (

--

sfunc = average_transition ,

stype = average_state ,

finalfunc = average_final ,

combinefunc = average_combine ,

initcond = '(0,0)',

-- “”

msfunc = average_transition ,

minvfunc = average_inverse ,

mstype = average_state ,

mfinalfunc = average_final ,

minitcond = '(0,0)',

--

parallel = safe

);







次に、テーブルを作成し、データを入力します。 数千行で十分です。



CREATE TABLE t(n) AS SELECT generate_series(1,1000)::numeric;







デフォルト設定では、PostgreSQLはそのようなテーブルの並列プランを構築しません-それは小さすぎます-しかしそれを説得することは難しくありません：



SET parallel_setup_cost=0;

SET min_parallel_table_scan_size=0;







EXPLAIN(costs off) SELECT average(n) FROM t;

QUERY PLAN

------------------------------------------

Finalize Aggregate

-> Gather

Workers Planned: 2

-> Partial Aggregate

-> Parallel Seq Scan on t







リクエストに関しては、次のことがわかります。

• 部分集約（部分集約）を実行する2つの計画されたワークフロー、
• 情報を収集するノードを収集する
• 最後の状態の集約（Finalize Aggregate）。

チェック：



SELECT average(n) FROM t;

NOTICE: 0(0) & 281257(678)

NOTICE: 281257(678) & 127803(226)

NOTICE: 409060(904) & 91440(96)

NOTICE: = 500500(1000)

average

---------

500.5

(1 row)







average_combine関数が2回ではなく3回呼び出されるのはなぜですか？ 事実、PostgreSQLでは、調整プロセスもいくつかの作業を行います。 したがって、2つの作業プロセスが開始されましたが、実際には3つの作業プロセスが実行されました。 そのうちの1つは678行、他の226行と3行目-96行を処理しました（ただし、これらの番号は何も意味せず、異なる起動では異なる場合があります）。

オラクル

覚えていれば、ODCIAggregateMerge関数はOracleで必須であるため、非常に最初に作成しました。 文書では、この関数は並列操作だけでなく順次操作にも必要であると主張しています-理由を理解するのは難しいと思います（実際には、順次処理中にその実装を扱う必要はありませんでした）



あとは、並列操作に対して安全な関数を宣言するだけです。



CREATE OR REPLACE FUNCTION average(val number) RETURN number

PARALLEL_ENABLE

AGGREGATE USING AverageImpl;

/







テーブルを作成します。



CREATE TABLE t(n) AS SELECT to_number(level) FROM dual CONNECT BY level <= 1000;







Oracleを説得するのはPostgreSQLよりも簡単です-ヒントを書くだけです。 計画は次のとおりです（簡単にするために、出力は大幅に調整されています）。



EXPLAIN PLAN FOR SELECT /*+ PARALLEL(2) */ average(n) FROM t;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------

| Id | Operation |

---------------------------------

| 0 | SELECT STATEMENT |

| 1 | SORT AGGREGATE |

| 2 | PX COORDINATOR |

| 3 | PX SEND QC (RANDOM) |

| 4 | SORT AGGREGATE |

| 5 | PX BLOCK ITERATOR |

| 6 | TABLE ACCESS FULL |

---------------------------------







計画には以下も含まれます。

• 部分集約（4）、
• 部分的なコンテキストを受け取るコーディネーター（2）、
• コンテキストの結果の組み合わせ（1）。

SELECT /*+ PARALLEL(2) */ average(n) FROM t;

AVERAGE(N)

----------

500.5

0(0) & 216153(657)

216153(657) & 284347(343)

= 500500(1000) flags:0







Oracleでは、コーディネーターは部分集約に関与していません。 したがって、2つのコンテキストのみが結合され、同じ理由で、ODCIAggregateMerge関数の出力のみが表示されます。

ドキュメント

DBMSに既に含まれている集約関数やウィンドウ関数などのドキュメントへのリンクを提供するときが来ました。 そこには多くの興味深いものがあります。



PostgreSQL：

• カスタム集計関数
• 集約を作成
• 標準の集計関数とウィンドウ関数

Oracle：

• カスタム集計関数
• 集約関数インターフェース
• 標準の集計および分析関数

セントの丸めに関する例

そして、人生から約束された例。 この機能を思いついたのは、RAS（ロシアの会計規則）の下で働いて、会計用のレポートを作成する必要があったときです。



四捨五入が発生する最も単純なタスクは、総費用（たとえば100ルーブル）をある原則（たとえば等しく）に従って部門（たとえば3個）に分配することです。



WITH depts(name) AS (

VALUES ('A'), ('B'), ('C')

), report(dept,amount) AS (

SELECT name, 100.00 / count(*) OVER() FROM depts

)

SELECT dept, round(amount,2) FROM report;

dept | round

------+-------

A | 33.33

B | 33.33

C | 33.33

(3 rows)







このクエリは問題を示しています。金額は丸める必要がありますが、ペニーは失われます。 しかし、RASはこれを許しません。



問題はさまざまな方法で解決できますが、私の好みでは、最もエレガントな方法はウィンドウ関数です。ウィンドウ関数は増加モードで動作し、ペニーとのすべての戦いを取ります。



WITH depts(name) AS (

VALUES ('A'), ('B'), ('C')

), report(dept,amount) AS (

SELECT name, 100.00 / count(*) OVER() FROM depts

)

SELECT dept, round2(amount) OVER (ORDER BY dept) FROM report;

dept | round2

------+--------

A | 33.33

B | 33.34

C | 33.33

(3 rows)







このような関数の状態には、丸め誤差（r_error）と現在の丸められた値（量）が含まれます。 次の値を処理するための関数は丸め誤差を増やし、既に1ペニーの半分を超えている場合は、丸められた金額に1ペニーを追加します。



state.r_error := state.r_error + val - round(val,2);

state.amount := round(val,2) + round(state.r_error,2);

state.r_error := state.r_error - round(state.r_error,2);







そして、結果を生成する関数は、単に既製のstate.amountを返します。



私は関数の完全なコードを提供しません：既に与えられた例を使用して、それを書くことは難しくありません。



独自の集計関数またはウィンドウ関数を使用する興味深い例に出くわした場合は、コメントで共有してください。