AccessクエリとSQLの関係 サブクエリとDSum DMax

本日は、MS Accessの話題である。

Oracleなどには、SUM() OVERっていう関数がある。分析関数なので、使い方が集計関数とちょっと違う。

SUM(a) OVER (PARTITION BY xxx)

みたいにして使う。
分析関数についてはこちら
MS Accessでは分析関数は使えない、と思っていたのではあるが、DSumなる関数を発見。これって分析関数なんじゃない？ということでちょっと調べている。
　DSum(フィールド名, テーブル名, 条件式)
と書いてある。どの引数も文字列で渡す。DSumなので、合計値が戻るらしい。
ふーん、なんとなくわかった。
サブクエリで書くところを、DSum関数でわかりやすくしただけかも？
SUM() OVERとは文法も違うし、機能もちょっと違う感じか。
DSumの最後の引数、条件式は省略可能とのこと。
　DSum(フィールド名, テーブル名)
そうか。これならなんとなく「SELECT SUM(フィールド名) FROM テーブル名」で得られるものと同じ感じがする。
ちょっとやってみるか。
サブクエリを使って検索をしたい場合のよくあるシチュエーションに、ある条件の最大値となっているレコードはどれか、といったものがある。サブクエリでMax集計関数を使うのだな、ということが推測される。
ここで、住所録テーブルに登場してもらおう。

住所録テーブルから最年長者のデータを取得するのには、

SELECT * FROM 住所録 WHERE 年齢 = (SELECT MAX(年齢) FROM 住所録)

な感じである。
このクエリでもちゃんと最年長のデータを取得できる。

サブクエリの代わりにDMaxが使えるはずである。サブクエリの部分をDMaxに置き換えてみよう。

SELECT * FROM 住所録 WHERE 年齢 = DMax("年齢", "住所録")

これで実行してみると...

おっ、できた。DMax関数の引数は、文字列形式となることに注意。普通の集計関数のようにフィールド名を書いてはいけない。文字列で渡さないとダメなのである。

SELECT * FROM 住所録 WHERE 年齢 = DMax(年齢, 住所録)

こんな風に書くと、ちゃんと結果が戻ってこない。

男女別に最大値で検索したい

男性の最年長者と女性の最年長者の2レコードが欲しくなったとする。
サブクエリで書いた場合は、以下のようになる。

SELECT * FROM 住所録 AS T
WHERE T.年齢 = (SELECT MAX(年齢) FROM 住所録 WHERE T.性別 = 性別)

サブクエリと上位のクエリで同じ住所録テーブルを使うので、別名を切るところがポイント。

DMaxでも条件式が書けるので、同じことをやってみよう。

SELECT * FROM 住所録 AS T
WHERE T.年齢 = DMax("年齢", "住所録", "性別='" & T.性別 & "'")

やった。これもできた。
実際は、以下のようにSQLビューでクエリを作成している。

条件式を文字列で組み立てる必要があるので、なんかやっかい。
サブクエリで書いてもDMaxで書いても大差ない気がする。

DCountで順位の計算

分析関数のDENSE_RANKを使えば、順位が計算できる。DENSE_RANKが使えない場合は、サブクエリを書けばよい。

SELECT T.氏名, T.性別, T.年収,
(SELECT COUNT(*)+1 FROM 住所録
WHERE Nz(年収,0) > Nz(T.年収,0)) AS 順位
FROM 住所録 AS T

年収が高い順に順位をつけてみた。NULLデータが含まれるので、Nzで年収がNULLなら0として扱う。
ORDER BYを書くのが面倒だったので、データシートビューの並べ替え機能で、順位を並び替えしている。
同点の場合に順位が飛ぶので、正確にはDENSE_RANKではなくRANKの代用となる。
しくみとしては、単純。サブクエリとなっているので難解のように見えるが、基本は、より高い年収となっているレコードの数をCountで数えているだけ。最上位のデータは上位にレコードがないので、0になる。0位というのもおかしいので、+1して最上位は1位としている。
サブクエリで、Countを使用しているのなら、DCountに変更できるはずである。
やってみよう。

SELECT T.氏名, T.性別, T.年収,
DCount("氏名", "住所録",
"Nz(年収,0) > " & Nz(T.年収,0)) + 1 AS 順位
FROM 住所録 AS T

できた。
まぁ、どっちでも好きな方を使えばいいかも、ですな。

DSumとSUM OVERの話もやりたかったが、本日は、ここまで。

最近、DMaxを使った。
Accessでは、UPDATEの値のところにサブクエリが書けない。
だけど、DMaxなどの関数は書けるのである。
うーん「SQLポケリ」には書いてないなぁ。
そもそも、DMaxなんて標準SQLじゃないから書いてないしね。
まだまだ知らないことが多いんだなぁ...
関連記事
AccessでUPDATEの更新値でサブクエリを使えない DMaxを使えばエラーにはならない

投稿者プロフィール

asai

システムエンジニア
喋れる言語：日本語、C言語、SQL、JavaScript

最新の投稿

• コンピューター関係の記事2025年5月18日Proxmoxの仮想PCにsquidプロキシをインストールした話
• カメラ2025年5月17日Nikon EN-EL25互換バッテリーを購入した話
• コンピューター関係の記事2025年5月11日AmazonアソシエイトのPA-APIがエラーになり広告を作れなくなった話
• コンピューター関係の記事2025年5月10日AWSのEC2にwordpressを入れて作ったブログサイトでapacheの設定をした話