极简逻辑表达式的设计和查询

在数据库开发中，对两个关系表进行连接查询，能够直接做“逻辑或”的查询，而对于逻辑与和逻辑非的查询，则稍复杂点，需要编写额外的代码来实现。在关系型数据库中，所谓的连接，实际上是集合的包含，只要包含一项，就满足连接条件，实现的逻辑或，这种设计，能够满足绝大多数的查询需求。有时，对于一条数据，可能需要通过多个逻辑表达式来定性，比如，判定一篇文章是否跟Microsoft Azure有关，通常简单的做法是从多个关键字的逻辑组合来定性：文章中同时含有关键字“Microsoft”和“Azure”，或者同时含有关键字“Windows”和“Azure”，把这种逻辑组合抽象成表达式，就是：( Microsoft & Azure ) | ( Windows & Azure )。

逻辑表达式的基本操作符是：与（&），或（|）和非（!），逻辑表达式的最小组合是：A&B，A|B 和 !A。关系型数据库的开发人员，在设计逻辑表达式时，必须保证满足业务需求，同时，尽可能支持多种逻辑组合，通常情况下，按照表达式的关系，我们把逻辑表达式拆分成四个元数据类型：Expression（表达式），SubExpression（子表达式），Operator（操作符）和Operand（操作数）。

在我目前接触的项目中，业务需求的逻辑表达式的组合相对简单，标准的表达式格式如下所示：

Expression = ((A & B) | (C & D)) & !(E | F)

该逻辑表达式表示：查询语句返回的结果集中，不能包含E和F，但是，必须包含A和B，或者包含C且不包含D。

对于该类表达式，我们可以抽象成更为通用的逻辑公式是：

Expression= (SubExpression1 | SubExpression2 | ...) & !ExcludeExpression
SubExpression=Operand1 & Operand2 & ...
ExcludeExpression=Operand1 | Operand1 | ...

这种高度概括的表达式蕴含的逻辑关系是：SubExpression中操作数之间的关系是逻辑与，ExcludeExpression中操作数之间的关系是逻辑或，该表达式蕴含的意思是：任意一条数据，不能够包括ExcludeExpression中的任何一个项目，但是，必须至少满足一个子表达式（SubExpression），子表达式中的项目都是逻辑与的关系。

一，用关系表存储逻辑表达式

举个例子，为了描述方便，我们用下面的逻辑表达式来说明：

Expression= ((A & B & C) | (C & D)) & !(E | F)

该表达式可以拆分成三个子表达式：A & B & C，C & D， ！(E | F)，对于子表达式 （A & B & C，C & D），其操作数之间的关系都是逻辑与；而对于子表达式！(E | F)，其操作数之间的关系是逻辑或。

1，通过XML文档表示逻辑表达式

逻辑表达式的子表达式之间，不是无关系的，为了在不同的应用程序间传递逻辑表达式，数据结构必须包含该表达式的所有关系和操作数，XML文档特别适合表达有特定关系的数据结构，按照之前拆分的四种元数据类型，我们把XML文档定义为三种不同的标签，分别是<Expression>，<SubExpression>和<Operand>，并为标签设置不同的属性，通过格式化的XML实现逻辑表达式的转存，示例如下：

declare @xml xml
set @xml='
<Expression ID="1">
  <SubExpression ID="1" OperandType="Tag" Operator="and">
    <Operand ID="1" />
    <Operand ID="2" />
    <Operand ID="3" />
  </SubExpression>
  <SubExpression ID="2" OperandType="Tag" Operator="and">
    <Operand ID="4" />
    <Operand ID="5" />
  </SubExpression>
  <SubExpression ID="3" OperandType="Tag" Operator="not">
    <Operand ID="6"  />
    <Operand ID="7"  />
  </SubExpression>
</Expression>'

2，把逻辑表达式存储到关系表

XML格式的文档适合数据的传递，不适合直接存储在关系型数据库中，并且XML格式的文档也不利于对数据执行查询操作，因此，必须把XML文档存储到数据表中，以利用关系数据库引擎执行集合操作的优势，实现数据的快速查询。当应用程序接收到这个XML文档，必须把XML蕴含的表达式拆分成：表达式（Expression），子表达式（SubExpression），操作数（Operand）和操作符（Operator），例如，我们可以利用以下脚本创建数据表：

create table dbo.Operands
(
    ExpressionID bigint not null,    
    SubExpressionID smallint not null,
    OperandID bigint not null,          --EntityID, TagID
    Operator varchar(8) not null,       --&,!
    OperandType varchar(8) not null,    --Entity,Tag
    primary key clustered(ExpressionID,SubExpressionID,OperandID)
)
with(data_compression=page);
go

注：OperandType是操作数的类型，分为：Entity和Tag，相应的操作数ID（OperandID）就是EntityID和TagID，本例只使用TagID，EntityID没有使用。

