在数据库开发中,对两个关系表进行连接查询,能够直接做“逻辑或”的查询,而对于逻辑与和逻辑非的查询,则稍复杂点,需要编写额外的代码来实现。在关系型数据库中,所谓的连接,实际上是集合的包含,只要包含一项,就满足连接条件,实现的逻辑或,这种设计,能够满足绝大多数的查询需求。有时,对于一条数据,可能需要通过多个逻辑表达式来定性,比如,判定一篇文章是否跟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)
本文列举的逻辑表达式非常简单,鉴于本人的知识和经验有限,难免有纰漏,如果有更好的解决方案,还请不吝告知,十分感谢!