●2009年5月22日(金)
@IT会議室 --- 存在肯定命題
存在肯定命題は、見かけることが多いですねぇ

分析関数とmodel句で扱ったのは、正解だったか :-)

●2009年5月24日(日)
CodeZineにOracleの階層問い合わせの記事の第1段を入稿しました。
私が階層問い合わせのSQLを考える時の、脳内のイメージを画像化してみました。

次は、階層問い合わせの記事の第2段を書こう。

準備として、脳内イメージ力強化のために、PL/SQLで階層問い合わせを作ってみました ;-)
PL/SQLでは、内部プロシージャと内部ファンクションは使えるが、内部パッケージは使えないようだ・・・
PL/SQL5 階層問い合わせを模倣(nocycle対応)

●2009年5月25日(月)
正規表現の入門2 難しいメタ文字
CodeZineで公開していただきました。

正規表現の入門3は、6月に、Regex Cookbookを読んで、
正規表現について勉強してから、執筆しようと思います。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PostgreSQL Conference 2009 Japan - JPUG 10th Anniversary Conference
講演者に、PostgreSQL8.4新機能 window関数の使用例
というテーマで応募してしまった。
もし、講演させていただくことになったら、
シャレでOracle ACEシャツを着て行きたい ;-)

●2009年5月26日(火)
OTNJ --- 紐づく子供がいたら、自分と子孫を、子供の値で埋める
枝切りは、Not述語の使いどころな気がしてきました。
ドモルガンの法則で変形しないほうが、読みやすそうですね。

●2009年5月28日(木)
@IT会議室ビューワをupdateしました。
htmlから欲しいデータを抽出して加工するのは、正規表現の勉強に向いてる気がしてきました。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
MySQLでOracleのSQLを模倣1　(集合演算編)

CodeZineで公開していただきました。
SQLの集合演算における、私の脳内のイメージを画像化してます。

●2009年5月29日(金)
I reduced InlineView ;-)
Xだけなら、Xを1行にまとめて表示
X以外があれば、X以外を表示

●2009年5月31日(日)
枝切り条件でsys_connect_by_path関数を使う方法
といっても、インラインビューの中では枝切りしてません ;-)

枝切りの自由度を上げるには、PL/SQLを使うしかなさそうですね
PL/SQL5 階層問い合わせを模倣(nocycle対応)

create table EdakiriT(ID,nextID) as
select 1, 2 from dual union
select 2, 3 from dual union
select 3, 4 from dual union
select 4, 5 from dual union
select 5, 6 from dual union
select 6, 7 from dual union
select 7, 8 from dual union
select 1, 9 from dual union
select 9,10 from dual;

col path for a20

select ID,nextID,Level,path
from (select ID,nextID,sys_connect_by_path(to_char(ID),',') as path
        from EdakiriT
      start with ID = 1
      connect by prior nextID = ID)
start with ID = 1
connect by prior nextID = ID
       and 0 in(instr(path,'2'),instr(path,'4'));

ID  nextID  Level  path
--  ------  -----  ------
 1       2      1  ,1
 2       3      2  ,1,2
 3       4      3  ,1,2,3
 1       9      1  ,1
 9      10      2  ,1,9

●2009年6月1日(月)
How to show a row has been "removed" from a table --- I used OLAP  B-)
1行前の、Count(*) over(order by ソートキー)の結果を、
window指定を使わずに、Lag関数で取ろうとすると、インラインビューが一つ増えてしまいます。

●2009年6月2日(火)
英語 How to insert 1 to 10 number excluding 6 and 8 ? --- I like sys.odciNumberList ;-)
英語 I used Least,greatest and model clause

●2009年6月7日(日)
英語 Evaluate Expression from PL/SQL without passing it SQL engine

●2009年6月8日(月)
このカンファレンスは伝説になる
伝説のカンファレンスを体感してきた。

