Y’s Blog

2012,11,09, Friday PL/pgSQL で、第ｘＮ分位数を得る集約関数を定義する（その４） author : 脇田 洋志 　前回までで解説した、第ｘＮ分位数とジニ係数を求める関数を、実際に使ってみます。 　 　まず、次のような１フィールド５レコードのテーブルを用意します。 psql=> select * from sampleTable; num ----- 2 4 1 0 3 (5 rows) 　上記のテーブルを対象に、次のような SQL を実行します。 　arraySorted() は、引数として与えられたデータを昇順に配列に格納してその配列を返す集約関数であり、percentile() は昇順ソート配列からＮ分位数を求める（通常）関数、同様に gini() は昇順ソート配列からジニ係数を求める（通常）関数です。 psql=> select psql-> 　　percentile(arraySorted(num), 0. ) as "min", psql-> 　　percentile(arraySorted(num), 0.25) as "1/4", psql-> 　　percentile(arraySorted(num), 0.5 ) as "center", psql-> 　　percentile(arraySorted(num), 0.75) as "3/4", psql-> 　　percentile(arraySorted(num), 1. ) as "max", psql-> 　　gini(arraySorted(num)) psql-> from psql-> 　　sampleTable psql-> ; min | 1/4 |　center | 3/4 | max |　　　　　gini -----+-----+--------+-----+-----+------------------- 　　0 |　　1 | 　　　　2 |　　3 | 　　4 | 0.333333333333333 (1 row) 　上記のように、それぞれが正しく求められています。 　さて、上記の SQL を書いていて、ふと気になったことがありました。　この SQL では SELECT リスト中で６つの arraySorted() が使われています。　それらに渡される引数は６回とも num であり、どの arraySorted() も同じ結果を返すことが自明です。 　この場合、SQL に記述されたとおりに arraySorted() が６回（状態遷移関数レベルだと５行×６回＝30回）コールされるのだとすると、極めて冗長で無駄な処理となります。　恐らく PostgreSQL はこのコールを１回で済ますように最適化してくれる、と希望的に観測しましたが、確信は持てない。 　そこで、以下のような実験をしてみました。 　まず、集約関数 arraySorted() を構成する状態遷移関数 insArraySorted() 中で、raise 文を使ってメッセージテキストを表示するようにします。 -- insArraySorted() : 新規エレメントを昇順ソート配列中の適正な位置に挿入する CREATE FUNCTION insArraySorted(float8[], float8) RETURNS float8[] AS $BODY$ DECLARE 　　arrySrtd　　alias for $1;　　-- 昇順ソート配列 　　elemIns　　alias for $2;　　-- 挿入する新規エレメント 　　arrySrtdNew　　float8[]; 　　head　　integer; 　　tail　　integer; 　　size　　integer; 　　posIns　　integer; BEGIN 　　raise notice 'called !';　　-- ← この関数が呼ばれる都度、メッセージを表示させる 　　/* 新エレメントが NULL なら 現状のソート配列をそのまま返す */ 　　if elemIns is NULL then 　　　　return arrySrtd; 　　end if; ＜以下省略＞ 　そして、arraySorted() を１回だけ使用する、下記のような SQL を実行します。　すると結果は、 psql=> select psql-> 　　gini(arraySorted(num)) psql-> from psql-> 　　sampleTable psql-> ; NOTICE: called ! NOTICE: called ! NOTICE: called ! NOTICE: called ! NOTICE: called ! 　　　　　gini ------------------- 0.333333333333333 (1 row) 上記のとおり、状態遷移関数はテーブルの行数分（＝５）呼び出されるので、５回のメッセージテキストが表示されます。 　次に、先の SQL 文を実行します。　今度は arraySorted() が６回使われていますから、もし最適化が行われていなければ、５行×６回＝30回のメッセージテキストが表示されることになります。　その結果は、 psql=> select psql-> 　　percentile(arraySorted(num), 0. ) as "min", psql-> 　　percentile(arraySorted(num), 0.25) as "1/4", psql-> 　　percentile(arraySorted(num), 0.5 ) as "center", psql-> 　　percentile(arraySorted(num), 0.75) as "3/4", psql-> 　　percentile(arraySorted(num), 1. ) as "max", psql-> 　　gini(arraySorted(num)) psql-> from psql-> 　　sampleTable psql-> ; NOTICE: called ! NOTICE: called ! NOTICE: called ! NOTICE: called ! NOTICE: called ! min | 1/4 |　center | 3/4 | max |　　　　　gini -----+-----+--------+-----+-----+------------------- 　　0 |　　1 | 　　　　2 |　　3 | 　　4 | 0.333333333333333 (1 row) このとおり、前の SQL と同じく、表示されるメッセージテキストは５回だけ。　実行結果は正しく表示されていますから、見事に最適化されていることが判りました。　めでたし、めでたし。 　でも、これって常識？ ＜この項、おしまい＞ | http://blog.wakita.cc/index.php?e=89 | | サーバ・Linux::PostgreSQL | 01:38 PM | comments (0) | trackback (0) |

コメント コメントする name: email: URL: comments: Cookieに登録

この記事のトラックバックURL http://blog.wakita.cc/tb.php/89 トラックバック

PAGE TOP ↑