Ответ.

Аналитические отчёты с динамической фильтрацией, группировкой и агрегацией по множеству параметров — ядро BI и DataWareHouse-приложений. Ранее такие задачи решались исключительно через статические запросы, но с ростом требований появилась потребность динамического построения SQL на лету: число полей и группировок определяется пользователем или приложением.

Проблема — невозможность заранее детерминировать все варианты полей для группировки и агрегации. Приходится строить запросы динамически, что чревато ошибками и деградацией производительности при некачественном планировании запросов и использовании неподходящих индексов.

Решение — реализовать построение запросов с помощью динамического SQL (например, через EXEC или sp_executesql в T-SQL), формируя список полей для 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 для всех возможных группировок вместо динамического SQL?

Нет, CASE позволит делать вычисления в SELECT, но не формировать GROUP BY по динамически определяемым полям — их всё равно надо задавать явно. Статичный подход слишком ограничен для живой BI-аналитики.

Нужно ли использовать параметры SQL-запроса при динамическом конструировании WHERE, или можно вставлять значения напрямую в текст строки?

Всегда используйте параметры (например, через sp_executesql), иначе получите уязвимость SQL injection и защищённые значения (например, апострофы в строках) будут неправильно обработаны.

Поможет ли индекс по всем возможным комбинациям полей сразу ускорить любые отчеты?

Нет, создание композитного индекса по десяткам полей приводит к росту размера индекса и падению скорости вставки/обновления, при этом ускоряется только строго ограниченное число сценариев. Индексируйте только реально нужные поля/группы.

Типовые ошибки и анти-паттерны

Динамическая конкатенация строк без параметризации (SQL Injection)
Отсутствие анализа selectivity при проектировании индексов
Формирование GROUP BY без учета реального состава таблицы

Пример из жизни

Негативный кейс

В старом BI-отчёте "жёстко" закодили 30 вариантов группировок с if-else в приложении. Каждый новый параметр требовал обновления исходного кода и схемы индексов. Плюсы:

Простота понимания
Быстро реализуется малым числом полей

Минусы:

Нет гибкости
Сложно расширять и поддерживать

Позитивный кейс

Использовали динамический SQL с формированием select/group by на лету, код SQL в отдельном модуле, параметры строго с экранированием. Добавление новых полей — просто настройка справочника, индексы добавляются по частоте фильтрации. Плюсы:

Гибкость, масштабируемость
Безопасность

Минусы:

Требует чёткого контроля имен и типов в runtime
На этапе проектирования необходим качественный мониторинг индексов и расходов CPU