MS Access Tips/Sample and VBA and Blog customize etc...

First および Last 関数の仕様

クエリの集計の「最初」と「最後」、あるいは、DFirst、DLast関数も同じなのですが、これらの関して期待した値が取得できないという質問を掲示板で見かけます。これらの仕様に対する誤解が原因の場合がほとんどですが、ヘルプやMSサポートオンラインのこれらの仕様についての解説が、非常にわかりにくいものだったり、間違ったものになっているので、誤解するのも無理がないでしょう。

とういことで、この仕様について考察してみます。

難易度:

最初に断っておきますが、MSの情報が間違っている可能性大なのですが、実際のMDBの挙動から私が勝手に推測したものなので、私の解釈が間違っている可能性も大です。もし、おかしな点に気づかれたらどしどしつっこんでください。

まず、Accessのヘルプ First 関数および Last 関数より、引用。

解説
First 関数および Last 関数は、DAO の Recordset オブジェクトの MoveFirst メソッドおよび MoveLast メソッドに似ています。これら 2 つの関数は、クエリが返した結果セットの先頭のレコードまたは末尾のレコードに指定しているフィールド値を返すだけです。これらの関数が返すレコードの順序に特定の法則はなく、クエリに ORDER BY 句の記述がない限り、順序に意味はありません。

この解説を読むと、クエリでORDER BY 句が設定してあれば、その並び順においての先頭と最後を返しそうに思えます。が、実際に試してみるとそうはなりません。

MSサポートオンラインの [ACC2000] First および Last 関数が期待どおりのレコードを返さない より引用

原因
First()、Last()、DFirst()、および DLast() 関数では、並べ替え順、インデックス、および主キーが無視されます。これらの関数は、指定され...First()、Last()、DFirst()、および DLast() 関数では、並べ替え順、インデックス、および主キーが無視されます。これらの関数は、指定された並べ替え順での先頭または最後のレコードではなく、レコードがテーブルに入力されたときの順序に基づいて、削除されていない先頭または最後のレコードを返します。

「並べ替え順、インデックス、および主キーが無視」されると書いてあります。どうやら先記のヘルプは間違っているようですね。で、この情報では「テーブルに入力されたときの順序に基づいて」と書いてあるので、データの入力順の先頭と最後を返すと理解できますよね。

ところが、新規MDBでデータ入力して確認してみると期待した値を返すので安心して、実運用に移行すると、ある日、突然、期待する値を返さなくなってあわてることになります。気がつけばいいですが、気がつかずに間違ったデータのまま運用をし続けたという悲惨なことになる場合もあるかもしれません。

Access Clubの掲示板にも、そのような事態におちいって質問してきたスレッドがいくつかあります。
No28009.Dlast関数をつかった前のレコードの引継ぎについて
No42347.Dlastで継承したデータが全てクリアされる・・・・
No21419.前のレコードの引継ぎ

MSサポートオンラインの [ACC2003] MDB のレコードの並び順について より引用

原因
Access で並び順を指定していないクエリを実行した場合、返されるレコードの並び順に特定の規則はありません。そのため、このようなクエリでのデータの並び順は保証されていません。
Access 等のリレーショナル型データベースでは、データの登録順序を保持することはありません。物理的な (例えば、ハードディスク等) 配置に関しては、その空き領域の状態により格納先が判断されます。

「データの登録順序を保持することはありません」と明言しています。これが事実なら、入力順に基づいて先頭レコードとか最終レコードを特定することなどできるはずがありません。先のMS情報と矛盾してますが、現象から見るとこちらの情報の方が正しいと思われます。

まとめると、First(), Last(), DFirst(), DLast() は、並び替え順の指定やインデックス、主キーの設定を無視する。また、入力順も関係ない。ディスク上に格納されている順の先頭と最後を返す。ということのようです。

この理由を私なりに推測してみました。

データベースは大量のデータを効率よく扱うという性質上、入力したデータはいちばん効率のいい場所に格納します。 新規MDBでテーブルを作成した直後はディスク上に順に格納していきます。
データを削除したとき、ディスク上からデータを削除して空いた隙間を詰めるということは効率が悪いのでしません。 削除したというマークを付けるだけです。これは、大量のデータを削除してもファイルサイズに変化がないことから うかがえます。また、最適化はこの削除したときにあいた隙間を詰める処理といえます。
削除した隙間が空いているとき、その隙間が新規データが入るサイズ以上あればそこに新規データを上書きすることもあります。ディスク上のどこに格納するかは、入力順とかは考慮せずにAccessがいちばん効率がいいと判断した場所に格納します。
リレーショナルデータベースは一般的なこのようにデータ格納します。Accessもリレーショナルデータペーズなので例外ではないでしょう。

では、特定の並び順に基づいて先頭、最後を求めたいときはどうするかというと、上記の [ACC2000] First および Last 関数が期待どおりのレコードを返さない に記載されているように、クエリのトップ値を利用する、Min, Max関数で代用する、独自関数でMoveFirst, MoveLast を利用する、ことになります。また、入力順を基準にしたい場合は、オートナンバー型、あるいは入力日時など、入力順を格納するフィールドを用意することになります。

ところで、このような仕様の First(), Last(), DFirst(), DLast() にはどのような場合に使えばいいのでしょうか。

特定の並び順あるいは入力順の最初とか最後のデータを取得するというような目的には使えませんよね。 特定の条件に合うデータでどれでもいいのでとにかく代表値として一つ取得したいというとき、とか、別のキーフィールドでグループ化されていてそのグループ内のフィールド値はすべて同じと保証されている、あるいは1件しかないと保証されている、というときぐらいしか使い道はないと思われます。(クロス集計クエリなどでこのような場合がよくあります。)この場合は、並び替える必用がなくディスクの格納順に検索していくのでMaxやMinより高速に見つけることができると思われます。

拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/169-822bdb45
該当の記事は見つかりませんでした。