前回は「Accessで使えるSELECT文の基礎」について解説しました。
≫ 【SQL学習入門】1回目:Accessで使えるSELECT文(前編)
今回の記事は、グループ化やクロス集計など、通常のSELECT文から更に一歩踏み込んだ内容になります。
今回の記事は「SELECT文の基礎をマスターできている方」が対象になっています。
今この記事を読んでいる方が初めてSQL文を学習したことがなかったならば、前回の記事に目を通してから本記事をごらんください。
サンプルデータベースもダウンロードできます。
≫【SQL学習入門】1回目:Accessで使えるSELECT文(前編)
今回の内容は、本来は数回に分けて解説する内容ですが一回にまとめています。
SELECT文から一歩進んだ構文にはどのようなものがあるか、全体を把握することが本記事を作った目的です。
読み進めることで、一歩進んだSELECT文の書き方について理解できるとは思いますが、一番大事なのは「自分で手を動かしてみること」です。
ここで紹介しているSQL文を思い通りに操ることができれば、データ加工や集計の作業がかなり楽になるので、是非自分のものにしてしまいましょう。
SQL文で関数を使う
ここでは関数についての細かい説明は割愛しますが、SQLビューでもAccessで使える関数を直接書くことができます。
下の例文ではFormat関数を使って発注伝票の発注日を年月に変更してみました。
SELECT 発注登録No, 発注先名, 発注日, Format([発注日],'yyyy年mm月') AS 年月 FROM T02発注伝票;
注意しないといけないことは、関数を書いた直後にAS句で新しいフィールド名を命名することです。
AS句を使わないとエラーで先に進めません。
SQL文を実行すると、このような結果が表示されます。
表示形式は「〇年〇月」としましたが、Excelの表示形式と同じ感覚で自分の好きな形式にカスタマイズできます。
Excelの「TEXT」関数と同じ使い方ですね。
GROUP BY句で集計する
Accessのリボンに「集計クエリ」というメニューがあります。
特定の条件でグループ化し、合計・カウント・平均などを計算することができます。
GROUP BY句の後に書かれたフィールドはグループ化されます。
グループ化の対象ではないフィールドは、SUMやCOUNTなどで集計しないといけません。
抽出条件を設定しない場合
まずは抽出条件を指定せずに月別の発注金額と件数を計算してみましょう。
SELECT文は以下のようになっています。
SELECT 月名, Sum(発注金額) AS 合計金額, Count(発注登録No) AS 発注件数 FROM T02発注伝票 GROUP BY 月名;
ここでは「月名」のみをグループ化していますが、複数のフィールドをグループ化することもできます。
集計したフィールドはAS句を使って新しいフィールド名を命名します。
SQLビューを編集し、データシートビューで確認すると以下のような結果になります。
月別の金額と発注件数を計算することができました。
抽出条件を設定しない場合
GROUP BY句で条件抽出する場合は主にHAVINGを使います。
「ヤマダ商事」だけ集計する場合は、以下のSELECT文になります。
SELECT 月名, Sum(発注金額) AS 合計金額, Count(発注登録No) AS 発注件数 FROM T02発注伝票 GROUP BY 月名,発注先名 HAVING 発注先名='ヤマダ商事';
ここでは「月名」と「発注先名」の2フィールドをグループ化しています。
更に、発注先を「ヤマダ商事」に絞っています。
SQLビューを編集してみると下のような結果になりました。
GROUP BY句でもWHERE句を使えますが、使う場面が限られますので、HAVINGを優先して使うということを頭に入れておくとよいでしょう。
クロス集計クエリ
データの分析を行いたい時はクロス集計が便利です。
クロス集計することで、正規化された生データからは見えてこないデータの傾向を読み取ることができます。
Accessの場合はTRANSFORM文を使います。
TRANSFORM Sum(発注金額) AS 金額計 SELECT 発注先名 FROM T02発注伝票 GROUP BY 発注先名 PIVOT 月名;
「SELECT」と「GROUP BY」の直後に行見出し、「PIVOT」で列見出しにするフィールドを指定します。
SQLビューを編集すると以下のような結果になりました。
取引先毎の月別発注金額が集計されています。
通常の集計クエリでも同じよう結果を知ることができますが、クロス集計の方が見やすい表に仕上がります。
クロス集計クエリをSQLビューから作ることはかなり難易度が高いので、デザインビューで作ったSQL文を手直しできる程度までは習得しておきましょう。
ユニオンクエリ
２つのクエリを縦につないだクエリです。
SQL文は、SELECT文の間に「UNUON」をはさんでいます。
各クエリのフィールド順をあわせておかないと、エラーが発生して先に進めません。
SELECT 発注日, 月名, 発注先名, 発注金額 FROM T02発注伝票 WHERE 月名=4
UNION SELECT 発注日, 月名, 発注先名, 発注金額 FROM T02発注伝票 WHERE 月名=7;
4月のデータと7月のデータのみを重ねています。
SQLビューを編集したところ、以下のような結果になりました。
ユニオンクエリはデザインビューから作ることはできませんので、下地をデザインビューで作っておき、SQLビューにコピペする手順になるでしょう。
まとめ
今回はSELECT文の基本から一歩進んだSQL文について解説しました。
今回の内容は、SELECT文の基礎に比べると難易度が高いですが、Access以外の商用データベースでも同じような加工を行います。
つまり、WEBやアプリ開発なども視野に入れて学習したい人にとっても、今回の内容を知っておくと必ず役立ちます。
構文のイメージだけでも大まかに把握しておきましょう。
次回はUPDATE、INSEERT、DELETEと言った「テーブルのデータを変更するSQL文」になります。
≫ 【SQL学習入門】3回目:SQLでAccessテーブルのデータを編集する