勉強会カンファレンス2009に参加してきた感想とか話した感想とか感じたこととか。
SQLのcase式や、手続き型言語の多分岐のフローにおいて、
条件のきつい方から先に書くのは、基本的な書き方でして、
さすがは、勉強会カンファレンスの開催者だと思ってしまった :-)

case when プレス then 値
     when ビアバッシュ不参加 then 値
     when U-20 then 値
     else 値 end

●2009年6月9日(火)
CodeZineにOracleの階層問い合わせ2を入稿しました。

次は、partitioned Outer Joinか、
Oracleの階層問い合わせ3にしよう。

●2009年6月10日(水)
OTNJ --- 脳内で木をイメージ
CodeZineで執筆したので木の脳内イメージ力が強化されました :-)

英語 START WITH causing no rows to return

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PostgreSQL8.4の正式リリースを待ちつつ、
分析関数の衝撃(PostgreSQL編)の準備として、
ネタをまとめつつ、htmlで雛形を作成しようと思います。
正式リリースされたら、再帰withでもいろいろ実験したいですねぇ

●2009年6月11日(木)
クラスカルのアルゴリズム - naoyaのはてなダイアリー
最短距離問題ならOracleの階層問い合わせで解けるのですが、
こういった、一度訪問したノードに戻るタイプだと無理っぽいですねぇ
素朴なアルゴリズムで、Javaで解いてみました。

Javaアルゴリズムパズル 3-10 最小全域木問題

●2009年6月12日(金)
DB2 SQLパズル

Oracleの階層問い合わせの修行として、
DB2 expressをインストールして、再帰Withで
Oracleの階層問い合わせと同じ結果を取得するSQLを作ってます。

Oracleの階層問い合わせと再帰withを、比較してみました。

■■■行の出力順序(厳密には、Order byをつけないと保証されないと思いますが)■■■
再帰withだと幅優先探索の順序で行を返して、
Oracleの階層問い合わせだと深さ優先探索の順序で行を返すようですね。

■■■葉を取得する■■■
Oracleの階層問い合わせでは、10g以降なら、connect_by_IsLeafがある。
9iなら、order siblings byで深さ優先探索の行きがけ順であることを保証させてから、
LeadでLevelがインクリメントされなかったら葉と判断
10-149 Oracle9iでconnect_by_isleafを模倣

再帰withではexists述語で調査
もしくは、order siblings byを模倣してから、LeadでLevelがインクリメントされなかったら葉と判断

木ごとに全ての葉のレベルが等しいなら、
木ごとにレベルが最大の行を取得でもいいですが

再帰with句04 Oracleのconnect_by_IsLeafを模倣

■■■経路で枝切り■■■
Oracleの階層問い合わせでは、sys_connect_by_pathで枝切りできない。
再帰withでは可(ただし、経路を全て記憶する必要あり)。

ナイト巡回問題

■■■行数で枝切り■■■
Oracleの階層問い合わせでは、不可(RowNumによる、CountStopが効かない)

select *
  from (select RowNum as rn from dict)
 where RowNum <= 10
start with rn=1
connect by priot rn=rn-1;

また、connect by句でストアドファンクションでパッケージ変数でセッションごとに有効な変数を使うのは、
動作保証されると思えません。
なので、表関数で階層問い合わせを実装する必要あり
10-288 木のIDと節のIDのセットで識別

再帰withでも不可 (DB2 V9.5 express とPostgreSQL8.4b2 で確認)
with X(ID,OyaID,Level) as(
select ID,OyaID,1
  from IDTable
 where OyaID is null
union all
select b.ID,b.OyaID,Level+1
  from X,IDTable b
 where X.ID = b.OyaID
   and (select count(*) from X) <= 10)
select * from X;

PostgreSQLであれば、Limit句が使えるらしい

■■■訪問済ノードへの訪問防止(無限ループ防止)■■■
Oracleの階層問い合わせでは、connect by nocycleで実装されている。
再帰withでは経路を全て記憶する必要あり。

PostgreSQLであれば、union allの代わりにunionを使う方法もあるようです。

