サブクエリ（CSVファイルをADOで扱う㉒）

CSVファイルをADOで扱う連載の22回目です。前回の記事はこちらになります。

hirocom777.hatenadiary.org

集計関数は便利だけど

前回は、集計関数についてご紹介しました。CSVファイルの内容を色々な形で集計できます。

でも、集計関数はWHEREの後に記述できないんです。つまり、集計関数で得た値を元にデータを絞ることができないんです。今日はこの問題の解決方法をご紹介したいと思います。

今回もSQLを指定してCSVファイルを確認できるツール（CSV_ReaderSQL）を使います。ツールは以下からダウンロードしてください。

CSV_ReaderSQL110.zip - Google ドライブ

また、今回使用するファイルは前回と同じ（Test.csv）です。

"名前","クラス","点数"
"渡邊国男","A","76"
"吉永安子","B","67"
"松川章平","A",""
"棚橋梨沙","A","90"
"村木道雄","C","83"
"国分円","C","90"
"菅正一郎","B","95"
"杉山龍宏","B","77"
"大槻香苗","C",""

なぜ使えないのか

でもその前に、どうして集計関数はWHEREの後に使えないのでしょうか？実はSQLコマンド内での実行する順番が原因になっています。前回エラーとなったSQLコマンドを再掲します。

SELECT * FROM [Test.csv] 
WHERE IIF(ISNULL(点数) , 0 ,CINT(点数)) >= AVG(点数)

このSQLコマンドは、以下の順番で実行されます。

FROMでテーブル（CSVファイル）を指定
WHEREで絞込みの条件を指定
絞り込んだ条件で集計関数（AVG）を実行
SELECTで表示列を指定して表示

集計関数はWHEREが実行された後に実行されます。つまり、WHEREで指定した条件で絞った結果について集計関数で結果を得ます。したがって、集計関数はWHEREで指定する条件には使えないのです。卵と鶏の関係になってしまうんですね。それではどうすればいいのでしょうか？

この問題を解決するには、まず条件に指定する値（今回は点数の平均値）を確定してから、この値を使ってデータを絞る条件をWHEREで指定すればよさそうです。ツールでファイルのあるフォルダーを指定しておいて、SQLコマンド欄に以下を入力して実行ボタンを押してみてください。

SELECT * FROM [Test.csv] 
WHERE IIF(ISNULL(点数) , 0 , CINT(点数)) >= (SELECT AVG(点数) FROM [Test.csv])

結果は以下の様になりました。

名前	クラス	点数
棚橋梨沙	A	90
村木道雄	C	83
国分円	C	90
菅正一郎	B	95

平均点（前回求めた値82.57）以上のデータを選択できました。でも、どうして上手くいったでしょうか？

サブクエリ

これはサブクエリ（副問い合わせ）を使用した結果です。サブクエリとはSQLコマンド内に括弧で括って埋め込んだSELECT文の事です。サブクエリを含んだSQLコマンドは先にサブクエリの内容を処理してから、その結果を用いて全体の処理を実行します。今回の場合、以下のSELECT文を括弧で括ってサブクエリとしました。

SELECT AVG(点数) FROM [Test.csv]

このSQLコマンドを単体で実行すると、以下の結果を返します。

Expr1000
82.57142857

Expr1000は列名を指定しなかった場合にシステムが勝手につける名前のことで、この場合は無視していただいて結構です。一般的なSELECT文は結果として複数の値（表形式の結果）を返しますが、このSELECT文は1つの値（スカラー値とも言う）だけを返します。この様なSELECT文をサブクエリとして使用した場合、サブクエリ自体をその値として使用できます。

逆に1つの値を取得することを目的としてサブクエリを使用する場合は、必ず1つの値だけを返すSELECT文を記述しなければいけません。今回のSELECT文は1つの集計関数を使用しているので、返ってくる値は必ず1つになります。

この様に、サブクエリを使用すると実行する順番を入れ替えて実行できます。