Mysql phpの質問です。どちらも5です。


table名:text
フィールド:id,priority,honbun

このようなテーブルがあります。
priorityの値が高いレコードを優先的に表示させたいです。
例えばpriorityの値が 80 10 10 という3つのレコードがあれば、
それぞれ80% 10% 10%の頻度で表示させたいです。

もし1 1 1 であれば33% 33% 33%です。お解かりいただけますでしょうか。

これをPHP+MySQLで実現させたいのですが途方にくれております。
SQLのみでは厳しそうなのでPHPで前処理が必要でしょうか?

ちょっと複雑になりそうな気がしますのでポイントは500~1000ポイント用意させていただきます。もしよろしければお教え下さい。

質問後にコメント欄に追記するかもしれません。それではみなさんよろしくお願い致します。

回答の条件
  • 1人5回まで
  • 登録:2007/06/29 05:25:01
  • 終了:2007/07/05 16:45:36

ベストアンサー

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542007/06/29 06:23:18

ポイント200pt

実装はMySQL 5.0から使えるストアド・プロシジャでもphpでも可能ですが、次のような方法が単純だと思います。

簡単に言うと、比率に応じて一時表(temporary table)に行を格納しておき、検索時にランダムに取り出す方法です。

(1)`text`表から比率のデータを得る

(2)(1)の比率から、それぞれの格納行数を決定

(3)(2)で決定した行数分、一時表に(1)で得た行を格納

(4)(3)のtemporary tableから検索。この時、「order by rand()」を指定

一時表は、後始末が不要な作業用の表であり、「create temporary table」で定義できます。

例えば、定義例は、こんな記述になります。

drop temporary table
 if exists     -- 既に存在した場合は、定義削除
 temp_text;
-- 一時表は、`text`表と同じ列構成、データ型にする
create temporary table temp_text
(id    int  auto_increment primary key,
 `priority`       int,
 honbun     varchar(100));

一時表の操作は、通常の表と同じように、insertやselectできます。

rand関数は、検索時のソート指定にも使えます。次のような指定で、ランダムにソートして結果が返されます。

select * from temp_table
 order by rand();
id:tokyosmash

回等ありがとうございます。

priorityが3 2 1 の場合はそれぞれ一時表に3行 2行 1行 記録して、それをrandで取り出せばいいわけですね。なるほど方法は理解できました。ありがとうございます。


ただ(3)で苦労しそうです。

$sql = "SELECT * FROM text";

$re = mysql_query($sql);

while(@$ro =mysql_fetch_assoc($re)) {

$data[] = Array(

'priority' => $ro['priority'],

);

}

これで配列に格納して、ここで取得した数字をそのまま行数としてINSERTするんですよね。INSERT文をforで繰り返せばいいのでしょうか。

2007/06/29 07:52:05

その他の回答(3件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542007/06/29 06:23:18ここでベストアンサー

ポイント200pt

実装はMySQL 5.0から使えるストアド・プロシジャでもphpでも可能ですが、次のような方法が単純だと思います。

簡単に言うと、比率に応じて一時表(temporary table)に行を格納しておき、検索時にランダムに取り出す方法です。

(1)`text`表から比率のデータを得る

(2)(1)の比率から、それぞれの格納行数を決定

(3)(2)で決定した行数分、一時表に(1)で得た行を格納

(4)(3)のtemporary tableから検索。この時、「order by rand()」を指定

一時表は、後始末が不要な作業用の表であり、「create temporary table」で定義できます。

例えば、定義例は、こんな記述になります。

drop temporary table
 if exists     -- 既に存在した場合は、定義削除
 temp_text;
-- 一時表は、`text`表と同じ列構成、データ型にする
create temporary table temp_text
(id    int  auto_increment primary key,
 `priority`       int,
 honbun     varchar(100));

一時表の操作は、通常の表と同じように、insertやselectできます。

rand関数は、検索時のソート指定にも使えます。次のような指定で、ランダムにソートして結果が返されます。

select * from temp_table
 order by rand();
id:tokyosmash

回等ありがとうございます。

priorityが3 2 1 の場合はそれぞれ一時表に3行 2行 1行 記録して、それをrandで取り出せばいいわけですね。なるほど方法は理解できました。ありがとうございます。


ただ(3)で苦労しそうです。

$sql = "SELECT * FROM text";

$re = mysql_query($sql);

while(@$ro =mysql_fetch_assoc($re)) {

$data[] = Array(

'priority' => $ro['priority'],

);

}

これで配列に格納して、ここで取得した数字をそのまま行数としてINSERTするんですよね。INSERT文をforで繰り返せばいいのでしょうか。

2007/06/29 07:52:05
id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402007/06/29 13:03:31

ポイント20pt

ちょっと意味を取り違えてるかもだけど、

SELECT id,priority,honbun FROM text
  ORDEBY rand() * priority
  LIMIT 1;

これを取り出したい行数分繰り返す?

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.3.3.2 数学関数

id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542007/07/03 16:27:13

ポイント200pt

#1回答者です。

ストアド・プロシジャによる実装例を示します。phpで実装する場合は、ストアド・プロシジャ中の処理を参考にしてください。

次の二つの作業用のテーブルを、使用しています。

(1)w_text:生成比率から生成行数を求めた結果を管理

 →このテーブルの情報は、php側で管理してもいいと思います。

(2)gen_text:生成行数に従って生成した行を保持

 →#1でも使用しているテーブルです。この表の検索結果をランダムに並び替えます。


1.表定義

drop table if exists `text`;
create table `text`
(id           int auto_increment primary key,
 `priority`   int,
 honbun       varchar(10));

-- 生成比率から生成行数を求め、管理する作業用テーブル
drop temporary table if exists w_text;
create temporary table w_text
(id           int,
 `priority`   int,
 honbun       varchar(10),
 gen_rows     int       # 生成比率により決定した生成行数
);

-- 生成行数分、行を生成して保持する作業用テーブル
drop temporary table if exists gen_text;
create temporary table gen_text
(w_id         int primary key auto_increment,   # debug用id
 id           int,
 `priority`   int,
 honbun       varchar(10));

2.ストアド・プロシジャの定義

--
-- ストアド・プロシジャの定義
-- 
drop procedure if exists gen_rows;    # 存在したら削除
delimiter //                          # 終端記号の変更
create procedure gen_rows
(in  p_gen_rows     int,              # 生成する行数の合計
 out p_RC           int)
begin
 declare vSumPriority  int;
 declare vID           int;
 declare vPriority     int;
 declare vHonbun       varchar(10);
 declare vGen_rows     int;

 declare w_rcnt        int;           
 declare eod           tinyint;
-- カーソル宣言
 declare cr1 cursor for
  select *
   from w_text;

-- 例外宣言
 declare continue handler for not found set eod=1;
-- 
 set p_RC=0;

-- 作業用の表にデータコピー
 insert into w_text(id,`priority`,honbun)
  select * from `text`; 
-- 生成比率の合計を得る
 select sum(`priority`)
  into vSumPriority
  from w_text;

 if vSumPriority > 0 then
-- 生成比率から生成行数を決める
  update w_text
   set gen_rows = p_gen_rows / vSumPriority * priority;

-- 生成行数分、行を格納
  set eod=0;
  open cr1;
  fetch cr1 into vID,vPriority,vHonbun,vGen_rows;
  while eod=0 do
   set w_rcnt=0;
   while w_rcnt<vGen_rows do
    set w_rcnt=w_rcnt+1;
    insert into gen_text
     values(null,vID,vPriority,vHonbun);
   end while;
   fetch cr1 into vID,vPriority,vHonbun,vGen_rows;
  end while;
  close cr1;
 else
  set p_RC=12;
 end if; 
end;
//
delimiter ;                       -- 終端記号を元に戻す

</pre>
id:tokyosmash

ご丁寧にありがとうございます!

ストアドプロシージャはちょっと苦手なのでPHPで処理したいと思います。参考になる部分がたくさんあってありがたいです。

取り急ぎ。

2007/07/04 16:55:14
id:chuken_kenkou No.4

chuken_kenkou回答回数722ベストアンサー獲得回数542007/07/03 16:31:12

ポイント200pt

#1です。

1と2は準備作業であり、ここからが比率に従って、ランダムに検索するための作業です。


行の生成比率を格納

-- 行の生成を実行
truncate table `text`;
insert into `text` values
(null,1,'a'),
(null,1,'b'),
(null,1,'c');

比率に従い、行を生成

truncate table w_text;
truncate table gen_text;
call gen_rows(18,@rc);    # 合計18行を、`text`表の比率で生成

生成結果を確認する場合は、以下のようなSQLで行なえます。

-- 確認
select * from gen_text;

select max(w_id),id,`priority`,honbun,count(*) as 件数
 from gen_text
 group by id,`priority`,honbun;

ランダムに検索結果を得ます。

-- ランダムに検索結果を得る
select * from gen_text
 order by rand();
id:tokyosmash

回答ありがとうございましました。

自動終了寸前なので、とりあえずここで終了させていただきます。ご丁寧にありがとうございました。何とかできると思います。

2007/07/05 16:44:40
  • id:tokyosmash
    ちょっとわかりにくい質問文だったかもしれません。
    レコードをランダムにセレクトする場合はrand関数を使うかと思います。そのrand関数にpriorityという値を加味させたいのです。

    質問文の「例えばpriorityの値が 80 10 10 という3つのレコードがあれば、それぞれ80% 10% 10%の頻度で表示させたいです。
    」という箇所は、10回SELECTを実行した場合に、それぞれのレコードが80% 10 % 10%の確立で抽出されるという事です。

    ただ、rand関数を使わなければならないわけではありません。あくまで概念を説明するためにrandを出しただけです。

    説明下手ですみませんが、よろしくお願い致します。
  • id:chuken_kenkou
    >INSERT文をforで繰り返せばいいのでしょうか

    そういう発想です。

    アプリケーション側で、最終的な行数が不定の情報を溜め込むのは、容易ではないと思います。そういう場合は、外部ファイルに吐き出すのは一般的な処理だと思います。

    せっかくデータベースを使える環境なのですから、MySQLの一時表を使えば、アプリケーション側の処理は単純化できると考えました。

    吐き出す行数が何十万件~何百万件といったケースもあるなら、一旦、insertしてselectし直すというのはオーバーヘッドになりますけどね。
  • id:tokyosmash
    ありがとうございます。
    不慣れなもので(3)には苦戦しそうです。


    ただゴールは見えてきました!
    進展がありましたらまた報告します。
    早朝よりご回答ありがとうございます。

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

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

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

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