最近のMSSQL(SQL Server)には、「GROUPING SETS」という便利な関数がある。

これは、例えばある売上データを、日付で集計したいし、
人(営業マン)でも集計したいという場合があったとする。

こういう場合は、それぞれでSQL文を発行するのが最も簡単なやり方だ。

ただ、間が空くと、タイミングによって、結果が異なるリスクもあるので、
できれば1つのSQL文を発行することによって同時に取得したい.

となると、以下の様に「UNION ALL」を使って、たすき掛けなデータを作ることになる。
┌──────────────────────────────────────┐
│SELECT [キー1],                                                             │
│       NULL      AS [キー2],                                                │
│       SUM([値]) AS [合計]                                                  │
│    FROM [表]                                                               │
│    GROUP BY [キー1]                                                        │
│UNION ALL                                                                   │
│SELECT NULL      AS [キー1],                                                │
│       [キー2],                                                             │
│       SUM([値]) AS [合計]                                                  │
│    FROM [表]                                                               │
│    GROUP BY [キー2]                                                        │
└──────────────────────────────────────┘

これを簡単にやってくれるのが「GROUPING SETS」関数だ。
基本はこんな感じ。だいぶ見やすい。
┌──────────────────────────────────────┐
│SELECT [キー1],                                                             │
│       [キー2],                                                             │
│       SUM([値]) AS [合計]                                                  │
│    FROM [表]                                                               │
│    GROUP BY GROUPING SETS (                                                │
│                 [キー1],                                                   │
│                 [キー2]                                                    │
│             );                                                             │
└──────────────────────────────────────┘


複合キーだって、共通のサブ階層だってへっちゃら。

以下の様に応用ができる。
┌──────────────────────────────────────┐
│SELECT [キー1-1],                                                           │
│       [キー1-2],                                                           │
│       [キー2],                                                             │
│       SUM([値]) AS [合計]                                                  │
│    FROM [表]                                                               │
│    GROUP BY GROUPING SETS (                                                │
│                 ([キー1-1], [キー1-2]),                                    │
│                 [キー2]                                                    │
│             ),                                                             │
│             [キーx-3]                                                      │
│    ORDER BY [キー2]   ASC,  --NULLが先頭になるのを避けるため               │
│             [キー1-1] ASC,                                                 │
│             [キー1-2] ASC,                                                 │
│             [キーx-3] ASC;                                                 │
└──────────────────────────────────────┘

ExceVBAでADODBを使ってアクセスする場合、オープン処理というのを行う。
なので、クローズ処理というのが対応して必要になる。
(プロセスを終了してしまえばクローズ処理は端折れるのだがなんか気持ち悪い)

で、ストアドプロシージャについては、「adCmdStoredProc」を指定することにより
専用処理を使用することができる。
これも「.Execute」をテーブルオブジェクトに代入すれば
Openした場合と同じように操作できる。

で、疑問なのだが、後者の場合、Close処理は必要なのだろうか？

これを解決するために、オープン状態を確認するデバッグ文を仕込んでみた。

その結果、後者の場合もオープン状態はオープン中だということがわかる。
ということで、後者も前者と同じ様にクローズ処理を実装した方がよさそうだ。

