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

    В этой статье я покажу, что и как можно сделать со списком условий. Я сформулирую небольшую тестовую задачу на основе базы 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. Настройка плана выполнения основного запроса должна строится на базе основного условия.
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 0

    Only users with full accounts can post comments. Log in, please.