久しぶりにSQLでごちゃごちゃやってみました。

集計かけてから更にマスタとつき合わせっていう
コレだけ見たら「はぁ？？」な内容なんですが
そんなので1時間ほど唸ってた時のメモを折角なので残しておきます。

テーブル一覧をやりましたが、今度はテーブルの項目一覧です

テーブルの列名、型一覧 SELECT     attname,     CASE atttypid         WHEN 20 THEN 'boolean'         WHEN 23 THEN 'interger'         WHEN 25 THEN 'text'         WHEN 700 THEN 'float'         WHEN 1082 THEN 'date'         WHEN 1114 THEN 'timestamp'     END FROM pg_attribute WHERE attnum > 0 AND attrelid = (select relfilenode from pg_class where relname = 'テーブル名') ORDER BY attnum

CASE atttypid の部分については、全ての型を網羅してるわけではないですが
今回わたしが使ってた型に関しては一通り入ってるか・・・って感じ。
型名自体は取得できないので、型のIDを手動で変換してやる必要があるわけね。

psqlをつかえば
￥ｄ テーブル名
という書き方で、列名＆型の一覧＋インデックスまで取得できますが、
コマンドラインでしか使えないのがちょとイタい。

今日はテーブル一覧を取得する方法について。

SELECT * FROM pg_tables


ってだけなんだけどさ・・・。これで一通りテーブル一覧が取得できちゃう。
で、tableownerで絞込みをかけたりすればシステム用テーブルなどが除外
できるんで、ユーザーテーブルだけの一覧も抽出できますな。

この情報のソースは↓

SAK Streets


VBからPerlからOracle,MySQL,PostgreSQLといろんな言語、DBについて
小ネタが満載です。

ここ最近調べたネタで結構使えるネタだったので、もういっちょ書いときます。

PostgreSQLだけではなく、MySQLやMS SQL Serverでも使えるようなのですが、
Select文などでのCASEキーワードです。
(Oracleの場合はDECODEを使うのが一般的だと思いますが・・・
私も今までそれしか知らなかったので、それが使えないPostgreって不便だな〜と思ってました。)

実際にどうやって使うかというと

肥満判定 SELECT code, date,     CASE         WHEN hantei = 'やせすぎ' or hantei = '肥満' THEN 1000         WHEN hantei = 'やせぎみ' or hantei = 'やや肥満'THEN 10         WHEN hantei = '標準' THEN 0         ELSE 0     END as rank FROM table

こんな感じで使いました。
これは、肥満度の判定結果の文字列を見て、それを数値に置き換えるということを
ごちょごちょ書いとるわけなんですね。



ちなみにOracleのDECODEを使うと

肥満判定 SELECT code, date, DECODE(hantei, 'やせすぎ', 1000, '肥満', 1000, 'やせぎみ', 10, 'やや肥満', 10, '標準', 0, 0) AS rank FROM table

って感じになるのかな？
(DECODEは引数として255指定できるらしいです。
並べてみるとCASEの方が柔軟性があって見やすいSQLが書けそうな気がするなぁ・・・)


で、Postgreに話を戻すと、Update文とかでもこのCASEが使えるんですね。
本とかで例題SQLを見ると「へぇ〜、便利」とも思ったのですが、とりあえず
使う機会はなさそうなので、ここでは端折ります(^ー^;

あと、CASEには２通りの書き方があって
a) CASE 式 WHEN 式 THEN 式・・・ってのと
b) CASE WHEN 条件 THEN 式・・・ってやつ
ぱっと見、違いが分かりにくいですが、
a)は単純に最初の式に対しての条件をずらずら書く(switch ... case形式？)
b)は条件式がいくつもある(if ... elseif ...形式？)
って違いなのかな？


こういうのは頭で考えてどうこうってよりは、実際に試してみたほうが
手っ取り早いと思います・・・何かの試験を受けようと思ったら別ですが・・・。
(あぁ・・・SJCAの勉強が・・・はかどってない)


ついでにもういっちょSELECTのサンプルです。

肥満判定分布 SELECT     SUM(CASE hantei WHEN 'やせすぎ' THEN 1 END) as yase,     SUM(CASE hantei WHEN 'やせぎみ' THEN 1 END) as yasegimi,     SUM(CASE hantei WHEN '標準' THEN 1 END) as normal,     SUM(CASE hantei WHEN 'やや肥満' THEN 1 END) as yayahiman,     SUM(CASE hantei WHEN '肥満' THEN 1 END) as himan FROM table

これを使うとSQL一文で集計表っぽいのができるので、とっても便利です。
またこれは上の説明で書いたCASE文のa)パターンを使って書いてますね。

他のマシンにDBを引越しするときなんかに使える技
DBのダンプ＆復元です。

・ダンプ
pg_dump h ホスト名 U ユーザ名 DB名 > 出力ファイル名

・復元
psql h ホスト名 U ユーザ名 DB名 < ダンプファイル名

そいだけです・・・。
でもフリーのDBでこんだけできるって素敵。

テスト用とは言え、会員マスタデータを全消去してしまった・・・

あ〜あ・・・。
おかげで、自分自身の仕事も止まってしまったし、メンバーの
仕事まで止めてしまった・・・。

ということで、今後の為にPostgreのデータインポート/エクスポート技をば。

psqlを立ち上げて・・・
・テキストファイルからのインポート
￥copy [テーブル名] from [テキストファイル名]

・テキストファイルへのエクスポート
￥copy [テーブル名] to [テキストファイル名]