Oracleの階層問い合わせでは訪問済ノードに再訪することができないです。
(dbms_random.value もしくは sys_guid を使えば一応できますが、動作保証されると思えません。)
8-43 Oracle9iでnocycleを模倣

最小全域木問題は、再帰withなら解けそうですね。

■■■脳内のイメージ■■■
私の脳内のイメージは、
Oracleの階層問い合わせでは、
１ start with句で根をイメージ
２ connect by句で木をイメージ(深さ優先探索の順序でノードをイメージ)

再帰withでは、
１ union allの前のselect文で根をイメージ
２ union allの後の内部結合で木をイメージ(幅優先探索の順序でノードをイメージ)

●2009年6月15日(月)
分析関数の衝撃(PostgreSQL編)の準備

PostgreSQLのCUIツールのPSQLでは、
EmEditorでSQLを作成してコピー
PSQLで右クリックでペーストして実行
といったことが可能(SQLが複数行でもOK)

Oracle(SQLPlus.exe)やMySQL(MySQL Command Line Client)でも可能(SQLが複数行でもOK)、
DB2 V9.5 expressのコマンド行プロセッサでは、SQLが複数行だとできないようだ(改行が区切り扱いのため)
DB2の設定変更で可能か調べてみようと思います。

PSQLのコマンド
\z
テーブル一覧を表示

\d TableName
テーブル構造を表示

●2009年6月16日(火)
US-OTN --- 場合分け付きの累計
model句の使いどころですねぇ
分析関数だけでやるとインラインビューが1つか2つ必要になりそう。

US-OTN --- range interval '1' month preceding

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Regex Cookbookが今日届きました。
正規表現の勉強をしよう。(英語の勉強もですが)

●2009年6月17日(水)
第1回　データベース管理システム「PostgreSQL」の特徴
［PostgreSQLウォッチ］第39回 PostgreSQL 8.4の新機能「再帰SQL」と最新情報
PostgreSQLの再帰withでは、経路をarrayで覚えれるらしい・・・

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
@IT会議室 --- 3テーブルのupdatableView
usingを使った内部結合にするか、mergeを使うかで悩んでしまった :-)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
昨日届いた、Regex Cookbookを、さっと読んで、英語力不足だと気づいた。
しかし、これが英語を学ぶモチベーションになるのです 8-)

●2009年6月18日(木)
US-OTNでたまに見かけるwith句の日付フォーマット対策用 alter session文

select to_date('2008-may-05','yyyy-mon-dd') from dual;
ORA-01843: not a valid month

alter session set NLS_DATE_LANGUAGE=ENGLISH;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PostgreSQL window関数メモ
分析関数の衝撃(PostgreSQL編)の執筆の準備を本格的に始めるとします。

PostgreSQLメモ
bool_and関数とbool_or関数に驚いた。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 I like Left Join better than Right Join

●2009年6月19日(金)
勉強会に勉強だけをしに来るヤツは素人
講師は学習効率高いですよ。

勉強会「分析関数とmodel句」では、
勉強会の題材の選択から始まって、予習復習,備忘録用の資料作成や、
リハーサルしたり、直前に自宅のSQL本や、Oracle社発行のSQLの資料を一通り読んだり
で大変でしたが :-)

OTNからリンクされてることに気づいた :-)
[OTN Japan ; Dev2DBA 5/30]

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PostgreSQLのメーリングリストで質問してみた。

件名 Lead関数で第3引数を指定すると文法エラーになる。

環境 8.4beta2 windows版

postgres=# select version();
                            version
----------------------------------------------------------------
 PostgreSQL 8.4beta2, compiled by Visual C++ build 1400, 32-bit

create table tes(
ID smallint,
SortKey smallint,
Val smallint);

insert into tes values(1,1,   1);
insert into tes values(1,2,   2);
insert into tes values(1,3,   4);
insert into tes values(1,4,   8);
insert into tes values(2,1,  16);
insert into tes values(2,2,  32);
insert into tes values(2,3,  64);
insert into tes values(2,4, 128);
insert into tes values(3,1,   1);
insert into tes values(3,3,   1);
insert into tes values(3,5,   2);
insert into tes values(4,1,null);
insert into tes values(4,2, 123);
insert into tes values(4,3,null);

