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

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

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

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

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

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

イラストで理解　SQL　はじめて入門

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2019/05/16
• メディア: 単行本（ソフトカバー）

関連記事
AccessクエリとSQLの関係 デザインビューとSQLビュー
AccessクエリとSQLの関係 フィールド
AccessクエリとSQLの関係 フィールドに式を書く
AccessクエリとSQLの関係 並び替え
AccessクエリとSQLの関係 抽出条件
AccessクエリとSQLの関係 抽出条件(または)
AccessクエリとSQLの関係 抽出条件(INとLIKE)
AccessクエリとSQLの関係 抽出条件(表示のチェックボックス)
AccessでUPDATEの更新値でサブクエリを使えない DMaxを使えばエラーにはならない

最近、DMaxを使った。

Accessでは、UPDATEの値のところにサブクエリが書けない。
だけど、DMaxなどの関数は書けるのである。

うーん「SQLポケリ」には書いてないなぁ。
そもそも、DMaxなんて標準SQLじゃないから書いてないしね。

まだまだ知らないことが多いんだなぁ...

関連記事
AccessでUPDATEの更新値でサブクエリを使えない DMaxを使えばエラーにはならない

---

サイト内を検索

タグ：SQL access