So-net無料ブログ作成

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



本日は、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集計関数を使うのだな、ということが推測される。

ここで、住所録テーブルに登場してもらおう。

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クエリとSQLの関係 デザインビューとSQLビュー
AccessクエリとSQLの関係 フィールド
AccessクエリとSQLの関係 フィールドに式を書く
AccessクエリとSQLの関係 並び替え
AccessクエリとSQLの関係 抽出条件
AccessクエリとSQLの関係 抽出条件(または)
AccessクエリとSQLの関係 抽出条件(INとLIKE)
AccessクエリとSQLの関係 抽出条件(表示のチェックボックス)



サイト内を検索


タグ:access SQL
nice!(0)  コメント(0) 

AREarthroidPro ARで地球を表示するアプリ

Copyright Atsushi Asai Google+朝井淳
[改訂第4版]SQLポケットリファレンス

[改訂第4版]SQLポケットリファレンス

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2017/02/18
  • メディア: 単行本(ソフトカバー)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2015/03/03
  • メディア: 単行本(ソフトカバー)
C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2011/04/08
  • メディア: 単行本(ソフトカバー)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

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