--これは、エラーになりません
select ID,SortKey,Val,
Lead(Val,2) over(partition by ID order by SortKey)
  from tes;

--Lead関数で第3引数を指定すると下記のエラーが発生します。
select ID,SortKey,Val,
Lead(Val,2,999) over(partition by ID order by SortKey)
  from tes;

postgres=# select ID,SortKey,Val,
postgres-# Lead(Val,2,999) over(partition by ID order by SortKey)
postgres-#   from tes;
ERROR:  function lead(smallint, integer, integer) does not exist
LINE 2: Lead(Val,2,999) over(partition by ID order by SortKey)
        ^
HINT:  No function matches the given name and argument types. You might need to
add explicit type casts.
postgres=#

--これだとエラーになりません。
with w(ID,SortKey,Val) as(
select 1,1,   1 union all
select 1,2,   2 union all
select 1,3,   4 union all
select 1,4,   8 union all
select 2,1,  16 union all
select 2,2,  32 union all
select 2,3,  64 union all
select 2,4, 128 union all
select 3,1,   1 union all
select 3,3,   1 union all
select 3,5,   2 union all
select 4,1,null union all
select 4,2, 123 union all
select 4,3,null)
select ID,SortKey,Val,
Lead(Val,2,999) over(partition by ID order by SortKey)
  from w;

マニュアルを読む限り問題なさそうですが・・・
Window Functions

---------------------------------------------------------------------------
第1引数と第3引数の型を一致させるための、明示キャストが必要だったようです。

select ID,SortKey,Val,
Lead(Val,2,999::smallint) over(partition by ID order by SortKey)
  from tes;

●2009年6月20日(土)
PostgreSQLのマニュアル
SELECT
UPDATE

●2009年6月22日(月)
Oracleの階層問い合わせ1 (start with句,connect by句)
CodeZineで公開していただきました。
私がOracleの階層問い合わせを使う時の、脳内のイメージを公開してみました。

start with句で根をイメージして、
connect by句で枝をイメージしてます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
RDBMS解剖学

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Update Statement: Multi Level Inline view.  --- I like Merge B-)

日付型での4人旅人算は、あまり見ないですねぇ
US-OTN  --- I used sense of Tabibitosan

●2009年6月23日(火)
DB2 V9.5 の分析関数

DB2のV9.5では、First_Value関数とLast_Value関数の他に、
Lead関数とLag関数でもignore nullsが使えるらしい・・・
これは、Oracle ACEとして、Oracleで代用する方法を紹介せざるをえない :-)

US-OTNで他の方法がないか質問してみました
10-308 Lag関数とLead関数のignore nullsを模倣

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PostgreSQL8.4で代用する方法

with W(SortKey,Val) as (
select  1,   2 union all
select  2,null union all
select  5,   4 union all
select  9,null union all
select 11,   6 union all
select 12,null union all
select 14,null union all
select 16,   5 union all
select 17,null union all
select 20,   3 union all
select 21,null union all
select 22,   4)
select SortKey,Val,firKey,lasKey,
max(case SortKey when lasKey then Lag2  end) over(partition by lasKey) as Lag2,
max(case SortKey when firKey then Lead2 end) over(partition by firKey) as Lead2
from (select SortKey,Val,
      case when Val is not null
           then Lag (Val,2,999) over(partition by Val is null order by SortKey) end as Lag2,
      case when Val is not null
           then Lead(Val,2,999) over(partition by Val is null order by SortKey) end as Lead2,
      max(case when val is not null then SortKey end) over(order by SortKey) as firKey,
      min(case when val is not null then SortKey end) over(order by SortKey desc) as lasKey
       from W) a
order by SortKey;

 sortkey | val | firkey | laskey | lag2 | lead2
