分類付き m_byochu

  • このフォーラムに新しいトピックを立てることはできません
  • このフォーラムではゲスト投稿が禁止されています
前の投稿 - 次の投稿 | 親投稿 - 子投稿.1 .2 | 投稿日時 2010.11.29 18:45
OhYeah!  管理人   投稿数: 986 オンライン
m_byochu の idbyochu を m_sakumotsu の idsaku と同様に、病害虫カテゴリ、害虫グループ、カテゴリ・グループごとの連番からなる8桁の ID になるように変更しました。ふりがなや別名の処理も行っています。
ただし、ふりがなについては、m_sakumotsu と違ってひらがなに統一されておらず、ひらがな・カタカナ混在です。新しい ACFinder が公開されれれば、ひらがなに統一できます。

かなり長くなったので、下記 URL からダウンロードしてください。m_tekiyo への idbyochu 書き戻しも行っていますので、そのまま ACFinder でお使いいただけます。
http://macs.o-ya.net/data/m_byochu.qry

しかし、自宅 PC だとそれほど遅くないのですが、事務所の PC だと m_tekiyo への idbyochu 書き戻しに恐ろしく時間がかかります。m_sakumotsu も含めて、m_tekiyo への id 書き戻し方法は要検討です。
投票数:0 平均点:0.00
前の投稿 - 次の投稿 | 親投稿 - 子投稿.1 | 投稿日時 2010.11.30 02:19
OhYeah!  管理人   投稿数: 986 オンライン
引用:しかし、自宅 PC だとそれほど遅くないのですが、事務所の PC だと m_tekiyo への idbyochu 書き戻しに恐ろしく時間がかかります。m_sakumotsu も含めて、m_tekiyo への id 書き戻し方法は要検討です。自宅 PC では、書き戻しパターン1で 13 秒弱、パターン2で 12 秒弱、パターン3で 11 秒前後です。事務所 PC は自宅 PC の2倍近く処理時間がかかるので、パターン3なら4秒近く短縮できるかもしれませんが、焼け石に水って感じです。それに、パターン3だと、m_sakumotsu と m_byochu のいずれかのみ更新するときと、両方更新するときで処理を変えなければなりません。
ってことで、m_tekiyo に idsaku, idbyoshu を書き戻さなくてすむようにするのが一番って結論になりました

パターン1
begin transaction;
update m_tekiyo set idbyochu = (select idbyochu from m_byochu where byochu = m_tekiyo.byochu);
commit;

パターン2
create temp table if not exists t_tekiyo as select * from m_tekiyo;
begin transaction;
update t_tekiyo set idbyochu = (select idbyochu from m_byochu where byochu = t_tekiyo.byochu);
commit;
drop table if exists m_tekiyo;
create table m_tekiyo as select * from t_tekiyo;
drop table if exists t_tekiyo;

パターン3
create temp table if not exists t_tekiyo as select bango,idsaku,sakumotsu,basho,b.idbyochu as idbyochu,byochu,mokuteki,baisu,ekiryo,jiki,kaisu,hoho,jikan,ondo,dojo,chitai,tekiyaku,kaisu1,kaisu2,kaisu3,kaisu4,kaisu5 from m_tekiyo left join m_byochu as b using (byochu);
drop table if exists m_tekiyo;
create table m_tekiyo as select * from t_tekiyo;
drop table if exists t_tekiyo;
投票数:0 平均点:0.00
前の投稿 - 次の投稿 | 親投稿 - 子投稿なし | 投稿日時 2010.11.30 02:41 | 最終変更
OhYeah!  管理人   投稿数: 986 オンライン
では、m_tekiyo に idsaku と idbyochu を書き戻さなくてもすむようにするにはどうするか?

現行の作物検索方法であるパターン1だと、自宅 PC で 0.33 秒程度です。
idsaku, idbyochu を m_sakumotsu, m_byochu から JOIN する tekiyo2 を使ってパターン1と同じ方法で検索するパターン2だと 0.75 秒程度と、ちょっと一息つく感じになります。それに、この方法だと、m_tekiyo から idsaku, idbyochu を引っ張ってきているビューは全て書き直す必要があるのも辛いところです。
で、tekiyo ビューを使って idsaku ではなく sakumotsu で検索するパターン3なら、0.4 秒弱でパターン1と体感差はありません。この方法だと、検索文中の idsaku, idbyochu をそれぞれ sakumotsu, byochu に変更するだけですみます。m_tekiyo や各種ビューに意味をなさない idsaku, idbyochu が残ってしまいますが、まあおいおい修正していけばよいかと…。

