人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

現在PostgresqlとPHPを使いプログラムを作っています。

TEXT型で作ったフィールドに{0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}のようなデータが入っています。

これをANDで検索したいと思っています。

INTで収まる数であれば
select * from test where (translate(test_f,'{,}','')::text::int & '1000000000'::int) > 0
でOKなのですが、個数が50以上ありINTに変換することはできません。

何かいい方法はないでしょうか?
よろしくお願いします。

●質問者: angel_wing
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:PHP postgreSQL SELECT test データ
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● y-kawaz
●10ポイント

そもそもテーブル設計に問題がある気がします。

1. フラグ数が固定なら普通にbool型のカラムを50個以上もつテーブルにしたらどうでしょう?

2. それともそのフラグ数は50個以上の固定数ではなく可変数なのでしょうか?もしそうであれば後述のように正規化してしまえばよいでしょう。

例えば今のテーブルが以下のようなものであるとします。

| key | flags |
|-----+-----------|
| foo | {1,0,1,0} |
| bar | {0,1,1,0} |

上記1 の場合は以下のようなテーブルになります。

| key | f1 | f2 | f3 | f4 |
|-----+----+----+----+----+
| foo | t | f | t | f |
| bar | f | t | t | f |

上記2 のようにフラグ数が可変の場合は以下のようになります。

| key | flag | value |
|-----+------+-------|
| foo | 1 | t |
| foo | 2 | f |
| foo | 3 | t |
| foo | 4 | f |
| bar | 1 | t |
| bar | 2 | f |
| bar | 3 | t |
| bar | 4 | f |
| bar | 5 | f |

このようなテーブルにすることで素直なSELECT文でデータを取得することが出来るようになりますよね。

◎質問者からの返答

根本的にDBの構造を変えることはできないので、このやり方では無理です


2 ● y-kawaz
●10ポイント

テーブル構造を変えないで出来る別解として以下のような方法もあります。

SELECT * FROM test WHERE test_f ~ '^\{([01],){8}1';

・PostgreSQL は ~ 演算子で正規表現が使えるのでそれを使っています。

・上記SQLは9番目の数字が1の行をSELECTします。

・正規表現を簡単に説明すると、^\{ が先頭で、([01],){8} は 0, か 1, の8回繰り返しを表し、その次(9番目)が1 という意味です。

{8} を {0}にすれば1番目が1、{1}なら2番目が2、{80}にすれば81番目が1の行をSELECT出来ます。

◎質問者からの返答

検索条件も同じように{1,0,1,0,0,0,0,1,0........ですので、このやり方では何回もSQLを実行させないといけないような気がします。

できれば1条件に対し1SQLの実行で終わるものをお願いします。


3 ● chuken_kenkou
●100ポイント ベストアンサー

質問の内容では、文字列をINTやBIGINTに変換する許容サイズを超えてるし、「0より大きい」という比較も許容サイズを超えてしまっています。

このテーブル設計では、性能を出すのは無理(=仮にインデクスがあっても使用できない)し、SQLの特長である柔軟な操作は無理だし、テーブル設計に変更が生じた場合も苦労すると思いますよ?

それでもどうしても今のテーブル設計でやりたいと言うなら、、、ストアドファンクションを作ってやってみては?


1.ストアドファンクションの定義例

1.1 定義

「1.4 ファンクションのソース」を参照。


1.2 仕様

1.2.1 機能

'0'と'1'の組み合わせで指定された文字列を、指定されたビット数に変換して返却する。

select fnc_CngCharToBit('1001',8)

B'00001001'

指定できるのは、最大400桁。

変換後のビット数の指定が必要にしているのは、PostgreSQLで「&」(ビット積)を行う場合、桁数が合っている必要があるため。

→INTに変換できる範囲の時は、結果的に長さが合っていた。


1.2.2 引数

fnc_CngCharToBit(引数1,引数2)

引数1:'0'と'1'の組み合わせの文字列で、最大400桁まで。

引数2:結果を何ビットにするか指定する。

「&」(ビット積)を行う場合は、PostgreSQLの仕様で、桁数を合わせる必要がある。


1.3 使い方

質問の内容では、ビット列の長さがBIGINTでも格納できない範囲であり、「 > 0 」といった検索条件を指定することはできない。

そのため、「 > fnc_CngCharToBit('0'::text,80) 」といった指定をする必要がある。

select * 
 from test
 where fnc_CngCharToBit(translate(test_f,'{,}','')::text,80) 
 & fnc_CngCharToBit('1000000000'::text,80)
 > fnc_CngCharToBit('0'::text,80)
;

1.4 ファンクションのソース

create or replace function fnc_CngCharToBit
(pChar varchar(400), -- 0と1の文字列
 pSize int) -- ビット数
 Returns bit varying(400) As $$
Declare
 vPos int;
 vCharLen int;
 vBitChar char(1);
 vBitLen int;
 vBit bit varying(400);
Begin
 --
 vCharLen:=length(pChar);
 raise info 'fnc started,pChar=%,pSize=%,length=%',pChar,pSize,vCharlen;
 vBit:=b'';
 vBitLen:=0;
 if vCharLen>0 and pSize>0 and vCharLen<=pSize then
 vPos:=1;
 while vBitLen<vCharLen and vBit is not null loop
 vBitChar:=substr(pChar,vPos,1);
 raise info 'vPos=%,vBitChar=%',vPos,vBitChar;
 if vBitChar='0' then
 vBit:=vBit||b'0';
 elseif vBitChar='1' then
 vBit:=vBit||b'1';
 else
 vBit:=null;
 end if;
 vBitLen:=length(vBit);
 raise info 'vBit=%,length=%',vBit,vBitLen;
 vPos:=vPos+1;
 end loop;
 while vBitLen<pSize and vBit is not null loop
 vBit:=b'0'||vBit;
 vBitLen:=length(vBit);
 end loop;
 end if;
 raise info 'fnc ended,vBit=%,length=%',vBit,vBitLen;
 return vBit;
end;
$$ language 'plpgsql'
;


</pre>

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