iakioの日記 RSSフィード

2009-02-11

配列を行に分解

| 17:12 | 配列を行に分解 - iakioの日記 を含むブックマーク はてなブックマーク - 配列を行に分解 - iakioの日記

カンマ区切りの文字列を行に分解することはできたけど、単に配列を行に分解することってできたっけ?

=> SELECT ARRAY[10,20,30];
  array
---------
 {10,20,30}
(1 row)
これを
----
 10
 20
 30
(3 rows)
こうしたい

うーん。generate_seriesだろうか。

=> SELECT a[i] FROM
   (SELECT s1.a, generate_series(array_lower(s1.a, 1), array_upper(s1.a, 1)) AS i FROM
   (SELECT ARRAY[10,20,30] AS a) AS s1) AS s2;
 a
----
 10
 20
 30
(3 rows)

できなくはないけど、、、

2/12追記

8.4にはunnest()という関数があるとコメントいただいたので試してみました。バージョンは8.4のRPM版(postgresql-server-8.4devel_20081229-1PGDG.rhel5.1)

=> SELECT * FROM unnest(ARRAY[10, 20, 30]);
 unnest
--------
     10
     20
     30
(3 rows)

これこれ。2次元以上の配列も再帰的に処理してくれる模様です。

=> SELECT * FROM unnest(ARRAY[ARRAY[10, 20], ARRAY[30, 40]]);
 unnest
--------
     10
     20
     30
     40
(4 rows)

pgsqlpgsql2009/02/11 18:418.4でunnest()が追加されてますが、以前のバージョンではそれしかないみたいです。

elfelf2009/02/17 19:55!!!!

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20090211

2008-12-31

大晦日だけどボウリングのスコア計算するよ

| 19:48 | 大晦日だけどボウリングのスコア計算するよ - iakioの日記 を含むブックマーク はてなブックマーク - 大晦日だけどボウリングのスコア計算するよ - iakioの日記

window functionがcommitされたので何かやってみようと思ったのですが、結局ボウリングしか思いつきませんでした。といってもスコア計算の部分は今までと一緒で、最後の表示部分にrow_number()とsum()を使っているだけですが。

with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      left join score s2 on (s2.idx = s1.idx + 1)
      left join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
),
sof(idx, pins1, pins2, pins3, score_of_frame) as (
    select idx
         , pins1, pins2, pins3
         , case when pins1 = 10 then pins1 + pins2 + pins3
                when pins1 + pins2 = 10 then pins1 + pins2 + pins3
                else pins1 + pins2
            end as score_of_frame
           from f
)
select row_number() over w as frame
     , pins1
     , pins2
     , case row_number() over w when 10 then pins3
       else null end as pins3
     , score_of_frame
     , sum(score_of_frame) over w
  from sof
window w as (order by idx)

実行

 frame | pins1 | pins2 | pins3 | score_of_frame | sum
-------+-------+-------+-------+----------------+-----
     1 |     1 |     4 |       |              5 |   5
     2 |     4 |     5 |       |              9 |  14
     3 |     6 |     4 |       |             15 |  29
     4 |     5 |     5 |       |             20 |  49
     5 |    10 |     0 |       |             11 |  60
     6 |     0 |     1 |       |              1 |  61
     7 |     7 |     3 |       |             16 |  77
     8 |     6 |     4 |       |             20 |  97
     9 |    10 |     2 |       |             20 | 117
    10 |     2 |     8 |     6 |             16 | 133
    11 |     6 |       |       |                | 133
(11 rows)

大分見やすくなったのではないでしょうか。

11フレームまで出ているのを気にしてはいけません。

参考:

AketiJyuuzouAketiJyuuzou2009/07/01 22:06http://oraclesqlpuzzle.hp.infoseek.co.jp/blog.html
2009年7月1日(月)のエントリでアレンジしてみました。

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20081231

2008-12-13

GiSTインデックスを使って日付の範囲を高速に検索

| 22:43 | GiSTインデックスを使って日付の範囲を高速に検索 - iakioの日記 を含むブックマーク はてなブックマーク - GiSTインデックスを使って日付の範囲を高速に検索 - iakioの日記

