Oracleの分析関数

Oracleは9iから分析関数というのが使えるようになってます。

ちょっと前にグループごとに通し番号をふるというのをやったんですが、それも分析関数を使ってます。

このへんのページが詳しいです。
http://www.geocities.jp/oraclesqlpuzzle/oracle-sql1-olap.html


レポートを出すときとか、すごく役に立つと思われます。

またこの分析関数、mysqlでも使えるようです。

今すぐ使うってわけじゃないですけど、覚えておけばそのうち必ず使えると思うのでメモしておきます。

Oracleで小数点の桁数を揃える

1.01
2
3.5


みたいな小数点がバラバラな数値を

1.01
2.00
3.50

のように小数点の桁数を揃えたい。

そんな時はこんな感じで。

trim(to_char(COLUMN,'9.99'))

to_charで桁数を揃えますが、その際に半角スペースが入ってしまうので、trimで消しています。

参考ページ
http://otn.oracle.co.jp/forum/thread.jspa?threadID=3003768&switchMode=threaded

[Oracle] ROW_NUMBERでグループごとに連番を割り当てる

ROW_NUMBERを使うとグループごとに連番の割り当てが可能です。

参考ページはこちら。
http://blog.livedoor.jp/akf0/archives/51421581.html

便利です。

Oracleの隠し関数「wm_concat」

Oracleに「wm_concat」という関数があることを知りました。隠し関数だそうです。

使い方はこのあたりを参考にさせていただきました。
http://blog.livedoor.jp/dd0125/archives/381992.html

上記の記事からそのまま抜粋させていただきます。

テーブル名: HOGE
ID STR
1 A
1 B
1 C
2 C
2 D

というデータがあった場合、

SELECT ID , wmsys.wm_concat(STR) AS STR
FROM HOGE
GROUP BY ID

とSQLを打つと、

ID STR
1 A,B,C
2 C,D

と返ってくる

この関数を使えば、SQLで出来ることがかなり幅が広がると思います。

OracleのTRUNC関数で週初の日曜日を取得

date型のデータを様々な形で切り捨てするTRUNC関数というのを知りました。

http://www.shift-the-oracle.com/sql/functions/trunc-datetime.html

便利です。
今回使ったのはDAYのフォーマットでの切り捨て。日付を渡すと、その日付の所属する週、つまり直前の日曜日の日付が返されます。

こんな感じ。

select TRUNC(TO_DATE('2012-03-08'), 'DAY') from dual
-> 2012/03/04

他にも四半期(Q)とかその月の最初の曜日(W)とか、いくつか引数が用意されているので、覚えておけばいつか役に立つと思います。

Oracleで時刻を24時間表示にする

TO_CHAR('YYYY/MM/DD HH:MI')

こうすると、HHの部分が12時間表記になる。これを24時間表記にするにはHH24を使う。

こんな感じ。

TO_CHAR('YYYY/MM/DD HH24:MI')