パターン1
BEGIN TRANSACTION;
DROP TABLE IF EXISTS ttTekiyoSaku;
CREATE TEMP TABLE ttTekiyoSaku AS SELECT * FROM tekiyo WHERE idsaku IN(SELECT idsaku FROM m_sakumotsu WHERE sakumotsu REGEXP '(^|、|\()(キャベツ|あぶらな科野菜|野菜(類)?).*?(\)|、|$)' AND sakumotsu NOT REGEXP '\((.*、)?(キャベツ|あぶらな科野菜|野菜(類)?)(、.*)?を除く');
COMMIT TRANSACTION;

パターン2
BEGIN TRANSACTION;
DROP TABLE IF EXISTS ttTekiyoSaku;
CREATE TEMP TABLE ttTekiyoSaku AS SELECT * FROM tekiyo2 WHERE idsaku IN(SELECT idsaku FROM m_sakumotsu WHERE sakumotsu REGEXP '(^|、|\()(キャベツ|あぶらな科野菜|野菜(類)?).*?(\)|、|$)' AND sakumotsu NOT REGEXP '\((.*、)?(キャベツ|あぶらな科野菜|野菜(類)?)(、.*)?を除く');
COMMIT TRANSACTION;

パターン3
BEGIN TRANSACTION;
DROP TABLE IF EXISTS ttTekiyoSaku;
CREATE TEMP TABLE ttTekiyoSaku AS SELECT * FROM tekiyo WHERE sakumotsu IN(SELECT sakumotsu FROM m_sakumotsu WHERE sakumotsu REGEXP '(^|、|\()(キャベツ|あぶらな科野菜|野菜(類)?).*?(\)|、|$)' AND sakumotsu NOT REGEXP '\((.*、)?(キャベツ|あぶらな科野菜|野菜(類)?)(、.*)?を除く');
COMMIT TRANSACTION;

tekiyo2
CREATE VIEW tekiyo2 AS
SELECT
bango, shurui, meisho, tsusho, b.idsaku as idsaku, a.sakumotsu as sakumotsu, c.idbyochu as idbyochu, a.byochu as byochu,
mokuteki, jiki, baisu, ekiryo, hoho, basho, jikan, ondo, dojo, chitai,
tekiyaku, kongo, kaisu,
seibun1, keito1, kaisu1,
seibun2, keito2, kaisu2,
seibun3, keito3, kaisu3,
seibun4, keito4, kaisu4,
seibun5, keito5, kaisu5,
yoto, koka, zaikei, ryakusho
FROM m_tekiyo as a LEFT JOIN m_kihon USING(bango), m_sakumotsu as b USING(sakumotsu), m_byochu as c USING(byochu);
投票数:0 平均点:0.00
前の投稿 - 次の投稿 | 親投稿 - 子投稿なし | 投稿日時 2010.12.01 23:17 | 最終変更
OhYeah!  管理人   投稿数: 986 オンライン
分類付き m_sakumotsu 確定仕様版」でも書いたように、m_tekiyo への idbyochu の書き戻しがなんとか我慢できる時間で可能になりました。ってことで、下記を確定仕様とします。仕様は確定しましたが、内容はまだ変更する可能性があります(特に cid)。

下記 URL で表示されるクエリを「全て選択」 → 「コピー」 → ACFinder の SQL タブに「貼り付け」 → ACFinder の SQL タブで「実行」という手順で、新しい m_byochu が使えます。ACFinder 本体が、まだ新しい m_byochu クエリーを使用するようになっていないので、今のところデータ更新の度にこの操作を行う必要があります。
http://macs.o-ya.net/data/m_byochu.qry

なお、今のところ betsumei のふりがなはカタカナ/ひらがな混在となっています。カタカナ → ひらがな変換に対応した ACFinder が公開されれば、betsumei は全てひらがなに統一します。

SQL タブで
--/d
create temp view if not exists byochu as select idbyochu, cname as 区分, gname as グループ, byochu, betsumei from m_byochu left join spec.cbyochu using (cid) left join spec.ggaichu using (gid);
を実行しておくと、
select * from byochu;
で病害/害虫などの区分名や害虫のグループ名も表示可能となります。

>kabe さん
病害虫タブの検索は、下記のように修正してください。
CREATE TEMP TABLE ttTekiyoByochu AS SELECT * FROM tekiyo  WHERE idbyochu IN(SELECT idbyochu FROM m_byochu WHERE concat('、', byochu, betsumei) MATCH '%病害虫名%');
投票数:0 平均点:0.00

  条件検索へ