SQL.source.text

<h4>Введение</h4>
В первой статье ( <a href="https://habrahabr.ru/post/278595/">Движок для построения отчётов на SQL. Идея</a> ) я поделился идеей. Теперь поделюсь решением ( черновиком ). Этот черновик - мой первый опыт "серьезной" работы с <b>T-SQL</b>, поэтому не стоит его принимать за образец "хорошего" кода.
Самое важное в этом черновике это механизм подстановки формул в динамический запрос. Второе по важности это механизм сохранения результатов вычислений. 
<cut />
Когда я приступал к работе - я ожидал больших трудностей, но на самом деле все оказалось очень просто. Много писанины и всего пара моментов когда пришлось остановиться и подумать. Первый момент это генерация номера строки в выдаче запроса, Второй - генерация значения для ключевого поля . 
Глаза бояться - руки делают!
Я начну сразу с самого основного и интересного, для тех кому этого мало - ниже будет доскональный разбор логики. Приступим.

<h4>Вычисление формул</h4>
<h5>Разница между Колонками и Разделами</h5>
Есть существенная разница между вычислением формулы для заполнения колонки и вычислением формулы для заполнения полей раздела ( шапки или подвала ). Эта разница заключается в том что колонка вычисляется для каждой строки отдельно, а раздел вычисляется один раз для всех строк разом.
Формулы для шапки это всегда агрегатные функции и результат вычисления формулы надо "вклеить" в "посадочное место" шаблона.
Вычисленное значение для колонки надо "забиндить" ( связать ) со строкой для которой эта колонка была вычислена.
Поэтому для вычисления колонки и шапки разработаны разные шаблоны.
<h5>Шаблон для Раздела</h5>
<source lang="sql">
SET @sql_text = 
N' SELECT  @result = '
+ @formula 
+ N' FROM table '
</source>
Все линейно:
<ul>
	<li>вычислили формулу;</li>
	<li>результат подставили в шаблон;</li>
	<li>что получились сохранили в соответствующей таблице ( <b>report_region_instances</b> );</li>
	<li>конец - шапка сформирована;</li>
</ul>
<h5>Шаблон для Колонки</h5>
С колонками посложней. Если результат вычисления шапки это одно значение, то результат вычисления колонки это множество - таблица состоящая из одной колонки и некоторого количества строк.
Для сохранения результата ( в таблицу report_cell_instances ) необходимо каждую строку пронумеровать, что бы во время вывода отчёта все строки колонок можно было синхронизировать между собой. 
Для этого необходимо строки некоторым единым способом упорядочить - отсортировать. Добавляем в запрос на вычисление колонки фразу "ORDER BY", в "SELECT" добавляем " ROW_NUMBER() OVER( ORDER BY )".
Шаблон запроса: 
<source lang="sql">
SET @sql_text = 
N' SELECT 
    ROW_NUMBER() OVER( ORDER BY key_column) ,'
 + @formula 
 + N' FROM table ORDER BY key_column'
</source>
Не сложно. Следующий интересный момент это сохранение вычислений - результатов нашей работы.

