DB | 月初と月末の Timestamp

PostgreSQL で今日を基準に月初、月末の Timestamp を求める SQL。

-- 先月1日
select date_trunc('month', current_timestamp - interval '1 month');

-- 先月末日
select date_trunc('month', current_timestamp) - interval '1 day';

-- 今月1日
select date_trunc('month', current_timestamp);

-- 今月末日
select date_trunc('month', current_timestamp + interval '1 month') - interval '1 day';

ちなみに「interval」は省略可。

DB | 一意な部分インデックス

create table GIRL_FRIENDS (
    BOY_ID int,
    名前 text,
    本命 boolean,
    ...
);

上のような一夫多妻的な GIRL_FRIENDS テーブルがあったとして、ガールフレンドは何人いてもいいけど本命は一人だけという制約を課したい場合、今まではアプリケーション側だけで対応してたんだけど、部分インデックスに一意制約を加えれば PostgreSQL でもこの制約を課せることを今日知った。

この例の場合は次のようにインデックスを作る。

create unique index IDX_GIRL_FRIENDS on GIRL_FRIEND (BOY_ID) where 本命 = true;

DB | 範囲指定条件でのインデックス

PostgreSQL でのお話。単純な例に置き換える。

SELECT count(*) + 1 FROM 生徒 WHERE クラス = 'B組' AND 得点 > 80;

上の SQL 文は、テストの得点が 80 点の場合の B 組内での順位を求めるもの。

この生徒テーブルに (得点, クラス) の複合インデックスを張ってたんだけど、EXPLAIN 文で調べても上記の SQL 文でこのインデックスが使われてる気配がない。
おかしいな、PostgreSQL はバカだなあ、こんなの Oracle だったらテーブルスキャンすることもなくインデックスだけ見れば解決できそうじゃん。

でも、さっき自分が間違っていることに気付いた。

SELECT count(*) + 1 FROM 生徒 WHERE クラス = 'B組' AND 得点 = 80;

これならインデックスは使われる。が、実際の SQL は「得点 > 80」と、範囲を指定しているので、複合インデックスのキーは (クラス, 得点) という順になっていなければいけなかった。つまり、以下の SQL 文にインデックスを張るようなイメージで考えればよかった。

SELECT count(*) + 1 FROM 生徒 WHERE クラス = 'B組' ORDER BY 得点;
※この SQL は正しくないですよ。

複合インデックスの最初のキーに持ってくるのはカーディナリティが高いカラムであるべきという考えに縛られてしまってて気付かなかった。

さらに、指定する得点によってはインデックスが使われないケースも。そこで WHERE 句に条件を追加(得点は100点満点とする)。

SELECT count(*) + 1 FROM 生徒 WHERE クラス = 'B組' AND 得点 > 80 AND 得点 <= 100;

これで、EXPLAIN 文で調べていた限りではインデックスが使われるようになった。

DB | PostgreSQL to_char 関数の挙動

select '"' || to_char(1234, '00000') || '"';

上のSQLの結果は " 01234" になる(0 の前に半角スペースが入っている)。

select '"' || to_char(1234, 'FM00000') || '"';

このように FM 修飾子を付ければ、自分で想定していた "01234" という結果になった。

この修飾子については PostgreSQL ドキュメントの「日付/時刻書式用のテンプレートパターン修飾子」の表に載っているのだけど、数値型のテンプレートパターン修飾子は掲載されていないので気付かなかった(to_char の例には載っている)。

DB | PostgreSQL 8.4

PostgreSQL 8.4 の新機能 — Let's Postgres

もうリリースされて二ヶ月になるのか。全然知らなかった。

Window関数はすごい便利そう。ちゃんと見てないけど、Oracleのrank関数と同等の機能が使えるようになったってことでしょ。
row_number()の導入はようやく、といった感じ。

再帰SQLは難しい。使い道が分からない。

DB | PostgreSQL ウォームスタンバイ

継続的アーカイブとポイントインタイムリカバリ（PITR）

PostgreSQLデータベースのウォームスタンバイの手段として初めてWALアーカイブとpg_standbyを使ってみた。これまでpg_dumpを使ったバックアップでのコールドスタンバイしかやったことなかったので。

WALアーカイブファイルはscpでスタンバイサーバにログシッピング。WALファイルの容量が結構大きかったので少し驚いたけど、pg_standbyの %r オプションによって、リストア後の古いWALファイルは自動的に削除されるようなので一安心。

archive_command、restore_commandの設定は今まで使ったことなかったし、ポイントインタイムリカバリについてもよく分かってなかったので、いろいろと勉強になった。

DB | .pgpass

パスワードファイル

cronとかからpg_dumpするときにパスワードを指定するにはどうしたらいいんだろうと調べていたら、こんなファイルがあることを知った。

