ＡＣＣＥＳＳでの文字列の比較

はじめまして、こんにちは。ＡＣＣＥＳＳについて教えてください。あるテーブルに列が１、２とあったとします行は全部で80万行です。１の列にあるデータを一つづつ取り出して、２の列にあるデータと比較し、一致していれば真を返して、一致していなければ偽を返すプログラム（つまり80万×80万の計算になるとおもいます。）を組みたいのですがどうすればいいのでしょうか？エクセルだと簡単にＶＬＯＯＫＵＰ関数でできると思うのですが、アクセスでのやり方がわかりません。データも80万件もあるのでエクセルが使えません。何かヒントとなるホームページやキーワードを教えていただけないでしょうか。よろしくお願いします。

No.4 ベストアンサー 回答者： fuuten_no_neko 回答日時： 2005/08/27 10:34

面白いので試してみました。

80万行のデータは手持ちがなかったので、１２万件（郵便番号）をもとにしての試験です。
1050YENさんのクエリは正しく表示されます。しかし最後尾に移動しようとすると反応がなくなりました。実際にはCPUの稼働率が１００％になり、アクセスは必死に計算を繰り返していたのでしょう。

1050YENさんのクエリにケチを付ける気は毛頭ありません。それにeri1235さんの目的も今一つ判りませんが、迂回路を試してみました。

１．Temp(テーブル)のデータ削除
２．Tempに　列2 をグループ化して insert
３．もとテーブルの 真偽 フィールドをすべて False
４．もとテーブルの 真偽 フィールドを Temp とのリレーションを取れるものだけ True
この部分のSQL文は

UPDATE テーブル1 INNER JOIN temp ON テーブル1.列1 = temp.列2 SET テーブル1.真偽 = True;

です。処理時間は１回目
削除 200
追加 2844
初期化 9103
更新 25356

２回目
削除 10
追加 2613
初期化 7160
更新 14180
（ミリ秒）とキャッシュの影響か２回目以降の方がかなり速くなります。
環境：Pen4 1.5GH + 523KB RAM + Win2K SP2 + DP6 UP2
データの質やCPUパワーで大幅な変化があるかもしれないので、ご参考までに。

No.8 回答者： 1050YEN 回答日時： 2005/08/30 11:04

#fuuten_no_neko さん

>引き続き＃６を試験しました。
感謝です。

なんか質問者不在っぽいスレになっちゃいましたが、私から個人的にポイントあげたいと思うぐらい、調査の労力への感謝の気持ちで
「あざ～すっ」

とりあえず、ご苦労様の気持ちが言いたかったので、この発言は消されるかもしれませんが、書き込んで起きます。

この回答へのお礼

1050YENさん、ご返事遅くなって申し訳ございません。
やっと時間がとれたので、お礼をさせていただきます。
私の質問に真摯にお答えいただきありがとうございました。
最後のほうは私のレベルではついていけない話でしたが、今後勉強して皆様のように話ができるレベルになりたいです。
本当にありがとうございました。

お礼日時：2005/10/22 11:58

No.7 回答者： fuuten_no_neko 回答日時： 2005/08/30 10:50

引き続き＃６を試験しました。

環境：Pen4 1.5GH + 523KB RAM + Win2K SP2 + DP6 UP2
データベース接続：ADO Provider=Microsoft.Jet.OLEDB.4.0
データ数：119653

データ操作
１．初期化クエリ
UPDATE テーブル1 SET テーブル1.真偽 = False;
２．更新クエリ
UPDATE テーブル1 SET 真偽 = True WHERE Exists (SELECT dummy.[列1] FROM テーブル1 as dummy WHERE dummy.列2 = テーブル1.列1);

実行時間
初期化　2714
更新　 7481
（ミリ秒。測定ごとに値は変化した）
トータル時間が短く、余計なテーブルを作成しないからこちらの方が LEFT JOIN 方式より良さそうです。

＃５で Exists 方式を試験しなかったのは理由がありました。＃４にも書いたように、最後尾まで表示できなかったためです。上記の結果から何か手違いがあったかと、再度アクセスのクエリ・デザインビューで、＃３のSQL文をコピーペースとして試験しました。頭の部分を表示後最後尾に移動しようとすると、砂時計アイコンが出、CPU稼働率が１００％になったままアクセスは反応しなくなりました。そのまま外出して２時間後に戻っても同じ状態です。

現状でまとめると
A.Existを利用した更新クエリ
長所：データの編集や絞り込みなど使い回しがきく
短所：時間が掛かる。表示までの時間を試験結果から単純に比例計算すると約７０秒

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さん。
こんなにも詳しく研究していただきまことにありがとうございます。また、回答が遅くなりまことに申し訳ございません。
私のレベルでは、ここまでの話はわかりませんが、もっと勉強して皆様のようなすばらしい技術者になりたいです。
今回は本当にありがとうございました。

お礼日時：2005/10/22 12:01

No.6 回答者： 1050YEN 回答日時： 2005/08/29 15:44

#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万チームのリーグ戦が行われるイメージになると思います。またグループ化も結構かかりますよ。

No.5 回答者： fuuten_no_neko 回答日時： 2005/08/28 12:09

＃４です。

面白いので引き続き試してみました。現状で判ったことは

Ａ．データを選択・表示するだけならば 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;

＃２に示されたＳＱＬ文もよいのですが、最後にグループ化しているために列1に重複があると不都合が出る場合があります。それと編集に移行しようとしたときも具合が悪い。
IIFは必要ないでしょう。=,Exist,IsNull　いずれも真偽を返すので、そのまま使った方が効率がよい。

Ｂ．編集が必要ならばインデックスの付いた仮テーブルが必要

これは仕様上そうなっているのか、良く判りませんが、試行錯誤の結果ではそうなりました。
上記に示したクエリ、あるいは＃２で提示されたクエリでは編集できませんでした。
そこで

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を変えるのは無意味ですが。

No.3 回答者： 1050YEN 回答日時： 2005/08/27 02:18

質問者の言いたいことは、#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

No.2 回答者： manbow_ 回答日時： 2005/08/27 01:32

ご苦労お察しします。

質問の意図は「１の列のデータが２の列のいずれかと一致する場合に真を返す」という意味ではないでしょうか。だったら同じテーブル同士を左側外部結合する必要があります。（列１、列２）のテーブルに対し次のクエリで求める結果が出てくると思います。

SELECT テーブル.列１, テーブル_1.列２, IIf([テーブル]![列１]=[テーブル_1]![列２],"真","偽") AS 式1
FROM テーブル LEFT JOIN テーブル AS テーブル_1 ON テーブル.列１ = テーブル_1.列２
GROUP BY テーブル.列１, テーブル_1.列２;

アクセスの使い方はいろいろな人がWEB上に公開されています。「ＡＣＣＥＳＳ　クエリの書き方」とかでけんさくしてみてください。ご自分にぴったりのページが見つかると思います。たとえば↓などです。

http://www.accessclub.jp/index.html

この回答へのお礼

ご返事が遅くなりまことに申し訳ございません。
詳しくクエリをかいていただきありがとうございました。
なんとか問題も解決できました。

お礼日時：2005/10/22 12:02

No.1 回答者： kiyama_t 回答日時： 2005/08/27 00:34

クエリでもよければ。

select 列１,列２,iif(列１=列２,"真","偽") from テーブル
これを実行すれば、一覧形式で出ますよ？

この回答へのお礼

ご返事が遅くなりまことに申し訳ございません。
詳しくクエリをかいていただきありがとうございました。
なんとか問題も解決できました。

お礼日時：2005/10/22 12:03