PostgreSQL8.4 配列型 集計
- PostgreSQL8.4の配列型を使用してテーブルの中身の数値ごとの件数を知りたいです。
- テーブルには配列型の列があり、各列の数値ごとに件数を取得する方法を探しています。
- SQLのみを使用して単純かつ高速な方法を探しています。
- ベストアンサー
PostgreSQL8.4 配列型 集計
次のような構造テーブルがあったとします。 create table sample_tbl ( no numeric(7) primary key, val numeric[] not null ) ※val列は 配列型。 テーブルの中身は次のような状態 no val ------- ---------------- 1 {1,2,5} 2 {1,3,4,5} 3 {2} 4 {1,5} 5 {1,2,3,4,5} 6 {1,5} 最終的に "val列" の中身の数値ごとの件数を知りたいです。 ＜欲しい結果＞ 1 → 5件 2 → 3件 3 → 2件 4 → 2件 5 → 5件 SQLのみを用いたできるだけ単純（一時テーブルなどを使わずに） 且つ高速な方法を探しております。 何か良い方法はありますでしょうか・・？ よろしくお願いいたします。
- eicat
- お礼率100% (4/4)
- PostgreSQL
- 回答数1
- ありがとう数1
- みんなの回答 （1）
- 専門家の回答
質問者が選んだベストアンサー
8.4なら「unnset関数」が使えます。 select item,count(*) from ( SELECT unnest(val) as item from sample_tbl ) t group by item order by item;
関連するQ&A
- データベース：主キーが文字列の場合IDを振るべき？
テーブルの主キーが文字列の場合、 その主キーの文字列を通し番号に置き換えて、 その文字列は別テーブルに移したほうが検索が速いですか？ 例えば、テーブルが CREATE TABLE spec ( test_name VARCHAR(40) NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_name) ); で、40文字という長いテスト項目名の場合、 CREATE TABLE spec ( test_id INTEGER NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_id) ); と CREATE TABLE test ( test_id INTEGER NOT NULL, test_name VARCHAR(40) NOT NULL, PRIMARY KEY(test_id) ); という二つのテーブルに分けたほうが検索は速くなりますか？ （もちろん、結合する時間も含めてです。） もし速くなるとしても、文字数が3文字など少ない場合は 通し番号に置き換えてもきっと効果は薄いですよね？ 何文字以上の文字列なら通し番号に置き換えたほうが速いですか？ 皆さんはどのように決めていますか？
- ベストアンサー
- その他(データベース)
- PHPでPostgreSQLを使う場合
外部参照はつけてもつけなくても同じでしょうか? そのメリットは無いのでしょうか? 例えば create table num_tbl { no int4 primary key, str text }; create table cont_tbl { no int4 references num_tbl(no), cont text }; の場合 references num_tbl(no) をつけることによってPHPのコーディングにメリットはあるでしょうか? どちらにしても select num_tbl.str,cont_tbl.cont from num_tbl,cont_tbl where cont_tbl.no=num_tbl.no; のような使い方になり何かメリットがあるようには思えないのですが
- ベストアンサー
- PostgreSQL
- データベース：二つのテーブルの繋ぎ方
私（あなた）が検査担当者だとします。 そして、測定担当者から CREATE TABLE measurement ( lot VARCHAR(10) NOT NULL, test_id INTEGER NOT NULL, data NUMERIC(17,7), PRIMARY KEY(lot, test_id) ); という、あるロットでのテスト番号に対する測定値データが送られてきたとします。 そして、設計担当者から CREATE TABLE spec ( test_name VARCHAR(10) NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_name) ); という、テスト項目名に対する上限・下限の仕様データが送られてきたとします。 ここで、テスト番号とテスト項目名は同じ数だけあって、お互いが一致するように同じ順番で並んでいるとします。 この二つのテーブルを使って、測定値が範囲内か範囲外かを調べるのが目的です。 この場合、どうやって二つのテーブルを繋ぐのが一般的ですか？ 自分の考えだと、一旦、specテーブルにtest_idを加えて CREATE TABLE spec ( test_id INTEGER NOT NULL, test_name VARCHAR(10) NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_id, test_name) ); とするのですが、第二（第三？）正規化に基づいて CREATE TABLE spec ( test_id INTEGER NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_id) ); と CREATE TABLE test ( test_id INTEGER NOT NULL, test_name VARCHAR(10), PRIMARY KEY(test_id) ); の二つに分けるのかな、と思っています。 正規化の観点から言うと、きっとこれが正しいのかなと思っています。 でも、元々、specテーブルにはテスト項目名が入っていたのでテスト項目名と上限・下限が一目で分かっていたのに、このように分けると、いちいちspecテーブルでテスト番号を確認してから、testテーブルでそのテスト番号に該当するテスト項目名を見つけなくてはならなくなってしまいます。クエリーでの検索もJOINをしなければならなくなって遅くなりませんか？ 敢えて正規化しないでおく、なんて手段もありますか？ こんな場合、皆さんならどうしますか？ 「こうするように決められている」なのか、 「ああもできるし、こうもできるんですが、どっちでもいいですよ」なのか、 温度差みたいなものもできれば知りたいです。
- ベストアンサー
- その他(データベース)
- Oracleのシーケンスありのテーブル作成について
Oracleのテーブルを作るのに、ある列を自動で「全体の通し番号」みたいなユニーク（一意）な番号を振りたいく、 シーケンス(sequence)を作成しテーブルを作ろうとしているのですがうまくいきません。 どこがわるいのでしょうか。 create sequence "yamaaf_seq"; CREATE TABLE TBL_AFFILIATE_SESS( UNIQ_ID NUMBER(11,0) DEFAULT nextval('yamaaf_seq') NOT NULL, ORG_CODE VARCHAR2(64) NOT NULL, primary key("UNIQ_ID"));
- ベストアンサー
- Oracle
- TO_NUMBERの結果は、カラムに追加できない?
いつもお世話になっています。 TO_NUMBER実行時、結果はnumeric型で取得できると思うのですが、 その値をカラムに追加することはできるのでしょうか？ insert into number2 values((select numberStr,TO_NUMBER (numberStr,'000.000') from number),'aaa'); 実行すると、 副問い合わせは1列のみを返さなければなりませんというErrorが生じます。 なのでwhere句を追加しましたが、結果は同じです。 実行したsqlは、 create table number ( count integer not null primary key, numberStr varchar(20) not null ); create table number2 ( no numeric not null primary key, numberS varchar(30) not null); insert into number values(5,'555.55000'); insert into number2 values((select numberStr,TO_NUMBER (numberStr,'000.000') from number where count=5),'aaa'); です。 宜しくお願いします。
- ベストアンサー
- PostgreSQL
- 他テーブルの集計結果を別テーブルのカラムに格納
新規テーブル作成時にあるカラムに別のテーブルの集計結果を入れることはできるのでしょうか？ 例えば、商品を定義したテーブルの中に、その商品が合計いくつ売れているかを集計するカラムを設置し、常に発注テーブルからその商品の合計発注数を引っ張って来て格納するイメージです。 テーブル作成時のみ集計するではなく、常に参照し動きがあれば数値が自動で変わるようになる方法を探しています。 いちいちselectで集計しなくても始めからカラムに集計値が入っていれば楽だなぁと思い質問しました。 どなたかわかる方いらっしゃいましたらよろしくお願いします。 商品テーブル CREATE TABLE product ( product_no SERIAL NOT NULL, （商品id） product_name VARCHAR(200) NOT NULL, （商品名） product_price VARCHAR(20) NOT NULL, （料金） product_sum INTEGER, （合計発注数）←ここ PRIMARY KEY(product_no) ); 発注テーブル CREATE TABLE order ( order_no SERIAL NOT NULL, （発注id） product_no INTEGER NOT NULL REFERENCES product(product_no) ON UPDATE CASCADE ON DELETE CASCADE,（商品id） order_num INTEGER NOT NULL,（その商品の発注数） order_date TIMESTAMP,（発注日） PRIMARY KEY(order_no) );
- 締切済み
- PostgreSQL
- データベースの考え方で。
データベースの考え方、で教えてください。 路線検索と、路線にひもづく駅名の時刻表を表示させる、データベースを作りたいと思っているのですが、 その場合、考え方としては、以下のような感じになるのでしょうか？ 路線名のtbl sql文: create table transit ( id integer not null, name varchar(20) not null ); 路線名tblにひもづく、駅名tbl sql文: create table station ( id integer not null primary key, transit_id integer not null, name varchar(20) not null ); 駅名tblにひもづく、時刻表tbl sql文: create table timetable ( id integer not null primary key, station_id integer not null, hour integer not null, minute integer not null ); 表示パターンとしては、 ================================= ○○線： XX駅 6:10 20 7:30 55 . . . . . . 22:15 36 ================================== のような感じです。 よろしくお願いします。
- 締切済み
- MySQL
- 親子関係があるテーブル内での検索
親のidを保持するテーブルです。 CREATE TABLE `tbl` ( `id` int PRIMARY KEY, `parent_id` int, FOREIGN KEY(`parent_id`) REFERENCES `tbl`(`id`) ) ENGINE=InnoDB; このテーブルで「parent_idがNULLで、かつ、子を持たない（親というか子でもないというか）行」 を検索したいのですが、どのようなSQLを記述すれば良いのでしょうか？
- ベストアンサー
- MySQL
- 表の結合と並べ替え
次のようなテーブル構成で、 CREATE TABLE tbl1 ( id int PRIMARY KEY AUTO_INCREMENT ) ENGINE=InnoDB; CREATE TABLE tbl2 ( id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) ) ENGINE=InnoDB; CREATE TABLE tbl1_tbl2 ( id int PRIMARY KEY AUTO_INCREMENT, tbl1_id int, tbl2_id int, FOREIGN KEY(tbl1_id) REFERENCES tbl1(id), FOREIGN KEY(tbl2_id) REFERENCES tbl2(id) ) ENGINE=InnoDB; CREATE TABLE tbl3 ( id INT PRIMARY KEY AUTO_INCREMENT, tbl1_id int, created datetime, FOREIGN KEY(tbl1_id) REFERENCES tbl1(id) ) ENGINE=InnoDB; [tbl1] id 1 2 3 [tbl2] id name 1 a 2 b [tbl1_tbl2] id tbl1_id tbl2_id 1 1 1 2 1 2 3 1 2 4 2 1 5 3 1 6 3 2 [tbl3] id tbl1_id created 1 1 2011-10-17 01:51:39 2 1 2011-10-17 02:51:39 3 3 2000-10-17 01:51:39 ここから次のような「tbl1のid」「tbl2の個数」「tbl3の最新のcreated」を基準とした （下の例は「tbl1のid」を昇順にした結果です） それぞれの並べ替た結果を得たいのですが、どのようなSQL文を記述すれば良いのでしょうか？ [tbl1のid] [tbl2のnameのリスト] [tbl3の最新のcreated] 1 a,b,b 2011-10-17 02:51:39 2 a 3 a,b 2000-10-17 01:51:39
- ベストアンサー
- MySQL
- SJISでPHP５＋POSTGRESQL8
を使うとだめな場合があります。 調べてみると、 漢字文字列の配列を使った場合です。 pg_query("create table tbl(ken text[])"); pg_query("insert into tbl values('{大阪府,宮崎県}'); や pg_query("create table tbl(ken text[])"); pg_query("insert into tbl values('{\"大阪府\",\"宮崎県\"}'); です。 「府」、「宮」のSJIS下位コードが'{'と同じであるのが原因のようです。 他に「本」、「マ」、「笠」がつく漢字もだめです。 「本」の下位コードは'{'と同じで「マ」、「笠」の下位コードは'}'と同じです。 バージョンや環境によってはうまくいくようですが、 今の環境で積極的に対処する方法はあるでしょうか？
- ベストアンサー
- PHP
お礼
早速のご回答をありがとうございます。 unnest関数は知らなかったので 勉強になりました。 当方の環境にて目的を満たすことを 確認きましたので、これで大丈夫だと思います。