---------+-----+--------+--------+------+------
       1 |   2 |      1 |      1 |  999 |     6
       2 |     |      1 |      5 |  999 |     6
       5 |   4 |      5 |      5 |  999 |     5
       9 |     |      5 |     11 |    2 |     5
      11 |   6 |     11 |     11 |    2 |     3
      12 |     |     11 |     16 |    4 |     3
      14 |     |     11 |     16 |    4 |     3
      16 |   5 |     16 |     16 |    4 |     4
      17 |     |     16 |     20 |    6 |     4
      20 |   3 |     20 |     20 |    6 |   999
      21 |     |     20 |     22 |    5 |   999
      22 |   4 |     22 |     22 |    5 |   999

相関サブクエリでもいいかもしれませんねぇ
select SortKey,Val,
coalesce((select b.Val
            from W b
           where b.Val is not null
             and b.SortKey < a.SortKey
          order by b.SortKey desc Limit 1 offset 1),999) as Lag2,
coalesce((select b.Val
            from W b
           where b.Val is not null
             and b.SortKey > a.SortKey
          order by b.SortKey Limit 1 offset 1),999) as Lead2
from W a
order by SortKey;

●2009年6月23日(火)
minute based result --- I like model clause ;-)
Laurent Schneiderさんのmodel句のサンプルを応用してみた。

英語 It seems that Typ=185 is not defined 
dateリテラルとTimestampリテラルはマニュアルにのってますが、
Timeリテラルは隠し機能のようだ・・・

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
DB2 V9.5 express
での分析関数の実験結果

create table ignTes(
SortKey integer,
Val integer);

insert into ignTes values
( 1,   2),
( 2,null),
( 5,   4),
( 9,null),
(11,   6),
(12,null),
(14,null),
(16,   5),
(17,null),
(20,   3),
(21,null),
(22,   4);

select SortKey,Val,
Lag (Val,2,999,'IGNORE NULLS') over(order by SortKey) as Lag2,
Lead(Val,2,999,'IGNORE NULLS') over(order by SortKey) as Lead2
  from ignTes;

SortKey   Val  Lag2  Lead2
-------  ----  ----  -----
      1     2   999      6
      2  null   999      6
      5     4   999      5
      9  null     2      5
     11     6     2      3
     12  null     4      3
     14  null     4      3
     16     5     4      4
     17  null     6      4
     20     3     6    999
     21  null     5    999
     22     4     5    999

select SortKey,Val,
Lag (Val,2,cast(null as integer),'IGNORE NULLS') over(order by SortKey) as Lag2,
Lead(Val,2,cast(null as integer),'IGNORE NULLS') over(order by SortKey) as Lead2
  from ignTes;

SortKey   Val  Lag2  Lead2
-------  ----  ----  -----
      1     2  null      6
      2  null  null      6
      5     4  null      5
      9  null     2      5
     11     6     2      3
     12  null     4      3
     14  null     4      3
     16     5     4      4
     17  null     6      4
     20     3     6   null
     21  null     5   null
     22     4     5   null

**************************************************************************************
create table updTes(
id  integer,
Val integer);

insert into updTes values
(1,   1),
(1,   2),
(1,   4),
(1,   8),
(2,  16),
(2,  32),
(2,  64),
(2, 128),
(3,   1),
(3,   1),
(3,   2),
(4,null),
(4, 123),
(4,null);

update updTes
set Val = max(Val) over(partition by ID);

select * from updTes;

id  Val
--  ---
 1    8
 1    8
 1    8
 1    8
 2  128
 2  128
 2  128
 2  128
 3    2
 3    2
 3    2
 4  123
 4  123
 4  123

-- 分析関数の前に、where句で先にフィルタされる
update updTes
set Val = max(Val) over()
where Val < 10;

select * from updTes;

id  Val
--  ---
 1    8
 1    8
 1    8
 1    8
 2  128
 2  128
 2  128
 2  128
 3    8
 3    8
 3    8
 4  123
 4  123
 4  123