今まで自分が管理するサーバでしかPostgreSQLを運用したことなかったので、スーパーユーザのpostgresユーザは認証ナシで各DBにアクセスできるように設定し、postgresユーザがバックアップを取るようにしていたから必要性がなかった。

DB | ランキングを求める

How to Optimize Rank Data in MySQL | O'Reilly Media

データベースに記録されたスコアを元にランキングを求める際の、パフォーマンスに秀でるテーブルやSQLの設計手法に関する記事。「High Performance MySQL」の著者によるもの。

サイト上で獲得点数のランキングを表示するゲームを作ったことがこれまでに何度もあるんだけど、リアルタイムで順位を求めることがほとんどだった。この記事の中で最もよくないとされる方法。まあ、MySQLではないのだけど。
あるスコアの順位を求めるには基本的に

SELECT count(*) FROM score WHERE score.score >= スコア;

この記事では、更新コストは掛かっても順位カラムをテーブルに持たせておけ、count()は使うな(InnoDBは特にcount()が遅いので)という方針をより良い設計として挙げている。そして、更新のパフォーマンスを上げるためにMySQLのユーザ変数機能を使った順位の求め方を紹介している。それでも更新コストは比較的大きいので、得点更新時に毎回やるのではなく後でバッチ処理した方がいいんだけどねと、ちょろっと述べている。

今丁度、規模の大きめなランキングシステムをMySQL使って作る設計をしているので、いろいろと悩んでいる。運用後の変更は難しそうなので最初からバシッと決めたいところ。

MySQLのユーザ変数については、この記事を読んで初めて知った。他にどういう用途で使えるのかまだ分からないけど、面白そうな機能。
「ハイパフォーマンスMySQL」も読んでおきたいのだけど、第1版は結構古いので第2版の原著を読むか。ここ一年くらい、洋書の原著を買っても読む余裕がなくて、さあ読もうとなると日本語版が出てたりする。

DB | SQLパズル

誰かこれのMySQL(4.1)版を教えてください。

SELECT
    MIN(t0.No) + 1
FROM
    GetNo AS t0
    LEFT JOIN GetNo AS t1 ON t0.No + 1 = t1.No
WHERE
    t1.No IS NULL;

これだと1が抜けている場合にダメだ。

DB | GISとか幾何演算とか

Flashのステージ上の座標データをサーバサイドで処理したくてその方法を調べてたんだけど、GIS(Geographic Information System)という技術に行き着いた。名前だけは時々聞くことはあったけど、内容については全く知らないものだった。で、ざっと調べてみたけど、GISが示す範囲が大きくてやっぱりよく分かりませんでした。

OpenGISという仕様もあり、それを実装した(?)PostGISというPostgreSQLの拡張版もある。MySQLもOpenGISを実装している？　うーん、理解しきれません。

そんなGISよりもPostgreSQLに標準で備わっている「幾何データ型」と「幾何関数と演算子」が一番使いやすいのではないかと思いました。
標準SQLにはない型なので、ORマッパーから扱えず、自前でSQLを書かないといけないのが面倒そうだけど。

幾何演算はDBではなくプログラムの中でもやりたいと思ったら、JGCLというJavaライブラリも発見。幾何と言うとFlashのflash.geomパッケージ位しか知らず、ほとんど未知の分野だったのでちょっと興味が湧いてきた。

Flashの座標データ処理はやっぱりFlashでやってもらうことにして、GISやら幾何ライブラリやらについて調べてみようと思いました。

DB | PostgreSQL 8.3

【特集】期待度大のバージョンアップ - PostgreSQL 8.3の改良点を徹底分析 | エンタープライズ | マイコミジャーナル

新機能てんこ盛りだ。
同期スキャンと
ORDER BYの高速化が普段使う上では一番役に立ちそう。ORDER BYの高速化はOFFSET付きの場合はダメとあるけど、「offset=0」が付いてしまってもダメなのかな？

全文検索機能の組み込みも今後使えるかも。

DB | Oracle NULLS FIRST

NULL の特性 - Oracle/オラクルをマスターするための基本と仕組み

NULL はデフォルトの昇順ソートでは最後、降順ソートでは最初に並び替えられる。(≒無限大)
ORDER BY のオプション指定により、その並び順を変更することができる。

NULLS FIRST

NULL 値を順序の最初にする

NULLS LAST

NULL 値を順序の最後にする

このオプションの使い方を勘違いしていた。

ORDER BY ORDER_NO DESC NULLS FIRST

DB | Oracle MINUS演算子

SQL:EXCEPT,MINUS(集合演算子 差集合)について

EXCEPT演算子はPostgreSQL、DB2等で使用できます
MINUS演算子はOracleで使用できます

DB, Java.Spring | PostgreSQL 8.2 RETURNING句

INSERT