SQL Server内置函数用于解析XML文档，通过XML解析函数，我们可以利用TSQL脚本把XML文档插入到表Operands中。这意味着，在表Operands中，当Operator列值为not时，表示逻辑非，表示OperandID是<ExcludeExpression>标签存储的操作数；当Operator列值为and时，表示逻辑与，表示OperandID是<SubExpression>标签存储的操作数。

;with cte_Expressions as 
(
    select e.v.query('.') as Expression
        ,e.v.value('@ID','int') as ExpressionID
    from @xml.nodes('/Expression') as e(v)
)
,cte_SubExpression as 
(
    select e.ExpressionID
        ,se.SubExpression
        ,se.SubExpressionID
        ,se.OperandType
        ,se.Operator
    from cte_Expressions e
    cross apply
    (
        select t.v.query('.') as SubExpression
            ,t.v.value('@ID','int') as SubExpressionID
            ,t.v.value('@OperandType','varchar(16)') as OperandType
            ,t.v.value('@Operator','varchar(16)') as Operator
        from e.Expression.nodes('/Expression/SubExpression') as t(v)
    ) as se
)
--insert into dbo.Operands
select p.ExpressionID
    ,p.SubExpressionID
    ,d.OperandID
    ,p.Operator
    ,p.OperandType
from cte_SubExpression p
cross apply
(
    select t.v.value('@ID','int') as OperandID
        ,t.v.value('@Exclude','int') as Exclude
    from p.SubExpression.nodes('/SubExpression/Operand') as t(v)
) as d

二，使用TSQL实现逻辑与和逻辑非

数据表包含的数据是Tag和Data之间的映射关系，如果Data包含的Tag满足逻辑表达式，那么就认为Data和逻辑表达式（Expression）之间存在映射关系，也就是说，Data满足Expression。

例如，有以下数据表DataTable，该表只有两列DataID和TagID，表示Data具有相应的Tag：

create table dbo.DataTable 
(
    DataID bigint not null,
    TagID bigint not null,
)
go

insert into dbo.DataTable
(DataID,TagID)
values
(1,1)
,(1,2)
,(1,3)
,(1,7)
,(2,4)
,(2,5)
,(3,1)
,(3,2)
,(3,3)
,(4,1)
,(4,3)
,(6,6)
go

1，逻辑非的实现

逻辑非的含义是：在DataTable中，一个DataID不能包含任意一个ExcludeExpression中的TagID。

;with cte_exclude_data
as
(
    select distinct 
        dt.DataID
    from dbo.DataTable dt
    inner join dbo.Operands o
        on dt.TagID=o.OperandID
    where Operator='not'
)
select dt.DataID
    ,dt.TagID
from dbo.DataTable dt
left join cte_exclude_data ed
    on dt.DataID=ed.DataID
where ed.DataID is null

2，逻辑与的实现

 逻辑与的含义是：在DataTable中，一个DataID必须包含SubExpression中的所有Tag。

;with cte_operands as 
(
    select o.ExpressionID
        ,o.SubExpressionID
        ,o.OperandID
        ,count(0) over(partition by o.ExpressionID,o.SubExpressionID) as Operands
    from dbo.Operands o
    where o.Operator='and'
)
select o.ExpressionID
    ,o.SubExpressionID
    ,o.Operands
    ,dt.DataID
from cte_operands o
inner join dbo.DataTable dt
    on o.OperandID=dt.TagID
group by o.ExpressionID
    ,o.SubExpressionID
    ,o.Operands
    ,dt.DataID
having count(distinct dt.TagID)=o.Operands

3，查询脚本示例 

把逻辑与和逻辑非的代码合并到一起，就能实现表达式蕴含的逻辑，这里给出一个示例脚本：

;with cte_operands as 
(
    select o.ExpressionID 
        ,o.SubExpressionID
        ,o.OperandID
        ,count(o.OperandID) over(partition by o.ExpressionID,o.SubExpressionID) as Operands
    from dbo.Operands o
    where Operator='and'
)
,cte_exclude_data as 
(
    select distinct 
        d.DataID
    from dbo.DataTable d
    inner join dbo.Operands o
        on d.TagID=o.OperandID 
    where o.Operator='not'
)
,cte_data as 
(
    select d.DataID
        ,d.TagID
    from dbo.DataTable d
    left join cte_exclude_data ed
        on d.DataID=ed.DataID
    where ed.DataID is null
)
select o.ExpressionID
    ,o.SubExpressionID
    ,dt.DataID
from cte_operands o
inner join cte_data dt
    on o.OperandID=dt.TagID
group by o.ExpressionID
    ,o.SubExpressionID
    ,dt.DataID
    ,o.Operands
having count(distinct dt.TagID)=o.Operands

在我参与的项目中，这种设计（Design）模式已经用于产品环境，能够满足业务查询的需求，但是查询速度不是很理想，有待优化。

 

附上较复杂的逻辑表达式，该格式能够表达的逻辑组合更为丰富，后续我会分享深入探索的随笔：

Expression = ((A & B) | (C & ! D)) & !(E | F)

本文列举的逻辑表达式非常简单，鉴于本人的知识和经验有限，难免有纰漏，如果有更好的解决方案，还请不吝告知，十分感谢！