ソース表から集約されたデータを表に入力するためのMySQLクエリ -- mysql フィールド 関連 問題

A mysql query to fill a table with data aggregated from a source table












1
vote

問題

日本語

別のテーブルのフィールドから集約されたデータを含むテーブル(Freq)のフィールドを入力したい(TBL1)。新しいフィールドを更新しなければならない毎時スクラッチから準備するクエリでこれを行いました。 送信コードにテーブルとクエリの詳細が表示されます。 nフィールド値は1から10まで制限されています、あなたはそれを作成するルーチンを見ることができます。同じ値にも、TBL1からの値が制限されています。私は想像することができない単一の、より効果的なクエリで、サイクリングなしで同じ結果を得ることが可能であるのだろうか。

<事前> <コード> /*---- tbl1 ---*/ 1,id,db2020,tbl1,SMALLINT UNSIGNED,binary,5,3,0 /* same as latter table, used to avoid */ 2,Data,db2020,tbl1,DATE,binary,10,10,0 /* accidental data corruption of the same table */ 3,id2,db2020,tbl1,TINYINT UNSIGNED,binary,3,3,0 4,fld1,db2020,tbl1,TINYINT UNSIGNED,binary,3,2,0 5,fld2,db2020,tbl1,TINYINT UNSIGNED,binary,3,2,0 6,fld3,db2020,tbl1,TINYINT UNSIGNED,binary,3,2,0 /*--- freq ---*/ /* here aggregated data from tbl1 go to the corrensponding fields */ 1,N,db2020,freq,TINYINT UNSIGNED,binary,2,0,0 /* primary key, it's also the set of all possible values in the fields of tbl1*/ 2,fld1,db2020,freq,SMALLINT UNSIGNED,binary,4,0,0 /* the frequency of the N value in tbl1.fld1 */ 3,fld2,db2020,freq,SMALLINT UNSIGNED,binary,4,0,0 /* the frequency of the N value in tbl1.fld2 */ 4,fld3,db2020,freq,SMALLINT UNSIGNED,binary,5,0,0 /* the frequency of the N value in tbl1.fld3 */ 5,Tins,db2020,freq,INT UNSIGNED,binary,6,0,0 /* the sum of the values in fld1, fld2, fld3 */ CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_index`() /* to fill freq N field when the table is just created or empty */ BEGIN DECLARE a INT Default 1 ; simple_loop: LOOP insert into freq(N) values(a); SET a=a+1; IF a=11 THEN /* 10 is the max value for N */ LEAVE simple_loop; END IF; END LOOP simple_loop; END CREATE DEFINER=`root`@`localhost` PROCEDURE `freq_count`() /* aggregates tbl1 field values to freq table */ BEGIN /* it works, but i'd like to find something better */ DECLARE i INT Default 0; /* with no need to cycle for every new field, it looks slow */ DECLARE f_name text(3); DECLARE comando text(250); DECLARE astring text(10); fname_loop: LOOP SET i=i+1; IF i= 4 THEN LEAVE fname_loop; END IF; CASE WHEN i = 1 THEN SET f_name = 'fld1'; WHEN i = 2 THEN SET f_name = 'fld2'; WHEN i = 3 THEN SET f_name = 'fld3'; END CASE; IF (i < 4) THEN SET @comando = CONCAT('UPDATE freq, (select ',f_name, ', count(',f_name,') as tot',f_name, ' from tbl1 group by ',f_name,') as t2 ', 'SET freq.',f_name,' = t2.tot',f_name, ' WHERE freq.N = t2.',f_name,';'); /* select @comando; */ PREPARE stmt1 FROM @comando; EXECUTE stmt1; END IF; END LOOP; END

さらに、FREQテーブルを見れば、TINSフィールドが表示されます。このフィールドは、同じ行のフィールドの合計で、おそらく他のすべてのフィールドを更新するのと同じ操作で埋められます。私は次の別々のクエリを試しましたが、それは単一の行以上のために機能しません、そして代わりに誤った値でティンを埋めます。

<事前> <コード> CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_totals`() /* this would fill the Tins field */ BEGIN /* but works badly, only for a single row */ DECLARE i INT default 1; /* when in the loop fills Tins with wrong values */ DECLARE total INT default 0; /* it looks that total isn't cleared after the end of a cycle */ simple_loop: LOOP /* and anyway it tooks a lot of time */ set total = 0; UPDATE freq, (SELECT( if(fld1,fld1,0) + if(fld2,fld2,0) + if(fld3,fld3,0)) AS total FROM freq WHERE N = i) AS t2 SET freq.Tins = t2.total WHERE N = i; /* whithout the double where it definitely doesn't work a single time...*/ SET i= i+1; IF i=11 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END

