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

本日は、MS Accessの話題である。
DSC_1228[1].jpg
Oracleなどには、SUM() OVERっていう関数がある。分析関数なので、使い方が集計関数とちょっと違う。

SUM(a) OVER (PARTITION BY xxx)

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

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

な感じである。
このクエリでもちゃんと最年長のデータを取得できる。
Access 最年長者を取得 2017-08-19 (1).png
サブクエリの代わりにDMaxが使えるはずである。サブクエリの部分をDMaxに置き換えてみよう。

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

これで実行してみると...
Access 最年長者を取得 2017-08-19 (1).png
おっ、できた。DMax関数の引数は、文字列形式となることに注意。普通の集計関数のようにフィールド名を書いてはいけない。文字列で渡さないとダメなのである。

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

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

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

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

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

サブクエリと上位のクエリで同じ住所録テーブルを使うので、別名を切るところがポイント。
Access サブクエリで男女別に最年長者を検索 2017-08-19 (2).png
DMaxでも条件式が書けるので、同じことをやってみよう。

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

やった。これもできた。
実際は、以下のようにSQLビューでクエリを作成している。
Access DMaxで男女別に最年長者を検索 2017-08-19 (5).png
条件式を文字列で組み立てる必要があるので、なんかやっかい。
サブクエリで書いてもDMaxで書いても大差ない気がする。

DCountで順位の計算

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

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

Access サブクエリで順位を計算 2017-08-19 (8).png
年収が高い順に順位をつけてみた。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

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

オレ的には、サブクエリだなぁ...汎用性もありそうだし。DMaxやDCountはAccessでないと使用できない。OracleやSQL Serverには存在しない。

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

Access クエリ 徹底活用ガイド ~仕事の現場で即使える

Access クエリ 徹底活用ガイド ~仕事の現場で即使える

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2018/05/25
  • メディア: 大型本


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

投稿者プロフィール

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