db=# SELECT DATE_TRUNC('month', now() + '1 months') + '-1 days';
?column?
------------------------
2010-02-28 00:00:00+09
(1 row)
翌月の月初日から 1 日引きます。
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 ではない 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() を使ったのと 同じ値を取得すことができました。
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 が出なくなりました。