あなたは私を助けてもらえますか?

親切な

更新: 私は最後の手続き「合計」を洗練することに成功しました 今それはうまく機能します:

<事前> <コード> /* a better approach to filling a summed field without need for loops*/ CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_totals`() BEGIN UPDATE freq, (SELECT( if(fld_1,fld_1,0) + if(fld_2,fld_2,0) + if(fld_3,fld_3,0)) /* if is to translate NULLs in zeroes in sum */ AS total, N AS N1 FROM freq WHERE N > 0) AS t2 SET freq.Tins = t2.total, WHERE freq.N = t2.N1; END

それは他の誰かに役立つことができることを願っています。

アップデート1: 私は同じように「入力インデックス」手順をよりよくコーディングすることに成功しました。 これが私のコードです:

<事前> <コード> /*---- a brute-force method to fill an index in a new or truncated table ----*/ /* should be refined using a variable to create the values list, but presently I didn't succeed */ /* in creating such a variable that 'values' syntax doesn't reject. A string doesn't work. */ CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_indice`() BEGIN insert into freq(N) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) END /*---- index fill end ----*/ /*---- a better solution to automatically fill an index field in a new or truncated table ---*/ CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_indice`() BEGIN declare cmd varchar(500); declare stp INT default 91; declare cntr int default 1; set @cmd = 'insert into freq(N) values '; while cntr < stp DO if ((stp - cntr) > 1) then set @cmd = CONCAT(@cmd, '(',cntr,'),'); else set @cmd = CONCAT(@cmd, '(',cntr,')'); /* this is to avoid the last comma, after the last row value */ end if; set cntr = cntr + 1; end while; /*select @cmd;*/ PREPARE stmt1 FROM @cmd; EXECUTE stmt1; END /*---- better fill end ----*/

そして現在、それはすべて...人です! ; - )

英語

I want to fill some fields of a table (freq) with aggregated data from fields of another table (tbl1). I did this with a query I prepare from scratch every time I have to update a new field. You can see the tables and query details in the code I submit. N field value is limited from 1 to 10, you can see the routine by which I create it. To the same value are also limited the values coming from tbl1. I wonder if it's possible to obtain same results without cycling, with a single, more effective query that I'm not able to imagine...

    /*---- tbl1 ---*/      1,id,db2020,tbl1,SMALLINT UNSIGNED,binary,5,3,0 /* same as latter table, used to avoid */      2,Data,db2020,tbl1,DATE,binary,10,10,0          /* accidental data corruption of the same table */     3,id2,db2020,tbl1,TINYINT UNSIGNED,binary,3,3,0     4,fld1,db2020,tbl1,TINYINT UNSIGNED,binary,3,2,0     5,fld2,db2020,tbl1,TINYINT UNSIGNED,binary,3,2,0     6,fld3,db2020,tbl1,TINYINT UNSIGNED,binary,3,2,0      /*--- freq ---*/                    /* here aggregated data from tbl1 go to the corrensponding fields */      1,N,db2020,freq,TINYINT UNSIGNED,binary,2,0,0   /* primary key, it's also the set of all possible values in the fields of tbl1*/     2,fld1,db2020,freq,SMALLINT UNSIGNED,binary,4,0,0 /* the frequency of the N value in tbl1.fld1 */     3,fld2,db2020,freq,SMALLINT UNSIGNED,binary,4,0,0 /* the frequency of the N value in tbl1.fld2 */     4,fld3,db2020,freq,SMALLINT UNSIGNED,binary,5,0,0 /* the frequency of the N value in tbl1.fld3 */     5,Tins,db2020,freq,INT UNSIGNED,binary,6,0,0      /* the sum of the values in fld1, fld2, fld3 */       CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_index`() /* to fill freq N field when the table is just created or empty */     BEGIN    DECLARE a INT Default 1 ;     simple_loop: LOOP                 insert into freq(N) values(a);        SET a=a+1;         IF a=11 THEN /* 10 is the max value for N */            LEAVE simple_loop;      END IF;    END LOOP simple_loop; END  CREATE DEFINER=`root`@`localhost` PROCEDURE `freq_count`() /* aggregates tbl1 field values to freq table */ BEGIN                                              /* it works, but i'd like to find  something better */ DECLARE i INT Default 0;                           /* with no need to cycle for every new field, it looks slow */ DECLARE f_name text(3); DECLARE comando text(250); DECLARE astring text(10);     fname_loop: LOOP                 SET i=i+1;        IF i= 4 THEN           LEAVE fname_loop;        END IF;        CASE         WHEN i = 1 THEN SET f_name = 'fld1';         WHEN i = 2 THEN SET f_name = 'fld2';             WHEN i = 3 THEN SET f_name = 'fld3';        END CASE;        IF (i < 4)  THEN          SET @comando = CONCAT('UPDATE freq, (select ',f_name,          ', count(',f_name,') as tot',f_name,          ' from tbl1 group by ',f_name,') as t2 ',          'SET freq.',f_name,' = t2.tot',f_name,          ' WHERE  freq.N = t2.',f_name,';');              /*           select @comando;  */          PREPARE stmt1 FROM @comando;          EXECUTE stmt1;                END IF;         END LOOP;  END 