省略可能なRETURNING句により、INSERTは実際に挿入された各行に基づいて計算された値を返すようになります。 これは、通番のシーケンス番号など、デフォルトで与えられた値を取り出す時にかなり便利です。 しかし、そのテーブルの列を使用した式を指定することができます。 RETURNINGリストの構文はSELECTの出力リストと同一です。

INSERT INTO emp(name, age) VALUES('あめちゃん', 1) RETURNING id;

SELECT id FROM emp WHERE name='あめちゃん';

今Springを使ってWebアプリを作ってるんだけど、ではどうやってこのID値を取得すればいいのだろうと悩みました。更新系は JdbcTemplate#update() を使うけど、このメソッドの戻り値は更新された行数であって RETURNING 句の値ではない。そもそも RETURNING 句はIDだけ返すわけではなく * を指定すれば全てのカラムが返ってくる。つまりINSERTとSELECTを順に一度に実行しているようなもの。なので、結論から言うと JdbcTemplate の場合は query() メソッドを使ってINSERT文を実行すればよい。RETURNING 句でIDだけを返すのなら、queryForInt() が最適。

DB | Oracle BYPASS_UJVCヒント

Oracle:Update時に、ORA-01779: キー保存されていない表にマップする列は変更できません　のエラーが発生

SELECT項目に 「/*+ BYPASS_UJVC */」を入れることにより、エラーが発生しなくなる。

Oracleの細かいバージョンによっては使えない場合もあるので注意。

DB | Oracle 全テーブルの、レコード数を数える

Oracle Technology Network (OTN) Japan - 掲示板 : 全テーブルの、レコード数を数える方法 ...

select
   table_name,
   to_number(
     extractvalue(
       xmltype(
    dbms_xmlgen.getxml('select count(*) c from '||table_name))
       ,'/ROWSET/ROW/C')) count
 from user_tables
 WHERE TABLE_NAME NOT LIKE 'BIN$%'
   and (iot_type != 'IOT_OVERFLOW' or iot_type is null)
order by table_name

DB | CASE式のススメ

プログラミングと開発者のためのCodeZine：CASE式のススメ（前編）（データベース, SQL, CASE）

このトリックの重宝するところは、SQLの結果を二次元表の形に整形できることです。

これを含めミックさんのSQL記事は勉強になった。

DB | generate_series関数

集合を返す関数

今日初めて知ったPostgreSQLの関数。8.0から導入されたのかな。
むちゃむちゃ便利。

この関数だけで連続した数値のテーブルを表現することができる。他のテーブルと結合して使う場合は、

generate_series(0, 10) as s(n) left outer join other_table t1 on (s.n = t1.column)

DB | 度数分布

４ 章 データの代表値

度数分布のヒストグラムを描くために、SQLで度数分布テーブルを引っ張り出してきたいのだけど、これがなかなか難しい。基本系のSQLはコレ。

select
　floor(得点 / 階級幅) * 階級幅 as 得点の範囲,
　count(*) as 度数
from
　生徒
group by
　1
order by
　得点の範囲

一般に度数分布テーブルはどうやって求めるのだろう？　データベースからプログラムに全生徒の得点レコードを取り込んでしまえば、多少複雑な計算もできるだろうけど、全レコードを取り込むコストを想像するとスマートなやり方じゃないような気がする。
となると、やっぱりSQL一発でやるものなのか。うーむ。

今日作ったSQLでは階級幅が「(データの最大値-最小値) / 階級数」をベースとしているため、飛び抜けて大きい値があるデータの場合、あまり役に立たない階級幅の度数分布テーブルになってしまう。メディアンは算出できるけど、それが分かったところで最適な階級幅は求められそうもない。標準偏差とか使うのかなあ？　標準偏差ってやつが何なのか理解できてないのだけど・・・

DB | 順位から得点を取得するSQL

SELECT
    count(*) as 順位
FROM
    生徒
WHERE
    得点 >= {得点}

SELECT
    count(*) as 順位
FROM
    生徒
WHERE
    得点 >= (SELECT
                得点
            FROM
                生徒
            WHERE
                生徒ID = {生徒ID}
            )

SELECT
    得点
FROM
    生徒
ORDER BY
    得点 desc limit 1 offset {順位} - 1

DB | Oracle ランダムソート

ワンポイント

select * from [社員マスタ] order by dbms_random.random()

DB | PostgreSQL - NULLの存在する列の逆順ソート

[pgsql-jp: 31790] Re: NULLの存在する列の逆順ソート

select num from hoge order by num is null, num desc;

DB | Oracleと8080番ポート

Tomcat が起動しない場合の対処方法 ＠みっちーわーるど