ちなみに、実験したのは、以下のような感じの処理
(グローバル変数(メンバ変数)の設定処理は省略して記載)
┌──────────────────────────────────────┐
│Private mデータベース As New ADODB.Connection                               │
│Private mSQL文 As String                                                    │
├──────────────────────────────────────┤
│Dim テーブル As New ADODB.Recordset                                         │
│Dim SQLコマンド As New ADODB.Command                                        │
│'-(オープン) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│With SQLコマンド                                                            │
│    .ActiveConnection = mデータベース                                       │
│    .CommandText = mSQL文                                                   │
│End With                                                                    │
│テーブル.Open SQLコマンド                                                   │
│If mテーブル.State = adStateClosed Then                                     │
│    MsgBox "エラー", vbCritical                                             │
│    ' エラー処理                                                            │
│    Exit Sub                                                                │
│End If                                                                      │
│MsgBox テーブル.State   ' オープン状態のの確認 → 1(adStateOpen)＝オープン中│
│'-(参照処理) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│' テーブル.Fields(列名).Value などを参照                                    │
│'-(クローズ) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│If テーブル.State <> adStateClosed Then                                     │
│    テーブル.Close                                                          │
│End If                                                                      │
└──────────────────────────────────────┘
┌──────────────────────────────────────┐
│Private mデータベース As New ADODB.Connection                               │
├──────────────────────────────────────┤
│Dim テーブル As New ADODB.Recordset                                         │
│Dim SQLコマンド As New ADODB.Command                                        │
│'-(オープン) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│With SQLコマンド                                                            │
│    .ActiveConnection = mデータベース                                       │
│    .CommandType = adCmdStoredProc                                          │
│    .CommandText = "ストアドプロシージャ"                                   │
│    Set テーブル = .Execute                                                 │
│End With                                                                    │
│MsgBox テーブル.State   ' オープン状態のの確認 → 1(adStateOpen)＝オープン中│
│'-(参照処理) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│' テーブル.Fields(列名).Value などを参照                                    │
│'-(クローズ) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -│
│If テーブル.State <> adStateClosed Then                                     │
│    テーブル.Close                                                          │
│End If                                                                      │
└──────────────────────────────────────┘

以下のような感じ。
┌──────────────────────────────────────┐
│Dim 今月頭 As Date: 今月頭 = DateSerial(今年, 今月    , 1)                  │
├──────────────────────────────────────┤
│Dim 今月末 As Date: 今月末 = DateSerial(今年, 今月 + 1, 0)                  │
└──────────────────────────────────────┘
日に「0」を指定することにより、「-1日」(前日)扱いとなる。
よって、来月の前日を求めれば今月末が求まるということだ。

MSSQLでは「計算列」という自動で計算してくれる列がある。
たいていは、「単価×数量」の結果を格納する列にそれを設定するのだが、
キーのグルーピングにも応用できそうだ。

以下のような感じ。(主キーの先頭4文字を別キーとする)
┌──────────────────────────────────────┐
│CREATE TABLE [dbo].[表] (                                                   │
│    [キー]                  [int]               NOT NULL,                   │
│    [部分キー]              AS SUBSTRING([キー], 0, 4),                     │
│    CONSTRAINT [主キー] PRIMARY KEY CLUSTERED (                             │
│        [キー]                  ASC                                         │
│    )                                                                       │
│);                                                                          │
├──────────────────────────────────────┤
│CREATE INDEX [索引] ON [dbo].[表] (                                         │
│    [部分キー]                                                              │
│);                                                                          │
└──────────────────────────────────────┘

インデックスも張れるので、都度文字列編集しながらの検索とかが
インデックスを効かせて快適(快速)に行うことができる。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM [表]                                                               │
│    WHERE SUBSTRING([キー], 0, 4) = 'XXXX';                                 │
├──────────────────────────────────────┤
│SELECT *                                                                    │
│    FROM [表]                                                               │
│    WHERE [部分キー] = 'XXXX';                                              │
└──────────────────────────────────────┘
これは便利。

なお、「計算列」には直接INSERTできない(エラー)から注意(というか当たり前か)。

以下の二つの案がある

【あらかじめ集計テーブルを作り結合する案】
┌──────────────────────────────────────┐
│SELECT [親表].[キー],                                                       │
│       ISNULL([子表].[件数], 0) AS [件数]                                   │
│    FROM [親表]                                                             │
│        LEFT JOIN (                                                         │
│           SELECT [外部キー],                                               │
│                  COUNT(*)   AS [件数]                                      │
│               FROM [子表]                                                  │
│               GROUP BY [外部キー]                                          │
│        ), 0) AS [子表]                                                     │
│          ON [子表].[外部キー] = [親表].[キー]                              │
└──────────────────────────────────────┘

【副問い合わせにて集計する案】
┌──────────────────────────────────────┐
│SELECT [親表].[キー],                                                       │
│       ISNULL((                                                             │
│           SELECT COUNT(*)                                                  │
│               FROM [子表]                                                  │
│               WHERE [子表].[外部キー] = [親表].[キー]                      │
│       ), 0) AS [件数]                                                      │
│    FROM [親表];                                                            │
└──────────────────────────────────────┘

結果の種類が多い場合は前者が、少ない場合は後者が有利っぽいが、
最適化された結果、そんなに変わらないみたい。
＃一見、後者は毎回集計しそうだが、あらかじめ集計テーブルを作っぽい

コピペという点では後者が有利っぽいが、性能的にはちょっとだけ前者がよさそう。

「SQL Server Management Studio」のクエリにて、実行計画を表示する設定。
┌──────────────────────────────────────┐
│SET STATISTICS PROFILE ON;                                                  │
└──────────────────────────────────────┘
元に戻すには以下の通り。
┌──────────────────────────────────────┐
│SET STATISTICS PROFILE OFF;                                                 │
└──────────────────────────────────────┘

┌──────────────────────────────────────┐
│CREATE VIEW [dbo].[仮想表] WITH SCHEMABINDING                               │
│AS                                                                          │
│    SELECT [列]                                                             │
│        FROM [dbo].[表]                                                     │
│;                                                                           │
├──────────────────────────────────────┤
│CREATE UNIQUE CLUSTERED INDEX [AK_仮想表] ON [dbo].[V_仮想表] (             │
│    [キー]                  ASC                                             │
│);                                                                          │
└──────────────────────────────────────┘
・仮想表作成時に「WITH SCHEMABINDING」をつける
  ・仮想表作成時にDB名を指定しない  ×「[DB].[dbo].[表]」→「[dbo].[表]」
    →つまりは他DBを絡めたビューには指定できないということ
・仮想表の中で参照している元の仮想表にも「WITH SCHEMABINDING」をつける
  →上記ルールが元の仮想表まですべて適用されるということ
・仮想表の中で参照している元の仮想表においては集約関数は使用不可

「秒」を「時分秒」に換算するのに以下を使ってみた。
┌──────────────────────────────────────┐
│SELECT RTRIM(CONVERT(char(12),                                              │
│                     DATEADD(SECOND,                                        │
│                             [秒数],                                        │
│                             CONVERT(datetime, 0)),                         │
│                     108)                                                   │
│       )                                                                    │
└──────────────────────────────────────┘
ところが、24時間(86400秒)を超えると、「日」が繰り上がり「時」が「0」に戻る。
つまり本来「25時間」であるべきところが「(1日と)1時間」になってしまう。

MySQLだったら「SEC_TO_TIME()」という便利な関数があり、
「25時間」もへっちゃらなのだが。
┌──────────────────────────────────────┐
│SELECT SEC_TO_TIME(`秒数`);                                                 │
└──────────────────────────────────────┘

ということで、自前で実装してみた。
┌──────────────────────────────────────┐
│CREATE FUNCTION [dbo].[sec_to_time] (                                       │
│    @引数秒数               int                                             │
│) RETURNS varchar(max)                                                      │
│AS                                                                          │
│BEGIN                                                                       │
│    RETURN (                                                                │
│        SELECT CONVERT(varchar, FLOOR(@引数秒数 / 3600))                    │
│             + RIGHT(                                                       │
│                   RTRIM(CONVERT(char(12),                                  │
│                                 DATEADD(SECOND,                            │
│                                         ABS(@引数秒数),                    │
│                                         CONVERT(datetime, 0)),             │
│                                 108)),                                     │
│               6)                                                           │
│    );                                                                      │
│END;                                                                        │
└──────────────────────────────────────┘

だいたい速い。

結果は同じでも以下の二つの経路があったとする
・レコードを絞り込んで、それについてのみ計算を行う
・全て計算を行った上で、レコードを絞り込む

一つの計算あたり複雑で負荷がかかる場合、断然前者の方が速い。
なんといっても、日の目を見ない計算をハナからやらないのだから。

例えば、その複雑な計算を全件について行う[重いVIEW]というのがあった場合、
必要なデータである[抽出Ｄ]についてだけ結果が欲しいとすると、
単純に以下の様に内部結合を行う。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM [重いVIEW]                                                         │
│        INNER JOIN [抽出Ｄ]                                                 │
│          ON [重いVIEW].[キー] = [抽出Ｄ].[キー];                           │
└──────────────────────────────────────┘
この場合、「全て計算してから抽出する」パターンになりがちだ。
(「INNER JOIN」の順番を逆にしても同じ)

WHERE文を明示的に指定する、例えば以下のような場合は
「抽出してから計算する」パターンにもっていきやすい。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│     FROM [重いVIEW]                                                        │
│     WHERE [キー] = (SELECT TOP 1 [キー] FROM [抽出Ｄ]);                    │
└──────────────────────────────────────┘
ただ、これだと、一件しか対応できない。

で、これを複数件でも耐えうる形にするのが相関副問い合わせだ。
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM [重いVIEW]                                                         │
│    WHERE EXISTS (                                                          │
│              SELECT *                                                      │
│                  FROM [抽出Ｄ]                                             │
│                  WHERE [抽出Ｄ].[キー] = [重いVIEW].[キー]                 │
│          );                                                                │
└──────────────────────────────────────┘
相関副問い合わせ自体が速いということではないらしいのだが
手続きの順番が抽出優先になるという点で、相関副問い合わせは速く感じる。

つまり、相関副問い合わせは難しげだがマスタすると強力な武器になるということだ。

┌─┬────────────────────────────────────┐
│真│'abc' = 'abc'                                                           │
├─┼────────────────────────────────────┤
│真│'abc' = 'abc '                                                          │
└─┴────────────────────────────────────┘
上記のような場合、後者は「偽」になりそうなものである。

しかしながら、これは(「真」になるのは)、
「ANSI/ISO SQL-92」の立派な規格なのである(比較では後続の空白は無視)。

ということで、データを管理・入力する時は、トリムを推奨しようねという話でした。

では、既に混入済みの場合は、どうやってみつけだすのか？

基本的にバイナリに変換して比較すれば、厳密に比較ができる。
┌─┬────────────────────────────────────┐
│真│CONVERT(binary, 'abc') = CONVERT(binary, 'abc')                         │
├─┼────────────────────────────────────┤
│偽│CONVERT(binary, 'abc') = CONVERT(binary, 'abc ')                        │
└─┴────────────────────────────────────┘

但し、型が違うと(特に「nvarchar」と「varchar」など)、
バイナリ変換される結果も変わってくるので、
念のため以下の様に事前に型を合わせておくというのも検討せねばならない。
┌─┬────────────────────────────────────┐
│真│CONVERT(binary, CONVERT(nvarchar(4000), 'abc'))                         │
│  │                       = CONVERT(binary, CONVERT(nvarchar(4000), 'abc'))│
├─┼────────────────────────────────────┤
│偽│CONVERT(binary, CONVERT(nvarchar(4000), 'abc'))                         │
│  │                      = CONVERT(binary, CONVERT(nvarchar(4000), 'abc '))│
└─┴────────────────────────────────────┘
変換を介するということは処理速度が遅くなので、必要なければ端折るのがよい。

また、以下の様な方法もある(「COLLATE JAPANESE_BIN =」ではないので注意)。
┌─┬────────────────────────────────────┐
│真│'abc' COLLATE JAPANESE_BIN LIKE 'abc'                                   │
├─┼────────────────────────────────────┤
│偽│'abc' COLLATE JAPANESE_BIN LIKE 'abc '                                  │
└─┴────────────────────────────────────┘