update updTes
set Val = row_number() over();

select * from updTes;

id  Val
--  ---
 1    1
 1    2
 1    3
 1    4
 2    5
 2    6
 2    7
 2    8
 3    9
 3   10
 3   11
 4   12
 4   13
 4   14

update updTes
set Val = sum(Val) over(order by Val);

id  Val
--  ---
1     1
1     3
1     6
1    10
2    15
2    21
2    28
2    36
3    45
3    55
3    66
4    78
4    91
4   105

●2009年6月24日(水)
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Window Functions

Note: The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for
lead, lag, first_value, last_value, and nth_value.
This is not implemented in PostgreSQL: the behavior is always the same as the standard's default,
namely RESPECT NULLS.

訳すと
標準SQLでは、Lead関数,Lag関数,first_value関数,Last_Value関数,nth_value関数
には、RESPECT NULLSとIGNORE NULLSが定義されてます。（PostgreSQLには実装されてませんが)
必ずデフォルトであるRESPECT NULLSとなります。

マニュアルに代用案まで書かれてると、面白いと思うのですが

But there is good news!!!
Alternative solution is here :-)

みたいな感じで

PostgreSQL版はnth_value関数を使って多分こうでしょうか

with W(ID,Val) as(
select 1,null union all
select 1, 999 union all
select 1, 888 union all
select 1, 777 union all
select 2,null union all
select 2,null union all
select 2, 666 union all
select 3,null union all
select 3,null union all
select 4, 555 union all
select 4, 444 union all
select 4, 333 union all
select 5,null union all
select 5, 222 union all
select 5, 222)
select ID,Val,resp_null,
max(igno_null) over(partition by ID) as igno_null
from (select ID,Val,
      nth_value(Val,3) over(partition by ID
      order by Val desc
      Rows between Unbounded Preceding and Unbounded Following) as resp_null,
      nth_value(Val,3) over(partition by ID,Val is null
      order by Val desc Rows between Unbounded Preceding and Unbounded Following) as igno_null
        from W) a
order by ID,Val desc;

 id | val | resp_null | igno_null
----+-----+-----------+----------
  1 |     |       888 |       777
  1 | 999 |       888 |       777
  1 | 888 |       888 |       777
  1 | 777 |       888 |       777
  2 |     |       666 |
  2 |     |       666 |
  2 | 666 |       666 |
  3 |     |           |
  3 |     |           |
  4 | 555 |       333 |       333
  4 | 444 |       333 |       333
  4 | 333 |       333 |       333
  5 |     |       222 |
  5 | 222 |       222 |
  5 | 222 |       222 |

Oracle版も作ってみました。
8-53 nth_value関数を模倣

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Window Functions

Likewise, the standard's FROM FIRST or FROM LAST option for nth_value is not implemented:
only the default FROM FIRST behavior is supported.
(You can achieve the result of FROM LAST by reversing the ORDER BY ordering.) 

訳
同様に、nth_value関数のFROM FIRSTとFROM LASTオプションは、実装されてません。
デフォルトのFROM FIRSTのみサポートします。(From Lastは、逆ソートで実現できます)

From Lastを試してみた :-)

with W(Val) as(
select null union all
select 999  union all
select 888  union all
select 777  union all
select 666  union all
select 555  union all
select 444  union all
select 333  union all
select 222)
select Val,
nth_value(Val,3)
over(order by Val desc nulls Last
Rows between Unbounded Preceding and Unbounded Following) as "Top3(ignull)",
nth_value(Val,3)
over(order by Val desc
Rows between Unbounded Preceding and Unbounded Following) as "Top3(resnull)",
nth_value(Val,3)
over(order by Val
Rows between Unbounded Preceding and Unbounded Following) as "Worst3"
  from W
order by Val desc;

 val | Top3(ignull) | Top3(resnull) | Worst3
