現在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に変換することはできません。

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

回答の条件
  • 1人2回まで
  • 登録:2007/11/19 01:11:08
  • 終了:2007/11/24 23:40:36

ベストアンサー

id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542007/11/20 12:36:12

ポイント100pt

質問の内容では、文字列を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>
	
	

  

その他の回答(2件)

id:y-kawaz No.1

y-kawaz回答回数1420ベストアンサー獲得回数2252007/11/19 03:51:47

ポイント10pt

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

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文でデータを取得することが出来るようになりますよね。

id:angel_wing

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

2007/11/19 18:32:08
id:y-kawaz No.2

y-kawaz回答回数1420ベストアンサー獲得回数2252007/11/19 17:43:16

ポイント10pt

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

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出来ます。

id:angel_wing

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

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

2007/11/19 18:34:10
id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542007/11/20 12:36:12ここでベストアンサー

ポイント100pt

質問の内容では、文字列を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>
	
	

  

  • id:angel_wing
    chuken_kenkouさんのやつでできました。ストアドファンクションを使ったことがなくて試行錯誤してしまいました。
    でもこれだと速度的に問題が出そうなので、もう少し煮詰めたいと思います。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません