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

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-05

データの取得:集約集計、並べ替え(DISTINKT,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入門です。前回でデータをINSERTすることができるようになりましたが、処理時間に不満がありました。せっかくデータベースを使うのでしたら、エクセルで扱いに困ってしまうような大量データをストレスなく扱いたいところです。
テーブル定義は以下になります。

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"), 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関数については、

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入門」の記事

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


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

他のテーブルを基にデータを追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)
SQL関数と演算子|SQL入門(12月1日)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)|SQL入門(11月30日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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



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