PostgreSQL 8.4の分析関数
2009.11.17 Author: Jas
こんにちは。技術ネタ投稿でおなじみのJasmineです。
先日、ある複雑な処理で、SQLは読み辛くなり、速度もイマイチで困っておりました。
「PostgreSQLの8.4が使えれば、新機能の分析関数が使えるのに・・・」
っとブツブツ文句を言っていると、周りから「8.4使えるよ」との声が。
なんですと！
早速、PostgreSQL 8.4の新機能、分析関数を使ってみることになりました。
今回は、group毎にidの昇順で連番(rank)を付けていくサンプルを紹介したいと思います。
以下のような結果を目標にします。
| ID | GROUP | RANK |
|---|---|---|
| 111 | 1 | 1 |
| 123 | 1 | 2 |
| 125 | 1 | 3 |
| 140 | 1 | 4 |
| 121 | 2 | 1 |
| 135 | 2 | 2 |
| 138 | 2 | 3 |
| 145 | 2 | 4 |
| 105 | 3 | 1 |
| 128 | 3 | 2 |
| 132 | 3 | 3 |
| 137 | 3 | 4 |
まずは、分析関数を使わない場合のSQLはこちら。
SELECT t.id, t.group,
(SELECT count(*) + 1 FROM test_table t2
WHERE t.id > t2.id AND t.group = t2.group) AS rank
FROM test_table t
自己結合を使っているので、テーブルのデータ数が多くなると負荷が大きくなり、速度も遅いです。
件数によっては、あまりに処理が遅すぎるため、途中で停止するほど時間がかかります。
Postgre 8.4の分析関数を使った場合はこちら。
SELECT t.id, t.group,
Row_Number() over(partition by group order by group, id) AS rank
FROM test_table t
SQLもシンプルになり、処理の内容もわかりやすくなりました。
しかも、先に紹介した自己結合のSQLよりも何倍も速いです。
数十万件程度のデータ件数でも数秒～数十秒程度で処理が終わります。
これで無事に処理速度の問題もSQLの読みやすさの問題も改善しました。
名古屋のWebシステム開発・ネットワーク構築会社 コネクティボへ
