mysql:13863
From: "F.Y" <"F.Y" <fumi_sby@xxxxxxxxxx>>
Date: Fri, 13 Apr 2007 13:33:57 +0900 (JST)
Subject: [mysql 13863] Re: マトリックステーブルの作成方法
sumと「まとめて入れる」はどう違うんだ、など意味わがんない部分多々あり答 えにくし。 調査員多数がそれぞれ最大3つまでの合コン会場に潜入しイケメン度を報告して きた。 もっともイケメン度の高い合コン会場を3つはしごするための資料が欲しい。 truncate table 会場一時; insert into 会場一時 values(調査員, 会場1) select 調査員, min(会場) from 報告 group by 調査員; update 会場一時 set イケメン度 = ( select sum(イケメン度) from 報告 where 会場一時.調査員 = 報告.調査員 and 会場一時.会場1 = 報告.会場 ); update 会場一時 set 会場2 =( select min(会場), from 報告 where 報告.調査員 = 会場一時.調査員 and 報告.会場 <> 会場一時.会場1); update 会場一時 set イケメン度 = ( select 会場一時1.イケメン度 + sum(イケメン度) from 報告 where 会場一時.調査員 = 報告.調査員 and 会場一時.会場2 = 報告.会場 ); update 会場一時 set 会場3 =( select min(会場) from 報告 where 報告.調査員 = 会場一時.調査員 and 報告.会場 not in (会場一時.会場1, 会場一時.会場2) ); update 会場一時 set イケメン度 = ( select 会場一時.イケメン度 + sum(イケメン度) from 報告 where 会場一時.調査員 = 報告.調査員 and 会場一時.会場3 = 報告.会場 ); truncate table 目標会場; insert into 目標会場 (会場1, 会場2, 会場3, イケメン度) select 会場1, 会場2, 会場3, sum(イケメン度) from 会場一時 group by 会場1, 会場2, 会場3; 文法その他、まったく検証しておらず。 方向性そのものも合ってるかどうかかな〜り怪しい。 --- ml@xxxxxxxxxx wrote: > 上田です。 > > 5.0.37-max MySQL Community Edition を使用しています。 > あるテーブル同士のマトリックステーブルの作成方法を探しています。 > > 以下のテーブルがあります。 > CREATE TABLE user ( > genre INT NOT NULL, > number INT NOT NULL, > unit INT NOT NULL, > PRIMARY KEY(genre,number) > ); > > CREATE TABLE user_skill ( > genre INT NOT NULL, > number INT NOT NULL, > seq INT NOT NULL, > line INT NOT NULL, > value INT NOT NULL, > PRIMARY KEY(genre,number,seq) > ); > > CREATE TABLE matrix ( > genre_1 INT NOT NULL, > genre_2 INT NOT NULL, > genre_3 INT NOT NULL, > sum_unit INT NOT NULL, > PRIMARY KEY(genre_1,genre_2,genre_3) > ); > > CREATE TABLE matrix_skill ( > genre_1 INT NOT NULL, > genre_2 INT NOT NULL, > genre_3 INT NOT NULL, > seq INT NOT NULL, > line INT NOT NULL, > sum_value INT NOT NULL, > PRIMARY KEY(genre_1,genre_2,genre_3,seq) > ); > > データは以下のようになります。 > user > 1,1,100 > 1,2,200 > 1,3,300 > 2,1,0 > 2,2,10 > 2,3,10 > 3,1,1000 > 3,2,100 > > user_skill > 1,2,1,1,10 > 1,2,1,2,20 > 1,3,1,1,10 > 2,1,1,1,-1 > 3,2,1,2,10 > > userはgenreとnumberで識別され、0個以上のuser_skillを持ちます。 > genreは1-3の値を持ちます。 > user_skillはlineで種別、valueで値を持ちます。 > > これらのデータのマトリックスをmatrixおよびmatrix_skillテーブルに導入 した > く思います。 > > マトリックスを作るルールは > userのgenreで絞込みgenreが1-3のそれぞれの種類のuserを全組み合わせる。 > matrixのgenre1-genre3にはuserのnumberが入る > matrixのsum_unitにはgenre1-genre3のuserのunitのsumが入る > matrix_skillはgenre1-genre3のuserのuser_skillをまとめて入れる > matrix_skillのlineはuser_skillのline、sum_valueはuser_skillのvalueのsum > が入る > となります。 > > 単純にuser同士のマトリックスならばuser同士をjoinしてINSET -- SELECT > で > SQL一発で作れそうなのですが、user_skillがあるので頭を悩ませています。 > > 実際のテーブルではもう少し複雑な条件で組み合わせる必要があり、全組み 合わ > せが2兆通りほどになる見積もりになっています。 > 試しにスクリプトで組み合わせを作成して動かしてみると、終了までざっと3 年 > ぐらいかかりそうになったので早々に止めました。 > そのときのCPU使用率を確認するとスクリプト70%、mysqld30%となっていまし た > ので、MySQLでクエリーだけで済ませればもう少しは早くなるのではないかと 目 > 論んだ次第です。 > > おそらくプロシージャを作る必要があるのではと思われますが、形だけでも ご教 > 授ください。 > > > -------------------------------------- Protect Your PC Now ! - 0 yen Security Campaign http://pr.mail.yahoo.co.jp/premium/
13862 2007-04-13 09:26 [<ml@xxxxxxxxxx> ] マトリックステーブルの作成方法 -> 13863 2007-04-13 13:33 ┗["F.Y" <fumi_sby@xxxx] 13949 2007-05-09 08:05 ┗[<ml@xxxxxxxxxx> ]