水曜以外もどうでしょう

北海道発。食べ歩きの記録グルメ情報や北海道の見どころなどをメモ替わりに書いています。

PREV | PAGE-SELECT | NEXT

≫ EDIT

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
応援宜しくお願いします。ポチ↓↓
fC2ブログランキング にほんブログ村 グルメブログ 北海道食べ歩きへ 人気ブログランキングへ


| スポンサー広告 | --:-- | comments(-)| trackbacks(-)| TOP↑

≫ EDIT

postgresql操作メモ、ソート(order by)での空白の扱い

postgresqlのソート機能、order byについての実験。

テーブルをソートする場合、空のレコードの扱いが、
textとintegerでは違うみたい。

<結論としては、
(1)textは昇順で空白を先頭にソートする。
(2)textは降順で空白を末尾にソートする。
(3)integerは昇順で空白を末尾にソートする。
(4)integerは降順で空白を先頭にソートする。
ようだ。

textとintegerは逆なんだね。
これは注意が必要。
まずはその結果を確認。



以下の様なテーブルをソートしてみる。

Column | Type |
---------+---------+
id | integer |
t | text |
i | integer |

test=# select * from tbl order by sino;
id | t | i
----+---------+-----
1 | 15 | 10
2 | 2 |
3 | 5 | 20
4 | | 15
5 | 30.123 |



●テキストの空白は、「一番小さい」と判断される。

★テキストを昇順でソート

test=# select id,t from tbl order by t;
id | t
----+---------
4 |
1 | 15
2 | 2
5 | 30.123
3 | 5

★テキストを降順でソート

test=# select id,t from tbl order by t desc;
id | t
----+---------
3 | 5
5 | 30.123
2 | 2
1 | 15
4 |

●数値の空白は、「一番大きい」と判断される。

★数値を昇順でソート

test=# select id,i from tbl order by i;
id | t
----+--------
1 | 10
4 | 15
3 | 20
2 |
5 |

★数値を降順でソート

test=# select id,i from tbl order by i desc;
id | i
----+--------
2 |
5 |
3 | 20
4 | 15
1 | 10


空白の扱い、この仕様でも良い場合もあるが、
「空白」ということは、don't careということで、
無条件に末尾につけたいとも思う。

よって、(2)(3)はOKとして、
(1)(4)の条件で、空白のレコードを末尾につけたい。

これを実現するために、空白だったら末尾にソートされる様に最大値(または最小値)に置き換わるような関数を定義してみる。

ちなみに、関数はオーバーロードが可能なので、
同じ関数名で、引数の型違いを定義できる。

単純に、第一引数のチェックして、空なら第二引数の値に置き換える。

sql文だけでなく、制御構造や変数を使うには、PLpgSQLをを使うと良い。
PLpgSQLを使うには、

コマンドプロンプトで、
createlang plpgsql db名
を実行しておく。

例)
createlang plpgsql test

---------- 文字版 --------------------
CREATE OR REPLACE FUNCTION check_null(text, text)
/* 第一引数を評価してNULLなら,第2引数を返す */
RETURNS text
AS '
DECLARE
ret text;

BEGIN
IF $1 = '''' THEN /* 文字列が空 */
ret := $2;
ELSE
ret := $1;
END IF;
RETURN ret;
END;
' LANGUAGE PLpgSQL;

---------- 数値版 --------------------
CREATE OR REPLACE FUNCTION check_null(integer, integer)
/* 第一引数を評価してNULLなら,第2引数を返す */
RETURNS integer
AS '
DECLARE

ret integer;

BEGIN

IF $1 IS NULL THEN /* 文字列が空 */
ret := $2;
ELSE
ret := $1;
END IF;
RETURN ret;
END;
' LANGUAGE PLpgSQL;


(3)の条件で、降順ソートすると
t = 空白
のレコードを最後にもってくることができる。
check_null(t, '999999999')
は「tの値をみて、空白なら'999999999'とみなす」意味なので、
昇順なら'999999999'が最後になる。
※'999999999' は 「tの値域にくらべてすごく大きな値」の意味

test=# select id,t from tbl order by check_null(t, '999999999');
id | t
----+---------
1 | 15
2 | 2
5 | 30.123
3 | 5
4 |



(4)の条件で、降順ソートすると
i = 空白
のレコードを最後にもってくることができる。
check_null(i, 0)
は「iの値をみて、空白なら0とみなす」意味なので、
降順なら0が最後になる。


test=# select id,i from tbl order by check_null(i, 0) desc;
id | i
----+--------
3 | 20
4 | 15
1 | 10
2 |
5 |


これで、「空白」をふくむカラムでソートしたときに、
「空白」のあるレコードを最後に持ってくることができる。

次の問題は、
text型に格納しまった数値を、
数値としてソートしたい場合だ。

これはdbの設計ミスなのだが、
「最初はソートする予定もなく、text型で宣言したが、
 後後から数値としてソートしたくなった」
ため、困った実例だ。

具体的には、

id | t
----+---------
1 | 15
2 | 2
5 | 30.123
3 | 5
4 |



id | t
----+---------
2 | 2
3 | 5
1 | 15
5 | 30.123
4 |

文字(asciiコード)としてみると、2より、15の方が先(小さい)のね。
これを数値としてみて、2 < 15 としたいのだ。
また、小数を扱える様にもしたい。

この辺はまたの機会に。
スポンサーサイト
応援宜しくお願いします。ポチ↓↓
fC2ブログランキング にほんブログ村 グルメブログ 北海道食べ歩きへ 人気ブログランキングへ


| データベース | 21:00 | comments:0| trackbacks:0| TOP↑

COMMENT















非公開コメント

TRACKBACK URL

http://vmemo.blog36.fc2.com/tb.php/49-a92e96de

TRACKBACK

PREV | PAGE-SELECT | NEXT

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。