■CALENDAR■
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31     
<<前月 2019年12月 次月>>
■LOGIN■
現在のモード: ゲストモード
USER ID:
USER PW:
■ADMIN■
ADMIN ID:
ADMIN PW:
■NEW ENTRIES■
■RECENT COMMENTS■
■RECENT TRACKBACK■
  • 吉本隆明の死去に寄せて - 反「反核」ということ
  • 2010 年の釣り - 2010~11 シーズン開幕! 肝和えの素(カワハギ)
  • 震災後の復興を考える
  • 夏本番ですが ・・・ 水の事故を防ぐために
  • 2011 年の釣り - 遅ればせながらシーズン開幕 東京湾のマゴチ
■CATEGORIES■
■ARCHIVES■
■PROFILE■
■POWERED BY■
BLOGN(ぶろぐん)
BLOGNPLUS(ぶろぐん+)
■OTHER■

PL/pgSQL で、第xN分位数を得る集約関数を定義する(その4)
 前回までで解説した、第xN分位数とジニ係数を求める関数を、実際に使ってみます。

 
 まず、次のような1フィールド5レコードのテーブルを用意します。

psql=> select * from sampleTable;
num
-----
2
4
1
0
3
(5 rows)
 上記のテーブルを対象に、次のような SQL を実行します。
 arraySorted() は、引数として与えられたデータを昇順に配列に格納してその配列を返す集約関数であり、percentile() は昇順ソート配列からN分位数を求める(通常)関数、同様に 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 リスト中で6つの arraySorted() が使われています。 それらに渡される引数は6回とも num であり、どの arraySorted() も同じ結果を返すことが自明です。
 この場合、SQL に記述されたとおりに arraySorted() が6回(状態遷移関数レベルだと5行×6回=30回)コールされるのだとすると、極めて冗長で無駄な処理となります。 恐らく PostgreSQL はこのコールを1回で済ますように最適化してくれる、と希望的に観測しましたが、確信は持てない。
 そこで、以下のような実験をしてみました。

 まず、集約関数 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() を1回だけ使用する、下記のような 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)
上記のとおり、状態遷移関数はテーブルの行数分(=5)呼び出されるので、5回のメッセージテキストが表示されます。

 次に、先の SQL 文を実行します。 今度は arraySorted() が6回使われていますから、もし最適化が行われていなければ、5行×6回=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 と同じく、表示されるメッセージテキストは5回だけ。 実行結果は正しく表示されていますから、見事に最適化されていることが判りました。 めでたし、めでたし。

 でも、これって常識?

<この項、おしまい>
| http://blog.wakita.cc/index.php?e=89 |
| サーバ・Linux::PostgreSQL | 01:38 PM | comments (0) | trackback (0) |










http://blog.wakita.cc/tb.php/89
PAGE TOP ↑