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