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

更新クエリで定義域集計関数を使わずに集計する

集計クエリで、抽出条件が複雑だったり、外部接続でインデックスが利用できないなどの原因で、処理が重くなる場合があります。ですので集計結果を親テーブルの方に書き込んでおきたいという要望が出る場合があります。

集計クエリあるいはそれを含むクエリは更新できません。ですのでこれを更新クエリにして実行しても「更新できません」と怒られます。DCountやDSumなどの定義域集計関数を使用して更新クエリにすれば更新できます。しかし、定義域集計関数は重いので処理に時間がかかります。

例えば、下記のような場合。

アクセス2010 他テーブルを参照してCount | OKWave

更新クエリで集計

難易度:

概要

上記の質問にDCountを使った更新クエリで回答したのですが、その後、処理に時間がかかるので、もっと速くできないかと、再質問されました。

アクセス2010 他テーブルを参照してCount | OKWave

そこで思いついたのが、更新クエリで集計関数や集計クエリを使わずに集計する方法。

T_売上
商品ID件数金額計
100
200
300

追記 2012/03/06: 件数 と 金額計 の「既定値」プロパティを 0、「値要求」プロパティを「はい」に設定しておきます。

T_売上明細
商品ID金額
1100
1200
1300
2150
2250
3300
3100
3120
3300

と言うテーブルがあり、T_売上 の 件数 と 金額計 に集計結果を書き込む。

T_売上
商品ID件数金額計
13600
22400
34820

更新クエリでDCount, DSumを使用

更新クエリでは集計クエリは使えません。更新できないと怒られます。そこで、DCount, DSum等の定義域集計関数を使うことになります。

DCount, DSum使用

DCount, DSumを不使用で集計する方法

DCount, DSumを使わずに

売上明細のレコードの値を順に加算しているだけの単純なクエリです。D系関数を使うよりかなり速く処理できます。

動作原理の解説

2012/03/11 追記:

上記のクエリの出力結果は、下記のようになります。

商品ID金額件数金額計
11000 0
12000 0
13000 0
21500 0
22500 0
33000 0
31000 0
31200 0
33000 0

例えば、T_売上の商品IDが 1 のレコードは1件ですが、T_売上明細と結合することで、3件に増えています。これは、実際にレコードが増えたわけではなく1件のレコードが繰り返し出力されているにすぎません。

これを更新クエリにすると、1行ずつ処理をしていきますので、T_売上.商品IDが 1 のレコードに対して3回繰り返し更新することになります。SET の式は現在の値に加算していく式になっていますので、件数フィールドには、1を3回加算することになり、金額計フィールドには、カレントの金額が加算されていきます。同様にT_売上.商品IDが 2 のレコードには2回繰り返して加算されます。これは商品IDの並び順には影響をうけません。

更新イメージ
商品ID金額件数金額計
11001100
12002300
13003600
21501150
22502400
33001300
31002400
31203520
33004820

拍手する

5 Comments

korune says..."SQL UPDATE"

これはレコードの更新が商品ID順に処理されるとは限りませんから、
正しくない気がします

2012.03.11 14:20 | URL | #/.OuxNPQ [edit]
hatena says..."re: SQL UPDATE"

商品ID順には影響を受けません。

記事に動作原理の解説を追記しましたので、参照ください。

2012.03.11 23:10 | URL | #5uE6dEgY [edit]
korune says..."re:re: SQL UPDATE"

何時も勉強になります

「上記のクエリの出力結果は、下記のようになります。 」
に示された出力結果は、その後のUPDATEで上から順番に処理されるのですかね?
というか、HDDやメモリ内の物理的レコード並び順がそうなってるとは限らない気がするのですが
JOINしながらUPDATEする記述も見つけられませんし、ブラックボックス
Access97以前のJETでは、こういう処理が可能だった記述を読んだことはあります
うーん、私には難しいです、スミマセン

2012.03.12 09:03 | URL | #/.OuxNPQ [edit]
hatena says..."re:re:re: SQL UPDATE"

> というか、HDDやメモリ内の物理的レコード並び順がそうなってるとは限らない気がするのですが

記事の例ではID順に表示してありますが、ID順ではないとしても、
リンクした同じIDに加算していくだけの処理ですので、
結果は同じになります。単純な足し算なので足す順番には
結果は依存しません。

1行に対して複数回更新が実行されたりすることがあるのなら、
合わなくなりますが、そのようなことがあるとは考えにくいです。

いくつかサンプルを作成して実験もしていますが、
問題なく正しい結果になります。

2012.03.12 13:05 | URL | #5uE6dEgY [edit]
korune says..."re:re:re:re: SQL UPDATE"

有難うございます

なんとなく解ってきたような気がします
いつも勉強になって感謝しております

2012.03.12 13:30 | URL | #/.OuxNPQ [edit]

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/350-85d15fa7
まとめtyaiました【更新クエリで定義域集計関数を使わずに集計する】
集計クエリで、抽出条件が複雑だったり、外部接続でインデックスが利用できないなどの原因で、処理が重くなる場合があります。ですので集計結果を親テーブルの方に書き込んでおきたいという要望が出る場合があります。 集計クエリあるいはそれを含むクエリは更新できま...
該当の記事は見つかりませんでした。