これは面白い。

  • 何年何月何日(date型)の前後何日(interval型)を表すfuzzydateという型を定義
  • fuzzydateの開始と終了のepochをbox型(!?)の開始と終点にして返すGeometricDate()関数を作る
  • で、fuzzydate型のカラムにGeometricDateの関数インデックスを張る

でもなんか似たの見たことある気がするなぁ。これかな。

あと、IPアドレスを検索するのに、ip4rというのがあるそうです。

追記:そうだ、ここで見たんだった

SusannahSusannah2011/08/21 06:30It's really great that poelpe are sharing this information.

zhvtefqszhvtefqs2011/08/24 02:482VJUHL , [url=http://ebteaocgoujd.com/]ebteaocgoujd[/url], [link=http://hubzldxbwcwl.com/]hubzldxbwcwl[/link], http://xuszjhpdkacb.com/

nlvmgvnlvmgv2011/08/26 01:47uJVjoG <a href="http://bnrjfalpthlk.com/">bnrjfalpthlk</a>

fgruyltfgruylt2011/08/31 19:07CO2x5r , [url=http://bzixislhkpup.com/]bzixislhkpup[/url], [link=http://dlcdtlvztlei.com/]dlcdtlvztlei[/link], http://fojskeyridim.com/

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20081213

2008-12-12

BETWEEN SYMMETRIC

| 22:09 | BETWEEN SYMMETRIC - iakioの日記 を含むブックマーク はてなブックマーク - BETWEEN SYMMETRIC - iakioの日記

ついでにもうひとつ、最近覚えたSQL

=> SELECT i FROM generate_series(1, 10) AS s(i) WHERE i BETWEEN 2 AND 3;
 i
---
 2
 3
(2 rows)

=> SELECT i FROM generate_series(1, 10) AS s(i) WHERE i BETWEEN 3 AND 2;
 i
---
(0 rows)

=> SELECT i FROM generate_series(1, 10) AS s(i) WHERE i BETWEEN SYMMETRIC 3 AND 2;
 i
---
 2
 3
(2 rows)

「a BETWEEN b AND c」のbとcの大小関係が逆でも賢く判断してくれるようです。gram.y見て発見して「そんなのドキュメントに載ってねーだろ」と思ったらちゃんと載ってました。

中では単に、「(a >= b AND a <= c) OR (a >= c AND a <= b)」をやっているようです。

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20081212

2008-12-11

IS NOT DISTINCT FROM

| 20:58 | IS NOT DISTINCT FROM - iakioの日記 を含むブックマーク はてなブックマーク - IS NOT DISTINCT FROM - iakioの日記

PostgreSQL NULL同士をイコールとして比較したい場合 - 130単位

お、IS NOT DISTINCT FROMなんて知らなかった。

=> \pset null <NULL>
Null display is "<NULL>".
=> select 1 = null;
 ?column?
----------
 <NULL>
(1 row)

=> select 1 IS NOT DISTINCT FROM null;
 ?column?
----------
 f
(1 row)

=> select 1 IS NOT DISTINCT FROM 1;
 ?column?
----------
 t
(1 row)

=> select 1 IS NOT DISTINCT FROM 2;
 ?column?
----------
 f
(1 row)

なるほど。

留意点としてあるのが、空白とNULLを比較した場合には、値が同じ(真)とは評価されないということ。これに対応するには、もう少し工夫が必要といえます。

PostgreSQL NULL同士をイコールとして比較したい場合 - 130単位

coalesce()を使ってNULLを空白に変換してから比較してみてはどうでしょうか。比較対象が文字列型であれば、

coalesce(a.pref, '') = coalesce(b.pref, '')

のようになると思います。

bdrjdaaruphbdrjdaaruph2011/04/23 07:46E1oYuX <a href="http://bhzbkjtrmhta.com/">bhzbkjtrmhta</a>

tmdeovptmdeovp2011/04/24 17:38oY2JZP , [url=http://vqhjzqfwaerj.com/]vqhjzqfwaerj[/url], [link=http://ajqfgstlblea.com/]ajqfgstlblea[/link], http://vgyniyubckjq.com/

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20081211