SQL入門
データの取得:集約集計、並べ替え(DISTINCT,GROUP,HAVING,ORDER)

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
公開日:2019-11-30 最終更新日:2021-08-24

データの取得:集約集計、並べ替え(DISTINCT,GROUP,HAVING,ORDER)


エクセルVBAでデータベースを扱うためのSQL入門です。
前回もっとも単純なSELECT文(SELECT…FROM…WHERE)を紹介しました。
これは、1行のデータはそのまま1行のデータとして、順番はDBに保存されている順で取得されるものでした。


今回は、複数の行を集約して同じデータは1件にしたり、キーを基に集計した値を取得したり、並べ替えて取得したりするSQLを説明します。
これらは、エクセルでの重複の削除、SUMIFS関数、そして並べ替え等に相当します。

データの取得:SELECTの構文

SELECTの全構文はかなり複雑なものになります。
SELECT [ DISTINCT ] 任意の式 [ , 任意の式 ・・・]
FROM テーブル名 , テーブル名 ・・・
[ WHERE 論理式 [, 論理式 ・・・ ]]
[ GROUP BY 任意の式 [, 任意の式 ・・・ ]]
[ HAVING 任意の式 [, 任意の式 ・・・ ]]
[ ORDER BY 任意の式 [, 任意の式 ・・・ ]]

任意の式には、列名または各種の式(関数等)を指定できます。
関数については、今後すこしずつ紹介していきます。

DISTINCT句
DISTINCT句を指定すると、取得したデータの重複を取り除いてユニークなデータとして取得できます。

GROUP BY句
指定された任意の式のリストを基に、データをグループ化します。
グループ化された件数や合計を計算するにはSQL関数を使います。

HAVING句
集計後の条件でデータを絞り込めます。
集計関数(SQL関数)はWHERE句の条件式に指定できませんので、集計後の値を基に条件を指定する場合はこのHAVING句を使用します。

ORDER BY句
ORDER BY句を使う事で、ソートした結果を取得することができます。
複数の列(または任意の式)を指定でき、それぞれに昇順/降順を指定できます。

テスト用のテーブルとVBAコード

前回の「データの挿入:バルクインサート」で作成したデータを使用します。
・テスト用のテーブル作成 ・VBAの改善点の概要 ・バルクインサートのSQL解説 ・バルクインサートのVBAコードと動作検証 ・文字列操作を改善する方法とVBAコード ・標準モジュールでの使い方と、100万行の実行結果 ・バルクインサートの最後に
テーブル定義は以下になります。

CREATE TABLE t_sales (
 'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,'code' TEXT NOT NULL
,'name' TEXT NOT NULL
,'address' TEXT
,'sales_date' TEXT
,'item_code' TEXT
,'item_name' TEXT
,'item_price' INTEGER
,'item_count' INTEGER
,'item_amount' INTEGER
,'comment' TEXT
);

以降のサンプルVBAでは、以下のクラスを使用しています。
VBAクラスの全コード:データの取得
SQL入門の「データの取得:条件指定(SELECT,WHERE)」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite

Sub SelectDistinct()
  Dim clsDB As New clsSQLite
  clsDB.DataBase = "C:\SQLite3\sample.db"
  
  Dim ws As Worksheet
  Set ws = ActiveSheet
  
  Dim sSql As String
  sSql = ""
  sSql = sSql & ""
  sSql = sSql & ""
  sSql = sSql & ""
  
  'SQL文字列, 出力セル, シートクリア, 取得カラム名出力
  If Not clsDB.SheetFromRecordset(sSql, ws.Range("A1"), enmClear.Clear, True) Then
    MsgBox clsDB.ErrMsg
    Exit Sub
  End If
  
  Set clsDB = Nothing
End Sub

以降では、上記VBAの、
sSql = sSql & ""
この部分の変更としてSQLを掲載します。

DISTINCT句

DISTINCT句を指定すると、取得したデータの重複を取り除いてユニークなデータとして取得できます。

sSql = sSql & "SELECT DISTINCT code,name"
sSql = sSql & " FROM t_sales"

codeとnameの重複の無いデータを取得しています。

VBA マクロ SQL

GROUP BY句

指定された任意の式のリストを基に、データをグループ化します。
グループ化された件数や合計を計算するにはSQL関数を使います。

sSql = sSql & "SELECT code,name,sales_date"
sSql = sSql & ",SUM(item_amount),COUNT(*)"
sSql = sSql & " FROM t_sales"
sSql = sSql & " WHERE code = '001'"
sSql = sSql & " GROUP BY code,name,sales_date"