Moreover, if you look to freq table, you'll see Tins field. This field should be filled with the sum of the fields in the same row, and possibly in the same operation that updates all other fields. I have tried the following separate query, but it doesn't work for more than a single line, and instead fills Tins with wrong values, maybe because I wasn't able to find a way to zero the 'total' variable among rows.

    CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_totals`() /* this would fill the Tins field */     BEGIN                                                      /* but works badly, only for a single row */      DECLARE i INT default 1;                                  /* when in the loop fills Tins with wrong values */      DECLARE total INT default 0;                              /* it looks that total isn't cleared after the end of a cycle */      simple_loop: LOOP                                         /* and anyway it tooks a lot of time */      set total = 0;      UPDATE freq, (SELECT( if(fld1,fld1,0) + if(fld2,fld2,0) + if(fld3,fld3,0)) AS total         FROM freq WHERE N = i) AS t2          SET freq.Tins = t2.total WHERE N = i;           /* whithout the double where  it definitely doesn't work a single time...*/         SET i= i+1;          IF i=11 THEN          LEAVE simple_loop;         END IF;        END LOOP simple_loop;            END    

Can you please help me?

Kind regards

UPDATE: I succeeded in refining the last procedure 'sum totals' Now it works well:

            /* a better approach to filling a summed field without need for loops*/                   CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_totals`()     BEGIN      UPDATE freq,      (SELECT( if(fld_1,fld_1,0) + if(fld_2,fld_2,0) + if(fld_3,fld_3,0)) /* if is to translate NULLs in zeroes in sum */       AS total, N AS N1      FROM freq      WHERE N > 0) AS t2      SET freq.Tins = t2.total,       WHERE freq.N = t2.N1;          END  

That in the hope it can turn useful to someone else.