CONNECT SYSTEM/XXXXX@YYYYY AS SYSDBA ←XXXXX はSYSTEMのパスワード、YYYYYは接続文字列です
SET ECHO ON
SPOOL CATNOQM
@D:\oracle\ora92\rdbms\admin\catnoqm.sql ←実際のOracleのパスになおして下さい
SPOOL OFF
DROP TABLESPACE XDB INCLUDING CONTENTS AND DATAFILES;
SHUTDOWN IMMEDIATE
STARTUP

DB | PostgreSQL8.0のJDBCドライバを7.4.x互換モードで使う

Heretic Programmer(2005-08-30)

jdbc:postgresql://localhost/testdb?protocolVersion=2

DB | SQL - IS NOT FALSE

SQL質疑応答スレ Part 2

via はぶにっきのコメント

こんなSQLは初めて見た。後でよく調べてみよう。

http://arton.no-ip.info/collabo/backyard/?PreparedStatementAndNull

DB | Postgresで全文検索

PostgreSQL Tsearch2日本語化パッチによる日本語全文検索システム

via klog

MEMO。

DB | Postgres UNIQUE制約の延期

CREATE TABLE

DEFERRABLE
NOT DEFERRABLE

これは制約を延期させることが可能かどうかを制御します。 延期させられない制約は各コマンドの後すぐに検査されます。 延期可能な制約の検査は (SET CONSTRAINTS コマンドを使用して)トランザクションの終了時まで延期することができます。 NOT DEFERRABLE がデフォルトです。 現在、外部キー制約のみがこの句を受け付けることができます。他の種類の全ての制約は延期させられません。

DB | coalesce

coalesce

coalesce /kòHəlés ｜ kNH-/
━ 【動】【自】
Ⅰ 〈折れた骨が〉癒合（ゆごう）する.
Ⅱ 合体する； 合同［連合］する.
語源
ラテン語「共に生育する, 堅固になる」の意； coalition と同語源

New College English-Japanese Dictionary, 6th edition (C) Kenkyusha Ltd. 1967,1994,1998

読めないので、きっと余り使われていない関数だと勝手に予想しています。僕の耳で聞いた音をカタカナに直すと「コゥアレス」です。アクセントは「レ」です。「コアレス」と読んでれば笑われることはなさそうです。

最後の"ce"に惑わされて、いつも読み方を忘れてしまっていましたが、きっと今日からは大丈夫。読めることが嬉しくって無駄に複雑なSQLを書いてしまいそうです。

DB | Postgres 日付演算

PostgreSQL 編16 - 日付計算、曜日、月末日、時間計算、期間計算、日付抽出 - SAK Streets

■日の演算(日加算、日減算) ・うるう年や大の月、小の月も考えなくて良い。
select current_timestamp + '1 days';

DB | Windows PostgreSQL

PgFoundry: Project Info- PostgreSQL installer

via とらねこ放浪記

WindowsネイティブなPostgreSQL。これがあるなら、もうCygwinを使う理由の半分以上はなくなる。

DB | 今日のSQL

select * from table_name order by random()

何故だ。この挙動が理解できない・・・　random()が返すのは0.0～1.0の値のはず。order by 0.5だとエラーになるし。

Book, DB | PostgreSQL全機能リファレンス

最近買った本。カスタマーレビューが五つ星だけあって、良書。ドキュメントを読んでるだけでは気づかないことや分からないことが詳しく解説されていてます。

今回のプロジェクトでは大活躍。知っていることも、敢えてもう一度この本で調べなおす。すると新たな発見があったり。そんな一冊です。

おぅ、もう朝だ。

DB | EAのPostgreSQLデータ型

EA 3.6にデフォルトで定義されているものだけだと必要なデータ型が抜けてたので、いくつか型を追加した。
postgresql.xml

EAのベストプラクティスの一つは、よく使う型やパッケージ構成を設定した独自のBase.EAPを作成して、新規プロジェクト生成時に参照プロジェクトに指定すること。

DB | 今日のSQL

int[] ids = new int[] { 1, 2, 3 };
PreparedStatement pstmt = conn.prepareStatement(
　　"SELECT * FROM item WHERE item_id IN (?)");
pstmt.setIntArray(1, ids);
...

DB | 今日のSQL

DELETE FROM
    link
WHERE
    (dest, src) = (SELECT
                        src,
                        dest
                    FROM
                        link
                    WHERE
                        link_id = ?
                    ) OR
    link_id = ?;

我ながらカッコイイSQL文だ。HQLじゃ書けまい!! (ホントか?)

DB | PostgreSQL bigintのインデックス

データ型

インデックスが付けられたsmallintあるいはbigintの列がテーブルにある場合、システムがそのインデックスを使用しようとした時に問題を引き起こすことがあります。例えば句が次のような形式の場合、

... WHERE smallint_column = 42

システムはインデックスを使用しません。

DB | The Effective Use of Joins in Select Statements

後で読むべし。