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

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

    More

    Comments 0

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