codeが'001'の日付ごとのitem_amountの合計を取得しています。
SUMは合計を求めるSQL関数です。
COUNTは件数を求めるSQL関数です。
関数についての詳細は次回:SQL関数と演算子
・集計/数値関数 ・文字列関数 ・日付時刻関数 ・その他の関数 ・演算子 ・CASE演算子 ・SQL関数と演算子の最後に

VBA マクロ SQL

C列の日付は文字列としてセルに入っています。
日付データにするには、VBAで以下を実行します。、

With Intersect(ws.Range("A1").CurrentRegion, ws.Columns("C"))
  .Value = .Value
End With

このようにValueを入れ直すことで、Excelが自動的に日付データに変換してくれます。

SQLiteの日付について
SQLiteには日付型がないので、文字列型で処理しています。
今後より良い方法が見つかりましたら追加記載します。

HAVING句

集計後の条件でデータを絞り込めます。
集計関数(SQL関数)はWHERE句の条件式に指定できませんので、集計後の値を基に条件を指定する場合はこのHAVING句を使用します。

sSql = sSql & "SELECT code,name,sales_date,SUM(item_amount)"
sSql = sSql & " FROM t_sales"
sSql = sSql & " GROUP BY code,name,sales_date"
sSql = sSql & " HAVING SUM(item_amount) > 7000000"

code,name,sales_dateでグループし、item_amountの合計が7000000以上を取得しています。

VBA マクロ SQL

ORDER BY句

ORDER BY句を使う事で、ソートした結果を取得することができます。
複数の列(または任意の式)を指定でき、それぞれに昇順/降順を指定できます。

sSql = sSql & "SELECT code,name,item_price,item_count"
sSql = sSql & " FROM t_sales"
sSql = sSql & " WHERE code = '001'"
sSql = sSql & " ORDER BY item_price ASC,item_count DESC"

codeが'001'のゼンデータを、
item_priceを昇順、item_countを降順で取得しています。

VBA マクロ SQL

VBAでのSQL使用例

上記までを複数組み合わせたSQLのサンプルです。

sSql = sSql & "SELECT code,name,sales_date,SUM(item_amount)"
sSql = sSql & " FROM t_sales"
sSql = sSql & " GROUP BY code,sales_date,item_code"
sSql = sSql & " HAVING SUM(item_amount) >= 250000"
sSql = sSql & " ORDER BY SUM(item_amount) DESC"

code,sales_date,item_codeでグループ化し、
そのグループのitem_amountが250000以上を、
item_amountの合計の降順で取得しています。

VBA マクロ SQL

列名(カラム名)の別名(エイリアス)をつけるAS句

列名(カラム名)には、別名(エイリアス)を付けることができます。

列名 [AS] 別名
任意の式 [AS] 別名

[AS]は省略できます。

sSql = sSql & "SELECT"
sSql = sSql & " code AS コード"
sSql = sSql & ",name AS 名称"
sSql = sSql & ",sales_date AS 販売日"
sSql = sSql & ",SUM(item_amount) AS 合計"
sSql = sSql & ",COUNT(*) AS 件数"
sSql = sSql & " FROM t_sales"
sSql = sSql & " GROUP BY code,sales_date,item_code"
sSql = sSql & " HAVING SUM(item_amount) > 250000"
sSql = sSql & " ORDER BY SUM(item_amount) DESC"

SQLが複雑になってくると、カラム名に短いエイリアスをつけることで、
SQLの他の部分で分かりやすい短い別名で参照することができるようになります。
別名(エイリアス)のより便利な使い方については、今後徐々に説明していきます。

VBA マクロ SQL

※FROMに記述するテーブル名にも別名(エイリアス)が指定できます。
これについては、テーブルの結合の時に詳しく解説します。

データの取得:条件指定の最後に

1つのテーブルからSELECTでデータ取得するSQLについて2回に渡って説明してきました。
今後は、複数のテーブルを結合して取得したり、より高度なSQLに進みます。

でもその前に、
今回出てきたSUMやCOUNTのようなSQL関数について良く使うものをいくつか解説します。
続けて、データとしては常に注意が必要なNULLについて解説していきます。
その後に、テーブルの結合に進んでいきます。
そこでは、データベースの正規化についても触れていきたいと思っています。



同じテーマ「SQL入門」の記事

データの挿入:バルクインサート
データの取得:条件指定(SELECT,WHERE)
VBAクラスの全コード:データの取得
データの取得:集約集計、並べ替え(DISTINCT,GROUP,HAVING,ORDER)
SQL関数と演算子
データベースにおけるNULLの扱い方
データベースの正規化とマスタの作成
全テーブル定義とテーブル自動作成VBA
テーブルを結合して取得(INNER JOIN,OUTER JOIN)
複数のSELECT結果を統合(UNION,UNION ALL)
データの更新(UPDATE)


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