UPDATE 1: I same way succeeded in better coding the 'fill index' procedure. Here's my code:

      /*---- a brute-force method to fill  an index in a new or truncated table  ----*/       /*  should be refined using a variable to create the values list, but presently I didn't succeed */      /* in creating such a variable that 'values' syntax doesn't reject. A string doesn't work. */       CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_indice`()      BEGIN        insert into freq(N) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)      END      /*----                  index fill end              ----*/     /*---- a better solution  to automatically fill an index field in a new or truncated table ---*/    CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_indice`()    BEGIN     declare cmd varchar(500);     declare stp INT default 91;     declare cntr int default 1;     set @cmd = 'insert into freq(N) values ';     while cntr < stp DO         if ((stp - cntr) > 1) then               set @cmd = CONCAT(@cmd, '(',cntr,'),');         else             set @cmd = CONCAT(@cmd, '(',cntr,')'); /* this is to avoid the last comma, after the last row value */         end if;        set cntr = cntr + 1;     end while;     /*select @cmd;*/    PREPARE stmt1 FROM @cmd;   EXECUTE stmt1;   END   /*----                        better fill end                        ----*/       

and, presently, that's all... folks! ;-)

</div
  

回答リスト


関連する質問

87  SQL Server 2005 MySQLの実装は?  ( Sql server 2005 implementation of mysql replace into ) 
MySQLには、これが非常に便利な REPLACE INTO sqlコマンドを持っています。 これはSQL Server 2005で簡単にエミュレートできますか? 新しいトランザクションの開始、<コード> Select() 、次に<コード> UPDATE...

136  DBスキーマの変更を追跡するためのメカニズム[閉]  ( Mechanisms for tracking db schema changes ) 
現在立つにつれて、この質問は私たちのQ&AMPにとって良いフィットではありません。フォーマット。私たちは事実、参考文献、または専門知識によって支持されることを期待していますが、この質問は...

0  Schema_miribations上の固有の制約のためにRails Unitテストが失敗する  ( Rails unit tests fail because of unique constraint on schema migrations ) 
rake test:units を実行しようとしています。 <事前> <コード> Mysql::Error: Duplicate entry '2147483647' for key 1: INSERT INTO `ts_schema_migrations...

317  パフォーマンスが低下する前にMySQLデータベースがどのくらいのBIGを取得するか  ( How big can a mysql database get before performance starts to degrade ) 
MySQLデータベースがパフォーマンスを低下させ始めるのはどのようなポイントで? 物理データベースのサイズが重要ですか? レコード数の問題は? は、任意の性能劣化線形または指数関数的ですか? 私は大規模なデータベースであると信じているものを持っています。...

39  PythonとMySQL  ( Python and mysql ) 
PostgreSQLを操作するようにPythonを取得することができますが、MySQLで動作することはできません。主な問題は、私がDjangoやPysqlなどのものをインストールする機能を持っていない共有ホスティングアカウントでは、私のコンピュータにインスト...

191  MySQLのバイナリデータ[閉じた]  ( Binary data in mysql ) 
閉鎖。この質問はもっと焦点を絞ったにする必要があります。現在答えを受け付けていません。 この質問を改善したいですか?この記事の編...

88  SQL Server 2005からMySQLにデータをエクスポートする方法[閉じた]  ( How to export data from sql server 2005 to mysql ) 
閉じたこの質問はオフトピックです。現在答えを受け付けていません。 この質問を改善したいですか?質問を更新するスタックオーバーフロ...

1  MySQL:v4からv5へのクエリの移行  ( Mysql migrating queries from v4 to v5 ) 
MySQL 4からMySQLのプロジェクトを移行するとき、クエリが互換性があることを確認するために私が対処する必要がある主なことは何ですか? 一般的なものは大丈夫ですが、私はMySQL 4のクエリに暗黙のうちに機能していることをMySQL 5で明示的に定義す...

177  MySQLトリガーにエラーをスローします  ( Throw an error in a mysql trigger ) 
<コード> M_PI3 <コード> M_PI4 がある場合は、そのテーブルの更新を妨げるエラーをスローできますか? ...

2  表1から表2まで2列の接合  ( Joining 2 columns from table1 to table 2 ) 
表2の2列に表1列を参照しますか? 私は50の正確な行でテーブル ' 'を作成しました '結婚式'テーブルに関連しようとしている(WeddingState、ContactState) これは私が作成した声明ですが、トップ WeddingState を正しく結...




© 2022 cndgn.com All Rights Reserved. Q&Aハウス 全著作権所有