PostgreSQL で月末日の取得

db=# SELECT DATE_TRUNC('month', now() + '1 months') + '-1 days';

        ?column?
------------------------
 2010-02-28 00:00:00+09
(1 row)

翌月の月初日から 1 日引きます。

Oracle で簡単棒グラフ表示

Oracle の SQL で簡単に棒グラフを作る小ネタです。

表示制御のために COLUMN とかを使っていますが ポイントは LPAD 関数の使い方です。

SQL> COLUMN TNAME FORMAT A30
SQL> COLUMN BAR   FORMAT A30

SQL> SELECT tname, LPAD('*', LENGTH(tname), '*') AS bar FROM TAB;

TNAME                          BAR
------------------------------ ------------------------------
W_AB_T_OPTAR_POLOLOPL_CLOSED   ****************************
W_START                        *******
E_AB_T_PLANT                   ************
E_AB_T_EERCC_BODY              *****************
E_AB_T_EERCC_HEAD              *****************
E_AB_PLANT_DATA                ***************
E_AB_KUJIDE_BODY               ****************
E_AB_KUJIDE_HEAD               ****************
R_AB_T_OPTAR_CLOSED            *******************
R_AB_T_OPTAR_KAMOKU            *******************
R_AB_KK_DATA                   ************

このサンプルはテーブルの名前の長さという かなりどうでも良いものを棒グラフにしてますが (棒グラフ化しなくてもそのままですし)、 棒グラフ化することで 見えやすくなるものは結構あると思います。

PostgreSQL の DISTINCT ON

PostgreSQL には 通常の DISTINCT ではない DISTINCT ON というものがあります。

次のようなテーブルがあるとします。

db1=# SELECT * FROM distinct_test;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 1      |      4 |      2
 2      |      2 |      1
 2      |      2 |      2
 3      |     -1 |      1
 3      |     -1 |      2
(6 rows)

まずは通常の DISTINCT です。

db1=# SELECT DISTINCT field1, field2
      FROM distinct_test;

 field1 | field2
--------+--------
 1      |      4
 2      |      2
 3      |     -1
(3 rows)

この場合、列に指定した field1, field2 の順番でソートされます。

列の指定の順番を変えます。

db1=# SELECT DISTINCT field2, field1
      FROM distinct_test;

 field2 | field1
--------+--------
     -1 | 3
      2 | 2
      4 | 1
(3 rows)

今度は field2, field1 でソートされます。
これはまぁ、こういうものです。

DISTINCT ON を使ってみます。
DISTINCT ON (field1, field2) のカッコの中と 列の指定 "field1, field2, field3" が 違うことに注意してください。

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 2      |      2 |      1
 3      |     -1 |      1
(3 rows)

行数は DISTINCT で field1, field2 を指定したときと同じです。

つまり DISTINCT ON を使用すると カッコの中で指定した列で まとめられ、カッコの中で指定していない列(この例だと field3 )は 最初に見つかったものが出力されます。

「 DISTINCT でまとめたいけど、他の列も欲しい!」というときに便利ですね。 ただ、このままだと DISTINCT ON に指定していない列は どの値が出るのか保障されないため、ソートをかけてやる必要があります。

ORDER BY によるソートは次のようになります。

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
       FROM distinct_test
       ORDER BY field1;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 2      |      2 |      1
 3      |     -1 |      1
(3 rows)

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test
      ORDER BY field2;

ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test
      ORDER BY field3;

ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions

DISTINCT ON の先頭で指定されている field1 以外の 列では エラーになってしまいます。

ORDER BY でソートするには、 DISTINCT ON で指定した列(指定順)の後に、DISTINCT ON 以外の列を書く必要があります。

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
       FROM distinct_test
       ORDER BY field1, field2, field3 DESC;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      2
 2      |      2 |      2
 3      |     -1 |      2
(3 rows)

こうすることによって GROUP BY で MAX() を使ったのと 同じ値を取得すことができました。

nonstandard use of escape in a string literal

PostgreSQL で 次の WARNING が発生することがあります。

WARNING:  nonstandard use of escape in a string literal
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

これは SQL の中にバックスラッシュの文字リテラルが ある場合に起こるのですが HINT の通り 'E' を付けることで対応できます。

次のような SQL で発生します。
警告は出ますが、結果も取得できます。

db1=# SELECT REPLACE(field1, '\n', '\\n') FROM table1;
WARNING:  nonstandard use of escape in a string literal
LINE 1: SELECT REPLACE(field1, '\n', '\\n') FROM table1
                               ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING:  nonstandard use of \\ in a string literal
LINE 1: SELECT REPLACE(field1, '\n', '\\n') FROM table1
                                     ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'. 

                       replace
------------------------------------------------------
test "test!"\ntest "test!"\ntest "test!"\nこんにちは

HINT に従って 文字リテラルに 'E' を付けます。

db1=# SELECT REPLACE(field1, E'\n', E'\\n') FROM table1;

                       replace
------------------------------------------------------
test "test!"\ntest "test!"\ntest "test!"\nこんにちは

これで WARNING が出なくなりました。