[pgsql-jp: 39901] Re: nth_value関数(パーティション切りあり)の結果がおかしい

[Hitoshi Harada]

原田です。

最後のORDER BY句をコメントアウトすると
 id | val | ?column? | igno_null
----+-----+----------+-----------
  1 | 777 | f        |       888
  1 | 999 | f        |       888
  1 | 888 | f        |       888
  1 |     | t        |
  2 | 666 | f        |
  2 |     | t        |
  2 |     | t        |
(7 rows)

という結果になります。
nth_valueはウィンドウフレーム内の指定された位置の値を返しますが、OVER句内に明示的なORDER
BYがない場合はその位置を決定する順序が不定です。現在の内部実装的にはPARTITION BY句でソートするので"id"と"val IS
NULL"で順序が決定しています。

nth_valueが値を返した後に最後のORDER BY句が実行されるので、見た目の結果に違和感が生まれやすいのですが、上記のようなロジックで動いています。



2009/06/24 19:47 に <postgresqlmailabcd ＠ mail.goo.ne.jp> さんは書きました:
> nth_value関数のignore nullsを代用しようとしたら、
> こんな現象に遭遇しました。
> 原因が分かる方がおられましたら、教えてください。
>
> select version();
>                            version
> ----------------------------------------------------------------
>  PostgreSQL 8.4beta2, compiled by Visual C++ build 1400, 32-bit
>
> -- nth_valueが正しい結果を返すSQL
> 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, 666)
> select ID,Val,
> nth_value(Val,3) over(partition by ID,Val is null) as igno_null
>  from W
> order by ID,Val desc;
>
>  id | val | igno_null
> ----+-----+-----------
>  1 |     |
>  1 | 999 |       777
>  1 | 888 |       777
>  1 | 777 |       777
>  2 |     |
>  2 | 666 |
>
> -- nth_valueが間違った結果を返すSQL
> 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 /* I added this row */
> select 2, 666)
> select ID,Val,
> nth_value(Val,3) over(partition by ID,Val is null) as igno_null
>  from W
> order by ID,Val desc;
>
>  id | val | igno_null
> ----+-----+-----------
>  1 |     |
>  1 | 999 |       888
>  1 | 888 |       888
>  1 | 777 |       888
>  2 |     |
>  2 |     |
>  2 | 666 |
>
> I added this rowの行を追加したら、結果が変わってしまいます。
> igno_nullは888ではなく、777が正しいはずです。
>
> Val is null
> を
> case when Val is null then 1 else 0 end
> に変更しても結果は888のままでした。
>
> **************************************************************
> 明智重蔵のブログ
> http://oraclesqlpuzzle.hp.infoseek.co.jp/blog.html
>
>



-- 
Hitoshi Harada