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

В этой статье я покажу, что и как можно сделать со списком условий. Я сформулирую небольшую тестовую задачу на основе базы 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. Настройка плана выполнения основного запроса должна строится на базе основного условия.

ссылка на оригинал статьи http://habrahabr.ru/post/270363/