Spiel mit einer Liste von Bedingungen

    In diesem Artikel werde ich zeigen, was und wie mit einer Liste von Bedingungen zu tun ist. Ich werde eine kleine Testaufgabe formulieren, die auf der AdventureWorks2008R2-Basis und einer der Optionen zu deren Lösung basiert.

    Beispielaufgabe:

    Berechnen Sie die Lieferkosten anhand der folgenden Bedingungen (eine übliche Aufgabe für Logistikunternehmen).

    Liste der Bedingungen:

    • Lieferung nach Berlin und Bonn Fahrräder
    • Lieferung sonstiger Waren nach Berlin und Bonn
    • Lieferung in andere Städte

    Einerseits scheint eine solche Aufgabe komplex zu sein, und jede Änderung der Bedingungen oder Tarife erfordert das Eingreifen eines Programmierers (was im Idealfall vermieden werden muss). In Wirklichkeit sind Listen von Bedingungen viel komplizierter und es gibt viele von ihnen.

    Zunächst müssen Sie die Liste der Tabellen, deren Aliase und alle zu einem Join zusammengefasst festlegen.

    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
    '
    

    Es gibt zwei Arten von Bedingungen:

    1. Eine Bedingung zum Filtern des verarbeiteten Arrays von Datensätzen (Hauptbedingung):

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

    2. Eine Reihe von Bedingungen, von denen jede einem Tarif entspricht (Bedingung1, ..., Bedingung3):

    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'
    

    Mit einer Reihe von Bedingungen können Sie Folgendes tun:

    1. Überprüfen Sie die Liste auf korrekte Bedingungen (ein Datensatz - eine Bedingung für das formulierte Problem):

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

    2. Erhalten Sie die Kosten der Dienstleistung für einen bestimmten Tarif:

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

    3. Etwas nicht der vorgeschlagenen Aufgabe entsprechend, aber Sie können den Schlüssel der Bedingung mit der höchsten Priorität für den aktuellen Datensatz erhalten, wenn Sie die Bedingungen in umgekehrter Reihenfolge nach Priorität sortieren:

    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. Achten Sie auf die Bedingung „Wenn 1 = 1, dann null“ - Ich habe diese Bedingung speziell hinzugefügt, damit CASE immer mindestens eine Bedingung
    4 hat. Sie können 1 und 2 Punkte kombinieren, um die Bedingungen visuell zu überprüfen.

    Wie Sie sehen, haben wir eine ziemlich regelmäßige Abfragestruktur, die sich leicht dynamisch aufbauen lässt. Bei der Erstellung und Verwendung solcher Abfragen sollte jedoch Folgendes beachtet werden:

    • Sicherheit - Der Benutzer sollte nicht berechtigt sein, den Text der Bedingungen und den Text der Werte zu bearbeiten. Im nächsten Artikel werde ich über das Benutzerprogramm zum Erstellen einer Abfrage sprechen
    • Überprüfen Sie beim Erstellen einer dynamischen Abfrage, ob Bedingungstext und Formeltext vorhanden sind. Als letzte Möglichkeit können Sie die Konstante der negativen (1 <> 1) oder positiven (1 = 1) Bedingung anstelle der leeren Bedingung einsetzen und 0 oder NULL anstelle des Werts verwenden.
    • Schließen Sie Bedingungen und Formeln immer in Klammern ein. Klammern sind nicht überflüssig.
    • Vergessen Sie nicht, dass die Liste der Bedingungen leer sein kann. Übe diese Situation
    • Die Technik des Hinzufügens des ersten und des nächsten Elements ist immer etwas anders (außer beim Erstellen eines CASE).

    Jetzt werden wir die Bedingungen kombinieren, eine dynamische Abfrage erstellen und ausführen (alles außer der letzten Zeile kann auf jeder MSSQL-Basis ausgeführt werden, die ich für 2008 getestet habe):

    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  cross apply () Calc
        where (  )
        group by sh.SalesOrderID, calc.conditionID
    end'
    set @sql = replace(@template, ''     , @sql)
    set @sql = replace(@sql     , ''           , @from)
    set @sql = replace(@sql     , '', @basicCondition)
    print @sql –- он симпатичный. ))
    -- до этого момента код выполнится на любой базе данных
    execute( @sql ) -- А вот сам запрос нужно запускать на базе AdventureWorks2008R2
    exec #exec_calc ''20071001'', ''20071031'' 
    

    Die Ergebnisse dieses Algorithmus:

    1. Es wird wenig Zeit für die Vorbereitung der Anforderung aufgewendet: Analyse der Bedingungstabelle, Erstellung der Anforderung selbst. All dies geschieht an sehr kleinen Tischen.
    2. Die meiste Zeit wird für die Berechnung der Tarife nach Tabellen mit Bestellungen aufgewendet. Nach diesen Tabellen werden alle Tarife in einem Durchgang berechnet.
    3. Das Einrichten des Ausführungsplans für die Hauptanforderung sollte auf der Hauptbedingung basieren.

    Jetzt auch beliebt: