集計関数(CSVファイルをADOで扱う㉑)

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

外部結合

前回は、外部結合についてご紹介しました。これで複数のファイルから色々な形のデータを取り出せます。

今回は、集計関数をご紹介したいと思います。

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

CSV_ReaderSQL110.zip - Google ドライブ

集計関数とは

 CSVファイル(テーブル)のデータ内容から集計した結果を取得したい場合があります。そんな時に使用するのが集計関数です。 今回は以下のァイルを使用します。ファイル名はTest.csvとしましょう。

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

件数を取得する

まずは、このファイルに記載されているデータ件数を表示してみましょう。ツールでファイルのあるフォルダーを指定しておいて、SQLコマンド欄に以下を入力して実行ボタンを押してみてください。

SELECT COUNT(名前) AS 件数 FROM [Test.csv]

結果は以下の様になりました。登録されているデータの件数を取得しています。

件数
9

COUNTは、データの件数を取得する集計関数です。カッコ内の引数には列名を指定します。今回のデータでは松川さんと大槻さんの点数欄にデータがありません。したがって、列名に点数を指定すると結果は以下の様になります。

件数
7

またCOUNTは、引数として*(アスタリスク)を設定できます。この場合CSVファイルに登録してあるデータ件数(9)を返します。引数として*を設定できる集計関数はCOUNTだけです。

WHEREで絞る

集計関数はWHEREと併せて使用できます。Aクラスの件数を取得してみましょう。

SELECT COUNT(*) AS 件数 FROM [Test.csv] WHERE クラス = 'A'

結果は以下の様になりました。Aクラスの件数を取得できています。

件数
3

その他の集計関数

その他にも以下の集計関数があります。

関数名 機能
AVG(列名) 列の平均値を求める
SUM(列名) 列の合計を求める
MAX(列名) 列の最大値を求める
MIN(列名) 列の最小値を求める

合計と平均を求める

先のファイルから、合計と平均を取得してみましょう。

SELECT SUM(点数) AS 合計,FORMAT(AVG(点数),'0.00') AS 平均 FROM [Test.csv]

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

合計 平均
578 82.57

注目していただきたいのは平均の結果です。データの件数は9件ですが、平均値は点数のデータがある行だけで計算しています(578÷7≒82.57)。自動的に判定しているんですね。

最大と最小を求める

今度は最大と最小を求めてみましょう。

SELECT MAX(点数) AS 最大,MIN(点数) AS 最小 FROM [Test.csv]

結果は以下の様になりました。最大と最小が求められています。

最大 最小
95 67

合計と平均を求めた場合もそうなのですが、WHEREと併せて使用することで条件を絞って値を求めることも可能です。試しにAクラスの最大と最小を求めてみましょう。以下のSQLを実行してみてください。

SELECT MAX(点数) AS 最大,MIN(点数) AS 最小 FROM [Test.csv] WHERE クラス = 'A'

結果は以下の様になりました。Aクラスの最大と最小が求められています。

最大 最小
90 76

点数が平均以上を表示する

それでは、点数が平均以上の行を表示してみようと思います。SQLは以下でどうでしょうか。

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

点数が空欄(NULL)の場合は、点数を0と置き換えます。実行してみると・・・あれ?エラーが出てしまいますね。実を言うと集計関数はWHEREで指定する条件には記述できないのです。困りましたね・・・

次回はサブクエリ

いかがでしょうか。集計関数は色々な場面で使用することがあると思います。でも、WHEREで指定する条件には記述できないのは不便ですね。次回はこの問題の解決方法について紹介します。お楽しみに!!

hirocom777.hatenadiary.org

CSVファイルをADOで扱う連載記事はコチラからどうぞ