-----+--------------+---------------+-------
     |          777 |           888 |    444
 999 |          777 |           888 |    444
 888 |          777 |           888 |    444
 777 |          777 |           888 |    444
 666 |          777 |           888 |    444
 555 |          777 |           888 |    444
 444 |          777 |           888 |    444
 333 |          777 |           888 |    444
 222 |          777 |           888 |    444

nth_value関数の厳密な仕様が知りたいですねぇ

PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Window Functions

Function   : nth_value(value any, nth integer)
Return Type: same type as value
Description: returns value evaluated at the row
             that is the nth row of the window frame (counting from 1); null if no such row

英文を読む限り
nth_value関数の第1引数でソートしたn番目なのか
order by句のウィンドウ内のsortKeyでソートしたn番目なのか
分かりません。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PostgreSQLのメーリングリストで教えていただきました。
over句のorder byでソートしたN番目(Row_Numberな順位)の行の値を返すのであって
over句でorder byを省略するとN番目が不定になるそうです。

-- 実行するたびに結果は異なる
with W(Val) as(
select 1 union all
select 2 union all
select 3)
select distinct nth_value(Val,3) over() as tes1
from (select * from W order by random()) a;

 tes1
------
    2

●2009年6月25日(木)
On The Peoplesoft Road
US-OTNの常連のgasparottoさんのブログ

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
8-10 first_Value関数を模倣(ignore nullsあり)
8-12 Last_Value関数を模倣(ignore nullsあり)

sinceなLast_Value(IgnoreNulls)と
untilなFirst_Value(IgnoreNulls)の
Oracle9iとPostgreSQL8.4での代用案

select SortKey,Val,
First_Value(Val ignore nulls) over(order by SortKey) as untilFirst,
First_Value(Val ignore nulls) over(
order by SortKey Rows between Current rows and Unbounded Following) as EntireFirst,
Last_Value(Val ignore nulls) over(
order by SortKey Rows between Current rows and Unbounded Following) as EntireLast,
Last_Value(Val ignore nulls) over(order by SortKey) as sinceLast
  from T

ウィンドウ全体の
first_Value(ignore nulls)とLast_Value(ignore nulls)ならソートキーの変更でよい
(case式を使って、Valがnullならソートキーをnullに変換する)

sinceなLast_Value(IgnoreNulls)および
untilなFirst_Value(IgnoreNulls)ならインラインビューが1つ必要
(インラインビューで、
Last_Valueならmaxで最大のソートキーを求め、
first_Valueならminで最小のソートキーを求める)

over句でのorder by句は、
・ウィンドウがどこまでかを決める
・Last_Value(first_Value)でのソートキー
の2役ですが、
Rows between Current row and Unbounded Followingを指定して、ウィンドウが必ずウィンドウ全体であれば
Last_Value(first_Value)でのソートキー
の1役なのです。

●2009年6月27日(土)
PostgreSQLパズルの9章
でOracleSQLパズルの9章と同じ問題を扱おう！！
と思ったけど半年ぐらいかかりそうだ

2問だけできました
PostgreSQLパズル 9-1  複数行を1行に変換
PostgreSQLパズル 9-2  和集合を求めて、スカラー問い合わせ

●2009年6月30日(月)
PostgreSQLでの存在肯定命題の論理積は、bool_or関数の使いどころですねぇ
PostgreSQLパズル 9-22  存在有無のブール値で論理演算

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQLをより賢く使うための12のヒント - ZDNet Japan
インスパイアされて作ってみました。
SQLを賢く使うための9のヒント

************************************************************
ヒント1 SQLの実行サイクルを短くする

手順1 EmEditorでSQLを書く
手順2 ctrl+A で全選択
手順3 ctrl+C でコピー
手順4 Alt+TABでSQLPlusWやSQLPlusやPSQLなどをアクティブにする
手順5 右クリックやCtrl+VやShift+Insertでペースト
手順1に戻る

************************************************************
ヒント2 SQLの各句の動きを脳内でイメージする

手順1 select文やupdate文などの評価順序を覚える
手順2 各句の動きを脳内でイメージ

************************************************************
ヒント3 SQL関連のコミュニティで切磋琢磨する