<h4>Сохранение результата.</h4>
С сохранением раздела ( шапки или подвала ) нет ни каких трудностей - банальный "INSERT" чего надо куда надо ( в таблицу report_region_instances ).
С сохранением вычисленной колонки тоже ни чего сложно, надо наш динамический запрос дополнить оператором "INSERT".
Проблема только в генерации значения поля уникального ключа. Есть отличное решение этой задачи с использованием автоинкрементной колонки ( свойство IDENTITY ), но я люблю иметь максимальный контроль за тем что делает моя программа, поэтому я воспользовался другим инструментом - "SEQUENCE" - и генерирую каждый номер в ручную.
Шаблон запроса: 
<source lang="sql">
SET @sql_text = 
N' INSERT INTO report_cell_instances 
( id , row_order , value )
SELECT (NEXT VALUE FOR [dbo].[report_cell_instances_sequence] OVER( ' 
+ @C_ORDER_BY 
+ N' ) ) AS Record_Id , ROW_NUMBER() OVER( ' 
+ @C_ORDER_BY 
+ N' ) AS Row_Order , '
+ @formula
+ N' AS Formula_Result FROM table' + @C_ORDER_BY
</source>

<h4>Доскональный разбор реализации</h4>
Реализация выполнена в качестве T-SQL скрипта, в рабочей реализации это должна быть хранимая процедура, состав входных параметров под вопросом - зависит от потребностей заказчика. У меня в скрипте это: 
<ol>
	<li> номер станции - выбирается случайным образом из таблицы meteo_stations_reference,  </li>
	<li> период дат - выбираются две случайные даты из таблицы meteo_measurements для выбранной станции</li>
	<li> клиент - выбирается случайным образом из таблицы consumer_reference</li>
</ol>
Другие вещи которые должны быть как минимум константами сделаны в стиле "hard code" aka "magic number", считайте это издержками "черновика".
Код я писал в <b>dbForge Studio</b>, у этого IDE самый лучший форматировщик исходников ( это единственный плюс этого IDE ), но у меня он не настроен, поэтому форматирование выполнено в ручную, и только там где я про него помнил.
По привычке к <b>C#</b> и <b>PL/SQ</b>L каждое предложение заканчивается знаком ";".
Остальное читайте в комментариях к коду ( совсем очевидные вещи не имеют комментария, извините я не достаточно зануден ):
<source lang="sql">
BEGIN
/*
Способ сортировки, используется в одном варианте для сортировки входных данных, для сортировки при нумерации строк и при генерации значения ключа
*/
  DECLARE @C_ORDER_BY NVARCHAR(MAX) = ' ORDER BY mm.meteo_station_id , mm.read_timestamp ' ;
/*
Шаблон для сохранения результирующей колонки
*/
  DECLARE @C_COLUMN_FORMULA_INSERT NVARCHAR(MAX) = 
N' INSERT INTO report_cell_instances 
  (id 
  ,instance_id 
  ,consumer_id 
  ,column_id 
  ,row_order 
  ,value)
';
/*
Шаблон для запроса на вычисление формулы, включает в себя все поля необходимые для вставки записи в таблицу report_cell_instances
*/
  DECLARE @C_COLUMN_FORMULA_SELECT NVARCHAR(MAX) = 
  N' SELECT 
      (NEXT VALUE FOR [dbo].[report_cell_instances_sequence] OVER( ' 
+ @C_ORDER_BY 
+ N' ) ) AS RecordId ,       
      @Instance_Id AS InstanceId ,
      @Consumer_Id AS ConsumerId , 
      @Column_Id AS ColumnId ,
      ROW_NUMBER() OVER( ' 
+ @C_ORDER_BY + N' ) AS Row_Order , ';
/*
Завершающая часть шаблона запроса на вычисление колонки
*/
  DECLARE @C_COLUMN_FORMULA_FROM NVARCHAR(MAX) = 
  N' FROM 
      meteo_measurements mm 
   WHERE 
      mm.meteo_station_id = @Station_Id 
      AND mm.read_timestamp BETWEEN @FromDate AND @ThruDate   
    ' + @C_ORDER_BY
  ;
/*
Параметры динамического запроса на вычисление колонки
@Station_Id - станция источник данных
@FromDate - брать данные для вычисления от даты
@ThruDate - брать данные для вычисления по дату
@Column_Id - значение ключа для колонки которая вычисляется
@Instance_Id - значение ключа экземпляра отчёта
@Consumer_Id - значение ключа клиента
*/
  DECLARE @ColumnFormulaParams NVARCHAR(MAX); 
  SET @ColumnFormulaParams = 
    N' @Station_Id bigint , ' + 
    N' @FromDate datetimeoffset(7) , ' +
    N' @ThruDate datetimeoffset(7) , ' +
    N' @Column_Id INT , ' +
    N' @Instance_Id INT , ' +
    N' @Consumer_Id INT '
  ;
/*
Станция на основе данных которой будет сформирован отчёт, берётся случайная из имеющихся
*/
  DECLARE @Station BIGINT ;
  SELECT TOP 1 @Station = sr.id FROM meteo_stations_reference sr ORDER BY NEWID();
/*
Вывод отладочной информации в консоль, в скрипте все вызовы "PRINT" служат только этой цели
*/
  PRINT N' @Staton  = ' + CAST ( @Station AS NVARCHAR ) ;
/*
параметры отбора данных для формирования отчёта,
@From - от даты
@Thru  - по дату
*/
  DECLARE @From DATETIMEOFFSET(7) ;
  DECLARE @Thru DATETIMEOFFSET(7) ;
/*
берём случайные даты
*/
  SELECT TOP 1 @From = mm.read_timestamp FROM meteo_measurements mm ORDER BY NEWID();
  SELECT TOP 1 @Thru = mm.read_timestamp FROM meteo_measurements mm ORDER BY NEWID();
/*
даты "сортируем" в порядке возрастания
*/
  DECLARE @SwapVariable DATETIMEOFFSET(7) ;
  IF ( @From > @Thru )
    BEGIN
      SET @SwapVariable = @Thru;
      SET @Thru = @From ;
      SET @From = @SwapVariable ;
    END; 
  PRINT N' @From = ' + CAST ( @From  AS NVARCHAR )+ N' @Thru = ' + CAST ( @Thru  AS NVARCHAR );
/*
ключ записи экземпляра отчёта, берётся из соответствующей последовательности
*/
  DECLARE @Instance INT ;
  SET @Instance = NEXT VALUE FOR [dbo].[report_instances_sequence] ;
/*
Добавили запись экземпляра отчёта, с ключом состояния 1 - "формируется"
*/
  INSERT INTO report_instances 
    ( id , name , description , state_id )
  VALUES 
    (@Instance,CAST(@Instance AS NVARCHAR ),' DEBUG ', 1 )
  ;
/*
Клиент, выбирается из существующих случайным образом
*/
  DECLARE @ConsumerId INT ;
  SELECT TOP 1 @ConsumerId = cr.id FROM consumer_reference cr ORDER BY NEWID();
  PRINT N' @ConsumerId  = ' + CAST ( @ConsumerId AS NVARCHAR ) ;
/*
Перебор и индивидуальная обработка записей набора данных в T-SQL возможна только с использованием механизма курсоров ( буду рад оказаться не правым ).
Курсор может накладывать некоторые ограничения на источник данных, которые могут привести к блокировке источника для доступа другими процессами.
Что бы не разбираться с грамотным использованием курсоров в T-SQL я решил сохранить результат выборки в коллекцию.
T-SQL не имеет механизма коллекций ( буду рад оказаться не правым ), заменой ему служит механизм временных таблиц, либо табличных переменных.
Временная таблица может быть записана на диск и проиндексирована, временная таблица существует даже после завершения выполнения блока кода.
Табличная переменная существует только в оперативной памяти и только внутри блока кода и не может быть проиндексирована.
Мне было нужно последовательно пробежаться по всем записям - индексация не требуется. 
Использовать данные в других блоках кода не предполагалось.
Размер выборки - в пределах десятка записей, даже если их будет 1000, то это не создаст существенной нагрузки на оперативную память.
Поэтому я выбрал сохранение выборки в табличную переменную, но для истории сохранил вариант с временной таблицей.
*/  
--  CREATE TABLE #consumers_report_columns(
--  column_id int )
--
--  INSERT INTO #consumers_report_columns ( column_id )
--  SELECT 
--    rc.column_id 
--  FROM 
--    consumers_report_columns rc 
--  WHERE
--    rc.consumer_id = @ConsumerId
--  ;
/*
Табличная переменная для колонок отчёта
*/
  DECLARE @consumers_report_columns TABLE ( column_id INT )
  INSERT INTO @consumers_report_columns (column_id)
  SELECT 
    rc.column_id 
  FROM 
    consumers_report_columns rc 
  WHERE
    rc.consumer_id = @ConsumerId
  ;

  /* -=* CYCLE BEGIN *=- */

--  DECLARE consumers_report_columns_cursor CURSOR FOR 
--  SELECT 
--    rc.column_id 
--  FROM 
--    #consumers_report_columns rc
--  ;
/*
Курсор для перебора записей табличной переменной с колонками
*/
  DECLARE consumers_report_columns_cursor CURSOR FOR 
  SELECT 
    rc.column_id 
  FROM 
    @consumers_report_columns rc
  ;
/*
переменная для текущей вычисляемой колонки
*/  
  DECLARE @ColumnId INT ;

  OPEN consumers_report_columns_cursor ;
  
  FETCH NEXT FROM consumers_report_columns_cursor 
  INTO 
      @ColumnId
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  
    PRINT N' @ColumnId  = ' + CAST ( @ColumnId AS NVARCHAR ) ;
/*
Код получения текста формулы конечно надо было выполнить единым запросом с получением колонок отчёта, но на тот момент я не был достаточно уверен в себе, поэтому процесс разбит на элементарные действия.
*/
/*
Получаем "ссылку" на формулу из записи текущей колонки
*/
    DECLARE @FormulaId INT;
    SELECT 
      @FormulaId = cl.formula_id
    FROM 
      columns cl
    WHERE
      cl.id = @ColumnId  ;
    PRINT N' @FormulaId  = ' + CAST ( @FormulaId AS NVARCHAR ) ;
/*
Получаем формулу для вычисления текущей колонки
*/    
    DECLARE @formula NVARCHAR(MAX);
    SELECT 
      @formula = fm.formula 
    FROM 
      formulas fm 
    WHERE
      fm.id = @FormulaId  ;
    PRINT N' @formula  = ' + @formula ;
/*
Текст динамического запроса для вычисления текущей колонки, здесь происходит только выборка данных без сохранения в таблицу
*/  
    DECLARE @column_formula_phrase NVARCHAR(MAX);  
    SET @column_formula_phrase = @C_COLUMN_FORMULA_SELECT + @formula + @C_COLUMN_FORMULA_FROM ;
    PRINT N' @column_formula_phrase  = ' + @column_formula_phrase ;
/*
лишнее присваивание, но мне в отладке так удобней
*/
    DECLARE @column_formula_sql NVARCHAR(MAX);
    SET @column_formula_sql = @column_formula_phrase ;
/*
Вызываем встроенную процедуру для выполнения динамического запроса вычисления колонки, вызов отладочный, в рабочей реализации не нужен, будут отображены результаты вычисления колонки
*/
    EXEC sp_executesql 
        @column_formula_sql
      , @ColumnFormulaParams
      , @Station_Id = @Station
      , @FromDate = @From
      , @ThruDate =  @Thru 
      , @Column_Id = @ColumnId
      , @Instance_Id = @Instance
      , @Consumer_Id = @ConsumerId
/*
формирование рабочего запроса для вычисления колонки и её сохранения в таблицу report_cell_instances
*/
    SET @column_formula_phrase = @C_COLUMN_FORMULA_INSERT + @C_COLUMN_FORMULA_SELECT + @formula + @C_COLUMN_FORMULA_FROM ;
    PRINT N' @column_formula_phrase  = ' + @column_formula_phrase ;
/*
Выполняем динамический запрос для вычисления и сохранения
*/
    SET @column_formula_sql = @column_formula_phrase ;                 
    EXEC sp_executesql 
        @column_formula_sql
      , @ColumnFormulaParams
      , @Station_Id = @Station
      , @FromDate = @From
      , @ThruDate =  @Thru 
      , @Column_Id = @ColumnId
      , @Instance_Id = @Instance
      , @Consumer_Id = @ConsumerId
  
    FETCH NEXT FROM consumers_report_columns_cursor 
    INTO 
        @ColumnId
  END 
  CLOSE consumers_report_columns_cursor;
/*
не уверен что "DEALLOCATE" действительно необходим
*/
  DEALLOCATE consumers_report_columns_cursor;  

  /* -=* CYCLE END *=- */
/*
освобождаем оперативную память
*/
--  DROP TABLE #consumers_report_columns
  DELETE @consumers_report_columns ;
/*
Вычисление колонок завершено
*/
/*
табличная переменная для разделов отчёта
*/
  DECLARE @consumers_report_regions TABLE ( region_id INT )
  INSERT INTO @consumers_report_regions (region_id)
  SELECT 
    rr.region_id 
  FROM 
    consumers_report_regions rr 
  WHERE
    rr.consumer_id = @ConsumerId
  ;
/*
Курсор по разделам отчёта записанным в табличную переменную 
*/
  DECLARE consumers_report_regions_cursor CURSOR FOR 
  SELECT 
    rr.region_id 
  FROM 
    @consumers_report_regions rr
  ;
/*
Часть шаблона для вычисления формулы раздела
*/
  DECLARE @C_REGION_FORMULA_SELECT NVARCHAR(MAX) = N' SELECT @Result = ' ;
/*
Завершающая часть шаблона для вычисления формулы раздела
*/
  DECLARE @C_REGION_FORMULA_FROM NVARCHAR(MAX) = 
N'
FROM 
  meteo_measurements mm 
WHERE 
  mm.meteo_station_id = @Station_Id 
  AND mm.read_timestamp BETWEEN @FromDate AND @ThruDate   
';
/*
Параметры динамического запроса вычисления формулы раздела
@Station_Id - вычисление формулы на данных со станции
@FromDate - вычисление на данных от даты
@ThruDate - вычисление на данных до даты
@Result - результат вычисления формулы
*/
  DECLARE @C_REGION_FORMULA_PARAMS NVARCHAR(MAX) = 
    N' @Station_Id bigint , ' + 
    N' @FromDate datetimeoffset(7) , ' +
    N' @ThruDate datetimeoffset(7) , ' +
    N' @Result NVARCHAR(MAX) OUT '
  ;   
/*Переменная для ключа текущего вычисляемого раздела*/
  DECLARE @RegionId INT ;

  OPEN consumers_report_regions_cursor ;
  
  FETCH NEXT FROM consumers_report_regions_cursor 
  INTO 
      @RegionId
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
  
    PRINT N' @RegionId  = ' + CAST ( @RegionId AS NVARCHAR ) ;
/*
Получаем шаблон раздела
*/
    DECLARE @Pattern NVARCHAR(MAX) ;
    SELECT
      @Pattern = rg.pattern
    FROM
      regions rg
    WHERE
      rg.id = @RegionId
    ;
    PRINT N' @Pattern  = ' + @Pattern ;
/*
Табличная переменная. для формул и их отметок в шаблоне раздела
*/
    DECLARE @region_formulas_and_placeholders TABLE ( formula NVARCHAR(MAX) , placeholder NVARCHAR(MAX) )
/*
Записываем формулы раздела в табличную переменную
*/
    INSERT INTO @region_formulas_and_placeholders ( formula , placeholder )
    SELECT
       fr.formula
      , rf.placeholder
    --  , rg.pattern
    FROM
      regions rg
      JOIN region_formulas rf 
      ON rg.id = rf.region_id
      JOIN formulas fr 
      ON rf.formula_id = fr.id
    WHERE
      rg.id = @RegionId
    ;
/*
Курсор для перебора записей табличной переменной с формулами раздела 
*/
    DECLARE region_formulas_and_placeholders_cursor CURSOR FOR 
    SELECT 
        fp.formula
      , fp.placeholder 
    FROM 
      @region_formulas_and_placeholders fp
    ;
/* переменная для текущей формулы раздела */
    DECLARE @region_formula NVARCHAR(MAX);
/* 
переменная для текущей отметки в шаблоне раздела. места куда надо вставить результат вычисления формулы
*/
    DECLARE @placeholder NVARCHAR(MAX);

    OPEN region_formulas_and_placeholders_cursor ;
    
    FETCH NEXT FROM region_formulas_and_placeholders_cursor 
    INTO 
        @region_formula
      , @placeholder
    
    WHILE @@FETCH_STATUS = 0
    BEGIN

      PRINT N' @region_formula  = ' + @region_formula + N' @placeholder = ' + @placeholder;
/*
Переменная для запроса на вычисление текущей формулы раздела
*/    
      DECLARE @region_formula_phrase NVARCHAR(MAX) ;  
      SET @region_formula_phrase = @C_REGION_FORMULA_SELECT + @region_formula + @C_REGION_FORMULA_FROM ;
      PRINT N' @region_formula_phrase  = ' + @region_formula_phrase ;
      
      DECLARE @region_formula_sql NVARCHAR(MAX) ; 
      SET @region_formula_sql = @region_formula_phrase ;
/*
Переменная для результата вычисления формулы раздела и подстановки в шаблон раздела в соответствии с текущей меткой
*/
      DECLARE @Substitute NVARCHAR(MAX) ; 
/*
Выполняем динамический запрос вычисления поля раздела, результат получаем в @Substitute
*/                   
      EXEC sp_executesql 
          @region_formula_sql
        , @C_REGION_FORMULA_PARAMS
        , @Station_Id = @Station
        , @FromDate = @From
        , @ThruDate =  @Thru
        , @Result = @Substitute OUT
      ;
      PRINT N' @Substitute = ' + @Substitute ;

/* Выполняем замену метки на вычисленное значение */
      SET @Pattern = REPLACE ( @Pattern , @placeholder , @Substitute ) ; 

      FETCH NEXT FROM region_formulas_and_placeholders_cursor 
      INTO 
          @region_formula
        , @placeholder
    END 
    CLOSE region_formulas_and_placeholders_cursor;
    DEALLOCATE region_formulas_and_placeholders_cursor;

/* удаляем из табличной переменной обработанные данные */
    DELETE @region_formulas_and_placeholders ;

    PRINT N' FINISH @Pattern ' + @Pattern ;
/*
Сохраняем вычисленный раздел в таблицу report_region_instances
*/
    INSERT INTO report_region_instances
    ( instace_id ,consumer_id ,region_id ,value )
    VALUES( @Instance , @ConsumerId , @RegionId , @Pattern )    
    ;
  
    FETCH NEXT FROM consumers_report_regions_cursor 
    INTO 
        @RegionId
  END

  CLOSE consumers_report_regions_cursor;
  DEALLOCATE consumers_report_regions_cursor;
/* очищаем табличную переменную - освобождаем оперативную память */
  DELETE @consumers_report_regions ; 
/*
Все колонки вычислены и сохранены. все разделы вычислены и сохранены - мы молодцы :)
*/
END;
</source>

<h4>Тестирование решения</h4>
Тестирование было поверхностным, поведение скрипта при ошибках в данных не проверялось.
<h5>Тестовый набор данных</h5>
Для генерации тестового набора я использовал генератор <b>dbForge Studio</b>.
В таблице <b>meteo_measurements</b>, тип для колонки <b>read_timestamp</b> пришлось поменять с "<b>timestamp</b>" на "<b>datetimeoffset</b>(7)", потому что значение с типом "<b>timestamp</b>" может создать только сервер, в ручную запрещено, а генерация набора данных - в <b>dbForge Studio</b> выполняется в ручном режиме - скриптом с конкретно прописанными операторами "INSERT".
Кроме того значение для колонки "meteo_station_id" пришлось подставлять руками, в смысле допиливать сгенерированный скрипт :
<ol>
	<li>заменить "measurements(read_timestamp," на "measurements(meteo_station_id,read_timestamp,"</li>
	<li>заменить <b>"</b>wind_speed) VALUES ('<b>"</b> на <b>"</b>wind_speed) VALUES ((SELECT TOP 1 id FROM meteo_stations_reference ORDER BY NEWID()),'<b>"</b></li>
</ol>
Тестовый набор пришлось ограничить на 15 000 записей, при генерации скрипта более чем на 16 000 сбивался перенос строк.
<h5>Таблицы с настройками</h5>
Кроме того для тестирования были добавлены записи в другие таблицы. Пара уникальных индексов была изменена, и я уже не помню которые это индексы, поэтому я просто повторю все основные таблицы.
DDL таблиц и DML с вставкой данных есть на GitHub.
<spoiler title="DDL таблиц и DML с вставкой данных">
<source lang="sql">
CREATE TABLE Linegro.dbo.meteo_stations_reference (
  id BIGINT NOT NULL
 ,name NVARCHAR(4000) NOT NULL
 ,description NVARCHAR(MAX) NULL
 ,CONSTRAINT PK_meteo_stations_reference PRIMARY KEY CLUSTERED (id)
 ,CONSTRAINT UK_meteo_stations_reference_name UNIQUE (name)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (1, N'ТАГАНАЙ', N'Метеостанция "Таганай"')
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (2, N'свердловск', N'Метеостанция на Метео горке')
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (3, N'Центральная', N'Москва Центральная')
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (4, N'Владик', N'Морской порт Владивосток')
INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (5, N'Якутия', N'Метеостанция республики Саха - Якутия')
GO
CREATE TABLE Linegro.dbo.meteo_measurements (
  meteo_station_id BIGINT NOT NULL
 ,read_timestamp DATETIMEOFFSET NOT NULL
 ,temperature DECIMAL(4, 1) NULL
 ,pressure INT NULL
 ,wind_direction INT NULL
 ,wind_speed INT NULL
 ,CONSTRAINT PK_meteo_measurements PRIMARY KEY CLUSTERED (meteo_station_id, read_timestamp)
 ,CONSTRAINT FK_meteo_measurements_meteo_stations_reference_id FOREIGN KEY (meteo_station_id) REFERENCES dbo.meteo_stations_reference (id)
) ON [PRIMARY]
GO
CREATE TABLE Linegro.dbo.consumer_reference (
  id INT NOT NULL
 ,name NVARCHAR(4000) NOT NULL
 ,description NVARCHAR(MAX) NULL
 ,CONSTRAINT PK_consumer_reference PRIMARY KEY CLUSTERED (id)
 ,CONSTRAINT UK_consumer_reference_name UNIQUE (name)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (1, N'ИП Наибенько', N'наиби прохожего на тебя похожего')
INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (2, N'МО РФ', N'Министерство обороны Российской Федерации')
INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (3, N'Рота 23', N'Артиллерийский расчёт рота 23')
INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (4, N'426 Горный 2016', N'группа номер 426 ( 2016 ) Горного университета города Екатеринубурга ')
GO
CREATE TABLE Linegro.dbo.formulas (
  id INT NOT NULL
 ,code NCHAR(50) NOT NULL
 ,formula NVARCHAR(MAX) NOT NULL
 ,CONSTRAINT PK_formulas PRIMARY KEY CLUSTERED (id)
 ,CONSTRAINT UK_formulas_code UNIQUE (code)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.formulas(id, code, formula) VALUES (1, N'temperature', N'COALESCE(temperature ,0) AS temperature')
INSERT Linegro.dbo.formulas(id, code, formula) VALUES (2, N'pressure', N'COALESCE(pressure,0) AS pressure')
INSERT Linegro.dbo.formulas(id, code, formula) VALUES (3, N'wind_direction', N'COALESCE(wind_direction,0) AS wind_direction')
INSERT Linegro.dbo.formulas(id, code, formula) VALUES (4, N'wind_speed', N'wind_speed AS wind_speed')
INSERT Linegro.dbo.formulas(id, code, formula) VALUES (5, N'temperature_max', N'MAX(COALESCE(temperature,0)) ')
INSERT Linegro.dbo.formulas(id, code, formula) VALUES (6, N'temperature_min', N'MIN(COALESCE(temperature,0)) ')
INSERT Linegro.dbo.formulas(id, code, formula) VALUES (7, N'temperature_avg', N'AVG(COALESCE(temperature,0)) ')
INSERT Linegro.dbo.formulas(id, code, formula) VALUES (8, N'speed_m_s', N'CAST ( COALESCE(wind_speed  ,0) AS NVARCHAR ) + N'' ( $M_S$ )'' AS speed_m_s')
GO
CREATE TABLE Linegro.dbo.columns (
  id INT NOT NULL
 ,formula_id INT NOT NULL
 ,name NVARCHAR(MAX) NOT NULL
 ,description NVARCHAR(MAX) NULL
 ,CONSTRAINT PK_columns PRIMARY KEY CLUSTERED (id)
 ,CONSTRAINT FK_columns_formulas_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (1, 1, N'температура', N'температура воздуха ( градусов Цельсия )  ')
INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (2, 2, N'давление', N'атмосферное давление ( мм рт ст )')
INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (3, 3, N'ветер', N'направление ветра')
INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (4, 4, N'скорость', N'скорость ветра ( м/с )')
INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (5, 8, N'скорость', N'скорость ветра')
GO
CREATE TABLE Linegro.dbo.regions (
  id INT NOT NULL
 ,pattern NVARCHAR(MAX) NOT NULL
 ,name NVARCHAR(4000) NOT NULL
 ,description NVARCHAR(MAX) NULL
 ,CONSTRAINT PK_regions PRIMARY KEY CLUSTERED (id)
 ,CONSTRAINT UK_regions_name UNIQUE (name)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (1, N'max temp = $MAX_TEMP$ , min temp = $MIN_TEMP$ , average temp = $AVG_TEMP$', N'temp_statistics', N'статистика по температуре')
INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (2, N'группа номер 426 ( 2016 ) Горного университета города Екатеринубурга ', N'426_2016_title', N'шапка')
INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (3, N' ', N'empty', N'пустое поле')
INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (4, N'Министерство обороны Российской Федерации', N'МО РФ', NULL)
INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (5, N'ИП Наибенько', N'ИП Наибенько', NULL)
GO
CREATE TABLE Linegro.dbo.consumers_report_columns (
  column_id INT NOT NULL
 ,consumer_id INT NOT NULL
 ,column_order INT NOT NULL
 ,CONSTRAINT PK_consumers_report_columns PRIMARY KEY CLUSTERED (consumer_id, column_id)
 ,CONSTRAINT UK_consumers_report_columns_column_order UNIQUE (consumer_id, column_order)
 ,CONSTRAINT FK_consumers_report_columns_columns_id FOREIGN KEY (column_id) REFERENCES dbo.columns (id)
 ,CONSTRAINT FK_consumers_report_columns_consumer_reference_id FOREIGN KEY (consumer_id) REFERENCES dbo.consumer_reference (id)
) ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 1, 1)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 2, 1)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (4, 2, 3)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (2, 2, 5)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (1, 2, 14)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 3, 10)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (5, 3, 20)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (2, 4, 11)
INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (1, 4, 22)
GO
CREATE TABLE Linegro.dbo.consumers_report_regions (
  consumer_id INT NOT NULL
 ,region_id INT NOT NULL
 ,region_order INT NOT NULL
 ,type_id INT NULL
 ,CONSTRAINT PK_consumers_report_base PRIMARY KEY CLUSTERED (consumer_id, region_id)
 ,CONSTRAINT UK_consumers_report_regions_region_order UNIQUE (consumer_id, region_order)
 ,CONSTRAINT FK_consumers_report_regions_consumer_reference_id FOREIGN KEY (consumer_id) REFERENCES dbo.consumer_reference (id)
 ,CONSTRAINT FK_consumers_report_regions_regions_id FOREIGN KEY (region_id) REFERENCES dbo.regions (id)
 ,CONSTRAINT FK_consumers_report_regions_report_region_types_id FOREIGN KEY (type_id) REFERENCES dbo.report_region_types (id)
) ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (1, 5, 1, 2)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (2, 1, -1, 4)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (2, 4, 1, 1)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 1, 50, 5)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 3, -100, 3)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 4, 5, 1)
INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (4, 2, 10, 3)
GO
CREATE TABLE Linegro.dbo.region_formulas (
  id INT NOT NULL
 ,formula_id INT NOT NULL
 ,region_id INT NOT NULL
 ,placeholder NVARCHAR(4000) NOT NULL
 ,CONSTRAINT PK_region_formulas PRIMARY KEY CLUSTERED (id)
 ,CONSTRAINT UK_region_formulas UNIQUE (region_id, formula_id)
 ,CONSTRAINT FK_region_formulas_formulas_formula_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id)
 ,CONSTRAINT FK_region_formulas_regions_id FOREIGN KEY (region_id) REFERENCES dbo.regions (id)
) ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (1, 5, 1, N'$MAX_TEMP$')
INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (2, 6, 1, N'$MIN_TEMP$')
INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (3, 7, 1, N'$AVG_TEMP$')
GO
CREATE TABLE Linegro.dbo.report_instace_states_reference (
  id INT NOT NULL
 ,code NCHAR(50) NOT NULL
 ,description NVARCHAR(MAX) NULL
 ,CONSTRAINT PK_report_instace_states_reference PRIMARY KEY CLUSTERED (id)
 ,CONSTRAINT UK_report_instace_states_reference_code UNIQUE (code)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (1, N'формируется', N'формируется')
INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (2, N'готов', N'готов')
INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (3, N'сохранён', N'сохранён')
INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (4, N'удалён', N'удалён')
GO
</source>
</spoiler>
Значения вычисляемых формул "сохраняются" как <b>NVARCHAR</b>(MAX), но шаблоны сохранения результатов не предусматривают преобразования типа - это на совести пользователей и их квалификации.

<h4>Упущенные моменты</h4>
В решении нет обработки таблицы <b>formula_parameters</b>, не выполняется подстановка произвольного значения в формулу.

<h4>Ссылки</h4>
<ol>
	<li> <a href="https://habrahabr.ru/post/278595/">Движок для построения отчётов на SQL. Идея</a>  </li>
	<li> <a href="https://habrahabr.ru/post/220185/">Как заполнить базу данных MS SQL разнородными случайными данными или 17 часов ожидания</a> </li>
	<li> <a href="https://www.devart.com/ru/dbforge/sql/studio/download.html">dbForge Studio for SQL Server</a> </li>
	<li> <a href="http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql">How to request a random row in SQL?</a> </li>
	<li> <a href="http://www.sommarskog.se/dynamic_sql.html">The Curse and Blessings of Dynamic SQL</a> </li>
	<li> <a href="http://www.sqlteam.com/article/temporary-tables">Temporary Tables</a> </li>
</ol>