回答。

動的フィルタリング、グループ化、および多数のパラメータに基づく集計を伴う分析レポートは、BIおよびDataWareHouseアプリケーションの中核です。以前はこのようなタスクは静的なクエリでのみ解決されていましたが、要件の増加に伴い、SQLを動的に構築する必要が生じました：フィールドの数やグループ化はユーザーやアプリケーションによって決定されます。

問題 — グループ化や集計のためのすべてのフィールドの組み合わせを事前に決定することができません。動的にクエリを構築する必要があり、これはエラーの原因となり、クエリの質の低下や不適切なインデックスの使用によるパフォーマンスの低下を引き起こします。

解決策 — 動的SQLを使用してクエリを構築します（例えば、T-SQLのEXECまたはsp_executesqlを介して）、ユーザーのパラメータからSELECT、GROUP BYおよび関連する集計関数のフィールドリストを形成します。この際、フィールド名の適切なエスケープとSQLインジェクションからの保護を確保する必要があります。SQL Serverの例は次のとおりです：

DECLARE @select_fields nvarchar(max) = N'customer_id, year';
DECLARE @agg_fields nvarchar(max) = N'SUM(amount) AS total, COUNT(*) AS row_count';
DECLARE @group_by nvarchar(max) = N'customer_id, year';

DECLARE @sql nvarchar(max) = 
    N'SELECT ' + @select_fields + ', ' + @agg_fields + N' FROM sales WHERE sale_date >= @start AND sale_date <= @end GROUP BY ' + @group_by;

EXEC sp_executesql @sql, N'@start DATE, @end DATE', @start='2023-01-01', @end='2023-12-31';

主な特徴：

ユーザーパラメータに基づく柔軟なクエリ構造の構築。
必須のパラメータ化を伴う安全な動的SQLの使用。
フィルタリングおよびグループ化のための適切なフィールドのインデックス化の必要性を理解する。

注意を要する質問。

すべての可能なグループ化のためのCASEを使用した1つの汎用クエリを事前に作成できますか？

いいえ、CASEはSELECT内での計算は可能ですが、動的に決定されたフィールドでGROUP BYを形成することはできません。それらは明示的に設定する必要があります。静的アプローチはリアルタイムのBI分析にはあまりにも制限があります。

動的にWHEREを構築する際にSQLクエリのパラメータを使用する必要がありますか、それとも値を直接文字列に挿入してもよいですか？

常にパラメータを使用してください（例：sp_executesqlを介して）、そうしないとSQLインジェクションの脆弱性が生じ、保護された値（例：文字列内のアポストロフィ）が正しく処理されません。

すべての可能なフィールドの組み合わせに対してインデックスを作成すると、すべてのレポートが速くなりますか？

いいえ、数十のフィールドに対する複合インデックスの作成は、インデックスのサイズの増加と挿入/更新速度の低下を引き起こしますが、加速されるのは厳密に限られたシナリオだけです。本当に必要なフィールド/グループのみをインデックス化してください。

一般的なミスとアンチパターン

パラメータ化なしの動的文字列の連結（SQLインジェクション）
インデックス設計時の選択性の分析が不十分
テーブルの実際の構成を考慮せずにGROUP BYを形成

実生活の例

ネガティブケース

古いBIレポートでは、アプリケーション内でif-elseで30のグループ化のバリエーションが「ハードコーディング」されていました。新しいパラメータを追加するのに元のコードとインデックススキームの更新が必要でした。 利点：

理解しやすい
少数のフィールドで迅速に実装可能

欠点：

柔軟性がない
拡張や保守が困難

ポジティブケース

動的SQLを使用して、リアルタイムでselect/group byを構築し、SQLコードを別のモジュールに分離し、パラメータを厳密にエスケープしました。新しいフィールドの追加は、単に参照の設定であり、インデックスはフィルタリングの頻度に基づいて追加されます。 利点：

柔軟性、スケーラビリティ
セキュリティ