************************************************************
ヒント4 最もシンプルなSQLをしつこく考える

************************************************************
ヒント5 SQLの書籍を読む

************************************************************
ヒント6 複数のDBのSQLを学ぶ

そして、拡張機能を代用する方法を考えるのです。

************************************************************
ヒント7 パズル感覚で楽しむ

************************************************************
ヒント8 数学やアルゴリズムを学ぶ

************************************************************
ヒント9 時には、SQLではなく、手続き型言語で実装する

●2009年7月1日(月)
大晦日だけどボウリングのスコア計算するよ
インスパイアされて作ってみました。

create table score(idx,pins) as
select  1, 1 union
select  2, 4 union
select  3, 4 union
select  4, 5 union
select  5, 6 union
select  6, 4 union
select  7, 5 union
select  8, 5 union
select  9,10 union
select 10, 0 union
select 11, 1 union
select 12, 7 union
select 13, 3 union
select 14, 6 union
select 15, 4 union
select 16,10 union
select 17, 2 union
select 18, 8 union
select 19, 6;

with recursive Base(idx,nextidx,pins,next1,next2,maxIdx) as(
select idx,idx+case pins when 10 then 1 else 2 end,
pins,
Lead(pins,1,0) over(order by idx),
Lead(pins,2,0) over(order by idx),
max(idx) over()
  from score),
W(idx,nextidx,pins,next1,next2,maxIdx) as(
select idx,nextidx,pins,next1,next2,maxIdx
  from Base
 where idx=1
union all
select b.idx,b.nextidx,b.pins,b.next1,b.next2,b.maxIdx
  from W,Base b
 where W.nextidx=b.idx
   and b.idx != b.maxIdx)
select idx,pins,next1,next2,
case when pins = 10 then pins + next1 + next2
     when pins + next1 = 10 then pins + next1 + next2
     else pins + next1
end as score_of_frame
from W;

 idx | pins | next1 | next2 | score_of_frame
-----+------+-------+-------+----------------
   1 |    1 |     4 |     4 |              5
   3 |    4 |     5 |     6 |              9
   5 |    6 |     4 |     5 |             15
   7 |    5 |     5 |    10 |             20
   9 |   10 |     0 |     1 |             11
  10 |    0 |     1 |     7 |              1
  12 |    7 |     3 |     6 |             16
  14 |    6 |     4 |    10 |             20
  16 |   10 |     2 |     8 |             20
  17 |    2 |     8 |     6 |             16

************************************************************
Oracle版も作ってみました。
6-12 ボウリングのスコア計算

●2009年7月2日(木)
PostgreSQL8.4が正式リリースされましたね。
pgInstallerは、まだのようなので待つとします。
PostgreSQL: Windows packages

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 Regular Expressions like feature in 9i --- owa_pattern

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Oracleの階層問い合わせ2 (Level,sys_connect_by_path)
CodeZineで公開していただきました。

●2009年7月4日(土)
PostgreSQL8.4のarray_agg関数が強力な件
PostgreSQLの配列型で、包含関係をチェックするContains述語もどきが使える件

PostgreSQLメモ 配列の比較に関する実験
PostgreSQLパズル 9-9  差集合が空集合かチェック
PostgreSQLパズル 9-10  共通集合が空集合かチェック
PostgreSQLパズル 9-28  等しい集合が存在する、集合を求める
PostgreSQLパズル 9-56  集合が等しい組み合わせを求める

配列型は、最終的に親言語に渡さない形で、使いこなすといいかもしれない・・・
array_agg関数といったwindow関数でも使える便利な集合関数がありますし・・・

9.21. 行と配列の比較
［PostgreSQLウォッチ］第3回 ついに正式リリースされた7.4の新機能を見る：ITpro

@IT  新しい業界標準「SQL99」詳細解説
PostgreSQLで配列型のカラムを使ってみる - ぱせらんメモ

●2009年7月13日(月)
小田さんのブログで紹介されていたマンガでOracle