Игра со списком условий

В этой статье я покажу, что и как можно сделать со списком условий. Я сформулирую небольшую тестовую задачу на основе базы AdventureWorks2008R2 и один из вариантов ее решения.

Пример задачи:

Рассчитать стоимость доставки по факту по следующим условиям (обычная задача для логистических компаний).

Список условий:

• Доставка в Берлин и Бонн байков
• Доставка в Берлин и Бонн других товаров
• Доставка в другие города

С одной стороны кажется, что такая задача сложная и любое изменение условий или тарифов требует вмешательства программиста (от чего, в идеале, нужно уйти). В реальности, списки условий, значительно замысловатей и их много.

Для начала нужно определиться со списком таблиц, их алиасами и всех их объединить в один join.

declare @from varchar(1000) = '
     sales.SalesOrderHeader sh  with(nolock)
join sales.SalesOrderDetail sd  with(nolock)  
	on sh.SalesOrderID		= sd.SalesOrderID
join Production.Product	pp  with(nolock)  
	on sd.ProductID			= pp.ProductID
join Production.ProductModel	ppm with(nolock)  
	on pp.ProductModelID		= ppm.ProductModelID
join Production.ProductSubcategory pps with(nolock)  
	on pp.ProductSubcategoryID	= pps.ProductSubcategoryID
join Production.ProductCategory ppc with(nolock)  
	on pps.ProductCategoryID	= ppc.ProductCategoryID
join sales.Customer	 sc  with(nolock)  
	on sh.CustomerID		= sc.CustomerID
join person.[Address] pa  with(nolock)  
	on sh.ShipToAddressID		= pa.AddressID
'

У нас есть два типа условий:

1. Условие для фильтрации обрабатываемого массива записей (ОсновноеУсловие):

declare @basicCondition varchar(1000) = ' sh.ShipDate between @begDate and @endDate
    and sh.[Status] = 5'

2. Набор условий, каждое из которых соответствует одному тарифу (Условие1,…, Условие3):

if OBJECT_ID('tempdb..#Conditions') is not null drop table #Conditions
create table #Conditions (
  ConditionID 	int identity(1,1) primary key,
  Name   		varchar(100),
  [Text] 		varchar(200),
  [Value] 		varchar(200)
)

insert #Conditions(Name, [Text], [Value]) select 'Доставка в Берлин и Бонн байков', 'pa.city in (''Berlin'', ''Bonn'') and ppc.Name in (''Byke'')' , 'sd.OrderQty * pp.Weight'
insert #Conditions(Name, [Text], [Value]) select 'Доставка в Берлин и Бонн других товаров', 'pa.city in (''Berlin'', ''Bonn'') and ppc.Name not in (''Byke'')', 'sd.OrderQty * pp.Weight'
insert #Conditions(Name, [Text], [Value]) select 'Доставка в другие города', 'pa.city not in (''Berlin'', ''Bonn'')', 'sd.OrderQty * pp.Weight'

Имея набор условий можно сделать следующее:

1. Проверить список, на корректность условий (одна запись – одно условие, для сформулированной задачи):

select <КлючевоеПоле>, 
  Errors = iif(<Условие1>,<Название1>,’’)
+ ‘, ‘ + iif(<Условие2>,<Название2>,’’)
+…
from <Секция FROM>
where 
( <ОсновноеУсловие> )
and 
( 1 <> iif(<Условие1>,1,0) + iif(<Условие2>,1,0)+… )

2. Получить стоимость услуги для заданного тарифа:

Select <…>
From <Секция FROM>
Cross apply (
   Select id = <КлючУсловия1>,  price = <Price1>, value = <ФормураРасчета1> where <Условие1>
  Union all
   Select id = <КлючУсловия2>,  price = <Price2>, value = <ФормураРасчета2> where <Условие2>
  ….
) Services
Where <ОсновноеУсловие>

3. Немного не по предложенной задаче, но можно получить ключ самого приоритетного условия для текущей записи, если отсортировать условия по приоритету в обратном порядке:

Select service = case
  When <Условие1> then <КлючУсловия1>
  When <Условие2> then <КлючУсловия2>
  When <Условие3> then <КлючУсловия3>
…
  When 1=1 then null
End, <Другие поля>
From <Секция FROM>
Where <ОсновноеУсловие>

PS. Обратите внимание на условие “when 1=1 then null ’ – я специально добавил это условие, чтоб в CASE всегда было хотя бы одно условие
4. Можно объединить 1 и 2 пункты для наглядной проверки условий.

Как видно, мы имеем достаточно регулярную структуру запроса, которая легко строится динамически. Но при построении и использовании таких запросов нужно учитывать следующее:

• Безопасность – пользователь не должен иметь право править текст условий и текст значений. В следующей статье я расскажу об инструменте пользователя для построения запроса
• При построении динамического запроса проверяйте наличие текста условия и текста формулы. В крайнем случае, вместо пустого условия можно подставить константу отрицательного (1<>1) или положительного (1=1) условия, а вместо значения использовать 0 или NULL.
• Всегда заключайте условия и формулы в скобки. Скобки лишними не бывают.
• Не забывайте, что список условий может быть пустым. Отработайте такую ситуацию
• Методика добавления первого элемента и последующих всегда немного отличается (кроме построения CASE).

А теперь объединим условия, построим динамический запрос и выполним его (все кроме последней строки можно выполнять на любой базе MSSQL, я тестировалл на 2008):

declare @sql varchar(max) 
select @sql =  case when @sql is null then '' else  @sql + char(10)  + ' union all '+char(10) end -- перед первым SELECT-ом UNION ALL не нужен
    + ' select ConditionID = '+convert( varchar(10), ConditionID )+', [Value] = ('+chk.Value+') where  ' + chk.Condition
  from #Conditions
  outer apply ( select -- чуть-чуть разгружу верхнюю строчку, для наглядности
[Condition]  = case when [text]  <> '' then [text]  else '1<>1' end , 
[Value]      = case when [Value] <> '' then [Value] else 'null' end 
			  ) chk

If @sql is null set @sql = ' select ConditionID = null, [Value] = null where 1<>1 '
drop table #Conditions

-- собираем запрос на основе шаблона
declare @template varchar(max) = '
create procedure #exec_calc (@begDate datetime, @endDate datetime )
as begin
  select sh.SalesOrderID, calc.ConditionID, Value = sum( Calc.Value)
    from <FROM> cross apply (<CONDITIONS>) Calc
    where ( <BASIC_CONDITIONS> )
    group by sh.SalesOrderID, calc.conditionID
end'

set @sql = replace(@template, '<CONDITIONS>'     , @sql)
set @sql = replace(@sql     , '<FROM>'           , @from)
set @sql = replace(@sql     , '<BASIC_CONDITIONS>', @basicCondition)

print @sql –- он симпатичный. ))
-- до этого момента код выполнится на любой базе данных
execute( @sql ) -- А вот сам запрос нужно запускать на базе AdventureWorks2008R2
exec #exec_calc ''20071001'', ''20071031'' 

Результаты данного алгоритма:

1. Незначительное время тратится на подготовку запроса: Анализ таблицы условий, построение самого запроса. Все это делается в рамках очень маленьких таблиц.
2. Основное время тратится на расчет тарифов по таблицам с заказами. По этим таблицам все тарифы рассчитываются за один проход
3. Настройка плана выполнения основного запроса должна строится на базе основного условия.