- ベストアンサー
ACCESSでの文字列の比較
はじめまして、こんにちは。ACCESSについて教えてください。あるテーブルに列が1、2とあったとします行は全部で80万行です。1の列にあるデータを一つづつ取り出して、2の列にあるデータと比較し、一致していれば真を返して、一致していなければ偽を返すプログラム(つまり80万×80万の計算になるとおもいます。)を組みたいのですがどうすればいいのでしょうか?エクセルだと簡単にVLOOKUP関数でできると思うのですが、アクセスでのやり方がわかりません。データも80万件もあるのでエクセルが使えません。何かヒントとなるホームページやキーワードを教えていただけないでしょうか。よろしくお願いします。
- みんなの回答 (8)
- 専門家の回答
質問者が選んだベストアンサー
- ベストアンサー
面白いので試してみました。80万行のデータは手持ちがなかったので、12万件(郵便番号)をもとにしての試験です。 1050YENさんのクエリは正しく表示されます。しかし最後尾に移動しようとすると反応がなくなりました。実際にはCPUの稼働率が100%になり、アクセスは必死に計算を繰り返していたのでしょう。 1050YENさんのクエリにケチを付ける気は毛頭ありません。それにeri1235さんの目的も今一つ判りませんが、迂回路を試してみました。 1.Temp(テーブル)のデータ削除 2.Tempに 列2 をグループ化して insert 3.もとテーブルの 真偽 フィールドをすべて False 4.もとテーブルの 真偽 フィールドを Temp とのリレーションを取れるものだけ True この部分のSQL文は UPDATE テーブル1 INNER JOIN temp ON テーブル1.列1 = temp.列2 SET テーブル1.真偽 = True; です。処理時間は1回目 削除 200 追加 2844 初期化 9103 更新 25356 2回目 削除 10 追加 2613 初期化 7160 更新 14180 (ミリ秒)とキャッシュの影響か2回目以降の方がかなり速くなります。 環境:Pen4 1.5GH + 523KB RAM + Win2K SP2 + DP6 UP2 データの質やCPUパワーで大幅な変化があるかもしれないので、ご参考までに。
その他の回答 (7)
- 1050 円(@1050YEN)
- ベストアンサー率69% (477/687)
#fuuten_no_neko さん >引き続き#6を試験しました。 感謝です。 なんか質問者不在っぽいスレになっちゃいましたが、私から個人的にポイントあげたいと思うぐらい、調査の労力への感謝の気持ちで 「あざ~すっ」 とりあえず、ご苦労様の気持ちが言いたかったので、この発言は消されるかもしれませんが、書き込んで起きます。
引き続き#6を試験しました。 環境:Pen4 1.5GH + 523KB RAM + Win2K SP2 + DP6 UP2 データベース接続:ADO Provider=Microsoft.Jet.OLEDB.4.0 データ数:119653 データ操作 1.初期化クエリ UPDATE テーブル1 SET テーブル1.真偽 = False; 2.更新クエリ UPDATE テーブル1 SET 真偽 = True WHERE Exists (SELECT dummy.[列1] FROM テーブル1 as dummy WHERE dummy.列2 = テーブル1.列1); 実行時間 初期化 2714 更新 7481 (ミリ秒。測定ごとに値は変化した) トータル時間が短く、余計なテーブルを作成しないからこちらの方が LEFT JOIN 方式より良さそうです。 #5で Exists 方式を試験しなかったのは理由がありました。#4にも書いたように、最後尾まで表示できなかったためです。上記の結果から何か手違いがあったかと、再度アクセスのクエリ・デザインビューで、#3のSQL文をコピーペースとして試験しました。頭の部分を表示後最後尾に移動しようとすると、砂時計アイコンが出、CPU稼働率が100%になったままアクセスは反応しなくなりました。そのまま外出して2時間後に戻っても同じ状態です。 現状でまとめると A.Existを利用した更新クエリ 長所:データの編集や絞り込みなど使い回しがきく 短所:時間が掛かる。表示までの時間を試験結果から単純に比例計算すると約70秒 B.LEFT JOIN を利用した選択クエリ SELECT テーブル1.列1, テーブル1.列2, IsNull([テーブル1_1].[列2]) AS 真偽 FROM テーブル1 LEFT JOIN テーブル1 AS テーブル1_1 ON テーブル1.列1 = テーブル1_1.列2; 長所:表示までの時間が比較的早い 短所:データを編集できない なお、細かい差ですが、更新クエリでも「更新」そのものは LEFT JOIN の方が早いです。ここら辺から >個人的にですが、連結句はこういう膨大なデータの時、キーマッチできない場合は極力避けた方がよいのでは? というのは、存在する/しない関係なく、データマトリックス分の演算を行うため、80万件のデータであれば、80万チームのリーグ戦が行われるイメージになると思います。またグループ化も結構かかりますよ。 は少し違うようです。データベースはあらかじめソートして「リーグ戦」にならないよう処理しているようです。 ちなみに「列1」「列2」にインデックスは掛かっていませんが、かけてみると更新クエリの処理時間が1/3ぐらいに短縮されました。 ともかく Exist の「選択」と「更新」のあまりな違いなど、色々勉強になりました。
お礼
fuuten_no_nekoさん。 こんなにも詳しく研究していただきまことにありがとうございます。また、回答が遅くなりまことに申し訳ございません。 私のレベルでは、ここまでの話はわかりませんが、もっと勉強して皆様のようなすばらしい技術者になりたいです。 今回は本当にありがとうございました。
- 1050 円(@1050YEN)
- ベストアンサー率69% (477/687)
#3です。 #fuuten_no_neko さんのような、別オブジェクトやカラムを利用してよいなら、別方法もありますよ^^; まず、構造です。 ※[テーブル1]テーブル構造 [列1]テキスト型 [列2]テキスト型 [真偽]テキスト型 ← これを前回はクエリにて、再現させておりましたが、カラムとして実在させます。 ※マクロ マクロにて、3つのSQL文を発行し、テーブル1の[真偽]フィールドに、"真" or "偽"という値を更新する方法です。 以下の設定をしたマクロを作成してください。 1.アクション:エコー エコーの設定:いいえ 2.アクション:メッセージの設定 メッセージの表示:いいえ 3.アクション:SQLの実行 SQLステートメント:UPDATE テーブル1 SET 真偽 = Null; 4.アクション:SQLの実行 SQLステートメント:UPDATE テーブル1 SET 真偽 = '真' WHERE Exists (SELECT dummy.[列1] FROM テーブル1 as dummy WHERE dummy.列2 = テーブル1.列1); 5.アクション:SQLの実行 SQLステートメント:UPDATE テーブル1 SET 真偽 = '偽' WHERE 真偽 IS NULL 6.アクション:メッセージの設定 メッセージの表示:はい 7.アクション:エコー エコーの設定:はい あとは実行するだけです。 --------------------------------------------------------------------------------------------------- 個人的にですが、連結句はこういう膨大なデータの時、キーマッチできない場合は極力避けた方がよいのでは? というのは、存在する/しない関係なく、データマトリックス分の演算を行うため、80万件のデータであれば、80万チームのリーグ戦が行われるイメージになると思います。またグループ化も結構かかりますよ。
#4です。面白いので引き続き試してみました。現状で判ったことは A.データを選択・表示するだけならば LEFT JOIN を使用するのが効率よい クエリ1 SELECT テーブル1.列2 FROM テーブル1 GROUP BY テーブル1.列2; クエリ2 SELECT テーブル1.*, IsNull([クエリ1].[列2]) AS 式1 FROM テーブル1 LEFT JOIN クエリ1 ON テーブル1.列1 = クエリ1.列2 ORDER BY テーブル1.ID; #2に示されたSQL文もよいのですが、最後にグループ化しているために列1に重複があると不都合が出る場合があります。それと編集に移行しようとしたときも具合が悪い。 IIFは必要ないでしょう。=,Exist,IsNull いずれも真偽を返すので、そのまま使った方が効率がよい。 B.編集が必要ならばインデックスの付いた仮テーブルが必要 これは仕様上そうなっているのか、良く判りませんが、試行錯誤の結果ではそうなりました。 上記に示したクエリ、あるいは#2で提示されたクエリでは編集できませんでした。 そこで INSERT INTO temp ( 列2 ) SELECT テーブル1.列2 FROM テーブル1 GROUP BY テーブル1.列2; と、tempテーブルに出力します。この時重要なのが temp テーブルの 列2 フィールドがインデックスになっていることです。 この temp を利用して SELECT テーブル1.*, IsNull([temp].[列2]) AS 式1 FROM テーブル1 LEFT JOIN temp ON テーブル1.列1 = temp.列2 ORDER BY テーブル1.ID; のクエリでデータを編集することが出来ました。 ちなみにこの方式ですと、列1を変更して列2に含まれる(含まれない)で、ダイナミックに判定が変わります。当然のことながら列2を変えるのは無意味ですが。
- 1050 円(@1050YEN)
- ベストアンサー率69% (477/687)
質問者の言いたいことは、#2さんの言っている通りだとすると、 「列1のデータが、別レコードでもいいから、同テーブル列2内に存在するか」 ってことですよね? [テーブル1]に対しての結果イメージ 列1 列2 結果 A A ○ B C × C D ○ D E ○ E G ○ F Z × G A ○ ってな感じで であればExistsを利用し、存在チェックを行うだけです。 Accessなので、後はIIFによる分岐です。 SELECT 列1 ,列2 ,IIF(Exists(SELECT * FROM テーブル1 AS ダミー WHERE 列2 = テーブル1.列1),'真','偽') AS [真偽] FROM テーブル1
- manbow_
- ベストアンサー率36% (15/41)
ご苦労お察しします。 質問の意図は「1の列のデータが2の列のいずれかと一致する場合に真を返す」という意味ではないでしょうか。だったら同じテーブル同士を左側外部結合する必要があります。(列1、列2)のテーブルに対し次のクエリで求める結果が出てくると思います。 SELECT テーブル.列1, テーブル_1.列2, IIf([テーブル]![列1]=[テーブル_1]![列2],"真","偽") AS 式1 FROM テーブル LEFT JOIN テーブル AS テーブル_1 ON テーブル.列1 = テーブル_1.列2 GROUP BY テーブル.列1, テーブル_1.列2; アクセスの使い方はいろいろな人がWEB上に公開されています。「ACCESS クエリの書き方」とかでけんさくしてみてください。ご自分にぴったりのページが見つかると思います。たとえば↓などです。 http://www.accessclub.jp/index.html
お礼
ご返事が遅くなりまことに申し訳ございません。 詳しくクエリをかいていただきありがとうございました。 なんとか問題も解決できました。
- kiyama_t
- ベストアンサー率25% (19/74)
クエリでもよければ。 select 列1,列2,iif(列1=列2,"真","偽") from テーブル これを実行すれば、一覧形式で出ますよ?
お礼
ご返事が遅くなりまことに申し訳ございません。 詳しくクエリをかいていただきありがとうございました。 なんとか問題も解決できました。
お礼
1050YENさん、ご返事遅くなって申し訳ございません。 やっと時間がとれたので、お礼をさせていただきます。 私の質問に真摯にお答えいただきありがとうございました。 最後のほうは私のレベルではついていけない話でしたが、今後勉強して皆様のように話ができるレベルになりたいです。 本当にありがとうございました。