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で指定する条件には記述できないのは不便ですね。次回はこの問題の解決方法について紹介します。お楽しみに!!